Once your programs start to get larger you often end up with many ConnectionStrings sprewn about and this can lead to a number of problems.
<connectionStrings>
<add name="pubs" connectionString="your connection string"/>
</connectionStrings>
You can then access this connection string from you code by:
[C#]
string connStr = ConfigurationManager.ConnectionStrings["pubs"].ConnectionString;
[VB]
Dim connStr As String = ConfigurationManager.ConnectionStrings("pubs").ConnectionString
We could wrap the code up nicely in a function:
Function GetConnectionString() As String
Return ConfigurationManager.ConnectionStrings("pubs").ConnectionString
End Function
And call it like this:
pubsConnection.ConnectionString = GetConnectionString()
Now this is just a simple example. In a development environment you would probably have multiple connection strings for the development server, deployment server, etc. Your code would then test the server name (using Environment.MachineName for example) and then choose the appropriate connection string.
Performance
I like the flexibility that this provides but I am not entirely sold on the performance. With thousands of users accessing a site we are reading the configuration file repeatedly. Some day I will benchmark this operation to see what the performance hit (if any) there is as I am not exactly sure if everything is getting cached.
I started thinking about a solution that minimized file lookups and came up with this...
Global.asax
You can add a Global.asax file to you project (by right clicking your project in solution explorer and selecting Add new item, then select Global Application Class) open the file and add the following line between the <script>
tag and Application_Start:
<script runat="server">
Public Const ConnectionString As String = "This is my connection string" ' Add this line
Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
We access the connection string from our code like this:
Dim ConnectionString As String = ASP.global_asax.ConnectionString
The Const declaration declares the Connection string as Read Only and ensures we only have one instance for every user in the entire application. There are no file lookups, so in theory we should get very good performance. Again, this is not a production code example, in the real world you would have multiple connection strings and you would assign the appropriate one inside the Application_Start method. Look for a benchmark between these two different methods of accessing a connection string in the future.
pubsConnection.ConnectionString = GetConnectionString()