Passing List as Query Parameter to SQL Server

Question

I have to pass a list to a SQL Server query using C#. My code is here:

using (SqlDataReader _myReader_2 = _myCommand_3.ExecuteReader())
{
    _Node_Neighbor.Clear();
        while (_myReader_2.Read())
        {
            _Node_Neighbor.Add(Convert.ToInt32(_myReader_2["Target_Node"]));
        }

    _myReader_2.Close();

  //Here I have to pass this _Node_Neighbor i.e. of type List<int> to another  
  //SQL Server query as:

    try
        {
            SqlCommand _myCommand_4 = _con.CreateCommand();

            _myCommand_4.CommandText = @"SELECT COUNT(*) FROM GraphEdges
                                         WHERE Source_Node IN @Source_Node 
                                         AND Target_Node IN @Target_Node";

            _myCommand_4.Parameters.AddWithValue("@Source_Node", _Node_Neighbor);
            _myCommand_4.Parameters.AddWithValue("@Target_Node", _Node_Neighbor);

            _Mutual_Links = Convert.ToInt32(_myCommand_4.ExecuteScalar());
        }
        catch(Exception e)
        {
            Console.WriteLine(e.Message);
        }
}

Whereas, to pass a list as a parameter, I think there might be some other way that's why I'm getting this error as: No mapping exists from object type Systems.Collections.Generic.List

Thanks!

You'll need to use the IN() syntax. But I'm afraid that doesn't work with parameters. — huysentruitw, Feb 25 '16 at 05:50
I'm not 100% sure on this so don't quote me on it but I _think_ there is a way to do it via stored procedures. Could be wrong. You'd have to research that yourself as I've never done it myself — Gabe, Feb 25 '16 at 05:52
Have you tried `_Node_Neighbor.ToArray()` ? I'm pretty sure parameters support arrays. — Jonathan Carroll, Feb 25 '16 at 05:53
@JonathanCarroll I have tried your's suggestion but got an error as: `No mapping exists from object type System.Int32[] to a known managed provider native type.` — maliks, Feb 25 '16 at 05:56
Why not just directly format your query instead of using parameters? `string.Format("...WHERE Source_Node IN ('{0}')...", string.Join("','", _Node_Neighbor))` — Jonathan Carroll, Feb 25 '16 at 06:00
@JonathanCarroll I have all of these values in integer format — maliks, Feb 25 '16 at 06:01
Use string.join to convert the list to csv format and then your query will work e.g. select * from dummy where id in () — Mahesh Malpani, Feb 25 '16 at 06:03
In query you have give @_Node_Neighbor as parameter, but while Adding with value you are passing @Source_Node as parameter name — Olivarsham, Feb 25 '16 at 06:05
@MaheshMalpani I have to select _Node_Neighbor list each time from database that's why I can't give string as a format — maliks, Feb 25 '16 at 06:18
You can use TVP instead of passing with IN clause. Check this question http://stackoverflow.com/questions/20143012/sqlparameter-and-in-statement and this answer http://stackoverflow.com/a/20143173/311255 — SelvaS, Feb 25 '16 at 06:49
Possible duplicate of [Adding multiple parameterized variables to a database in c#](http://stackoverflow.com/questions/31965233/adding-multiple-parameterized-variables-to-a-database-in-c-sharp) — Zohar Peled, Feb 25 '16 at 06:55

Akash Kava · Accepted Answer · 2016-02-25T07:11:17.343

5

In order to pass array/list for IN, you have to create one parameter for each value in the list.

try
    {
        SqlCommand _myCommand_4 = _con.CreateCommand();


        List<string> sqlParams = new List<string>();
        int i = 0;
        foreach(var value in _Node_Neighbor){
            var name = "@p"  + i++;
            _myCommand_4.Parameters.Add(name,value);
            sqlParams.Add(name);
        }

        string paramNames = string.Join(",", sqlParams);

        _myCommand_4.CommandText = "SELECT COUNT(*) FROM GraphEdges"
                       " WHERE Source_Node IN (" + paramNames + ") " 
                         " AND Target_Node IN (" + paramNames + ")";


        _Mutual_Links = Convert.ToInt32(_myCommand_4.ExecuteScalar());
    }
    catch(Exception e)
    {
        Console.WriteLine(e.Message);
    }

edited Feb 25 '16 at 07:11

answered Feb 25 '16 at 07:03

Akash Kava

39,066
20
121
167

Just an FYI, you should be using string.Format in your query instead of concatenating `paramNames` to avoid sql injection. – Jonathan Carroll Feb 25 '16 at 17:17
1

@JonathanCarroll, `i` is integer, unnecessary string.Format is burden for simple concatenation. Sql Injection occurs only when you append non value type in sql string. There is no way in this world this can cause SQL Injection. In other answer, where values could be of non value type, that may lead to SQL Injection as they come from outside of the code. – Akash Kava Feb 26 '16 at 07:03

score 0 · Answer 2 · edited Feb 25 '16 at 07:22

0

Use User defined table type in sql server and datatable in ADO.NET code.

edited Feb 25 '16 at 07:22

Hari Prasad

16,716
4
21
35

answered Feb 25 '16 at 06:50

Pratik

17
3

4

It would be good to expand on that answer. The way it is now, it's nothing more than a comment. – TT. Feb 25 '16 at 06:56

score -1 · Answer 3 · answered Feb 25 '16 at 06:53

Build the dynamic query in c# if array is not large

using (SqlDataReader _myReader_2 = _myCommand_3.ExecuteReader())
        {
            _Node_Neighbor.Clear();
            while (_myReader_2.Read())
            {
                _Node_Neighbor.Add(Convert.ToInt32(_myReader_2["Target_Node"]));
            }

            _myReader_2.Close();

            //Here I have to pass this _Node_Neighbor i.e. of type List<int> to another  
            //SQL Server query as:

            try
            {
                var query = @"SELECT COUNT(*) FROM GraphEdges
                WHERE Source_Node IN

                (##Source_Node)

                AND Target_Node IN 
                (##Target_Node)";
                var sourceNode = "";
                foreach (var item in _Node_Neighbor)
                {
                    sourceNode += item + ",";
                }

                sourceNode = sourceNode.TrimEnd(',');

                var targetNode = "";
                foreach (var item in _Node_Neighbor)
                {
                    targetNode += item + ",";
                }

                targetNode = targetNode.TrimEnd(',');

                query = query.Replace("##Source_Node", sourceNode).Replace("##Target_Node", targetNode);

                SqlCommand _myCommand_4 = _con.CreateCommand();

                _myCommand_4.CommandText = @"SELECT COUNT(*) FROM GraphEdges
                                     WHERE Source_Node IN @Source_Node 
                                     AND Target_Node IN @Target_Node";


                _Mutual_Links = Convert.ToInt32(_myCommand_4.ExecuteScalar());
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

Passing List as Query Parameter to SQL Server

3 Answers3

Linked