Why Doesn't My SqlBulkCopy Work?

asp.net c# sqlbulkcopy sql-server-2005


I am using SqlBulkCopy object to write a datatable into an sql server table. However, everytime I recheck my database it remains intact with no changes. A

I have tried to do Google search to determine my problem but i am unable to resolve it.

The datatable came from an .xls file.

public static DataTable dt = new DataTable();

private void ExportToGrid(String path, String filen)
        int idx = filen.IndexOf(".");
        string tf = filen.Remove(idx, 4);

        OleDbConnection MyConnection = null;
        DataSet DtSet = null;
        OleDbDataAdapter MyCommand = null;
        MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");

        ArrayList TblName = new ArrayList();

        DataTable schemaTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        foreach (DataRow row in schemaTable.Rows)

        MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + TblName[0].ToString() + "]", MyConnection);
        DtSet = new System.Data.DataSet();

        MyCommand.FillSchema(DtSet, SchemaType.Source);

        DataTable dt = new DataTable();
        dt = DtSet.Tables[0];
        Session["dt"] = dt;
        int x = dt.Rows.Count;

        if (dt.Rows.Count > 0)
            theGridView.DataSource = dt;

        if (System.IO.File.Exists(path))


This is my writer function

private void StartImport()
        string servername = server;
        string database = database;
        string tbl = "dbo.LinkDb";

        Stopwatch sw = new Stopwatch();
        SqlBulkCopy bulkCopy = new SqlBulkCopy("Data Source=" + servername + ";Initial Catalog=" + database + ";Integrated Security=SSPI", SqlBulkCopyOptions.TableLock);
        bulkCopy.DestinationTableName = tbl;
        lblResult.Visible = true;
        lblResult.Text = (sw.ElapsedMilliseconds / 1000.00).ToString();

Below are the screenshot of the tables stored in my sql server. I assure you that I have been complying to Case Sensitive rules.


There was no exception thrown and average time elapsed is 0.018 - 0.020 secs

Appreciate any helps.

Thank you

2/8/2012 7:32:52 AM

Accepted Answer

Based on the code you have posted, you are writing an empty datatable to the database. Your "ExportToGrid" method fills dt, a DataTable declared locally, which loses scope outside of the method. Your write function is calling the static DataTable dt which is a new datatable.

Does dt need to be static? it seems as though this could be declared as

private DataTable dt;

then inside "ExportToGrid" instead of declaring another DataTable just instantiate the already declared dt rather than declaring a new one

dt = new DataTable();

Alternatively you could extract the DataTable straight from the GridView during the write Method:

DataTable dt = (DataTable)theGridView.DataSource;

This removes the need for variables outside of the scope of the method.

Lastly since you are storing your datatable within the session (I am not generally an advocate of storing large amounts of data in session variables but without knowing the specifics of your site I cannot really pass judgement), you could use the following:

DataTable dt = (DataTable)Session["dt"];
2/8/2012 2:52:58 PM

Popular Answer

I dont see anything obvious compared to my use except for the fact that I explicitly map columns from the data table to the database table.

Using cn As New SqlConnection(DataAccessResource.CONNECTIONSTRING)
    Using copy As New SqlBulkCopy(cn)
        copy.BulkCopyTimeout = 300
        copy.ColumnMappings.Add(0, 0)
        copy.ColumnMappings.Add(1, 1)
        copy.ColumnMappings.Add(2, 2)
        copy.ColumnMappings.Add(3, 3)
        copy.DestinationTableName = "Tablename"
    End Using
End Using

Connection string (sql server 2000!) looks like

"data source=DBSERVERNAME;initial catalog=DBNAME;persist security info=True;user id=USERNAME;password=PASSWORD;packet size=4096"

I doubt the connection string is a problem assuming youve used it elsewhere.

Finally have you checked the data types for the columns in the dataset datatable match the ones in the database. In my experience oledb load from excel does not always produce output you might expect, date fields and columns with mixed text and numbers being perticular problems.

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow