Open Connection

  • Thread starter Thread starter Dan, Vascas
  • Start date Start date
D

Dan, Vascas

Hi Gurus,
Please help. I have two Questions.
1. Is it bad to keep a database connection always open.
2. In one function we are always opening a new connection, but we are not
closing it. If we use that function second time it uses the connection it
made before, so no new connection has been made. Is it ok to do like this.Or
should we close the Connection when we are done using it.

We are using Framework2.0.

Thanks for your suggestion.
Dan, Vascas
 
1. Yes
2. For scalable systems, you should always close the connection once you are
done with it. It will avoid connection leaks, and result in a more scalable
application
 
1. Yes it's bad.
2. Unless you are required to hold on to the same physically open connection
for transaction reasons, there is really no good reason why you should try
and hold on to an open connection.
 
Hi Gurus,
Please help. I have two Questions.
1. Is it bad to keep a database connection always open.
2. In one function we are always opening a new connection, but we are not
closing it. If we use that function second time it uses the connection it
made before, so no new connection has been made. Is it ok to do like this.Or
should we close the Connection when we are done using it.

We are using Framework2.0.

Thanks for your suggestion.
Dan, Vascas
Click to expand...
1. Yes it is bad.
2.
a. Open the connection.
b. Execute the database operation (insert, update, select or delete).
c. Close the connection.

Here is a sample:

public GlobalLocation FetchCityData(string state, string city)
{
GlobalLocation gl = null;
SqlConnection cn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("sp_s_CityData",cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@City", city);
cmd.Parameters.AddWithValue("@State", state);
SqlDataReader dr = null;

try
{
cmd.Connection.Open();
dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.HasRows)
{
dr.Read();
gl = new GlobalLocation(dr.GetString(3), dr.GetString(4),
string.Format("{0}, {1}", dr.GetString(1), dr.GetString(2)));
}
}
catch
{
throw;
}
finally
{
if (!dr.IsClosed)
{
dr.Close();
}
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
}
}
return gl;
}


Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
Back
Top