SqlDataAdapter update insert with Insertbulkcopy

Recently done projects, involving multiple databases of multiple tables of association updates, because of the huge amount of data, one-to-update is also very time-consuming. So I want to use SqlDataAdapter once to submit a batch of data past. Here is the pit of your own experience:

1. Table Merge Section

DataTable Dtcbbill =Dbhelper.executedataadapter (Sqlhelper.cbbill, pars, strconnoldcbbill); DataTable Dtmember= Dbhelper.executedataadapter (Sqlhelper.accounts_m,NULL, Strconnmember); DataTable Dtuser= Dbhelper.executedataadapter (SQLHELPER.ACCOUNTS_U2,NULL, Strconnweb); //filter out users with billingDataTable DtM = ( fromCinchdtmember.asenumerable () join RinchDtcbbill.asenumerable () on c["f_accounts"] Equals r["f_accounts"]                   Selectc). CopyToDataTable (); DataTable DtU= ( fromCinchdtuser.asenumerable () join RinchDtcbbill.asenumerable () on c["f_accounts"] Equals r["f_accounts"]                   Selectc). CopyToDataTable (); Set the primary key to speed up the merge speed SetPrimaryKey (Dtcbbill); SetPrimaryKey (DtM); SetPrimaryKey (DtU); Mergetable (DtM, Dtcbbill); Mergetable (DtU, Dtcbbill);
。。。。 Dtcontribution

Merge hints <target>.f_cbmoney and <source>.f_cbmoney attribute conflicts: DataType property mismatch. After debugging, it is found that a field of the source Dtcbbill is a decimal, and the corresponding merge destination table dtcontribution the corresponding field is the int type. Modify the SQL statement, cast (a field as int) to resolve this type of exception. Online has said

In the call DT1. Merge (DT2), because two ServerID field types are inconsistent, one int32, one int64, resulting in the inability to merge. It can be combined in a importrow way:

Private DataTable Mergetable (DataTable dest, DataTable Source) {      = source. Select ();        for (int0; i < sourcerows.length; i++)      {          dest. ImportRow (Sourcerows[i]);       }        return dest;}

This method requires a lot of code changes, and there is no attempt.
2. Insertbulkcopy section

 Public Static intInsertbulkcopy (stringConnectionString, DataTable DT,stringdesttable) {    using(SqlConnection Connection =NewSqlConnection (connectionString))        {Connection.Open (); using(SqlTransaction transaction =connection.begintransaction ()) {            using(SqlBulkCopy SqlBulkCopy =NewSqlBulkCopy ((SqlConnection) Connection, sqlbulkcopyoptions.keepidentity, transaction)) {SqlBulkCopy. DestinationTableName=desttable;  for(inti =0; i < dt. Columns.count; i++) {SqlBulkCopy. Columnmappings.add (dt. Columns[i]. ColumnName, dt. Columns[i].                ColumnName); } sqlbulkcopy. BatchSize=10000; Try{sqlbulkcopy.                    WriteToServer (DT);                    Transaction.commit (); return 1; }                 Catch(Exception ex) {transaction.                    Rollback (); return 0; }              }        }    }}

You must ensure that the source table is exactly the same as the destination table field, including the case of the field. Because the casing is not the same, such as F_employee and F_employee, the insert will fail directly.

3. The most important SqlDataAdapter

