在C＃中将数据表保存到数据库 [英] Save datatable to database in C#

问题描述

将10K行数据表从服务器端保存到数据库的最佳方法是什么。如果存在于数据库中，将编辑某些行值。



我尝试过：



在c＃中使用For循环并连续获取每一行并命中DB，

What is the best way to save a 10K row Data table from sever End to Database. Where some row value will be edited if exist in Database.

What I have tried:

Using For loop in c# and get each row and hit DB continuously,

推荐答案

正如解决方案＃1中建议的koolprasad2003一样，你应该使用SqlBulkCopy来插入数据从数据表到数据库。请参阅文档 [ ^ ]了解更多详情。



您无法直接将数据加载到目标表中，因为您需要进行一些处理。您应该将其加载到临时表中，该表与目标表具有相同的结构，并在导入后执行目标表的插入/更新/删除。为此，您应该使用 MERGE语句 [ ^ ]。

As koolprasad2003 suggested in solution #1 you should use SqlBulkCopy to insert data form the data table into your database. See the documentation[^] for more details.

You cannot load the data into the target table directly as you need to do some processing. You should load it into a staging table, which will have the same structure as the target table and do the inserts/updates/deletes of the target table after the import. For that you should use the MERGE statement[^].

我认为SQLBulkCopy类会有所帮助更多，它允许您使用来自其他来源的数据有效地批量加载SQL Server表。

请查看以下链接

SqlBulkCopy-批量插入记录并更新现有行记录存在于使用C＃和VB.Net [ ^ ]

I think SQLBulkCopy class will help you more, it Lets you efficiently bulk load a SQL Server table with data from another source.
check out below link
SqlBulkCopy- Bulk Insert records and Update existing rows if record exists using C# and VB.Net[^]

你可以使用SqlBulkCopy进行批量插入/更新。



但是如果你想过滤。您可以在SQL中使用用户定义的表类型。

您可以将数据表传递给存储过程，然后通过SQL查询轻松过滤。



SQL：

you can use SqlBulkCopy for bulk insertion/Updation.

But if you want to filter. you can use User Defined Table Type in SQL.
In which you can pass the datatable to your stored procedure and then filter by your sql query easily.

SQL:

CREATE PROCEDURE [dbo].[Insert_Customers]
      @tblCustomers CustomerType READONLY
AS
BEGIN
      SET NOCOUNT ON;
     
      INSERT INTO Customers(CustomerId, Name, Country)
      SELECT Id, Name, Country FROM @tblCustomers
END

C＃：

C#:

using (SqlCommand cmd = new SqlCommand("Insert_Customers"))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Connection = con;
               cmd.Parameters.AddWithValue("@tblCustomers", dt);
               con.Open();
               cmd.ExecuteNonQuery();
               con.Close();
           }

这篇关于在C＃中将数据表保存到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！