SharePointCafe.Net

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 !