SqlBulkCopy的忽略的数据表从数据库中重复记录 [英] SqlBulkCopy Ignore Duplicate Records of Datatable From DataBase

问题描述

我可以忽略数据的重复记录已经present在SQL数据库中的数据表，我想过去到SqlBulkCopy的。如果是的话怎么也解释了我，如果没有和其他选项。

Can i ignore the duplicate records of a data already present in sql database from a datatable which i am passing to SqlBulkCopy. If Yes then How and also explain me if No and other option.

推荐答案

由于previous海报说，这不是建在我实现类似使用以下内容：

As previous poster said, this is not built in. I achieve similar using the following:

SQL存储过程接受与您需要的数据的TableValuedParameter。

SQL Stored Procedure that accepts a TableValuedParameter with the data you require.

在存储的过程，然后我的所有记录插入到一​​个临时表。一旦你拥有了它在那里，你可以使用sql语句MERGE语句中的存储过程将数据插入它不存在。

In the stored proc, I then INSERT all records into a temp table. Once you have it there, you can use SQLs MERGE statement in your stored proc to insert data where it doesn't already exist.

所以，让我们假设，我们的数据仅仅是存储在表中的人的人的名字。我们认为只有一个ID和名称。我也假设该表称为'人'。

So, let us assume that our data is simply people's names stored in a table people. We hold only an ID and a name. I also assume this table is called 'people'.

下面是我如何创建我的表值参数类型（在SQL Server中创建）

Here's how I create my Table Valued Parameter type (created in SQL Server)

CREATE TYPE udt_person AS TABLE(
[id] [INT] NOT NULL,
[name] [nvarchar(50)] NULL
)
GO

我现在创建存储过程：

I now create the stored procedure:

CREATE PROCEDURE SaveNewPeople @pPeople udt_Person
AS
BEGIN
    -- Create Temp table
    CREATE TABLE #tmpPeople (id INT, name VARCHAR 50)

    -- We will stage all data passed in into temp table
    INSERT INTO #tmpPeople
    SELECT id, name FROM @pPeople

    -- NB: you will need to think about locking strategy a bit here
    MERGE people AS p
    USING #tmpPeople AS t
    ON p.id = t.id
    WHEN NOT MATCHED BY TARGET THEN
        -- We want to insert new person
        INSERT (id, name) VALUES (t.id, t.name)
    WHEN MATCHED THEN
        -- you may not need this, assume updating name for example
        UPDATE SET p.name = t.name

END

现在我们有SQL的地方。

Now we have the SQL in place.

让我们创建的数据在C＃中的大头：

Let us create the bulk of data in C#:

DataTable ppl = new DataTable();
ppl.Columns.Add("id", typeof(int));
ppl.Columns.Add("name", typeof(string));

// table is created, let's add some people
var bob = ppl.NewRow();
bob["id"] = 1;
bob["name"] = "Bob";
ppl.Rows.Add(bob);

var jim = ppl.NewRow();
jim["id"] = 2;
jim["name"] = "Jim";
ppl.Rows.Add(jim);

// that's enough people for now, let's call the stored procedure
using(var conn = new SqlConnection("YouConnStringHere"))
{
    using(var cmd = new SqlCommand("SaveNewPeople", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        var tvp = new SqlParameter
        {
          ParameterName = "@pPeople",
          SqlDbType = SqlDbType.Structured,
          Value = ppl,
          TypeName = "udt_person"
        }
        cmd.Parameters.Add(tvp);
        conn.Open();
        cmd.ExecuteNonQuery();
    }

}

希望这给你的想法。如果随后修改了C＃中的数据表，你应该看到行插入，更新或忽略。

Hopefully this gives you the idea. If you then modified the C# datatable, you should see rows inserted, updated or ignored.

祝你好运。

这篇关于SqlBulkCopy的忽略的数据表从数据库中重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！