Optimization of C#sqlbulkcopy

Recently in the optimization of the project. The copy time of some data is now too long. A reasonable numerical relationship between the number of batches and the amount of copies needed to be optimized before going live.

Recent projects used in the SqlBulkCopy implementation of bulk replication, here, I have some code to filter out a simple explanation, I hope that you study and use of sqlbulkcopy helpful. Because it is the filter code, not the complete method, please try not to copy the use directly, The purpose of this article is to help you understand sqlbulkcopy simply.

/******************* calls SqlBulkCopy to implement a DataTable-to-SQL bulk copy ******************/
if (sqlconn.state = = connectionstate.closed)
{
Sqlconn.open ();
}

Define SQL transactions and embed them in the work of a bulk copy
SqlTransaction Objsqltran = Sqlconn.begintransaction ();

Defines sqlbulkcopy:sqlconn as the Sqlconnection,sqlbulkcopyoptions enumeration type, Objsqltran for the invoked transaction
SqlBulkCopy objsqlcopy = new SqlBulkCopy (sqlconn, sqlbulkcopyoptions.keepidentity, Objsqltran);

Number of rows copied in batches
Objsqlcopy.batchsize = 10;
Objsqlcopy.bulkcopytimeout = 240;
Target table name
Objsqlcopy.destinationtablename = "Detailaccountreport";

The source table maps to the target table fields because the default is in order and the name matches, so this step is important
OBJSQLCOPY.COLUMNMAPPINGS.ADD ("Projectdefid", "Projectdefid");
OBJSQLCOPY.COLUMNMAPPINGS.ADD ("Num", "Sumnum");
OBJSQLCOPY.COLUMNMAPPINGS.ADD ("Money", "Summoney");
OBJSQLCOPY.COLUMNMAPPINGS.ADD ("Explain", "Explain");

Try
{
Copy the Objdt of the Datatabel type as the source to the target table
Objsqlcopy.writetoserver (OBJDT);
Objsqltran.commit ();
}
Catch
{
Objsqltran.rollback ();
}
Finally
{
Objsqlcopy.close ();
Sqlconn.close ();
}

return OBJDT;

Description://The source table is mapped to the target table field because the default is in order and the name matches, so this step is important
OBJSQLCOPY.COLUMNMAPPINGS.ADD ("Projectdefid", "Projectdefid");
OBJSQLCOPY.COLUMNMAPPINGS.ADD ("Num", "Sumnum");
OBJSQLCOPY.COLUMNMAPPINGS.ADD ("Money", "Summoney");
OBJSQLCOPY.COLUMNMAPPINGS.ADD ("Explain", "Explain");

Notice here that the front is the source table field, followed by the target table field. The default is in order and name matching, so the structure of the two datasets is exactly the same, but in real-world use there are very few identical cases, so this code is especially important.

Objsqlcopy.writetoserver (OBJDT);

OBJDT is our project in the previously processed DataTable, WriteToServer () support for datatable,datareader,datarow[] operation, we can use flexibly.

Optimization of C#sqlbulkcopy