CODEWITHSUNDEEP
c#sql-server
ashishduh
ashishduh

Reputation: 6699

SqlBulkCopy not copying any data

Here's the schema for TABLE1:

CREATE TABLE [dbo].[TABLE1] 
(
    [id] [int] IDENTITY(1, 1) NOT NULL,
    [code] [int] NOT NULL,
    [description] [varchar](256) NOT NULL,
    [value] [numeric](18,2) NOT NULL
)

Here's my SqlBulkCopy code:

DataTable data = new DataTable();
data.Columns.Add("code", typeof(int));
data.Columns.Add("description", typeof(string));
data.Columns.Add("value", typeof(decimal));
foreach (Item item in items)
{
    data.Rows.Add(item.Code, item.Description, item.Value);
}

SqlConnection sqlConn = new SqlConnection(_connstr);
sqlConn.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.TableLock, sqlConn.BeginTransaction());
bulkCopy.DestinationTableName = "TABLE1";
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn col in dt.Columns)
{
    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
}

bulkCopy.WriteToServer(dt);
sqlConn.Close();

When debugging through it, everything looks good but after the WriteToServer function has been called, no data is added to the database. I've tried not resetting the column mappings but that gives error due to IDENTITY column. Not sure what else could be wrong.

Upvotes: 2

Views: 2940

Answers (1)

jason
jason

Reputation: 241583

It looks like you're never committing the transaction. You need to store a reference to the transaction that you started, and commit it:

var transaction = sqlConn.BeginTransaction();
SqlBulkCopy bulkCopy = new SqlBulkCopy(
    sqlConn,
    SqlBulkCopyOptions.TableLock, 
    transaction
);

and then

transaction.Commit();

after the call to execute the bulk copy and before you close the connection.

Upvotes: 8

Related Questions