1

I would like to create partially ordered parallel SqlCommand queue but I am not sure about one thing.

I need to consider situation when SqlCommand fails and it needs to be re-executed.

consider following code: 

SqlCommand myCommand = new SqlCommand();
myCommand.Parameters.AddWithValue("adsf", someVal);
myCommand.CommandType = CommandType.StoredProcedure;
while(myCommand.ExecuteNonQuery() <= 0) {
    log fail;
    wait some time;
}

Will be SqlCommand in correct state all tries? Or should I always create new SqlCommand and fill it with same parameters?

Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
Snurka Bill
  • 973
  • 4
  • 12
  • 29
  • If nothing changes in the parameters you want to pass and the sql connection is also still open you can re execute the `ExecuteNonQuery` method. – Igor Nov 22 '16 at 18:11
  • 2
    The command object's state will be fine. The database state, not necessarily so. – GSerg Nov 22 '16 at 18:12
  • Would you mind describing `partially ordered parallel SqlCommand queue`? – Am_I_Helpful Nov 22 '16 at 18:12
  • The return value of `ExecuteNonQuery` is `-1` only when the type of statement is `select` or rollback occurs. So the `while(myCommand.ExecuteNonQuery() <= 0)` is not checking if `SqlCommand` fails. Read more here http://stackoverflow.com/a/38060528/2946329 – Salah Akbari Nov 22 '16 at 18:39
  • This can produce an infinite loop, you should add a counter at least. And you should check @RETURN_VALUE instead of the result of `ExecuteNonQuery()`. – McNets Nov 22 '16 at 18:52

1 Answers1

1

According to MSDN

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

To summarize, if none of your rows are affected by your stored procedure, you're going to return a value of 0

If you have SET NOCOUNT ON in your stored procedure or a rollback occurs, you will always get a return value of -1

If you want to see if an error has occurred, you would need to put a try catch in your method. Obviously here I'm catching a generic exception. You would want to catch the specific exception and wrap the opening and the closing of your connection in a using statement to dispose of the connection when a failure occurs. 

int retryCount = 0;
        using (SqlCommand myCommand = new SqlCommand("MyStoredProcedure", new SqlConnection("Server=.;Database=MyDatabase;Trusted_Connection=True;")))
        {
            while (retryCount < 10)
            {
                try
                {
                    if (myCommand.Connection.State == ConnectionState.Closed)
                    {
                        myCommand.Connection.Open();
                    }

                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    retryCount++;
                    System.Threading.Thread.Sleep(1000);
                }
            }
        }
Jesse Petronio
  • 693
  • 5
  • 11