ADO.NET- 中批量添加数据的几种实现方法比较

女爷i 2022-05-10 07:56 172阅读 0赞

### 原文链接：[https://www.cnblogs.com/tracine0513/p/4036913.html?utm\_source=tuicool&utm\_medium=referral][https_www.cnblogs.com_tracine0513_p_4036913.html_utm_source_tuicool_utm_medium_referral] ###

# [ADO.NET- 中批量添加数据的几种实现方法比较][ADO.NET-] #

在.Net中经常会遇到批量添加数据，如将Excel中的数据导入数据库，直接在DataGridView控件中添加数据再保存到数据库等等。

**方法一：一条一条循环添加**

通常我们的第一反应是采用for或foreach循环一条一条的添加。这样的方法可想而知，效率肯定很低，可以慢到操作人员无法接受的那种。经过测试(局域网)，1W条数据将会耗时3分42秒842毫秒

for (int i = 0; i < dgv.Rows.Count; i++)
    {
        string sql  = "insert into .....";
        SqlHelper.ExcuteNonQuery(CommandType.Text, sql, null);
    }

**方法二：每一千条循环添加**

也许马上会有人想到Insert多条记录，即"Insert into TableName Values(' ',' ',' '),Values(' ',' ',' ')"，这种方法在一定程度上提高不少的效率，但是这种方法有几个弊端。比如说，在SQL Server 2000中它就不支持这种语法，会提示"第 2 行: ',' 附近有语法错误。"的警告，批量操作也就无从谈起。再比如，这种方法么次最多也只能Insert 1000条数据，如果超过了1000条就会报错："INSERT 语句中行值表达式的数目超出了 1000 行值的最大允许值。"。当然了，我们可以分为几次，每次添加一千条数据，这样还是比方法一的效率高不少。经过测试(局域网)，1W条数据将会耗时0分14秒766毫秒

int rowCount = dataGridView1.Rows.Count;
    int quotient = rowCount / 1000;  //商
    int remainder = rowCount % 1000;  //余数
    StringBuilder str = new StringBuilder();         
    for (int j = 0; j < quotient; j++)
    {
        str.Append("insert into BatchTable values");
        for (int i = 0; i < 1000; i++)
        {
            str.AppendFormat("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}'),"
                , dataGridView1[0, i].Value, dataGridView1[1, i].Value, dataGridView1[2, i].Value, dataGridView1[3, i].Value,
                dataGridView1[4, i].Value , dataGridView1[5, i].Value, dataGridView1[6, i].Value, dataGridView1[7, i].Value, 
                dataGridView1[8, i].Value, dataGridView1[9, i].Value, dataGridView1[10, i].Value);
        } 
        string sql = str.ToString().TrimEnd(',');
        SqlHelper.ExcuteNonQuery(CommandType.Text, sql, null);
        str.Clear();
    }
    
    if (remainder > 0)
    {
        str.Append("insert into BatchTable values");
        for (int i = 0; i < remainder; i++)
        {
            str.AppendFormat("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}'),"
                , dataGridView1[0, i].Value, dataGridView1[1, i].Value, dataGridView1[2, i].Value, dataGridView1[3, i].Value 
                , dataGridView1[4, i].Value, dataGridView1[5, i].Value, dataGridView1[6, i].Value, dataGridView1[7, i].Value,
                dataGridView1[8, i].Value, dataGridView1[9, i].Value, dataGridView1[10, i].Value);
        }
        string sql = str.ToString().TrimEnd(',');
        SqlHelper.ExcuteNonQuery(CommandType.Text, sql, null);
        str.Clear();
    }

**方法三：使用SqlBulkCopy类批量添加数据**

SqlBulkCopy类位于System.Data.SqlClient命名空间下，摘自MSDN：Microsoft SQL Server 提供一个称为 **bcp** 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。**SqlBulkCopy** 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下**SqlBulkCopy** 提供明显的性能优势。使用 **SqlBulkCopy** 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 [DataTable][] 实例或可使用[IDataReader][] 实例读取数据。

采用 SqlBulkCopy类进行批量添加数据将会大大调高效率。经过测试(局域网)，1W条数据将会耗时0分0秒292毫秒

public static bool ExcuteNonQuery(DataTable dt)
    {
        SqlConnection connection = new SqlConnection(connString);
        connection.Open();
        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connection);
        sqlbulkcopy.BulkCopyTimeout = 100;  //超时之前操作完成所允许的秒数
        sqlbulkcopy.BatchSize = dt.Rows.Count;  //每一批次中的行数
        sqlbulkcopy.DestinationTableName = dt.TableName;  //服务器上目标表的名称
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sqlbulkcopy.ColumnMappings.Add(i, i);  //映射定义数据源中的列和目标表中的列之间的关系
        }
        sqlbulkcopy.WriteToServer(dt);  // 将DataTable数据上传到数据表中
        connection.Close();
        return true;
    }

