Closing database connections

Question

To close my database I use this : SqlConnection.Close(); But my application is stuck in this method...

Each time I access the Database I do this :

string cmdSQL = "select min(shortAddress) from FreeShortAddress";

using (SqlCeCommand cmd = new SqlCeCommand(cmdSQL))    
{
    cmd.Connection = (SqlCeConnection)this.SqlConnection;    
    var r = cmd.ExecuteScalar();    
}

Is it the right way ?How can you see which connection (on which table) is blocked ?

Thank you.

[Edit]Do you know a command to force closing all connections without being stuck ?

yes. what i posted in my answer – Kevin Dec 28 '12 at 12:22 — Kevin, Dec 28 '12 at 12:22

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

The general pattern I use when opening and closing connections is like this: (taken from an older answer of mine on a question about the using statement.) This patterns closes the connection automatically when it is supposed to be closed. (When exiting the using statement)

using (SqlConnection connection = new SqlConnection(connectionString)) 
{    
  int employeeID = findEmployeeID();    
  try    
  {

            connection.Open();
            SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
            command.CommandTimeout = 5;

            command.ExecuteNonQuery();    
   } 
   catch (Exception) 
   { 
      /*Handle error*/ 
   }

}

score 1 · Answer 2 · answered Dec 27 '12 at 17:23

1

try using this:

using (SqlCeCommand cmd = new SqlCeCommand(cmdSQL))    
{
    cmd.Connection = (SqlCeConnection)this.SqlConnection;    
    var r = cmd.ExecuteScalar(CommandBehavior.CloseConnection);    
}

answered Dec 27 '12 at 17:23

Kevin

2,684
6
35
64

Ok, but later in my code I do the same but I don't use ExecuteScalar. When I close the SqlCeCommand, does the connection close too ? – user1932593 Jan 04 '13 at 09:22
it works with all the Executes: http://msdn.microsoft.com/en-us/library/system.data.commandbehavior(v=vs.100).aspx You do not need to close it later on if you are using with your executions – Kevin Jan 15 '13 at 14:15

score 0 · Answer 3 · answered Dec 27 '12 at 17:00

0

This is really a way to go while closing connection, I really used it everywhere to avoid clutter the code by using try catch statement, BTW you can use yield keyword in your version. but not with try catch.:)

answered Dec 27 '12 at 17:00

Paritosh

2,303
2
18
24

Closing database connections

3 Answers3