using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 批量插入更新数据
{class Program
{static void Main(string[] args)
{DateTime dt1 = System.DateTime.Now;
string connStr = @"server=SHEN\SQL2012;uid=sa;pwd=123456;database=LmhsDB";
using (SqlConnection con = new SqlConnection(connStr))
{con.Open();
SqlTransaction tran = con.BeginTransaction();
try
{//批量插入
Inserts(con, tran);
Updates(con, tran);
tran.Commit();
}catch
{tran.Rollback();
}}DateTime dt2 = System.DateTime.Now;
TimeSpan ts = dt2.Subtract(dt1);
Console.WriteLine("耗时:" + Convert.ToDouble(ts.TotalMilliseconds / 1000) + "秒");
Console.ReadLine();
}
public static void Inserts(SqlConnection con, SqlTransaction tran)
{DataTable dtIns = new DataTable();
//首先查出一张空表 作为模版（注意主键自增问题：SqlBulkCopyOptions.KeepNulls）
SqlCommand sqlIns = new SqlCommand("select ID,UserName,Gender,CreateTime "
+ " FROM InsertTest where 1=0 ", con, tran);
SqlDataAdapter sdaIns = new SqlDataAdapter();
sdaIns.SelectCommand = sqlIns;
sdaIns.Fill(dtIns);
//插入1000条
for (int i = 0; i < 1000; i++)
{DataRow dataRow = dtIns.NewRow();
dataRow[0] = 0;
dataRow[1] = "我叫" + Guid.NewGuid();
dataRow[2] = "男";
dataRow[3] = DateTime.Now;
dtIns.Rows.Add(dataRow);
}using (SqlBulkCopy sqlBulkIns = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepNulls, tran))