Parallel Execution of SQL in ADO.Net on C# - SQL Parameter is already contained

• Weegee71

  SSChasing Mays

  Points: 607

  More actions

  June 17, 2022 at 10:45 am

  #4050907

  Hi there

  Im not sure if this should be asked on a C# forum, but im taking a shot here. We have a C# Application which using ADO.Net , executes SQL Stored procedures.

  In  fact the application calles SQL stored procedures in Parallel.

  The issue we have is trying to achieve Parallel execution in c#

  When we start up the application,  we get the following error message

  System.ArgumentException: 'The SqlParameter is already contained by another SqlParameterCollection.'

  (Please see attached screenshot)

  The thing is from the SQL profile trace , i can see that the actual stored procedure spSetChannelReading has completed execution...

  We put a lock in c# but this leads to single threading which is slow and not desirable.

  So my question is that how do you do  parrallel execution of stored procedures in a C# application without encountering this error?

   

   

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  June 17, 2022 at 10:57 am

  #4050913

  That looks more like a C# error than a SQL issue.

  A brief Google search suggested this.

  ---

• Weegee71

  SSChasing Mays

  Points: 607

  More actions

  June 17, 2022 at 2:48 pm

  #4051029

  Thanks Phil. Yeah I saw that article and tried cmd.Parameters.Clear  but to no avail.

   

• Chris Wooding

  SSCarpal Tunnel

  Points: 4590

  More actions

  June 20, 2022 at 11:39 am

  #4052106

  I think the problem is that you're trying to use the same parameter for two procedures that are running concurrently. You'll need to have a separate set of parameters for each procedure; you can set multiple parameters to the same value, but you can't share the parameters themselves.