C # WinForm DataTable Batch Data processing Add, delete, change.

1. Batch new, with efficient sqlbulkcopy

New System.Data.SqlClient.SqlBulkCopy (con);                 1 ;                 = datatable.name;                 60000000 ;                Dts. WriteToServer (DataTable);                 = DtSource.Rows.Count.ToString ();

2.SqlCommandBuilder Update Batch modification issues

1DataSet ds =NewDataSet (); 2 ds.  Tables.add (table); 3             string_tablename =table.  TableName; 4             intresult =0; 5            using(SqlConnection sqlconn =NewSqlConnection (db. ConnectionString))6             {  7 sqlconn.  Open (); 8  9                 //using enhanced read-write lock TransactionsTenSqlTransaction Tran =sqlconn.  BeginTransaction (isolationlevel.readcommitted);  One                 Try   A                 {   -    -Ds. tables[0].  AcceptChanges ();  the                     foreach(DataRow DrinchDs. tables[0]. Rows) -                     {   -                        //all rows are set to modify State - Dr.  SetModified ();  +                    }   -                    //locating the target table for adapter +   ASqlCommand cmd =NewSqlCommand (string. Format ("select * FROM {0} where {1}", _tablename,"1=2"), sqlconn, Tran);  atSqlDataAdapter da =NewSqlDataAdapter (CMD);  -SqlCommandBuilder Sqlcmdbuilder =NewSqlCommandBuilder (DA);  -Sqlcmdbuilder.conflictoption =conflictoption.overwritechanges;  -Da. AcceptChangesDuringUpdate =false;  -                    stringColumnsupdatesql ="";  -Sqlparameter[] Paras =Newsqlparameter[table.  Columns.count];  in                    intParasindex =0;  -                     //The column setting parameter that needs to be updated is the parameter named "@+ column name" to                      for(inti =0; I < table. Columns.count; i++)   +                    {   -                        //stitching the column names and their parameter values to be updated here theColumnsupdatesql + = ("["+ table. Columns[i]. ColumnName +"]"+"[email protected]"+ table. Columns[i]. ColumnName +",");  *                         if(table. Columns[i]. Datatype.name = ="DateTime")   $                        {  Panax NotoginsengParas[i] =NewSqlParameter ("@"+ table. Columns[i]. ColumnName, Sqldbtype.datetime, at, table. Columns[i].  ColumnName);  -                         }   the                        Else if(table. Columns[i]. Datatype.name = ="Int64")   +                        {   AParas[i] =NewSqlParameter ("@"+ table. Columns[i]. ColumnName, SqlDbType.NVarChar, +, table. Columns[i].  ColumnName);  the                        }   +                        Else   -                        {   $Paras[i] =NewSqlParameter ("@"+ table. Columns[i]. ColumnName, SqlDbType.NVarChar, -, table. Columns[i].  ColumnName);  $                        }   -                     }   -                    if(!string. IsNullOrEmpty (columnsupdatesql)) the                     {   -                        //This is where the last "," of the stitching is removed .WuyiColumnsupdatesql = Columnsupdatesql.remove (Columnsupdatesql.length-1);  the                     }   -                     //The where condition statement is generated here Wu                    stringLimitsql = ("["+ table. columns[0]. ColumnName +"]"+"[email protected]"+ table. columns[0].  ColumnName);  -SqlCommand Updatecmd =NewSqlCommand (string. Format ("UPDATE [{0}] SET {1} WHERE {2}", _tablename, Columnsupdatesql, Limitsql));  About                     //do not modify the source DataTable $Updatecmd.updatedrowsource =Updaterowsource.none;  -Da. UpdateCommand =Updatecmd;  - da.  UpdateCommand.Parameters.AddRange (paras);  -                     //da. UPDATECOMMAND.PARAMETERS.ADD ("@" + table. Columns[0]. ColumnName, table. Columns[0].   ColumnName);  A                     //number of rows processed per round trip +Da. UpdateBatchSize =table.  Rows.Count;  theresult =da.  Update (ds, _tablename);  - ds.  AcceptChanges ();  $ Tran.commit ();  the    the                 }   the                Catch(Exception ex) the                 {   - Tran.  Rollback ();  in                    Throwex;  the                }   the                 finally   About                {   the sqlconn.  Dispose ();  the sqlconn.  Close ();  the                }   +}

Loading http://blog.csdn.net/liudong8510/article/details/17000997

C # WinForm DataTable Batch Data processing Add, delete, change.