C# 使用SqlBulkCopy类批量复制大数据

今天药忘吃喽~ 2022-05-28 11:44 102阅读 0赞

特别注意  sqlbulkcopy.ColumnMappings.Add(dt.Columns\[i\].ColumnName, dt.Columns\[i\].ColumnName);

插入的时候列的顺序可以不一致，但名称和数据类型最好要保存一致。不一致时候，也能进行正确的转换，除了比如DataTime数据类型，不能插入一个无效的string时间字符串。

本文转载：[http://zhoufoxcn.blog.51cto.com/792419/166052][http_zhoufoxcn.blog.51cto.com_792419_166052]

参考[http://www.cnblogs.com/scottckt/archive/2011/02/16/1955862.html][http_www.cnblogs.com_scottckt_archive_2011_02_16_1955862.html]

几种数据库的大数据批量插入

[http://www.cnblogs.com/netuml/p/3719298.html][http_www.cnblogs.com_netuml_p_3719298.html]

这篇文章不错通过xml配置映射关系：[http://www.cnblogs.com/pengyq/archive/2009/12/04/1616997.html][http_www.cnblogs.com_pengyq_archive_2009_12_04_1616997.html]

用途说明：

前些日子，公司要求做一个数据导入程序，要求将Excel数据，大批量的导入到数据库中，尽量少的访问数据库，高性能的对数据库进行存储。于是在网上进行查找，发现了一个比较好的解决方案，就是采用SqlBulkCopy来处理存储数据。SqlBulkCopy存储大批量的数据非常的高效，就像这个方法的名字一样，可以将内存中的数据表直接的一次性的存储到数据库中，而不需要一次一次的向数据库Insert数据。初次实验，百万级别的数据表，也只需几秒时间内就可以完全的存入数据库中，其速度，比传统的Insert方法不止快很多倍

MSDN摘要:Microsoft SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 [DataTable][] 实例或可使用 [IDataReader][] 实例读取数据。

![复制代码][copycode.gif]

///   <summary>   
         ///    
         ///   </summary>   
         ///   <param name="connectionString"> 目标连接字符 </param>   
         ///   <param name="TableName"> 目标表 </param>   
         ///   <param name="dt"> 源数据 </param>   
         private  void SqlBulkCopyByDatatable( string connectionString,  string TableName, DataTable dt)  
        \{  
             using (SqlConnection conn =  new SqlConnection(connectionString))  
            \{  
                 using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))  
                \{  
                     try  
                    \{  
                        sqlbulkcopy.DestinationTableName = TableName;  
                         for ( int i =  0; i < dt.Columns.Count; i++)  
                        \{  
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns\[i\].ColumnName, dt.Columns\[i\].ColumnName);  
                        \}  
                        sqlbulkcopy.WriteToServer(dt);  
                    \}  
                     catch (System.Exception ex)  
                    \{  
                         throw ex;  
                    \}  
                \}  
            \}  
        \}

![复制代码][copycode.gif]

SqlBulkCopy使用使用用于自定义表类型作为存储过程参数，批量写入数据。  
   
　　如果，大家使用SQL Server  2008，它提供一个新的功能表变量（Table Parameters）可以将整个表数据汇集成一个参数传递给存储过程或SQL语句。它的注意性能开销是将数据汇集成参数(O(数据量))。  
   
　　现在，我们修改之前的代码，在SQL Server中定义我们的表变量，具体定义如下：  
 -- =============================================  
\-- Author:        JKhuang  
\-- Create date:  08/ 16/ 2012  
\-- Description:    Declares a user table paramter.  
\-- =============================================  
CREATE TYPE jk\_users\_bulk\_insert AS TABLE (  
    user\_login varchar( 60),  
    user\_pass varchar( 64),  
    user\_nicename varchar( 50),  
    user\_email varchar( 100),  
    user\_url varchar( 100),  
    user\_activation\_key varchar( 60),  
    user\_status  int,  
    display\_name varchar( 250)  
)  
   
　　上面，我们定义了一个表参数jk\_users\_bulk\_insert，接着我们定义一个存储过程接受表参数jk\_users\_bulk\_insert，具体定义如下：  
 -- =============================================  
\-- Author:        JKhuang  
\-- Create date:  08/ 16/ 2012  
\-- Description:    Creates a stored procedure, receive  
\-- a jk\_users\_bulk\_insert argument.  
\-- =============================================  
CREATE PROCEDURE sp\_insert\_jk\_users   
@usersTable jk\_users\_bulk\_insert READONLY   
AS  
INSERT INTO jk\_users (user\_login, user\_pass, user\_nicename, user\_email, user\_url,   
user\_activation\_key, user\_status, display\_name, user\_registered)   
SELECT user\_login, user\_pass, user\_nicename, user\_email, user\_url,   
user\_activation\_key, user\_status, display\_name, GETDATE()   
FROM @usersTable   
　　接下我们在客户端代码中，调用存储过程并且将表作为参数方式传递给存储过程。  
  var sw = Stopwatch.StartNew();  
using ( var conn =  new SqlConnection(ConfigurationManager.ConnectionStrings\[ " SQLCONN2 "\].ToString()))  
\{  
    conn.Open();  
     /// / Invokes the stored procedure.   
     using ( var cmd =  new SqlCommand( " sp\_insert\_jk\_users ", conn))  
    \{  
        cmd.CommandType = CommandType.StoredProcedure;  
         /// / Adding a "structured" parameter allows you to insert tons of data with low overhead   
         var param =  new SqlParameter( " @userTable ", SqlDbType.Structured) \{ Value = dt \};  
        cmd.Parameters.Add(param);  
        cmd.ExecuteNonQuery();  
    \}  
\}  
sw.Stop();   
　　现在，我们重新执行写入操作发现写入效率与SqlBulkCopy相当。

原文地址：[https://www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html][https_www.cnblogs.com_zfanlong1314_archive_2013_02_05_2892998.html]

[http_zhoufoxcn.blog.51cto.com_792419_166052]: http://zhoufoxcn.blog.51cto.com/792419/166052
[http_www.cnblogs.com_scottckt_archive_2011_02_16_1955862.html]: http://www.cnblogs.com/scottckt/archive/2011/02/16/1955862.html
[http_www.cnblogs.com_netuml_p_3719298.html]: http://www.cnblogs.com/netuml/p/3719298.html
[http_www.cnblogs.com_pengyq_archive_2009_12_04_1616997.html]: http://www.cnblogs.com/pengyq/archive/2009/12/04/1616997.html
[DataTable]: http://www.cnblogs.com/zfanlong1314/admin/ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.chs/cpref4/html/T_System_Data_DataTable.htm
[IDataReader]: http://www.cnblogs.com/zfanlong1314/admin/ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.chs/cpref4/html/T_System_Data_IDataReader.htm
[copycode.gif]: /images/20220528/c101ad4169724a52930949adebc0e81c.png
[https_www.cnblogs.com_zfanlong1314_archive_2013_02_05_2892998.html]: https://www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html