ADO. NET and SQL Server: Batch addition and modification of data to databases

How to batch insert multiple data entries into a database data table?

Use sqlbulkcopy, for example:

 /// <Summary> ///  Batch import data to the database  /// </Summary> /// <Param name = "table">  Data source table  </Param> /// <Param name = "tablename">  Database Table Name  </Param> /// <Param name = "connstr">  Database connection string  </Param> /// <Param name = "mappings"> Ing between source table and database table columns  </Param>  Public static void  Bulkcopydatatable (  Datatable  Table,  String  Tablename,  String  Connstr,  Params  Sqlbulkcopycolumnmapping [] Mappings ){  // Use sqlbulkcopy to import data to the database in batches  Using  (  Sqlbulkcopy  Bulkcopy =  New  Sqlbulkcopy  (Connstr )){  // Write the name of the target table in the database  Bulkcopy. destinationtablename = tablename; // Create a ing between the data source and the database, that is, to let sqlbulkcopy know which column you want to insert to which column  Foreach  (  Sqlbulkcopycolumnmapping  Mapping  In  Mappings ){  // You can also use bulkcopy. columnmappings. Add ("data source column name", "data table column name ");  Bulkcopy. columnmappings. Add (Mapping );}  // Submit the data to the database Bulkcopy. writetoserver (table );}} 

How does one batch update multiple data entries in a database table?

Use sqldataadapter, for example:

 // Establish a database connection  Using  (  Sqlconnection  Conn =  New  Sqlconnection  (Connstr) {conn. open ();  // Create a new sqldataadapter. Because it is used only for updates, the Select SQL statement is not entered during instantiation. Using  (  Sqldataadapter  Adapter =  New  Sqldataadapter  (  ""  , Conn )){  // Write the SQL statement required for update  String  Sqlstr = "Update tablename set needupdate = @ newvalue where guid = @ guid"  ;  // Create an updatecommand assigned to the adapter by sqlcommand  Adapter. updatecommand =  New  Sqlcommand  (Sqlstr, Conn );  // Add column ing to updatecommand. The parameter meanings are: // SQL statement parameter name, field data type, and field length (I do not know what this means, char or something) and data source table column name  Adapter. updatecommand. Parameters. Add (  "@ Newvalue" ,  Sqldbtype  . Bit, 1,  "Newvalue"  ); Adapter. updatecommand. Parameters. Add (  "@ Guid"  ,  Sqldbtype  . Uniqueidentifier, 1,  "Guid"  );  // Set updatebatchsize to none if it is not set to 1. Adapter. updatecommand. updatedrowsource =  Updaterowsource  . None;  // Set the number of rows for each batch processing to the server  Adapter. updatebatchsize = 0;  // Update the table. The table is a data source table, and the data in the table can comply with the Mappings of the preceding parameters.  Adapter. Update (table );}} 

Batch delete operations may be similar to modifications. At present, there will be no more experiments, and you will have the opportunity to record them later.