An enumerated sequence of parameters (arrays, lists, etc.) in this context is not allowed in Dapper
I have the following code:
static void Main(string[] args){
string sql= "SELECT * FROM Posts WHERE 1=1 ";
SqlParameter[] @params= SetDynamicParameter(ref sql, "Param=Value", "Param2=Value2", "ParamN=ValueN");
IDbConnection connection = new SqlConnection(connectionString);
IEnumerable<Posts> en = connection.Query<Posts>(sql,param:@params);
Console.ReadKey(true);
}
static SqlParameter[] SetDynamicParameter(ref string sql,params string[] sarr) {
string condition = string.Empty;
List<SqlParameter> list = new List<SqlParameter>();
foreach (var item in sarr)
{
condition += " AND "+item.Split('=')[0] + "=@" + item.Split('=')[0];
list.Add(new SqlParameter("@" + item.Split('=')[0], item.Split('=')[1]));
}
sql += condition;
return list.ToArray() ;
}
Output error: An enumerated sequence of parameters (arrays, lists, etc.) is not valid in this context.
How can it be? Is there an equivalent solution?
source to share
If you use ExecuteNonQuery
instead Query<>
, you will no longer get this exception. But another problem will arise when using Query in Oracle.
My solution to this problem was to use a staging table. I used Oracle; Oracle has a special "dual" table that sits under the schema sys
. This table inspired me and I created another table under my custom schema.
CREATE TABLE USER_SCHEMA.DUAL2
(
DUMMY VARCHAR2(1000 BYTE)
)
NOCOMPRESS
NOCACHE
NOPARALLEL;
You can increase the length if you like. Gave some grants to the table. Then I changed my query from select to insert for the select statement.
INSERT INTO USER_SCHEMA.DUAL2
(SELECT ID
FROM ANOTHER_SCHEMA.SOME_TABLE
WHERE SOME_FLAG = 'M' AND NO IN :NO)
This is because ADO.NET cannot return a result from ExecuteNonQuery
. By default, it does requests SELECT
but returns -1 for each request. I simulate INSERT
by providing a request SELECT
. This returns the result.
var parameters = noSplit.Select(x => new { NO = x, SOME_FLAG = flag }).ToList();
var queryResult = dbConnection.Execute(insertSelectQuery, parameters, transactionIfExists);
if (queryResult != parameters.Count)
{
throw new Exception("Can not find some no");
}
source to share