ASP.NET Execute SQL Server stored procedure multiple times async

Question

I am calling a stored procedure via ASP.NET now I am trying to call it 200 times async, I am trying to do this by adding a transaction, however its not working out, here is my code:

try
{
    using (connection = new SqlConnection(connectionString))
    {
        connection.Open();
        transaction = connection.BeginTransaction();

        for (int i = 0; i < 200; i++)
        {
            using (SqlCommand command = new SqlCommand("TimeSlotAppointments", connection))
            {
                command.Transaction = transaction;

                command.CommandType = CommandType.StoredProcedure;

                SqlParameter parameter1 = command.Parameters.Add("@StartTime", SqlDbType.DateTime);
                parameter1.Direction = ParameterDirection.Input;
                parameter1.Value = DateTime.Now;

                command.ExecuteNonQuery();
            }
        }

        transaction.Commit();
    }
}
catch (SqlException e)
{
    Console.Write(e);
    transaction.Rollback();
}
finally
{
    connection.Close();
    connection.Dispose();
}

I am passing the current date and time as a parameter and when I check out the results in SQL Server I am expecting the @StartTime to be the same, but they are not, close, but the milliseconds increase for each record, am I going about this the wrong way? What I am trying to accomplish is executing the store procedure 200 times simultaneously.

If you want the DateTime to be the same for all records then store the DateTime.Now in a variable once outside the loop and use it for parameter1.Value. — Rick S
– Rick S, Commented Apr 4, 2017 at 15:31
why would you expect @StartTime to be the same, when you call DateTime.Now 200 times? time passes ... However: what is this code actually trying to do? it seems unlikely that you'd want to run the exact same SQL with the same parameters - 200 times. Also, you're not actually calling it async ("now I am trying to call it 200 times async") — Marc Gravell
– Marc Gravell, Commented Apr 4, 2017 at 15:33
I have a feeling this is an XY Problem. meta.stackexchange.com/questions/66377/what-is-the-xy-problem — Sean Lange
– Sean Lange, Commented Apr 4, 2017 at 15:34
What does a transaction have to do with async? A transaction is used to do the opposite. Force multiple concurrent commands to run in sequence — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 4, 2017 at 15:39
If you want to execute the same sproc N times, create the sproc once then change the parameter value N times: for (i=0;i<200;i++){parameter1.Value=someNewValue;cmd.ExecuteNonQuery();}. A better solution is to avoid the loop entirely. What does the sproc do and why don't you pass all the values you need at once? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 4, 2017 at 15:41

Sparrow · Accepted Answer · 2017-04-04 16:02:49Z

The start time value is different because you are assigning the value inside the loop and in every iteration, the time has changed (a few milliseconds as you mentioned). If you want to use the same value for all calls, then you need to store the time stamp outside the loop in a variable and use that value in your loop. This is how your code should look like:

try
{
    using (connection = new SqlConnection(connectionString))
    {
        connection.Open();
        transaction = connection.BeginTransaction();
        var startTime = DateTime.Now; // I added this line 

        for (int i = 0; i < 200; i++)
        {
            using (SqlCommand command = new SqlCommand("TimeSlotAppointments", connection))
            {
                command.Transaction = transaction;

                command.CommandType = CommandType.StoredProcedure;

                SqlParameter parameter1 = command.Parameters.Add("@StartTime", SqlDbType.DateTime);
                parameter1.Direction = ParameterDirection.Input;
                parameter1.Value = startTime;  // I changed this line

                command.ExecuteNonQuery();
            }
        }

        transaction.Commit();
    }
}
catch (SqlException e)
{
    Console.Write(e);
    transaction.Rollback();
}
finally
{
    connection.Close();
    connection.Dispose();
}

Collectives™ on Stack Overflow

ASP.NET Execute SQL Server stored procedure multiple times async

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related