在Ado.net C#中动态构建Where子句

Question

我每次会处理大约1000条记录，需要确定它们是现有记录还是新记录。

如果他们已经存在，我需要更新这些记录，如果是新的，则只需插入。我不知道它们中是否有任何现有记录，也不知道全部都是现有记录。

我认为最好的方法是对数据库进行一次查询，并尝试查找其中是否有任何现有记录，并将它们存储在内存中并检查该内存中的集合。

最初我被告知一个字段足以确定唯一性。所以我想我可以针对数据库中的1个字段执行一个大的in子句，但现在我发现这不是情况，我需要使用3个字段来确定记录是否已经存在。

这基本上是一个and子句。

select * from where columnA = "1" and ColumnB = "2" and ColumnC = "3"

我该如何在C# ado.net中正确地实现这个？

我猜我需要像一些超级where子句一样？

select * from where (columnA = "1" and ColumnB = "2" and ColumnC = "3") or  (columnA = "4" and ColumnB = "5" and ColumnC = "6") or [....998 more conditional clauses)

如果可能的话，我很乐意接受更好的想法。我仍然认为一次性完成比进行1000个单独的查询更好。

- chobo2

---

你正在使用哪种关系型数据库？例如，SQL Server支持表值参数，因此您应该能够使用单个存储过程完成整个操作。 （在此处阅读如何以线程安全的方式执行此操作） - Zohar Peled

那么这个表值参数 SP 会将重复项的结果返回给我的 C# 吗？ - chobo2

可以，你也可以在存储过程内部执行upsert操作，而无需先返回到C#。 - Zohar Peled

@chobo2 - 你可以研究一下MERGE语句。它应该能够在一次操作中完成你想要的事情。 - Moe Sisko

这里有一些信息和示例：https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 基本上，它允许您在多行上一次性执行INSERTs/UPDATEs/DELETEs。 - Moe Sisko

Answer 1

我只能帮你编写查询以响应你的请求。

        var recordCount = 1000;
        var query = "SELECT * FROM TableName WHERE";
        for (var i = 1; i < recordCount - 2; i += 3)
        {
            query += " (columnA = " + i + " and ColumnB = " + (i + 1) + " and ColumnC = " + (i + 2) + ") or ";
        }

- Farinaz

---

是的，我想到了（但是还是希望）它不会看起来像那样。虽然我一直在想这可能会成为一个非常慢的查询，因为它可能需要查看数据库中每行的所有1000个“或”语句。 - chobo2

Answer 2

我写这篇回答感觉有点傻，因为我认为你应该能够从其他帖子中组合出完整的答案 - 但是这不是我所想到的任何一个问题的确切重复。

Stackoverflow上已经有涉及此问题的问题和答案 - 但是在我的搜索中，我只找到了一些不是线程安全的答案，大多数都在使用merge。

有不同的问题和答案可以参考，例如我的回答在c#中向数据库添加多个参数化变量，您可以看到如何使用c#表值参数进行操作，以及Aaron Bertrand的答案在SQL Server中插入if条件，您可以看到如何创建一个安全的upsert - 但是我没有找到任何涵盖这个问题的完整答案 - 所以这里你可以找到：

首先，您需要在数据库中创建一个用户定义的表类型：

CERATE TYPE MyTableType AS TABLE
(
     Column1 int NOT NULL,
     Column2 int NOT NULL,
     Column3 int NOT NULL,
     -- rest of the columns in your table goes here
     PRIMARY KEY (Column1, Column2, Column3)
)

然后，您创建存储过程：

CREATE stp_UpsertMyTable
(
    @MyTableType dbo.MyTableType readonly -- table valued parameters must be readonly
)
AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

UPDATE t 
SET t.column4 = tvp.column4,
    t.column5 = tvp.column5 -- and so on for all columns that are not part of the key
FROM dbo.MyTable AS t
INNER JOIN @MyTableType AS tvp
    ON t.Column1 = tvp.Column1
    AND t.Column2 = tvp.Column2
    AND t.Column3 = tvp.Column3;

 -- Note: <ColumnsList> should be replaced with the actual columns in the table
INSERT dbo.MyTable(<ColumnsList>)
  SELECT <ColumnsList>
  FROM @MyTableType AS tvp
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.MyTable t
    WHERE t.Column1 = tvp.Column1
    AND t.Column2 = tvp.Column2
    AND t.Column3 = tvp.Column3
  );

COMMIT TRANSACTION;

GO

然后，C# 部分很简单：

DataTable dt = new DataTable();
dt.Columns.Add("Column1", typeof(int));
dt.Columns.Add("Column2", typeof(int));
dt.Columns.Add("Column3", typeof(int));
dt.Columns.Add("Column4", typeof(string));
dt.Columns.Add("Column5", typeof(string));

// Fill your data table here

using (var con = new SqlConnection("ConnectionString"))
{
    using(var cmd = new SqlCommand("stp_UpsertMyTable", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@MyTable", SqlDbType.Structured).Value = dt;
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

现在您可以使用表值参数进行完整且安全的upsert操作，只需要在c#和sql server之间进行一次往返即可。

- Zohar Peled