SqlDataAdapter adapter =NewSqlDataAdapter (); SqlConnection Conn=NewSqlConnection (Strconncash); adapter. SelectCommand=NewSqlCommand (SQLHELPER.SELCMDTEXT, conn); SqlParameter Parameter1= Adapter. SELECTCOMMAND.PARAMETERS.ADD ("@f_date", Sqldbtype.datetime);p arameter1. Value=date;datatable Dtcontri=NewDataTable (); adapter. Fill (Dtcontri); adapter. UpdateBatchSize= +; adapter. UpdateCommand=NewSqlCommand (SQLHELPER.UPDCMDTEXT, conn); adapter. UPDATECOMMAND.PARAMETERS.ADD ("@f_CBMoney", SqlDbType.Int,4,"F_cbmoney"); adapter. UPDATECOMMAND.PARAMETERS.ADD ("@f_CBMresult", SqlDbType.Int,4,"F_cbmresult"); adapter. UPDATECOMMAND.PARAMETERS.ADD ("@f_CBNum", SqlDbType.Int,4,"F_cbnum"); Sqlparameter[] Paramters=Newsqlparameter[]{NewSqlParameter ("@f_date", Sqldbtype.datetime,8,"f_date"),       NewSqlParameter ("@f_accounts", SqlDbType.VarChar, -,"f_accounts")};p aramters[0]. SourceVersion =datarowversion.original;paramters[1]. SourceVersion =datarowversion.original; Adapter. UpdateCommand.Parameters.AddRange (paramters); adapter. Updatecommand.updatedrowsource=Updaterowsource.none;adapter. InsertCommand=NewSqlCommand (SQLHELPER.INSCMDTEXT, conn); adapter. INSERTCOMMAND.PARAMETERS.ADD ("@f_accounts", SqlDbType.VarChar, -,"f_accounts"); adapter. INSERTCOMMAND.PARAMETERS.ADD ("@f_date", Sqldbtype.datetime,4,"f_date"); adapter. INSERTCOMMAND.PARAMETERS.ADD ("@f_CBMoney", SqlDbType.Int,4,"F_cbmoney"); adapter. INSERTCOMMAND.PARAMETERS.ADD ("@f_CBMresult", SqlDbType.Int,4,"F_cbmresult"); adapter. INSERTCOMMAND.PARAMETERS.ADD ("@f_CBNum", SqlDbType.Int,4,"F_cbnum"); Adapter. INSERTCOMMAND.PARAMETERS.ADD ("@f_StupeSurplus", SqlDbType.Int,4,"F_stupesurplus"); adapter. INSERTCOMMAND.PARAMETERS.ADD ("@f_status", Sqldbtype.tinyint,1,"F_status"); adapter. Insertcommand.updatedrowsource=Updaterowsource.none;//synchronizing DS data to the Dtcontri tableSetPrimaryKey (Dtcontri); Mergetablemanual (Dtcbbill,refDtcontri); //Perform an update insertintR =adapter. Update (Dtcontri); log. Info (Site+"CB Update&insert:"+ R);

/// <summary>///Set Primary Key/// </summary>/// <param name= "DT" ></param>Private voidSetPrimaryKey (DataTable dt) {dt. PrimaryKey=NewDatacolumn[] {dt. columns["f_accounts"] };}/// <summary>///Merging table Data/// </summary>/// <param name= "source" ></param>/// <param name= "target" ></param>Private voidMergetable (DataTable Source, DataTable Target,BOOLIsaddschema =true) {target. Merge (Source,false, Isaddschema?MissingSchemaAction.Add:MissingSchemaAction.Ignore);}/// <summary>///Manually merging table data/// </summary>/// <param name= "source" ></param>/// <param name= "target" ></param>Private voidMergetablemanual (DataTable source,refDataTable Target) {   foreach(DataRow Iteminchsource. Rows) {varrow = target. Rows.find (item["f_accounts"]); if(Row! =NULL) {row["F_cbmoney"] = item["F_cbmoney"]; row["F_cbmresult"] = item["F_cbmresult"]; row["F_cbnum"] = item["F_cbnum"]; }       Else       {          varNewRow =Target.          NewRow (); newrow["f_accounts"] = item["f_accounts"]; newrow["f_date"] = item["f_date"]; newrow["F_cbmoney"] = item["F_cbmoney"]; newrow["F_cbmresult"] = item["F_cbmresult"]; newrow["F_cbnum"] = item["F_cbnum"]; newrow["F_stupesurplus"] = item["F_stupesurplus"]; newrow["F_status"] = item["F_status"]; Target.        Rows.Add (NewRow); }    } }

This one pits the most.  According to Https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters This most valuable article finds that the number of log records updates is always 0. Then I do a demo, 1.1-point open research, datatable.savechanges just the source table updates to the offline table, not available. But offline table, take one of the DataRow, change the next data, even the original data can be updated to update successfully, finally by printing RowState property value, get, with Datatable1.merge (DataTable2,...) Merged data, RowState to unchanged. The manual merge is accomplished. Run, OK. It's finally done.

SqlDataAdapter update insert with Insertbulkcopy