C# .NET - Connection is closing after execution "ExecuteNonQuery"

Asked By Rajendra Reddy on 07-Jan-14 07:51 AM
Hello Techies,

I have method where I am trying to delete data from few tables. Tables have huge data, so time out exception we get.
But in my case I get timeout exception after executing "deleteCommand.ExecuteNonQuery() "and Connection getting closed.

What could be the reason for this behavior, Below is my method where I am facing this issue.

public void DeleteRecord()
{                  
   try
   {
     if (connection.State != ConnectionState.Open) 

connection.Open(); 
}                    

                    string deleteString = @"
                                        DELETE FROM Table1
                                        DELETE FROM Table2
                                        DELETE FROM Table3
                                        DELETE FROM Table4
                                        DELETE FROM Table5"

                    dbTransaction = connection.BeginTransaction();

                    using (SqlCommand deleteCommand = new SqlCommand(deleteString, connection, dbTransaction))
                    {
                        deleteCommand.ExecuteNonQuery();                    
                    }

                    dbTransaction.Commit();
   }
   catch (Exception x)
   {
                    if (connection.State != ConnectionState.Closed && dbTransaction != null) 
   {  
dbTransaction.Rollback(); 
   }                    
                    throw new Exception("Could not purge record", x);
  }          
  finally
  {
      if (connection.State != ConnectionState.Closed) 
      {
         connection.Close(); 
      }
  }
}

Thanks a lot for your time.
Robbe Morris replied to Rajendra Reddy on 07-Jan-14 08:31 AM
Use truncate table TableName instead.

You could also try dropping all the indexes first.  The performance of deletes involved removing each record one by one from both the table and any indexes.
Rajendra Reddy replied to Robbe Morris on 07-Jan-14 08:59 AM
Hello Robbe,

Thanks for your reply!

You mean there is no problem with my code right?
If yes, Then I will try to solve issue by going with your solution.

Regards,
Rajendra
Brad Roberts replied to Rajendra Reddy on 07-Jan-14 02:26 PM
I noticed that there are no statement delimiters(semi-colons) between deletes. (Perhaps the newline characters are imbedded and that suffices.) If this causes the execution to fail I am not sure the connection would be closed.