Passing List<int> 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
– huysentruitw, Commented Feb 25, 2016 at 5:50
Have you tried _Node_Neighbor.ToArray() ? I'm pretty sure parameters support arrays. — Jonathan Carroll
– Jonathan Carroll, Commented Feb 25, 2016 at 5:53
Why not just directly format your query instead of using parameters? string.Format("...WHERE Source_Node IN ('{0}')...", string.Join("','", _Node_Neighbor)) — Jonathan Carroll
– Jonathan Carroll, Commented Feb 25, 2016 at 6:00
In query you have give @_Node_Neighbor as parameter, but while Adding with value you are passing @Source_Node as parameter name — Olivarsham
– Olivarsham, Commented Feb 25, 2016 at 6:05
You can use TVP instead of passing with IN clause. Check this question stackoverflow.com/questions/20143012/… and this answer stackoverflow.com/a/20143173/311255 — SelvaS
– SelvaS, Commented Feb 25, 2016 at 6:49

Akash Kava · Accepted Answer · 2016-02-25 07:11:17Z

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, 2016 at 7:11

answered Feb 25, 2016 at 7:03

Akash Kava

40.1k21 gold badges126 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonathan Carroll Over a year ago

Just an FYI, you should be using string.Format in your query instead of concatenating paramNames to avoid sql injection.

Akash Kava Over a year ago

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

Hari Prasad · Accepted Answer · 2016-02-25 07:22:42Z

0

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

edited Feb 25, 2016 at 7:22

Hari Prasad

17k4 gold badges23 silver badges35 bronze badges

answered Feb 25, 2016 at 6:50

Pratik

173 bronze badges

1 Comment

TT. Over a year ago

It would be good to expand on that answer. The way it is now, it's nothing more than a comment.

Ravi Kumar Mistry · Accepted Answer · 2016-02-25 06:53:56Z

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

Collectives™ on Stack Overflow

Passing List<int> as Query Parameter to SQL Server

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related