有时候要做测试,数据库插入3000W条数据

写了个代码模板,下次调用起来就方便了  


int total = 10000000;
          int segementCount = 200000; 
          for (int n = 1; n <= total / segementCount; n  )
          {
              DataTable dt = new DataTable();
              dt.Columns.AddRange(new DataColumn[]{  
                      new DataColumn("TwitterId",typeof(long)),  
                      new DataColumn("TagName",typeof(string)),});
              for (int index = (n - 1) * segementCount; index < segementCount * n; index  )
              {
                  var dataRow = dt.NewRow();
                  dataRow["TwitterId"] = (long)index;
                  dataRow["TagName"] = "标签"   (index   10) % 100000;
                  dt.Rows.Add(dataRow);
                  var dataRow2 = dt.NewRow();
                  dataRow2["TwitterId"] = (long)index;
                  dataRow2["TagName"] = "标签"   (index   20) % 100000;
                  dt.Rows.Add(dataRow2);
                  var dataRow3 = dt.NewRow();
                  dataRow3["TwitterId"] = (long)index;
                  dataRow3["TagName"] = "标签"   (index   30) % 100000;
                  dt.Rows.Add(dataRow3);
              }
              Inert2DBBySqlBulkCopy(dt, "TestTagName");
              Console.WriteLine("insert"   segementCount * n * 3 / 10000   "w");
          }
 
 
  public static void Inert2DBBySqlBulkCopy(DataTable data, string tableName)
      {
          using (SqlConnection conn = new SqlConnection(ConnectionString))
          {
              if (conn.State != ConnectionState.Open)
                  conn.Open();
              using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn))
              {
                  sqlBulkCopy.ColumnMappings.Add(0, "TwitterId");
                  sqlBulkCopy.ColumnMappings.Add(1, "TagName"); 
                  sqlBulkCopy.BatchSize = data.Rows.Count; 
                  sqlBulkCopy.BulkCopyTimeout = 600; 
                  sqlBulkCopy.DestinationTableName = tableName; 
                  sqlBulkCopy.WriteToServer(data); 
              }
              if (conn.State != ConnectionState.Closed)
                  conn.Close();
          }
      }




还是挺快的,插入3000W条数据大概10分钟


Leave a Reply