如何在查询中插入多个整数参数? [英] How to insert multiple integer parameters into query?

问题描述

网站用户可以输入搜索条件来查询订单.用户，州，状态，订单ID等

Website user can enter search criteria to query orders. User, States, Status, OrderID, etc.

网站与API通信.查询参数位于标题中，因此我假设它们以字符串形式出现. API通过Dapper与Access进行通信.

Website communicates with API. Query parameters are in the header, so I assume they come in as strings. API communicates with Access via Dapper.

对于某些条件，他们可以发送多个值.所以我想使用"IN"子句.

For some criteria, they can send multiple values. So I want to use an "IN" clause.

where UserID in (150, 3303, 16547)

Dapper处理得很好.

Dapper handles this nicely.

connection.Query<int>("select * from table where Id in @Ids", new { Ids = new int[] { 1, 2, 3 } });

这在MS-Access中有效

This works in MS-Access

SELECT top 100 * from Orders where UserID in (150, 30330)

但这仅在值是int时有效.在Access中，字符串和字符串都给出条件表达式中的数据类型不匹配".

But that only works when the values are ints. String and Strings both give "Data type mismatch in criteria expression" in Access.

SELECT top 100 * from Orders where UserID in ("150", "30330")  // two strings
SELECT top 100 * from Orders where UserID in ("150, 30330")  // single string

这可能是一个巧合，但是我看到的所有示例都是整数.如果不指定大小，Access会在字符串上引发错误.使用DynamicParameters可以轻松指定大小.
但是，当字段为int时，我的精简程序代码会给出相同的错误(条件表达式中的数据类型不匹配):

It may be a coincidence, but all the examples I see are integers. Access throws an error on strings if you don't specify the size. Using DynamicParameters makes it easy to specify the size.
But when the field is an int, my dapper code gives the same error (Data type mismatch in criteria expression):

var paramlist = new DynamicParameters();
if ((!string.IsNullOrWhiteSpace(userId)) && userId != "0") {
    paramlist.Add("userId", userId, DbType.String, ParameterDirection.Input, 50);                
    sbWhere.AppendFormat("AND CustFID in (?) ", paramIndex++);
}

所以我认为问题是我告诉它参数是一个字符串.

So I assume the issue is that I'm telling it that the parameter is a string.

但是，如果我将参数设为int，则它将不会采用具有多个值的字符串.相反，如果我在字符串中包含()，它会抱怨'in'子句中缺少括号.

But if I make the parameter an int, then it won't take the string with multiple values. Conversely, if I include the () in the string, it complains about the parens being missing from the 'in' clause.

我尝试将数字字符串拆分为数组和/或列表.

I tried splitting the string of numbers into an array and/or list.

if ((!string.IsNullOrWhiteSpace(userId)) && userId != "0") {
    var userIds = userId.Split(',');  //.ToList(); fails, can't map to native type
    paramlist.Add("userId", userIds, DbType.String, ParameterDirection.Input, 1000);
    if (userIds.Length > 1) {
        sbWhere.AppendFormat("AND CustFID in @userId ", paramIndex++);
    } else {
        sbWhere.AppendFormat("AND CustFID = @userId ", paramIndex++);                
    }
}

，它给出:从对象类型System.String []到已知的托管提供程序本机类型不存在映射."我说的参数是int32还是字符串.

and it gives ": No mapping exists from object type System.String[] to a known managed provider native type." whether I say the parameters are int32 or string.

更新: 可能有多个搜索条件，所以我正在使用DynamicParameters.
这是我实现Palle Due想法的尝试.

UPDATE: There may be multiple search criteria, so I'm using DynamicParameters.
Here is my attempt at implementing Palle Due's idea.

if ((!string.IsNullOrWhiteSpace(userId)) && userId != "0") {
//    var userIds = userId.Split(',').Select(i => Int32.Parse(i)).ToList();// fails, can't map to native type
   IEnumerable<int> userIds = userId.Split(',').Select<string, int>(int.Parse);
   paramlist.Add("userId", userIds, DbType.Int32, ParameterDirection.Input);
   if (userIds.Count() > 1) {
      sbWhere.AppendFormat("AND CustFID in @userId ", paramIndex++);
   } else {
      sbWhere.AppendFormat("AND CustFID = @userId ", paramIndex++);                
   }
}

using (IDbConnection conn = Connection) {
   string sQuery = string.Format("SELECT {0} FROM vwweb_Orders {1}", columns, where);
   conn.Open();
   var result = await conn.QueryAsync<Order>(sQuery, paramlist);
   return result.ToList();
}

投掷

Message: System.AggregateException : One or more errors occurred. (Failed to convert parameter value from a SelectArrayIterator`2 to a Int32.)
----> System.InvalidCastException : Failed to convert parameter value from a SelectArrayIterator`2 to a Int32.
----> System.InvalidCastException : Object must implement IConvertible.

推荐答案

我放弃了. Dapper应该能够处理此问题，但这是一个较新的功能，所以...
我只是自己构建了IN子句.

I give up. Dapper should be able to handle this, but it's a newer feature, so...
I just built the IN clause myself.

if (userIds.Count() > 1) {
    sbWhere.AppendFormat("AND CustFID in ( ");
    int paramCnt = 0;
    foreach (int id in userIds) {
        sbWhere.AppendFormat("?, ");  // Access doesn't mind a trailing ,
        paramlist.Add("userId" + paramCnt.ToString(), id, DbType.Int32, ParameterDirection.Input);
        paramCnt++;
    }
    sbWhere.AppendFormat(") ");
} else {
    sbWhere.AppendFormat("AND CustFID = ? ");
    paramlist.Add("userId", userIds.ToArray<int>()[0], DbType.Int32, ParameterDirection.Input);
}

这篇关于如何在查询中插入多个整数参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！