CODEWITHSUNDEEP
c#sqlsqlbulkcopy
disasterkid
disasterkid

Reputation: 7278

bulkCopy.WriteToServer() not throwing exception when no data is copied

I'm trying to copy a DataTable into a destination table on Sql Server. I use the following code. I know that bulkCopy.WriteToServer() is an atomic (all-or-nothing) function. But when nothing has been copied, I would like to know! 

try
{
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
    {
        bulkCopy.DestinationTableName = "MyDestinationTable";
        bulkCopy.WriteToServer(dt);
        convertSuccess = true;
    }
}
catch
{
    convertSuccess = false;
}

I have seen the case that nothing was copied into "MyDestinationTable" but I was unaware of it because my convertSuccess flag returned true. Is there a way to know if the bulkCopy has done its job?

Upvotes: 0

Views: 2695

Answers (1)

Ramie
Ramie

Reputation: 1201

You can specify your NotifyAfter property and then handle the SqlRowsCopied event after x amount of rows.

static void Main() {
    try {
        using(SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction)) {
            //Event handling
            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
            bulkCopy.NotifyAfter = 50; //Put your rowcount here

            bulkCopy.DestinationTableName = "MyDestinationTable";
            bulkCopy.WriteToServer(dt);
            convertSuccess = true;
        }
    } catch {
        convertSuccess = false;
    }
}


private static void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) {
    Console.WriteLine("Copied {0} so far...", e.RowsCopied);
}

Upvotes: 1

Related Questions