Guidelines to connect Db
Guidelines to handle Db connection
Please confirm that you close the original connection to a database all the time and that you are using connection pooling to get better processing speed of database connection creation etc. Here are some tips for you:
1. Use web.config to store up the connection string:
2. Use this connection string forever and do not make any changes to it. If you were not doing this (for example using different DSNs) you will disable the connection pooling feature:
string dsn = System.Configuration.ConfigurationSettings.AppSettings[“dsn”];
3. Do not perform any tricks to try to deal with connections etc, just proceed and use it (no wrapper classes or so involved):
SqlConnection conn = new SqlConnection(dsn);
4. Formulate the time between conn.Open() and conn.Close() as small 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 further cmd parameters actions and so on
SqlDataReader reader = cmd.ExecuteReader();
//use the reader and do a bundle of additional tasks, 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();
//perform a batch of other work, not relying on the database
5. Always close the connection when you are 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();
}
This will close the db connection repeatedly while leave from the using block.