C#和长SQL Server查询，什么'；It’这是正确的方法

本文关键字：It 方法 Server SQL 和长 查询 什么 | 更新日期: 2023-09-27 18:22:06

我的问题本质上很简单——查询数据库，并将输出写入一组文件（~1000）。

我有一个SQL查询，需要从SQL Server上的C#执行。除了in子句中的一个值之外，查询非常简单。

例如，查询可能看起来像：

SELECT 
    ID, Name
FROM
    MyTable
WHERE
    SomeValue IN (/* list of values */)

值的列表大约有300个字符串，每个字符串有6个字符。这些值最终看起来像（"A00001"、"A00002"等），并由应用程序的用户指定为输入。

我最初的想法是将查询构建为一个长字符串，将每个值聚合在一起。然而，这似乎不是一种正确的方法，而且似乎很容易出错。我的下一个想法是使用存储过程，但我不确定数据应该如何传递到其中。应该如何以高效的方式构建这个查询，以便可靠地传递数据？

我的另一个选择是从SQL Server读取所有数据，然后在收到结果时用C#过滤结果。然而，如果我这样做，我会得到大约900万条记录，这似乎太多了，无法作为数据集读取到内存中。因为我是在解析大约5000条记录后编写文件的，所以我不想在仔细阅读收到的数据时让数据读取器连接到数据库。

什么是最好的方法？

C#和长SQL Server查询，什么'；It’这是正确的方法

本系列文章将详细讨论该主题：

  • SQL Server 2008中使用表值参数的数组和列表
  • SQL Server 2005及以后版本中的阵列和列表—当TVP不剪切时
  • SQL Server 2000及更早版本中的数组和列表

您应该从第一篇文章中推荐的方法开始，使用表值参数。另请参阅MSDN用法文章：SQL Server 2008（ADO.NET）中的表值参数：

void SelectValuesOfInterest(IEnumerable<SqlDataRecord> valuesOfInterest)
SqlCommand cmd = new SqlCommand(@"    SELECT 
        ID, Name
    FROM
        MyTable
    WHERE
        SomeValue IN (SELECT value FROM @tableValueParameter);"
, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tableValueParameter", valuesOfInterest);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.MyTVPType";
using (SqlDataReader rdr = cmd.ExecuteReader ())
{
 while rdr.Read() {...}
}
}

通常的方法是使IN子句成为子查询：

SELECT ProductID from Product where ManufacturerID in 
    (SELECT ManufacturerID from Manufacturer where HomeOfficeLocation = 'France')

如果你不能做到这一点：只要用户从你提供的列表中挑选，一个简单的串联列表就不会特别容易出错。

您肯定不想在大型数据集中循环查找少量项目。

编辑以添加

更完整一点：我经常做的是在前端创建一个逗号分隔的列表（例如"234, 345, 456"），并将其作为字符串传递给存储过程；然后在该proc中使用一个用户定义的函数，比如这里讨论的函数。

我并不反对临时表，但通常只有当我要对列表执行多个操作时，我才会使用它们。

我之所以说"只要用户从列表中选择"，是为了避免您必须验证（和净化）用户输入。

这里有一种使用xml:的方法

C#：

StringBuilder sb = new StringBuilder();
sb.AppendLine("<SelectedValues>");
foreach (string s in userSelectedValues)
  sb.AppendFormat("<row val='"{0}'" />{1}", s, Environment.NewLine);
sb.AppendLine("</SelectedValues>");

将此sb.ToString（）作为XML传递到存储过程

SQL:

CREATE PROCEDURE dbo.MyStoredProcedure
(
  @UserSelectedValuesXml XML
)     
SELECT * 
FROM dbo.MyTable
WHERE Id IN ( SELECT ref.value('@val', 'varchar')
              FROM @UserSelectedValuesXml.nodes('/SelectedValues/row') AS T(ref))

如果in子句中的项数足够大，您可能想要使用一个临时表-先在那里插入值，然后连接到临时表，或者将其用作SELECT的子查询源。

这篇CodeProject文章讨论了临时表的使用；但总结一下你的案例：

CREATE TABLE #TempQueryList(Item varchar(300))
// in your app code:
insert into #TempQueryList values ( @item ); -- use BIND variable
insert into #TempQueryList values ( @item ); -- use BIND variable
...
insert into #TempQueryList values ( @item ); -- use BIND variable
// then your query becomes:
SELECT 
  ID, Name
FROM
  MyTable
WHERE
  SomeValue IN (SELECT Item FROM #TempQueryList)

如果您可以使用子查询（如egrunin所示）来完成此操作，那将是最简单、最快的。如果您不能做到这一点（即，您所拥有的只是一个值列表），那么您可能需要查看表值参数。

这个问题有一些读者可能会感兴趣。

我会使用一个表值参数。根据您可以访问的SQL server的版本，您可以将整个数据表传递到存储过程中，然后像查询普通表一样对其进行查询。

基本上，您可以在C#中创建一个包含您的列表的表。您可以将其作为参数传递给存储过程。在过程中，您可以在MyTable和您传递的参数之间进行内部联接，以获得所需的结果。

相关文章：