I am trying to bulkcopy excel data to SQL but the data doesn't copy neither does the program throw an error. Help me point out what am doing wrong.
Here is a snippet of my code
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel Files|*.xls;*.xlsx";
openFileDialog.ShowDialog();
if (!string.IsNullOrEmpty(openFileDialog.FileName))
{
string ssqltable = "Student";
string myexceldataquery = "select Name,DOB,Email from [Sheet1$]";
try
{
//Excel connection string
string Econn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog.FileName + ";Extended Properties=Excel 12.0;";
//Sql connection string
string Sconn = "Data Source=.;Initial Catalog=db_Test;Integrated Security=True";
SqlConnection sqlconn = new SqlConnection(Sconn);
sqlconn.Open();
sqlconn.Close();
OleDbConnection oledbconn = new OleDbConnection(Econn);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
SqlBulkCopy bulkcopy = new SqlBulkCopy(Sconn);
bulkcopy.DestinationTableName = ssqltable;
while (dr.Read())
{
bulkcopy.WriteToServer(dr);
}
dr.Close();
oledbconn.Close();
}
You have a complete example in the SqlBulkCopy help page
Acording with that, I have removed the sqlconn.close() line and the loop while (dr.Read())
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel Files|*.xls;*.xlsx";
openFileDialog.ShowDialog();
if (!string.IsNullOrEmpty(openFileDialog.FileName))
{
string ssqltable = "Student";
string myexceldataquery = "select Name,DOB,Email from [Sheet1$]";
try
{
//Excel connection string
string Econn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog.FileName + ";Extended Properties=Excel 12.0;";
//Sql connection string
string Sconn = "Data Source=.;Initial Catalog=db_Test;Integrated Security=True";
using (SqlConnection sqlconn = new SqlConnection(Sconn))
{
sqlconn.Open();
//sqlconn.Close();
OleDbConnection oledbconn = new OleDbConnection(Econn);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
SqlBulkCopy bulkcopy = new SqlBulkCopy(Sconn);
bulkcopy.DestinationTableName = ssqltable;
bulkcopy.WriteToServer(dr);
}
}
finally
{
dr.Close();
oledbconn.Close();
}