在Ado.net C＃中动态构建子句 [英] Build Where Clause Dynamically in Ado.net C#

问题描述

在给定的时间，我将接收大约1000条记录，我必须确定它们是现有记录还是新记录。

I will be taking in around 1000 records at a given time and I have to determine if they are existing records or new records.

如果存在，我有更新记录，如果是新记录，则只需插入它们。我不知道它们中的任何一个是否存在，或者它们是否全部存在。

If they are existing I have to update the records, if new then just insert them. I will not know if any of them will be existing or if they all will be existing.

我认为最好对数据库执行一次查询并尝试找出其中是否存在任何一个并将其存储在内存中，并在内存中检查该集合并检查。

I thought that it might be best to do one query to the database and try to find if any of them exist in the db and store them in memory and check that collection in memory and check that.

最初我被告知我1个字段就足够了确定唯一性。所以我以为我可以对数据库中的1个字段执行1个大的in子句，但是现在我发现情况并非如此，我需要使用3个字段来确定记录是否存在。

Originally I was told I 1 field would be enough to determine uniqueness. So I thought I could just do 1 big in clause against 1 field in the database but now I found out that is not the case and I need to use 3 fields to determine if the record is existing or now.

这基本上是and子句

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

如何在C＃中正确纠正此问题ado.net ？

How can I properly right this in C# ado.net?

我想我需要像一些super where子句一样？

I am guessing I going to need to have like some super where clause?

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个单独的查询要好。

I am open to better ideas if possible. I still think doing it in 1 shot is better than doing 1000 separate queries.

推荐答案

我只能帮您编写针对您的请求

I can only help you to write query for your request

        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 ";
        }

这篇关于在Ado.net C＃中动态构建子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！