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.