Dapper SQL Server Update fails with error "The incoming request has too many parameters. The se-CodePudding

I am getting the below error when using dapper SQL Server update execution.

The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request

Code:

IEnumerable<EmployeeModel> objReturn = null;

var employeeQuery = @"Select employee_id,employee_name from DBO.Employee where IsActive in ('Y')";

this.EmplDBConnection.open();
var employeeList = await. this.EmplDBConnection.QueryAsync<EmployeeModel>(employeeQuery);
    
EmplDBConnection.Execute("Update DBO.employee set IsActive = @Employee_Status where employee_id in @Employee_Id,
    new {@Employee_Status = IsActive, Employee_Id = employeeList.Select(x => x.employee_id});
this.EmplDBConnection.Close();

CodePudding user response：

I think you mean:

employeeList.Select(x=> new {
    Employee_Status = IsActive,
    Employee_Id = x.employee_id})

as the args parameter. Dapper will unroll the generated sequence and issue one command per employee.

Note, however, that a single in query may be more performant. But since you seem to be getting the input from the DB: it feels like you can do all of this without even fetching anything. maybe just an execute, without a query, something like:

update DBO.Employee
set IsActive = -- your input needed here
where IsActive = 'Y'