.net 大数据量的批量插入

1. 使用Insert循环插入（每次一条）

2. 使用BulkCopy写入；

   1. 1,创建一个DataTable dt
      
      2,using (SqlBulkCopy sbc = new SqlBulkCopy(目标库连接字符串))//目标库的链接字符串
                  {
                      sbc.BulkCopyTimeout = 600;
                      sbc.BatchSize = dt.Rows.Count;
                      sbc.DestinationTableName = "目标表名";//目标表
                      sbc.WriteToServer(dt);
                  }
      }

   2. 第二种写法
                  SqlDataReader reader =commandSourceData.ExecuteReader();           
      
                  using (SqlConnection destinationConnection = new SqlConnection(connectionString))
                  {
                      destinationConnection.Open();
      
                      using (SqlBulkCopy bulkCopy =new SqlBulkCopy(destinationConnection))
                      {
                          bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns";                    
                          try
                          {                       
                              bulkCopy.WriteToServer(reader);
                          }                    catch (Exception ex)
                          {
                              Console.WriteLine(ex.Message);
                          }                    finally
                          {                        // Close the SqlDataReader. The SqlBulkCopy
                              // object is automatically closed at the end
                              // of the using block.
                              reader.Close();
                          }
                      }               
                      long countEnd = System.Convert.ToInt32(
                          commandRowCount.ExecuteScalar());
                      Console.WriteLine("Ending row count = {0}", countEnd);
                      Console.WriteLine("{0} rows were added.", countEnd - countStart);
                      Console.WriteLine("Press Enter to finish.");
                      Console.ReadLine();
                  }
              }

3. 使用表值参数写入。

   1.      SqlParameter[] paramters = new SqlParameter[]
                           {
                               SqlParamHelper.MakeInParam("@dt",SqlDbType.Structured)
                          };
                      DataSet ds = SqlHelper.ExecuteDataset(ComputingDB_ConnString, CommandType.StoredProcedure, "存储过程名", paramters);
      
                      string[] sqls = new string[]
                      {
                          @" insert into table1
                            select * from @dt
                          ",
                     };
      
                      using (SqlConnection connection = new SqlConnection(StatDB_ConnString))
                      {
                          connection.Open();
                          SqlTransaction trans = connection.BeginTransaction();
                          try
                          {
                              string[] typeNames = new string[] { "表类型名" };
                              for (int i = 0; i < sqls.Length; i++)
                              {
                                  paramters[0].Value = ds.Tables[i];
                                  paramters[0].TypeName = typeNames[i];
                                  SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqls[i], paramters);
                              };
                              trans.Commit();
                          }
                          catch (Exception ex)
                          {
                              trans.Rollback();
                              throw;
                          }
                      }

总结：Insert比较适合于少量数据的添加，如果是大批量的数据，只能考虑使用BulkCopy或表值参数方式，后俩者相比于前者会有一个量级的提升，随着数据量的提升这个差别会越来越大