Bulkcopy excel data to sql using c# application

c# excel sqlbulkcopy

Question

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();

        }

Accepted Answer

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();

    }


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why