C # add, delete, and modify winform DataTable batch data processing .,

C # add, delete, and modify winform DataTable batch data processing .,

1. Batch addition, using efficient SqlBulkCopy

 SqlBulkCopy DTS = new System.Data.SqlClient.SqlBulkCopy(con);                DTS.NotifyAfter = 1;                DTS.DestinationTableName = datatable.name;                DTS.BulkCopyTimeout = 60000000;                DTS.WriteToServer(datatable);                succ = dtSource.Rows.Count.ToString();

2. SqlCommandBuilder update batch modification Problems

1 DataSet ds = new DataSet (); 2 ds. tables. add (table); 3 string _ tableName = table. tableName; 4 int result = 0; 5 using (SqlConnection sqlconn = new SqlConnection (db. connectionString) 6 {7 sqlconn. open (); 8 9 // use the enhanced read/write lock transaction 10 SqlTransaction tran = sqlconn. beginTransaction (IsolationLevel. readCommitted); 11 try 12 {13 14 ds. tables [0]. acceptChanges (); 15 foreach (DataRow dr in ds. tables [0]. rows) 16 {17 // all rows are set to change status 18 dr. setModified (); 19} 20 // locate the target table 21 22 SqlCommand cmd = new SqlCommand (string. format ("select * from {0} where {1}", _ tableName, "1 = 2"), sqlconn, tran); 23 SqlDataAdapter da = new SqlDataAdapter (cmd ); 24 SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder (da); 25 sqlCmdBuilder. conflictOption = ConflictOption. overwriteChanges; 26 da. acceptChangesDuringUpdate = false; 27 string columnsUpdateSql = ""; 28 SqlParameter [] paras = new SqlParameter [table. columns. count]; 29 int parasIndex = 0; 30 // The column setting parameter to be updated is, the parameter name is "@ + column name" 31 for (int I = 0; I <table. columns. count; I ++) 32 {33 // The column name to be updated and Its Parameter Value 34 columnsUpdateSql + = ("[" + table. columns [I]. columnName + "]" + "= @" + table. columns [I]. columnName + ","); 35 if (table. columns [I]. dataType. name = "DateTime") 36 {37 paras [I] = new SqlParameter ("@" + table. columns [I]. columnName, SqlDbType. dateTime, 23, table. columns [I]. columnName); 38} 39 else if (table. columns [I]. dataType. name = "Int64") 40 {41 paras [I] = new SqlParameter ("@" + table. columns [I]. columnName, SqlDbType. NVarChar, 19, table. columns [I]. columnName); 42} 43 else 44 {45 paras [I] = new SqlParameter ("@" + table. columns [I]. columnName, SqlDbType. NVarChar, 2000, table. Columns [I]. ColumnName); 46} 47} 48 if (! String. isNullOrEmpty (columnsUpdateSql) 49 {50 // remove the last join here "," 51 columnsUpdateSql = columnsUpdateSql. remove (columnsUpdateSql. length-1); 52} 53 // where Condition Statement 54 string limitSql = ("[" + table. columns [0]. columnName + "]" + "= @" + table. columns [0]. columnName); 55 SqlCommand updateCmd = new SqlCommand (string. format ("UPDATE [{0}] SET {1} WHERE {2}", _ tableName, columnsUpdateSql, limitSql); 56 // do not modify the source DataTable 57 updateCmd. updatedRowSource = UpdateRowSource. none; 58 da. updateCommand = updateCmd; 59 da. updateCommand. parameters. addRange (paras); 60 // da. updateCommand. parameters. add ("@" + table. columns [0]. columnName, table. columns [0]. columnName); 61 // The number of rows processed each round-trip is 62 da. updateBatchSize = table. rows. count; 63 result = da. update (ds, _ tableName); 64 ds. acceptChanges (); 65 tran. commit (); 66 67} 68 catch (Exception ex) 69 {70 tran. rollback (); 71 throw ex; 72} 73 finally 74 {75 sqlconn. dispose (); 76 sqlconn. close (); 77} 78}

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