I simplified the case and write a sample code that pretty much done the same thing.
using System; using System.Data; using System.Data.SqlClient; using System.Threading.Tasks; namespace NS { class Program { static void Main(string[] args) { Parallel.For(0, 100, (i) => Run(i)); } static void Run(int i) { string strConn = "Data Source=(local);Initial Catalog=MyDB;Integrated Security=true"; using (SqlConnection conn = new SqlConnection(strConn)) { conn.Open(); SqlCommand cmd = new SqlCommand("sp_GetNext", conn); cmd.CommandType = CommandType.StoredProcedure; // Input param SqlParameter pInput = new SqlParameter("@in", SqlDbType.Int); pInput.Direction = ParameterDirection.Input; pInput.Value = 1; cmd.Parameters.Add(pInput); // Output param SqlParameter pOutput = new SqlParameter("@out", SqlDbType.Int); pOutput.Direction = ParameterDirection.Output; cmd.Parameters.Add(pOutput); cmd.ExecuteNonQuery(); Console.WriteLine(pOutput.Value); } } } }
The calling stored procedure is nothing special, it is ordinary ADO.NET routine to make a SP call. To simulate concurrent calls, I called Run() method by using Parallel.For(). This Parallel.For() method runs Run() method 100 times, almost concurrently. Depending on CPU cores, the actual number of worker threads can vary but it will simulate as many concurrent calls as possible from one machine. If test machine has single CPU or is a slow machine, probably the simulation might fail. But it gaves me decent level of simulation with my eight core machine.