我想从已经定义的字符串列表中选择数据,然后在某些条件下基于该列表进行过滤。

但是当我尝试下面的代码时:

List<string> inventoryList = new List<string>();

inventoryList.Add("a147");
inventoryList.Add("w150");

string.Format("SELECT * FROM (VALUES '{0}') AS InventoryList(InventoryCode)"
                    + " SELECT a.[InventoryCode] FROM [InventoryList] a INNER JOIN [PlayerAccount] b WITH (NOLOCK) ON a.[InventoryCode] = b.[PlayerInventoryCode] WHERE b.[PlayerID] = 146", inventoryList);

--- Other codes to connect to the database and so on ----


它将像这样生成:

SELECT * FROM (VALUES 'System.Collections.Generic.List`1[System.String]') AS InventoryList(InventoryCode) SELECT a.[InventoryCode] FROM [InventoryList] a INNER JOIN [PlayerAccount] b WITH (NOLOCK) ON a.[InventoryCode] = b.[PlayerInventoryCode] WHERE b.[PlayerID] = 146


我得到的错误是:


  例外:'System.Collections.Generic.List`1 [System.String]'附近的语法不正确。


编辑:

预期结果将为列名称创建InventoryList表,其中InventoryCode包含上述值:

InventoryList <-- table
InventoryCode <-- column name
1. a147
2. w150


然后,将像正常查询:

SELECT a.[InventoryCode] FROM InventoryList a INNER JOIN [PlayerAccount] ....

最佳答案

您在下面的预期SQL查询

SELECT * FROM (VALUES 'a147', 'w150') AS InventoryList(InventoryCode)
SELECT a.[InventoryCode] FROM [InventoryList] a INNER JOIN [PlayerAccount] b WITH (NOLOCK) ON a.[InventoryCode] = b.[PlayerInventoryCode] WHERE b.[PlayerID] = 146


有两个问题。第一个问题是,以下SELECT语句无效

SELECT * FROM (VALUES 'a147', 'w150') AS InventoryList(InventoryCode)


您会收到Incorrect syntax near 'a147'错误。它应包括()如下

SELECT * FROM (VALUES ('a147'), ('w150')) AS InventoryList(InventoryCode)


第二个问题,您不能在第二个SELECT语句中的InventoryList中执行SELECT,如下所示

SELECT a.[InventoryCode] FROM [InventoryList] a


因为InventoryList不是真实的表。您会收到Invalid object name 'InventoryList'错误。

您应该将两个语句合并如下

SELECT a.[InventoryCode]
FROM (SELECT * FROM (VALUES ('a147'), ('w150')) AS InventoryList(InventoryCode)) a
INNER JOIN [PlayerAccount] b WITH (NOLOCK)
    ON a.[InventoryCode] = b.[PlayerInventoryCode]
WHERE b.[PlayerID] = 146


现在我们进入使用C#生成上述查询的部分。如果inventoryList定义如下

List<string> inventoryList = new List<string>();

inventoryList.Add("a147");
inventoryList.Add("w150");


您可以像这样使用string.Join和Linq select的组合

string values = string.Join(",", inventoryList.Select(x => "('" + x + "')"));


产生以下输出:('a147'),('w150'),然后使用如下所示的values变量产生预期的SQL查询

string output = string.Format("SELECT a.[InventoryCode] FROM (SELECT * FROM (VALUES {0}) AS InventoryList(InventoryCode)) a INNER JOIN [PlayerAccount] b WITH (NOLOCK) ON a.[InventoryCode] = b.[PlayerInventoryCode] WHERE b.[PlayerID] = 146", values);


在线演示:https://dotnetfiddle.net/yLKsBv

关于c# - 从字符串sql列表中选择数据,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/41560647/

10-10 19:16