Sqlbulkcopy for Batch Data Replication (zz)

Recently, sqlbulkcopy is used in the project to implement batch replication.CodeIt is helpful for you to study and use sqlbulkcopy. It is a screening Code and is not a complete method. Please try not to copy it directly.ArticleTo help you understand sqlbulkcopy.

/** **************** Call sqlbulkcopy to implement batch copy of datatable to SQL ************* *****/
If (Sqlconn. State = Connectionstate. Closed)
{
Sqlconn. open ();
}

// Define SQL transactions and embed them into batch copy.
Sqltransaction objsqltran = Sqlconn. begintransaction ();

// Define sqlbulkcopy: sqlconn is sqlconnection, sqlbulkcopyoptions Enumeration type, and objsqltran is the transaction called
Sqlbulkcopy objsqlcopy = New Sqlbulkcopy (sqlconn, sqlbulkcopyoptions. keepidentity, objsqltran );

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

// This step is important because the source table maps the fields in the target table in order and by name.
Objsqlcopy. columnmappings. Add ( " Projectdefid " , " Projectdefid " );
Objsqlcopy. columnmappings. Add ( " Num " , " Sumnum " );
Objsqlcopy. columnmappings. Add ( " Money " , " Summoney " );
Objsqlcopy. columnmappings. Add ( " Explain " , " Explain " );

Try
{
//Copy an objdt of the Abel type to the target table as the source.
Objsqlcopy. writetoserver (objdt );
Objsqltran. Commit ();
}
Catch
{
Objsqltran. rollback ();
}
Finally
{
Objsqlcopy. Close ();
Sqlconn. Close ();
}

Return Objdt;

 

Note: // The source table maps the fields in the target table. This step is important because it is matched by order and name by default.
Objsqlcopy. columnmappings. Add ("projectdefid", "projectdefid ");
Objsqlcopy. columnmappings. Add ("num", "sumnum ");
Objsqlcopy. columnmappings. Add ("money", "summoney ");
Objsqlcopy. columnmappings. Add ("Explain", "Explain ");

Note that the source table field is in the front and the target table field is in the back. by default, the structure of the two datasets must be exactly the same. However, in actual use, this code is especially important.

Objsqlcopy. writetoserver (objdt );

Objdt is the previously processed able in our project. writetoserver () supports datatable, datareader, and datarow [] operations. You can use it flexibly.