Popular Posts

There was an error in this gadget

Oct 7, 2013

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;
    }


No comments:

Post a Comment