Store Excel Data To DataTable And Copy To SQLServer Database using SqlBulkCopy

This method stores the data to datatable and shows to GridView.

private void Import_To_Grid(string FilePath, string Extension, string isHDR)

{

        string conStr="";

        switch (Extension)

        {

            case ".xls": //Excel 97-03

                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;

                break;

            case ".xlsx": //Excel 07

                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;

                break;

        }

        conStr = String.Format(conStr, FilePath, isHDR);

        OleDbConnection connExcel = new OleDbConnection(conStr);

        OleDbCommand cmdExcel = new OleDbCommand();

        OleDbDataAdapter oda = new OleDbDataAdapter();

        DataTable dt = new DataTable();

        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet

        connExcel.Open();

        DataTable dtExcelSchema;

        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

        connExcel.Close();

        //Read Data from First Sheet

        connExcel.Open();

        //Bind Data to GridView

        GridView1.Caption = Path.GetFileName(FilePath);

        GridView1.DataSource = dt;

        GridView1.DataBind();

        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

        oda.SelectCommand = cmdExcel;

        oda.Fill(dt);

        connExcel.Close();

        BulkInsertDataTable(“databaseTable”, dt);

 }

This method insert the data from datatable to sqlserver.

public bool BulkInsertDataTable(string tableName, DataTable dataTable)

    {

        bool isSuccuss;

        string connString = ConfigurationManager.ConnectionStrings["db_connection_string"].ConnectionString;

        SqlConnection SqlConnectionObj = new SqlConnection(connString);

       

        try

        {  

            SqlConnectionObj.Open();

SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionObj, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);

            bulkCopy.DestinationTableName = tableName;

            bulkCopy.WriteToServer(dataTable);

            isSuccuss = true;

        }

        catch (Exception ex)

        {

            isSuccuss = false;

        }

        finally

        {

            SqlConnectionObj.Close();

        }

        return isSuccuss;

    }