ASP.NET 2.0: Data Sources and Connect Strings

My god, there seem to be a hundred different ways to connect to a data source in ASP.NET. Everything online pointed me to using a connect string when I was actually looking to define a data source in one place (the web.config file) and reuse it (hopefully, leaving the connection open and persistent) for my entire .NET web application. After running in circles for a day or two, I broke out the "Programming Microsoft ASP.NET 3.5" book and got moving in the right direction, with some quirks.

Define the Data Source

First, create your data source in the web.config of the application using the .NET 2.0 <ConnectionStrings> section.

<connectionStrings>
<add name="MyDataSource"
connectionString="Server=MyServer;Database=MyDatabase;UID=MyUsername;PWD=MyPassword"
providerName="System.Data.SqlClient" />
</connectionStrings>

From here you can call on this data source in a number of ways. To grab at the raw connection string in C#, you'll need to first add the reference System.Configuration to your project, then use the ConfigurationManager class to access the connection string info like this:

string strConnTxt = ConfigurationManager.ConnectionStrings["MyDataSource"].ConnectionString;
SqlConnection objConn = new SqlConnection(strConnTxt);

What exactly does this give you? Seeing as we are still just implementing a fancy way of using a connect string with every single call to new SqlConnection thoughout my application, I'm guessing this doesnt really keep the connection open and reusable like a ColdFusion datasource, right? Well, after a bit of research I've found that ADO (the part that handles the underlying database goo) actually does maintain a connection pool to keep the connection open and alive, so you dont waste time with reconnecting to the server with each database call. The trick is, it only uses this pool if the connection string you use is exactly the same on every call to create a new SqlConnection.

So, it makes sense to define all your data sources in the web.config and force your programmers to read the connection string from there. This way everyone is always using the exact same connect string in order to enable connection pooling.

Opening and Closing Database Connections Properly in C#

The connection pool is made up of a number of connection objects already connected to the data source. There is a minimum number of connection objects created initially, which are used (and no longer available in the pool) by your code when you create a new SqlConnection object. If you dont specifically close the connection when youre code is done using it, the connection does not die at the end of your code execution. The connection is killed when garbage collection decides to free up that resource. When all the connections are in use and a new connection is requested, the pool creates a new connection which is another round trip to the database.

To avoid creating new unnecessary connections in the pool, you can free up your connection by closing it in your code when youre done. You can do that easily in C# using the "using" statement:

using (SqlConnection objConn = new SqlConnection(strConnTxt))
{
// access the connection using your code here
}

The connection is closed at the end of the Using block (via Dispose method of the connection object)

Encrypting the Connection String in the web.config file

The next big step is to encrypt the connection string using the section of the web.config file and the aspnet_regiis.exe tool, I'll have to test that out another day when I have more time.