Select data from the list of string sql

Question

I wanted to select the data from the list of string that already being defined, and later on will filter based on that list with some conditions.

But while I am trying the below code:

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 ----

It will generate it like this:

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

and the error that I am getting is:

Exception: Incorrect syntax near 'System.Collections.Generic.List`1[System.String]'.

EDIT:

Expected result will create the InventoryList table with InventoryCode for the column name with the above values inside:

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

Then, will be query like normal:

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

Hi @ekad and @Mohid Shrivastava, the query should look like this: SELECT * FROM (VALUES 'a147', 'w150') AS InventoryList(InventoryCode) then, after the table InventoryList with the column name InventoryCode have the following data: a147 and w150, then I will query the result with another table. Thanks — Reinhardt
– Reinhardt, Commented Jan 10, 2017 at 3:32
inventoryList should be String.Join("','",inventoryList.ToArray()) — bansi
– bansi, Commented Jan 10, 2017 at 3:34

ekad · Accepted Answer · 2017-01-10 04:28:53Z

Your expected SQL query below

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

has two problems. The first problem is, the following SELECT statement is invalid

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

You'll get Incorrect syntax near 'a147' error. It should include ( and ) as below

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

The second problem, you can't do SELECT from InventoryList in the second SELECT statement as below

SELECT a.[InventoryCode] FROM [InventoryList] a

because InventoryList isn't a real table. You'll get Invalid object name 'InventoryList' error.

You should combine both statements as below

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

Now we get to the part of generating the above query using C#. If you have inventoryList defined as below

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

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

You can use a combination of string.Join and Linq select like this

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

to produce the following output: ('a147'),('w150'), then use the values variable as below to produce the expected SQL query

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);

Online demo: https://dotnetfiddle.net/yLKsBv

Thank you @ekad. Well explanation and it is helpful for me to understand more about it and it was already solved. Thanks to you :)

Balaji Marimuthu · Accepted Answer · 2017-01-10 03:40:54Z

1

You need to use the string.format method as below.

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

You could use string.join string.Join("','", inventoryList.ToArray()) for n number of items

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

edited Jan 10, 2017 at 3:40

answered Jan 10, 2017 at 3:35

Balaji Marimuthu

2,06814 silver badges14 bronze badges

2 Comments

bansi Over a year ago

that will restrict to just 2 items in the list

Balaji Marimuthu Over a year ago

@bansi thanks for notified that. i've updated the code.

Collectives™ on Stack Overflow

Select data from the list of string sql

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related