利用SqlBulkCopy插入数据

时间:2022-02-07 02:26:22

bulk方法主要思想是通过在客户端把数据都缓存在Table中,然后利用SqlBulkCopy一次性把Table中的数据插入到数据库

代码如下:

public static void BulkToDB(DataTable dt)

 { 

  SqlConnection sqlConn = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString); 
  SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
  bulkCopy.DestinationTableName = "BulkTestTable";
  bulkCopy.BatchSize = dt.Rows.Count;
  try {
    sqlConn.Open();
    if (dt != null && dt.Rows.Count != 0) bulkCopy.WriteToServer(dt);
  }
  catch (Exception ex)
  {
    throw ex;
  } finally
  {
    sqlConn.Close();
    if (bulkCopy != null) bulkCopy.Close();
  }
}
public static DataTable GetTableSchema()
{
  DataTable dt = new DataTable();
  dt.Columns.AddRange(new DataColumn[]
    { new DataColumn("Id",typeof(int)), new DataColumn("UserName",typeof(string)), new DataColumn("Pwd",typeof(string))}
  );
  return dt;
}
static void Main(string[] args)
{
  Stopwatch sw = new Stopwatch();
  for (int multiply = 0; multiply < 10; multiply++)
  {
    DataTable dt = Bulk.GetTableSchema();
    for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
    {
      DataRow r = dt.NewRow();
      r[0] = count;
      r[1] = string.Format("User-{0}", count * multiply);
      r[2] = string.Format("Pwd-{0}", count * multiply);
      dt.Rows.Add(r);
    }
   sw.Start();
    Bulk.BulkToDB(dt);
    sw.Stop();
    Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
  }
  Console.ReadLine();
}