批量数据在C＃.net APplication中插入或更新到SQl表 [英] Bulk data insert or update in C#.net APplication to SQl Table

问题描述

我的C＃.Net应用程序中有10000个数据行作为数据表。



我有与SQl数据库类似的表格



我需要更新表格如果新记录然后插入到sql中，如果它是我需要的现有数据更新价值。



请建议有效的方式来做这个



谢谢

Mohan

Hi,

I have 10000 Data rows as a Data Table in my C#.Net Application.

I have the similar table to SQl Data base

I need to update the table if new record then insert into sql , if it is existing data i need to update the values.

Please suggest the efficinet way to do this

Thanks
Mohan

推荐答案

OK然后。



从SQL Server 2008开始，有一个叫做表参数，您可以一次性将整个数据表发送到存储过程。存储过程可以使用连接或其他任何来进行比较并相应地更新/插入。



使用这样的批量数据而不是单独调用是快速的。



基本步骤如下：



在SQL Server中创建一个用户定义的表类型匹配表的那个。

OK then.

Since SQL Server 2008, there is a thing called table parameters whereby you can send up your entire datatable to a stored procedure in a single go. The stored proc can use a join or whatever to do the comparison and update/insert accordingly.

Working with batch data like this rather than making individual calls is lightning quick.

The basic steps are this:

create a user-defined table type in SQL Server which matches that of the table.

CREATE TYPE [dbo].[CustomerTable] AS TABLE(
    [CustomerId] [int] NOT NULL,
    [Field1] [int] NULL,
    [Field2] [int] NULL,

等



您可以像使用任何其他类型一样使用此类型作为参数到您的存储过程。在存储过程中，找出如何进行对帐并将整个事物放入事务中。它应该只需要一个参数

etc.

You can use this type like any other as a parameter to your stored procedure. In the stored proc, work out how to do the reconciliation and put the whole thing in a transaction. It should take just a single parameter

create proc UpdateCustomers
(
	@customers CustomerTable readonly
	...

然后调用它，像往常一样使用ADO。只需将数据表作为单个参数传递并将其标记为类型SqlDBType.Structured。



现在时间紧迫，但如果需要更多解释，请告诉我.Google会帮忙。

Then to call it, use ADO as you would normally. Just pass your datatable as the single parameter and mark it as type SqlDBType.Structured.

Bit short on time now, but let me know if more explanation is required. Google will help.

对于批量插入/ SQL活动，您可以参考下面的链接



处理从CSV到SQL Server的数据插入 [ ^ ]



希望这会对你有所帮助。

Hi,

For Bulk insert / SQL activities you can refer below link

Handling BULK Data insert from CSV to SQL Server[^]

Hope this will be helpful to you.

你好，

检查这个它可能有所帮助你。

http://msdn.microsoft.com/en-IN/ library / ms188365.aspx [ ^ ]





http://stackoverflow.com/questions/10374134/bulk-import-xml-into-sql-server [ ^ ]



问候，

Arun

Hi,
Check this out it may help you.
http://msdn.microsoft.com/en-IN/library/ms188365.aspx[^]


http://stackoverflow.com/questions/10374134/bulk-import-xml-into-sql-server[^]

Regards,
Arun

这篇关于批量数据在C＃.net APplication中插入或更新到SQl表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！