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

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.

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.

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

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.