CODEWITHSUNDEEP
c#sql-serverlistparameter-passing
maliks
maliks

Reputation: 1112

Passing List<int> as Query Parameter to SQL Server

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!

Upvotes: 2

Views: 8154

Answers (3)

Pratik
Pratik

Reputation: 17

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

Upvotes: 0

Akash Kava
Akash Kava

Reputation: 39956

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

Upvotes: 5

Ravi Kumar Mistry
Ravi Kumar Mistry

Reputation: 1103

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

Upvotes: -1

Related Questions