SqlBulkCopy

SqlBulkCopy是.net提供的一种将大批量数据插入到数据库中的方法..

若当面对十万级数据量以上的时候 使用单纯的SQL语句的响应速度已经远不能满足需求了

这时  一般考虑使用 SqlBulkCopy来进行优化

使用条件:  目标数据库为Sql Server(这事只能问MS了)...

核心语句：

private string desConStr =System.Configuration.ConfigurationManager.ConnectionStrings["des_connstr"].ConnectionString;//目标数据源SqlBulkCopy bulkCopy = new SqlBulkCopy(desConStr);//创建SqlBunlCopybulkCopy.BatchSize = 1000;//每批处理数据的数量bulkCopy.NotifyAfter = 1000;//响应超时时间bulkCopy.DestinationTableName = "table_name";//数据库表名bulkCopy.SqlRowsCopied +=  new SqlRowsCopiedEventHandler(EventHandler);  //每批处理数据后的事件bulkCopy.ColumnMappings.Add("column_name","column_name");//数据源列名与目标列名的映射bulkCopy.WriteToServer(SqlDataReader or DataTable);//执行输入..参数为数据源

下面简单的试例一下使用:

private static void BulkCopyTest()          {              string sourceConStr =System.Configuration.ConfigurationManager.ConnectionStrings["source_connstr"].ConnectionString;            string desConStr =System.Configuration.ConfigurationManager.ConnectionStrings["des_connstr"].ConnectionString;            DataTable dt= new DataTable();              using (SqlConnection sourceConn = new SqlConnection(sourceConStr))              {                  SqlCommand cmd= new SqlCommand("SELECT * FROM USER", sourceConn);                  sourceConn.Open();                  SqlDataReader sqlreader = cmd.ExecuteReader();                  using (SqlConnection desConnection = new SqlConnection(desConStr))                  {                      desConnection.Open();                      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(desConStr))                      {                          bulkCopy.BatchSize=1000;                        bulkCopy.NoitifyAfter=1000;                        bulkCopy.ColumnMappings.Add("uname", "uname");                          bulkCopy.ColumnMappings.Add("upass", "upass");                          bulkCopy.ColumnMappings.Add("token", "token");                          bulkCopy.DestinationTableName = "User";                          bulkCopy.WriteToServer(reader);                      }                  }                  reader.Close();              }          }