大批量数据<快速插入数据库>、数据库之间复制

最新推荐文章于 2022-07-29 11:13:53 发布

chentai3508

最新推荐文章于 2022-07-29 11:13:53 发布

阅读量121

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/RudyPiano/blog/289772

版权

public static void BulkToDB(DataTable dt)
{
   SqlConnection sqlConn = new SqlConnection(
   ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
   SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
   //设置要批量写入的表
   bulkCopy.DestinationTableName = "TargetTable";

    //得到目标表的列名
   string sql = "select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='BulkTestTable')";
   DataModelControl.ModelControl model = new DataModelControl.ModelControl();
   DataTable HeadDT = model.GetDatatable(sql);
   for(int i=0;i<HeadDT.Rows.Count;i++)
   {
         bulkCopy.ColumnMappings.Add(dt.Columns.ColumnName, HeadDT.Rows[0].ToString().Trim());
   }

   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();
}