我想从已经定义的字符串列表中选择数据,然后在某些条件下基于该列表进行过滤。
但是当我尝试下面的代码时:
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/