如何使用.NET将excel行插入SQL Server表 [英] How do I insert rows of an excel into a SQL server table using .NET

问题描述

我有一个带有一些行的excel表。我需要使用.Net将这些行添加到SQL服务器的表中。我已尝试批量插入但它没有帮助，因为它首先需要检查该特定记录（拥有相同的员工ID）已经存在。如果不是，则需要插入新记录，如果是，则需要使用exc表中针对该emp Id的新值更新该特定行。

请帮忙！



我的尝试：



我已经尝试过批量插入，但它直接导入到表中而不是逐行导入以检查员工ID条件。

I have an excel sheet with some rows.I need to add those rows into a table in SQL server using .Net.I have tried bulk insert but it does not help as it first needs to be checked if that particular record (having same employee id) is already present. If no,the new record needs to be inserted and if yes, that particular row needs to be updated with the new values in the excel sheet for that emp Id.
Please help !

What I have tried:

I have tried bulk insert but it directly imports into table and not row by row for checking the employee id condition.

推荐答案

你最好的选择是编写存储过程以适当地处理插入。我使用这样的东西：

Your best bet is to write a stored procedure to handle the insert appropriately. I use something like this:

-- try to update an existing record
UPDATE  [databaseName].[dbo].[tableName]
SET     [field1] = @parameter,
        [field2] = @parameter2
        ...
WHERE   [ID]     = @id
[AND    [field3] = @parameter3, ...]

-- if the number of rows affected is 0, the record we tried to update 
-- does not exist, so perform an insert.
IF (@@ROWCOUNT = 0)
    INSERT  INTO [databaseName].[dbo].[tableName]
            (field1,  field2, field3, ...)
    VALUES  (@parameter1, @parameter2, @parameter3, ...)

我的个人偏好是一次处理一条记录并在参数列表中指定字段，但您也可以传递XML字符串并让存储过程解析出来。

My personal preference is to process one record at a time and specify fields in a parameter list, but you can also pass an XML string and let the stored procedure parse it out.

这篇关于如何使用.NET将excel行插入SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！