Debugging SqlBulkCopy problems when it doesn't return any errors

Question

Right now my problem with sqlBulkCopy is that it finishes without any errors but when i look into the table i can't see any data.

The try catch block catches no errors and am i missing something? Because i don't see any problems with my code

This is the code i use:

DataTable table = new DataTable();
table.Columns.Add(new DataColumn("Buying ID", typeof(string)));
table.Columns.Add(new DataColumn("Buying BPC ID", typeof(string)));
table.Columns.Add(new DataColumn("Buying BP Name", typeof(string)));
table.Columns.Add(new DataColumn("Buying BP Post Code", typeof(string)));
... snip ...
table.Columns.Add(new DataColumn("Buyer BP Type", typeof(string)));
table.Columns.Add(new DataColumn("Seller BP Type", typeof(string)));
table.Columns.Add(new DataColumn("FileSource", typeof(string)));
table.Columns.Add(new DataColumn("ImportDate", typeof(System.DateTime)));

while ( (line = sr.ReadLine()) != null)
{   
    string[] data = line.Split('\t');
    DataRow row = table.NewRow();
    for (int i=0; i<data.Length; i++)
    {
        row[i] = data[i];
    }
    row["FileSource"] = fi.Name;
    row["ImportDate"] = DateTime.Now;
}

try
{
    db.Connection.Open();
    SqlBulkCopy bulkcopy = new SqlBulkCopy(db.Connection);
    bulkcopy.BatchSize = 500;
    bulkcopy.BulkCopyTimeout = 600;
    bulkcopy.DestinationTableName = "dbo.LNST_test";
    foreach (var col in table.Columns)
    {
        bulkcopy.ColumnMappings.Add(col.ToString(), col.ToString()); //col names in datatable are identical to col names in database
    }
    bulkcopy.WriteToServer(table);
    db.Connection.Close();
}
catch (Exception e)
{
    Console.WriteLine("ERROR : "+e.Message);
}

Either transaction, or no rows in the table, or you're looking at the wrong table in SSMS. — usr
– usr, Commented Dec 13, 2012 at 20:03
database is MSSQL 2008, i am connecting to it as administrator (it's only a test database) and noone else. So i don't think it's a problem with transactions :( — Gabriel
– Gabriel, Commented Dec 13, 2012 at 20:04
I just did some work with bulk copy and had no problems .. +1 to see what the outcome is...please update if you find solution — ohigho_state
– ohigho_state, Commented Dec 13, 2012 at 20:05
of course i will post a update if i find a solution, it's very weird that i get no errors but no data appears in database — Gabriel
– Gabriel, Commented Dec 13, 2012 at 20:07

Gabriel · Accepted Answer · 2012-12-13 20:15:32Z

2

Oh god i'm stupid... i forgot this one line: table.Rows.Add(row);

I tried to insert an empty datatable. This is why i love stackoverflow, even if i don't get the answer from this avesome community, it helps me to solve my problems :D

answered Dec 13, 2012 at 20:15

Gabriel

1,8382 gold badges25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

usr Over a year ago

I actually was suspicious if NewRow was enough but I'm not familiar with data tables :) Glad you found it.

Gabriel Over a year ago

yes this is the first time i use data tables and from other places i'm got used to the thing, when you call NewRow() function it creates a new row and add it to the datatable and returns a pointer to the row so i can update the row :)

Gabriel Over a year ago

@ohigho_state of course, this was just a stupid error on my side :) but i will leave this question here when somebody makes the same error so he can figure it out :D

Collectives™ on Stack Overflow

Debugging SqlBulkCopy problems when it doesn't return any errors

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related