Command.ExecuteNonQuery() is hanging up

Question

When I am debugging the stored procedure it works but my C# code hangs while executing the same stored procedure vai Command.ExecuteNonQuery();

When it stuck I can not use select command also on respective tables.

Can you please guys help me why my Command.ExecuteNonQuery(); is hanging up.

Here are the some code snippets

 using (command = manager.CreateStoredProcCommandWrapper(
            "UpdatePayrollStagingWithTaxYearID",
            new Object[] { HttpContext.Current.Session.SessionID, taxyearID }))
            {
                manager.ExecuteNonQuery(command);
            }


          public void ExecuteNonQuery(NTTGDBCommandWrapper commandWrapper)
        {
            NTTGArgumentValidation.CheckForNullReference(commandWrapper, "commandWrapper");
            Stack trans = GetTransactions();
            if (trans.Count > 0)
            {
                GetDatabase().ExecuteNonQuery(commandWrapper.InnerCommand, (IDbTransaction)trans.Peek());
            }
            else
            {
                GetDatabase().ExecuteNonQuery(commandWrapper.InnerCommand);
            }
        }

        protected Stack GetTransactions()
        {
            Stack transactionStack = (Stack)CallContext.GetData(this._contextIdentifierForTransactions);
            if (transactionStack == null)
            {
                transactionStack = new Stack();
                CallContext.SetData(this._contextIdentifierForTransactions, transactionStack);
            }

            return (transactionStack);
        }

        public virtual void ExecuteNonQuery(DBCommandWrapper command, IDbTransaction transaction)
        {
            PrepareCommand(command, transaction);
            DoExecuteNonQuery(command);
        }

         private void DoExecuteNonQuery(DBCommandWrapper command)
        {
            try
            {
                DateTime startTime = DateTime.Now;
                command.RowsAffected = command.Command.ExecuteNonQuery();
                this.instrumentation.CommandExecuted(startTime);
            }
            catch
            {
                this.instrumentation.CommandFailed(command.Command.CommandText, ConnectionStringNoCredentials);
                throw;
            }
        }

Can you post your data access code & sql? Just saying `Command.ExecuteNonQuery()` hangs isn't going to help. — Cam Bruce, Aug 07 '14 at 17:58
Did you try running SQL profiler while testing from C# to see what command is actually being executed on your sql server? — Tab Alleman, Aug 07 '14 at 18:03
Have you checked sp_who2 to make sure your process isn't being blocked by another process locking the table? — Dan, Aug 07 '14 at 18:10
Even with the edit, we don't see how parameters are attached to the sp call. — Joel Coehoorn, Aug 07 '14 at 18:15

score 4 · Answer 1 · answered Aug 07 '14 at 18:08

It looks like you are using transactions.

In your DoExecuteNonQuery() method, it doesn't look like you are calling IDbTransaction.Commit() or IDbTransaction.Rollback() when your sql succeeds or fails. Not doing this will cause SQL Server to hold on to any locks until the transaction is closed one way or the other, and would definitely cause ExecuteNonQuery() to hang.

score 2 · Accepted Answer · edited May 23 '17 at 12:01

You omitted the most important part of what we need to know to debug this. However, you did leave one important clue:

When it stuck I can not use select command also on respective tables.

That tells me that the query is running, and that it just takes too long. This at least allows me to suggest a few things to try:

If you're using parameterized queries (and you should be), you may have a situation where Sql Server is caching sub-optimal execution plans based on the first set of parameter values. Read this question on OPTION RECOMPILE and sp_updatestats to help address this.
If you're using parameterized queries, and use the AddWithValue() function, you can end up in a situation where .Net infers the wrong database types from your parameters, and as a result Sql Server has to do per-row conversions and can't use good indexes. Instead, use the Add() overload that expects a specific database type.
If you're not using query parameters, you need to start. Security issues aside (and they are signicant), query parameters are better because they allow Sql Server to better cache query plans and (issues above aside) produce better execution plans, resulting in faster queries.

But when I am trying to debug my SP with same values it runs perfectly. — , Aug 07 '14 at 18:10
@SharadSingh How you make the call matters. As an example, if the stored procedure calls for varchar parameters, and you use AddWithValue() to set this up, you can end up calling the procedure with an nvarchar value, and in the wrong circumstances the procedure will do per-row conversions and not use indexes. Same procedure, same values, **hugely** different execution times. — Joel Coehoorn, Aug 07 '14 at 18:17

score 0 · Answer 3 · answered Jan 20 '20 at 09:52

I personnaly resolved this issue by adding parameters to my command instead of concatenate strings.

command.Parameters.Add(new OracleParameter("@pParameter", pParamValue));

or

command.Parameters.Add(new SqlParameter("@pParameter", pParamValue));

...

Hope this can help.

Command.ExecuteNonQuery() is hanging up

3 Answers3