传递列表< int>作为SQL Server的查询参数 [英] Passing List<int> as Query Parameter to SQL Server

问题描述

我必须使用C#将列表传递给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);
        }
}  

因此，要传递列表作为参数，我认为可能还有其他一些方式，这就是为什么我收到此错误的原因: No mapping exists from object type Systems.Collections.Generic.List

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

谢谢！

推荐答案

为了传递IN的数组/列表，必须为列表中的每个值创建一个参数.

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

这篇关于传递列表< int>作为SQL Server的查询参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！