Read and insert bulk of data from excel to database in one shot.

Hi friends,
Thought I will share a small utility which will be useful for reading from excel and insert into database. A simple tool which reads from excel using the microsoft.interop.excel library. And then whole data is inserted into a DataSet. From which the records and inserted into the database with a single DataAdapter.Update() call. Simple, easy and fast way of inserting records to sql database.

Below is a piece of code to read from excel.

using Microsoft.Office.Interop.Excel;

 Application ExcelObj = new Application();

                Workbook theWorkbook = ExcelObj.Workbooks.Open(@"filenamepath", Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                                         Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                                         Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                                         Type.Missing, Type.Missing);

                Sheets sheets = theWorkbook.Worksheets;

                Worksheet worksheet = (Worksheet)sheets.get_Item(1);

DataSet empDataSet = new DataSet();
            string sqlConnectionString = @"Data Source=machine\SQLEXPRESS;Initial Catalog=Sample;Trusted_Connection=True;";
            SqlConnection newConnection = new SqlConnection(sqlConnectionString);           
            SqlDataAdapter newAdapter = new SqlDataAdapter("Select * from Employee", newConnection);
            SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(newAdapter);
            newAdapter.Fill(empDataSet, "Employee");
            BuildDataSet(strArray, ref empDataSet);
            newAdapter.Update(empDataSet, "Employee");