Bulk copy data from excel sheet to sql table

How to process bulk copy data from excel sheet to sql table. Here is code to copy data from excel to sql.

      C# Code:
       string excelFile = @"C:\Book1.xls";
        string ssqlTable = "[MyTableName]";
        string exceldataQuery = "select [id],[email],[status] from [Sheet1$]";
        try
        {
            string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelFile +
            ";extended properties=" + "\"excel 8.0;hdr=yes;\"";
            string ssqlconnectionstring = "Data Source=dbserver;Initial Catalog=Practice;User ID=sa;Password=@password;connection timeout=54000;Max Pool Size=300;Asynchronous Processing=true;";
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(exceldataQuery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
            bulkcopy.DestinationTableName = ssqlTable;
            while (dr.Read())
            {
                bulkcopy.WriteToServer(dr);
            }

            oledbconn.Close();
            Response.Write("Completed");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

Enjoy !

Comments

Popular

Top 8 C# 6.0 Features

SharePoint Interview Questions and Answers

Calling ASP.Net WebMethod using jQuery AJAX

What is Cloud Computing - A guide for beginners