Donaghy

Hi All,

I am trying to import data from an Excel spreadsheet into a SQL server db table though having some issues. This is the code I'm using:

private void Button1_Click(object sender, System.EventArgs e)
{
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
//OleDbCommand command = new OleDbCommand ("Select ID,Data FROM [Data$]", connection);
connection.Open();
OleDbCommand command =
new OleDbCommand ("Select ID,Data FROM [Data$]", connection);
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.; Initial Catalog=Test;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
connection.Close();
}
}

With this code I get several errors, the main two being DbDataReader & SqlBulkCopy. The error mentions that I may be missing an assembly reference though I am using the correct references according to my search on msdn. These are my assembly refs:

using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.Common;

Anyone any idea whats wrong here or if there is a better way to do this taks please let me know.

Thanks.



Re: Visual C# General Import Excel Spreadsheet Data into SQL Server Database Table

Ashish Basran

What are the errors you are getting while trying executing your code






Re: Visual C# General Import Excel Spreadsheet Data into SQL Server Database Table

Donaghy

Ok fixed the error with DbDataReader, though still getting the error with SQLBulkCopy.

This is the error i am getting:

Cannot implicitly convert type '<error>' to 'System.IDisposable'





Re: Visual C# General Import Excel Spreadsheet Data into SQL Server Database Table

Ashish Basran

try adding one more assembly ref

using System.Data;






Re: Visual C# General Import Excel Spreadsheet Data into SQL Server Database Table

Donaghy

Added System.Data but still no joy.

The SqlBulkCopy section appears to be the only issue now.





Re: Visual C# General Import Excel Spreadsheet Data into SQL Server Database Table

Ashish Basran

have you tried it executing without using block






Re: Visual C# General Import Excel Spreadsheet Data into SQL Server Database Table

Donaghy

I'm not too sure what you mean by 'Block'



Re: Visual C# General Import Excel Spreadsheet Data into SQL Server Database Table

Ashish Basran

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}

I mean, writing above code without using "using".

Following is using block.

using(....)
{

}