C# 批量新增的两种方法。
public class Test { private static readonly string strConnection = ""; public static void BulktoDb(DataTable dt) { //1、SqlBulkCopy try { using (SqlConnection sqlConn = new SqlConnection(strConnection)) { using (TransactionScope tran = new TransactionScope()) { sqlConn.Open(); //1、SqlBulkCopy using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn)) { //服务器上目标表的名称 bulkCopy.DestinationTableName = "数据库表名"; bulkCopy.BatchSize = dt.Rows.Count; bulkCopy.BulkCopyTimeout = 600; for (int i = 0; i < dt.Columns.Count; i++) { //映射数据源中的列和目标表中的列之间的关系 bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } bulkCopy.WriteToServer(dt); tran.Complete();//事物提交 } } } } catch (Exception ex) { throw new Exception(ex.Message); } //2、表值参数,sql2008+ //2-1 数据库代码 CREATE TYPE TestType(数据库表值参数名称) AS TABLE(Id int NOT NULL,Name nvarchar(20) NOT NULL) //2-2 const string sql = "insert into UserInfo (Id,UserName,Pwd)" + " SELECT nc.Id, nc.UserName,nc.Pwd" + " FROM @NewBulkTestTvp AS nc"; SqlConnection sqlConn2 = new SqlConnection(strConnection); SqlCommand cmd = new SqlCommand(sql, sqlConn2); SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt); catParam.SqlDbType = SqlDbType.Structured; catParam.TypeName = "数据库表值参数名称"; try { sqlConn2.Open(); if (dt.Rows.Count != 0) { cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (sqlConn2 != null) { if (sqlConn2.State == ConnectionState.Open) { sqlConn2.Close(); } sqlConn2.Dispose(); } if (cmd != null) cmd.Dispose(); } } }