Bulkcopy excel data to sql using c# application

c# excel sqlbulkcopy

Question

I'm attempting to mass transfer data from Excel to SQL, but neither the data nor the program copy or raise an error. Please let me know what I'm doing incorrectly.

This is a portion 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();

        }
1
0
7/25/2018 6:02:48 AM

Accepted Answer

The Help page for SqlBulkCopy has a comprehensive example for you.

In accordance with that, I have eliminated the loop and the sqlconn.close() line.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();

    }
0
7/25/2018 7:13:05 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow