Click here to Skip to main content
14,422,431 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am getting below Sql Exception message while deleting 20200 rows.

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

I Changed my code and pass maximum 2000 parameters but I still got the error.


What I have tried:

I used linq skip and take extension method and try to pass limited numbers of parameters.

DELETE FROM dbo.MyTable 
WHERE identifier = @pIdentifier AND value in (@pFieldValues)


int parametersCount = fieldValues.Count;
int restrictedSize = 2000;
try
{
	int NumberOfTimesToExecuteDeleteCommand = (int)Math.Ceiling((double)parametersCount / restrictedSize);

	for (int i = 1; i <= NumberOfTimesToExecuteDeleteCommand; i++)
	{
		cmd.AddParametersWithValues("@pFieldValues", fieldValues.Skip((i - 1) * restrictedSize).Take(restrictedSize));
		cmd.ExecuteNonQuery();
	}
}
catch (Exception ex)
{
	string er = ex.Message;
}
Posted
Updated 5 days ago
Rate this:
Please Sign up or sign in to vote.

Solution 1

I don't have the necessary setup to test this at the moment, but I think that all of your existing parameters just remain part of the command after you call ExecuteNonQuery. That would mean that in the second loop iteration, you end up with 4000 parameters in the query. Try clearing all parameters before adding new ones.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

This is because, at each iteration of your for loop, you are adding a new parameter to the command.
You could try:
try
{
   int NumberOfTimesToExecuteDeleteCommand = (int)Math.Ceiling((double)parametersCount / restrictedSize);
   cmd.Parameters.Add("@pFieldValues", SqlDbType.<enter your desired type here>);
   for (int i = 0; i < NumberOfTimesToExecuteDeleteCommand; i++)
   {
      cmd.Parameters["@pFieldValues"].Value = fieldValues.Skip(i * restrictedSize).Take(restrictedSize));
      cmd.ExecuteNonQuery();
   }
}

Or you could issue a separate delete command for each value, eventually wrapped in a transaction.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 3

I am an anti-ORM person, and I personally would create a Stored Procedure to do this and pass in your "parameters" as a delineated string.

Without knowing what type the field values are in your program or database and what they may contain; I am going to base this off of a common type (string or numeric) and I am going to just use a comma as a deliminator

Here is a sample SQL Stored Procedure
CREATE PROCEDURE dbo.MyTable_Delete_ByIdentifierAndValues (
	@Identifier	INT, 			-- choose appropriate data type
	@FieldValues	NVARCHAR(8000),
	@Delineator	NCHAR(1) = ','
) AS 
BEGIN
	DELETE	MyTable
	WHERE	identifier = @Identifier
	AND	[value] IN ( SELECT value FROM STRING_SPLIT(@FieldValues, @Delineator);
END
GO
And here is the rough C# code to utilize it
SqlConnection conn = new SqlConnection(connectionstring);

string Delineator = ",";
int RowsAffected = -1;

SqlCommand cmd = new SqlCommand("dbo.MyTable_Delete_ByIdentifierAndValues", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Indetifier", /* identifier */);
cmd.Parameters.AddWithValue("@FieldValues", /* FieldValues.ToDelineatedString */ );
cmd.Paramaters.AddWithValue("Delineator", Delineator);

conn.Open();
RowsAffected = cmd.ExecuteNonQuery();
conn.Close();
   
Comments
istudent 5 days ago
   
Thank you. I learned something today.
MadMyche 5 days ago
   
What did not work? What is the content of the identifier, fields, and delineater?
Richard Deeming 4 days ago
   
For SQL Server, I'd be inclined to use a table-valued parameter instead of a delimited string:
Use Table-Valued Parameters (Database Engine) - SQL Server | Microsoft Docs[^]

Or possibly an XML or JSON string, depending on the SQL Server version.

Particularly if the field values aren't integers, and could possibly contain the delimiter character within a single value. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100