because of the work of the project needs to settle a batch of the same batch number of data inserted into a table, and then through another page to show, so you need to insert a batch of data at once, so the use of SqlBulkCopy insert a batch of data
1 Public Static intInsertsettlementrecord (SqlConnection connection, list<inventorysettlement>model)2 {3DataTable dt =GetTableSchema ();4SqlBulkCopy bulkcopy =NewSqlBulkCopy (connection);//Instantiate SqlBulkCopy object5Bulkcopy.destinationtablename ="Tuhu_shop.dbo.InventorySettlement";//table name of the table to be inserted6Bulkcopy.batchsize =dt. Rows.Count;7 stringsql ="SELECT MAX (Batchno) as Batchno from Tuhu_shop.dbo.InventorySettlement with (NOLOCK)";8 varMaxbatchno = sqlhelper.executedatatable (connection, CommandType.Text, SQL). Convertto<inventorysettlement>(). ToList ();//Gets the maximum batch number in the current table, and each additional batch of data is added 1 on the current batch .9 int? Maxbatch =0;Ten if(maxbatchno[0]. Batchno = =NULL)//Judgment on batch number One { AMaxbatch =0; - } - Else the { -Maxbatch = maxbatchno[0]. batchno;//Assigning a batch number to a value - } - + foreach(varIteminchmodel)//loop inserting data into the DataTable - { +DataRow r =dt. NewRow (); Ar[0] =item. Shopid; atr[1] =item. PID; -r[2] =item. PName; -r[3] =item. Settlementcount; -r[4] =item. Settlementprice; -r[5] =item. Summoney; -r[6] =item. Settlementstatus; inr[7] = maxbatch+1; -r[8] =DateTime.Now; tor[9] =item. Settlementor; + dt. Rows.Add (r); - the } * foreach(DataColumn DCinchdt. Columns) $ {Panax Notoginseng BulkCopy.ColumnMappings.Add (DC. ColumnName, DC. ColumnName);//Make columns correspond, this code is very important, self-test less this sentence will insert failure - } the + if(dt! =NULL&& dt. Rows.Count! =0) A { the bulkcopy.writetoserver (DT);//INSERT INTO database table by SqlBulkCopy once + return 1; - } $ Else $ { - return 0; - } the } - Wuyi Use the SqlBulkCopy class to write data to a SQL Server table only. However, the data source is not limited to SQL Server; You can use any data source, as long as the data can be loaded into a DataTable instance or you can use a IDataReader instance to read the data. theFrom //www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html> - Public StaticDataTable GetTableSchema () Wu { -DataTable dt =NewDataTable (); AboutDt. Columns.addrange (Newdatacolumn[]{ $ //new DataColumn ("PKID", typeof (int)), - NewDataColumn ("Shopid",typeof(int)), - NewDataColumn ("PID",typeof(string)), - NewDataColumn ("PName",typeof(string)), A NewDataColumn ("Settlementcount",typeof(int)), + NewDataColumn ("Settlementprice",typeof(decimal)), the NewDataColumn ("Summoney",typeof(decimal)), - NewDataColumn ("Settlementstatus",typeof(BOOL)), $ NewDataColumn ("Batchno",typeof(int)), the NewDataColumn ("Createdatetime",typeof(DateTime)), the NewDataColumn ("Settlementor",typeof(string)) the }); the - returnDT; in}
Bulk insert multiple data into a table using SqlBulkCopy