Please make sure that you always close the underlying connection to a database and that you're using connection pooling to improve the processing speed of database connection creation etc. A few tips:
1. Use web.config to store the connection string:
<configuration>
<appSettings>
<add key="dsn" value="server=server; uid=user; pwd=password; database=db" />
</appSettings>
</configuration>
2. Always use this connection string and don't make modifications to it. If you're not doing this (i.e. using different DSNs) you'll disable the connection pooling feature:
string dsn = System.Configuration.ConfigurationSettings.AppSettings["dsn"];
3. Don't apply tricks to try to manage connections etc, just go ahead and use it (no wrapper classes or so involved):
SqlConnection conn = new SqlConnection(dsn);
4. Make the time between conn.Open() and conn.Close() as short as possible. E.g. don't do this:
SqlConnection conn = new SqlConnection(dsn);
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(...);
//and more cmd parameters actions etc
SqlDataReader reader = cmd.ExecuteReader();
//use the reader and do a lot of other work, not relying on the database
conn.Close();
but rather, use:
SqlConnection conn = new SqlConnection(dsn);
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(...);
//and more cmd parameters actions etc
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
//use the reader
conn.Close();
//do a lot of other work, not relying on the database
5. Always close the connection when you're done, even when an exception occurs. To do this, use the try...finally pattern:
SqlConnection conn = new SqlConnection(dsn);
try
{
//some init depending on conn
conn.Open();
//minimum number of lines of code depending on the open connection
}
finally
{
if (conn.ConnectionState == ConnectionState.Open)
conn.Close();
}
or the using pattern in C#:
using (SqlConnection conn = new SqlConnection(dsn))
{
//...
conn.Open();
}
which will close the db connection automatically when leaving the using block.