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

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

原文链接:https://www.cnblogs.com/tracine0513/p/4036913.html?utm_source=tuicool&utm_medium=referral

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

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

方法一:一条一条循环添加

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

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

方法二:每一千条循环添加

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

  1. int rowCount = dataGridView1.Rows.Count;
  2. int quotient = rowCount / 1000; //商
  3. int remainder = rowCount % 1000; //余数
  4. StringBuilder str = new StringBuilder();
  5. for (int j = 0; j < quotient; j++)
  6. {
  7. str.Append("insert into BatchTable values");
  8. for (int i = 0; i < 1000; i++)
  9. {
  10. str.AppendFormat("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}'),"
  11. , dataGridView1[0, i].Value, dataGridView1[1, i].Value, dataGridView1[2, i].Value, dataGridView1[3, i].Value,
  12. dataGridView1[4, i].Value , dataGridView1[5, i].Value, dataGridView1[6, i].Value, dataGridView1[7, i].Value,
  13. dataGridView1[8, i].Value, dataGridView1[9, i].Value, dataGridView1[10, i].Value);
  14. }
  15. string sql = str.ToString().TrimEnd(',');
  16. SqlHelper.ExcuteNonQuery(CommandType.Text, sql, null);
  17. str.Clear();
  18. }
  19. if (remainder > 0)
  20. {
  21. str.Append("insert into BatchTable values");
  22. for (int i = 0; i < remainder; i++)
  23. {
  24. str.AppendFormat("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}'),"
  25. , dataGridView1[0, i].Value, dataGridView1[1, i].Value, dataGridView1[2, i].Value, dataGridView1[3, i].Value
  26. , dataGridView1[4, i].Value, dataGridView1[5, i].Value, dataGridView1[6, i].Value, dataGridView1[7, i].Value,
  27. dataGridView1[8, i].Value, dataGridView1[9, i].Value, dataGridView1[10, i].Value);
  28. }
  29. string sql = str.ToString().TrimEnd(',');
  30. SqlHelper.ExcuteNonQuery(CommandType.Text, sql, null);
  31. str.Clear();
  32. }

方法三:使用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毫秒

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

方法四:利用SQL Server 2008新特性:表值参数,简称TVPS

MSDN中对表值参数的解释:

  1. 表值参数提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式,而不需要多次往返或特殊服务器端逻辑来处理数据。 您可以使用表值参数来包装客户端应用程序中的数据行,并使用单个参数化命令将数据发送到服务器。 传入的数据行存储在一个表变量中,然后您可以通过使用 Transact-SQL 对该表变量进行操作。
  2. ** 注意**:无法在表值参数中返回数据。 表值参数是只可输入的参数;不支持 OUTPUT 关键字。详情请参见MSDN

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

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

  1. --BatchTable为数据表名称
  2. create table [dbo].[BatchTable](
  3. [Gradation] [nvarchar](10) NULL,
  4. [Column1] [nvarchar](10) NULL,
  5. [Column2] [nvarchar](10) NULL,
  6. [Column3] [nvarchar](10) NULL,
  7. [Column4] [nvarchar](10) NULL,
  8. [Column5] [nvarchar](10) NULL,
  9. [Column6] [nvarchar](10) NULL,
  10. [Column7] [nvarchar](10) NULL,
  11. [Column8] [nvarchar](10) NULL,
  12. [Column9] [nvarchar](10) NULL,
  13. [Column10] [nvarchar](10) NULL
  14. ) ON [PRIMARY]
  15. GO

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

  1. Create type BatchTableTvps as Table
  2. (
  3. [Gradation] [nvarchar](10) NULL,
  4. [Column1] [nvarchar](10) NULL,
  5. [Column2] [nvarchar](10) NULL,
  6. [Column3] [nvarchar](10) NULL,
  7. [Column4] [nvarchar](10) NULL,
  8. [Column5] [nvarchar](10) NULL,
  9. [Column6] [nvarchar](10) NULL,
  10. [Column7] [nvarchar](10) NULL,
  11. [Column8] [nvarchar](10) NULL,
  12. [Column9] [nvarchar](10) NULL,
  13. [Column10] [nvarchar](10) NULL
  14. )
  15. GO

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

  1. DataTable table = GetDataTable("BatchTable"); //要插入的表
  2. string sql = "insert into BatchTable select * from @BatchTable";
  3. SqlParameter par = new SqlParameter("@BatchTable", SqlDbType.Structured);
  4. par.TypeName = "BatchTableTvps"; //指定表值参数类型
  5. par.Value = table;
  6. SqlParameter[] pars = new SqlParameter[] { par };
  7. 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

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70

发表评论

表情:
评论列表 (有 0 条评论,176人围观)

还没有评论,来说两句吧...

相关阅读