** 方法四：利用SQL Server 2008新特性：表值参数，简称TVPS**

MSDN中对表值参数的解释：

表值参数提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式，而不需要多次往返或特殊服务器端逻辑来处理数据。 您可以使用表值参数来包装客户端应用程序中的数据行，并使用单个参数化命令将数据发送到服务器。 传入的数据行存储在一个表变量中，然后您可以通过使用 Transact-SQL 对该表变量进行操作。

** 注意**：无法在表值参数中返回数据。 表值参数是只可输入的参数；不支持 OUTPUT 关键字。详情请参见MSDN

在ADO.NET中使用表值参数传递DataTable对象

**1、创建数据表对应的表值参数类型，此类型应与数据表结构一致，假如创建数据表的sql语句为**

--BatchTable为数据表名称
    create table [dbo].[BatchTable](
        [Gradation] [nvarchar](10) NULL,
        [Column1] [nvarchar](10) NULL,
        [Column2] [nvarchar](10) NULL,
        [Column3] [nvarchar](10) NULL,
        [Column4] [nvarchar](10) NULL,
        [Column5] [nvarchar](10) NULL,
        [Column6] [nvarchar](10) NULL,
        [Column7] [nvarchar](10) NULL,
        [Column8] [nvarchar](10) NULL,
        [Column9] [nvarchar](10) NULL,
        [Column10] [nvarchar](10) NULL
    ) ON [PRIMARY]
    GO

**2、那么对应的创建表值参数类型的语句为**

Create type BatchTableTvps as Table  
    (  
    [Gradation] [nvarchar](10) NULL,
        [Column1] [nvarchar](10) NULL,
        [Column2] [nvarchar](10) NULL,
        [Column3] [nvarchar](10) NULL,
        [Column4] [nvarchar](10) NULL,
        [Column5] [nvarchar](10) NULL,
        [Column6] [nvarchar](10) NULL,
        [Column7] [nvarchar](10) NULL,
        [Column8] [nvarchar](10) NULL,
        [Column9] [nvarchar](10) NULL,
        [Column10] [nvarchar](10) NULL
    ) 
    GO

懒人们一般的做法都是，选中要操作的数据表，单击右键 —> 编写表脚本为 —> Create到 —> 新查询编辑器窗口。然后稍稍修改就行了  
**3、然后就可以在C\#中指定SqlParameter参数对象的属性TypeName为刚刚创建的表值参数类型。**

DataTable table = GetDataTable("BatchTable"); //要插入的表
    string sql = "insert into BatchTable select * from @BatchTable";
    SqlParameter par = new SqlParameter("@BatchTable", SqlDbType.Structured);
    par.TypeName = "BatchTableTvps"; //指定表值参数类型
    par.Value = table;
    SqlParameter[] pars = new SqlParameter[] { par };
    int values = SqlHelper.ExcuteNonQuery(CommandType.Text, sql, pars);

**SqlBulkCopy类批量添加数据与表值参数TVPS批量添加数据比较**

同一环境下，添加1W条数据：SqlBulkCopy耗时292毫秒，TVPS耗时902毫秒

同一环境下，添加10W条数据：SqlBulkCopy耗时5秒768毫秒，TVPS耗时6秒617毫秒

同一环境下，添加20W条数据：SqlBulkCopy耗时11秒791毫秒，TVPS耗时16秒525毫秒

**测试源码下载链接**：[http://download.csdn.net/detail/tracine0513/8060011][http_download.csdn.net_detail_tracine0513_8060011]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70][]

[https_www.cnblogs.com_tracine0513_p_4036913.html_utm_source_tuicool_utm_medium_referral]: https://www.cnblogs.com/tracine0513/p/4036913.html?utm_source=tuicool&utm_medium=referral
[ADO.NET-]: https://www.cnblogs.com/tracine0513/p/4036913.html
[DataTable]: http://msdn.microsoft.com/zh-cn/library/system.data.datatable%28v=vs.80%29.aspx
[IDataReader]: http://msdn.microsoft.com/zh-cn/library/system.data.idatareader%28v=vs.80%29.aspx
[http_download.csdn.net_detail_tracine0513_8060011]: http://download.csdn.net/detail/tracine0513/8060011
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70]: /images/20220510/57b2837254144bf583166b18e195b8c0.png