Connectionstrings in ASP.NET 2.0

Sep 09 2006

Connectionstrings in ASP.NET 2.0

Posted by admin under ASP.NET 2.0

By email I was asked about <connectionstrings> in web.config and if it was justified to not use it in certain specific scenarios. While "being used to appSettings" is not enough to me, there could indeed be more complex scenarios and therefore a lot more to it than just "start using it instead".

First, if you like me don't use the datasouce controls a lot (objectdatasource, sqldatasource) etc, you might find that

a) how do I define (syntacically) the connectionstrings (when the forms designer doesn't do it for you)

b) when I need to define it myself - is there a difference when defining it in <connectionstrings> or <appSettings>?

Let me first give you some background. The connectionStrings element was added into the web.config to bring some order and structure to them. And more importantly, by using a specific element in the config file, the forms designer is able to enumerate all defined connectionStrings, and list them as alternatives when you are creating datasourcecontrols. A lot of the new features, like membership, personalization and the role Manager relies on connection strings being stored in that place.

So basically it is a good thing, but, as I say there are some cases when it might not be the optimal solution.

First lets look at the old .NET 1.1 way:

Accessing the ConnString should preferably be wrapped in some sort of "Globals" class:

public static string GetConnString()
{
return System.Configuration.ConfigurationSettings.AppSettings["ConnString"];
}

Peace of cake, right?

Now, if you are using an N-tiered solution, you might also have multiple DAL (data access layer) components. I typically do. In my code I instantiate the database handler class based on the DataAccessComponent value, for example I have one class for SQL Server, another class for MySQL, a third class for Access etc. All DAL classes implements the same interface, typically called something like IMyAppDataHandler. This way switching database engine from MSSQL to Access is just a matter of editing web.config:

<add key="DataAccessComponent"
value="DataAccessAccess.DataAccess, DataAccessAccess" />

and of course write the DataAccessAccess.DataAccess class code to implement all the queries and calls as defined in the generic interface IMyAppDataHandler. No changes in the webapplication or the businesslayer. Just as it should be, right?

But, how can you apply this to the new .NET 2.0 thinking and <connectionStrings> element? Lets look at the very simplest case:

<configuration>
...
<connectionStrings>
<add name="connectionstring"

connectionString="Server=(local);Database=admtest;Trusted_Connection=False;uid=sa;pwd=whatever;"
providerName="System.Data.SqlClient"/>
</connectionStrings>
...
</configuration>

Now can I make that work with my wanted behaviour? Well, the question is why should I? Since appSettings still works as before as it used to... Well, the reason for even trying is the feeling that you are "supposed" to use <connectionStrings>.

One way of doing it would be stripping out the providername and add the "DataAccessComponent" key back in appSettings:

However when reading the docs

"In ASP.NET 2.0, features, such as Session, Membership, Personalization, and Role Manager, rely on connection strings that are stored in the connectionStrings element. You can also use the connectionStrings element to store connection strings for your own applications".

Yes, it says "can" use it. And when loooking at the code above - what have I gained? Nothing...The connectionstring I have added can't be used by GUI designtime controls - since it doesn't have a providername, secondly I don't like the separation of the connstring and the dataaccesscomponent elements.

Doesn't feel right. So, in other words - I put it all in appsettings - no change for me .

So while being my personal opinion, there are sure cases when connectionstrings in appsettings still are justified. However, for the most .NET:ed solution, one maybe should use some sort of provider system instead - define the connection in <connectionStrings> and then use the providermodel for the different types of databases and have the provider point to the connectionStrings.

However, that is a project for the future. As always, my advise to you, you don't need to strive for the most .NET:ed solution, strive for a solution that works for you - and your customer.

ASPCode.net

Free asp.net and asp scripts

Connectionstrings in ASP.NET 2.0