c# SQL多入选择器

本文关键字：选择器 SQL | 更新日期: 2023-09-27 18:01:52

我的输出查询如下:

select * from Calls where CallerID = "User.Name" and Endpoint in ("C,D,V") order by JoinTime desc;

我的c#查询字符串与参数看起来像这样:

string SQLQuery = "select * from Calls where CallerID = @UserName and EndpointType in ({0}) order by JoinTime desc";

添加参数的代码如下所示:

...
string[] Endpoints = new string[] {"C","D","V"}; //EXAMPLE string array
string UserNameStr = "User.Name"; //EXAMPLE string UserNameStr value
...
string[] paramNames = Endpoints.Select((s, i) => "@endpoint" + i.ToString()).ToArray();
string inClause = string.Join(",", paramNames);

using (cmd = new MySqlCommand((string.Format(SQL, inClause)),connection))
{
    for (int i = 0; i < paramNames.Length; i++)
    {
        cmd.Parameters.AddWithValue(paramNames[i], Endpoints[i]);
    }
}
cmd.Parameters.Add("@UserName", MySqlDbType.String).Value = UserNameStr;
MySqlDataReader dataReader = cmd.ExecuteReader(); 
...

但是，如果我想添加另一个IN操作符，使输出查询看起来像这样呢? 

select * from Calls where CallerID = "User.Name" and Endpoint in ("C","D","V") and ConferenceCall in ("D","C") order by JoinTime desc;

我该怎么做?Linq中是否有一些可以使用的特性?

c# SQL多入选择器

和第一次一样:

// endpoints = comma-delimited list of endpoints
// conferenceCalls = comma-delimited list of conference calls
string SQLQuery = "select * from Calls " + 
                  "where CallerID = @UserName and " + 
                  "EndpointType in ({0}) and " + 
                  "ConferenceCall in ({1}) " + 
                  "order by JoinTime desc";
cmd = new MySqlCommand((string.Format(SQL, endpoints, conferenceCalls)),connection);

如注释所述，要非常小心地验证字符串以避免SQL注入攻击。没有直接的方法将集合作为SQL参数传递。有很多方法可以做到这一点(传递一个带分隔符的字符串并解析它是我见过的最常见的方法)，但如果您可以控制设置值并验证数据，那么您应该没问题。

相关文章：