CODEWITHSUNDEEP
c#sql-server-2008
user3846317
user3846317

Reputation:

Command.ExecuteNonQuery() is hanging up

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;
            }
        }

Upvotes: 1

Views: 5404

Answers (3)

Goren
Goren

Reputation: 91

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.

Upvotes: 0

Cam Bruce
Cam Bruce

Reputation: 5689

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.

Upvotes: 4

Joel Coehoorn
Joel Coehoorn

Reputation: 416101

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:

  1. 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.
  2. 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.
  3. 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.

Upvotes: 2

Related Questions