通过单个查询更新表中的大量数据. [英] Update bulk of data in table from single query.

问题描述

大家好！

我的场景是

我正在该Excel工作表中上传包含ID和名称的ExcelSheet.

然后使用读取excelsheet将其包含在服务器端的表中.

现在，我想针对该表中存在的每一行将状态列更新为sql server.


现在我正在使用以下代码:

  foreach (数据行行 in  dt.Rows中的行)
                 {
                     数据库数据库= DatabaseFactory.CreateDatabase(DALConstants.ConstantsBase.MIS);
                     字符串 sqlcommand = "  ;
                     dbCommand databasecommand =
                     database.AddInParameter(databasecommand，" ，DbType.字符串，行[" ].ToString());
                     database.AddInParameter(databasecommand，" ，DbType.字符串，行[" ].ToString());

                      int  i = database.ExecuteNonQuery(databasecommand);
                 }

但它每次都会命中服务器.
我不想要它?


因此

有什么解决办法吗?

谢谢
@ ChetanV @

解决方案

(1)您的代码每次都将与数据库连接，以插入记录.不推荐这种方式.你应该避免它.
(2)您可以使用BulkCopy机制.这是一些很好的链接.

链接1 [链接2 [ ^ ]

使用bcp将excel文件设置为数据库中的表.创建一个用于处理更新的存储过程.根据where子句，它可以在一次调用中更新所有行

将类型化的数据集用于批量插入

使用DataSet和DataAdapter插入关系数据 [ ^ ]

Hello Guys !

My Scenario Is

i am uploading ExcelSheet Which Contain id and Name in that excel sheet.

then that contain convert into table on server side using reading excelsheet.

now i want to update a status column against each and every row present in that table to sql server.


now i am using following code :

foreach (DataRow row in dt.Rows)
                 {
                     Database database = DatabaseFactory.CreateDatabase(DALConstants.ConstantsBase.MIS);
                     string sqlcommand = "[PRDMIS_ExcelDeletion]";
                     DbCommand databasecommand =
                     database.AddInParameter(databasecommand, "ClientCode", DbType.String, row["id "].ToString());
                     database.AddInParameter(databasecommand, "Name", DbType.String, row["Name"].ToString());

                     int i = database.ExecuteNonQuery(databasecommand);
                 }

but it will hit server every time.
that i dont want it?


therefore

Is there any solution for that.

thanks
@ChetanV@

解决方案

(1) Your code will connect with database each time, for inserting record. This is not recommended way. You should avoid it.
(2) You can go with BulkCopy mechanism. Here are some good link.

Link 1[^]

Link 2[^]

use bcp to set the excel file as a table in your database. Create a stored procedure that handles the updates. Based on the where clause it can update all rows in 1 call

Use typed dataset for bulk insert

Inserting relational data using DataSet and DataAdapter[^]

这篇关于通过单个查询更新表中的大量数据.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！