There are many ways you can connect to a database in ASP.NET, the way i’m going to explain here based on “try and error” during my development years. I will try to explain why i do each step and what errors i had in the past so you may prevent these in your web applications. Experienced programmer should know each of these steps and may disagree so discussion is open to debate.
What will you gain from using this technique ?
- Organized in one place connection strings will prevent you from searching and replacing every time you change connection string or its name.
- You will always have a connection closed and disposed from memory even if there was an error which prevented you from getting to the line where Sqlconnection is closed and disposed from memory.
Step one — Keep all connection string at one place
Instead of hard coding every single place you connect to database you can set connection string value inside of web.config or a class. There is no rule where set connection string better in a class or web.config but any ASP.NET developer will first look at ConnectionStrings section of web.config so it would be better to keep connection strings where they belong to.
<connectionStrings> <add name="Dismark" connectionString="Data Source=.\sqlexpress;Initial Catalog=Dismark;Integrated Security=True;Pooling=False" providerName="Sytem.Data.SqlClient"/> <add name="Support" connectionString="Data Source=.\sqlexpress;Initial Catalog=Support;Integrated Security=True;Pooling=False" providerName="Sytem.Data.SqlClient"/> </connectionStrings>
As you can see there are two database connection strings, it is important to name databases and give connection string names according to what they represent. “Dismark” is an application name so i name the database and connection string the same name so in two month i won’t be needed to guess what “ConnectionString1” means. “Support” is a centralized database for costumer ticket support system because the client has few applications while it is much easier to work with centralized support ticket system rather have one per application. So each time you create an application for yourself or for a client ask yourself what whether are gonna be other applications or not because you can create external centralized Support, ErrorReports and Billing databases.
Step two — Make connections easier to use by having a function to retrieve already opened SqlConnection.
This step reduces amount of code you need to write each time you connect to database with your code. For example we will create a class inside of a App_Code folder named DB which will handle all our connections.
This is how your old code could look like
using System;
using System.Configuration;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Dismark"].ConnectionString);
cn.Open();
// code goes here
cn.Close();
cn.Dispose();
}
}
Instead lets create a DB class with two functions, each function connects to specific database, opens a connection and returns to the caller.This way we will write less code each time we connect to database, it will be easier to change if for some reason we will change a database moved to another server or we will have to change connection string name.
using System;
using System.Configuration;
using System.Data.SqlClient;
public class DB
{
public static SqlConnection connectToDismark()
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Dismark"].ConnectionString);
cn.Open();
return cn;
}
public static SqlConnection connectToSupport()
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Support"].ConnectionString);
cn.Open();
return cn;
}
}
This is a call to these functions from page where you connect to a database. As you can see there are less code to write and we don’t have to remember database’s exact name as Visual Studio’s intellisense helps us when we type DB class name.
SqlConnection cn2 = DB.connectToDismark(); // code goes here cn2.Close(); cn2.Dispose();
Step 3 — Encapsulation of connection within using block
As you see there are two methods remain, connection.Close() and connection.Dispose(). One obviously closes the connection to database and other one disposes it from memory. We need to keep in mind that sometimes we won’t reach those lines of code because of error of some kind (ie. bad input from user which cause an error to SqlCommand object). Which means connection will remain open until Sql Server closes it. Not good practice as server may have limit or still use resources to keep the connection alive, instead of that we can encapsulate connection within using block which will automatically close() and dispose() connection when we leave its scope. Lets see how its done.
using (SqlConnection cn3 = DB.connectToDismark())
{
// code goes here
}
Sqlconnection class inherits from IDisposable interface which allows to use it within using block, same goes for SqlDataReader class.
Much faster and secure isn’t it ?
Tags: connection string