SQL Connection: Connecting to Multiple Databases

Using application settings in Web.Config

In this tutorial, the storing of a connection string in a web.config file and its usage for connecting to multiple databases will be considered. One advantage of storing information in a web.config file is that all testing can be done with a development database, and when going into production all that needs to be changed is the web.config file information. The web.config file is secure because its accessibility is limited. The manner in which the web.config file is set up to store database information, assuming a connection to the pubs database on the local server accessed with SQL authentication, is as follows:

<Configuration>
<appSettings>
<add key="ConnectionString"
value="DATABASE=pubs;Server=localhost;UID=sa:"/>
</appSettings>
</Configuration>

It is also possible to store the same database information in an external file data.config and link to it. In this case the key/value pair information is carried in the external file. Once the web.config carries this information it can be retrieved by using code as follows:

Code in VB.NET
DIM conn as String conn=ConfigurationSettings.AppSettings("ConnectionString")

It is possible to connect to any of a plurality of SQL 2000 servers by storing the connection string information in a web.config file. For a local SQL server you can connect to any of the databases, or for databases on different accessible SQL 2000 servers.

The VB.NET studio available to this tutorial restricts connection to databases on the local machine only. The two servers chosen for this tutorial are a SQL 2000 server (aka localhost), and a Desktop MSDE called XPHTEK/TEST. The servers and the databases accessed are as shown in the next two pictures.

Both servers are accessed by the ASP.NET and therefore, the databases that are accessed should have the proper login information as shown in these pictures.

If these login permissions are not there, they should be configured before they can be accessed via web application. With these initial tasks taken care of, the web.config file for these servers are configured as shown below:

<appSettings>
<add key="books"
value="Server=localhost;integrated security=SSPI;database=Biblio"/>
<add key="info"
value="Server=XPHTEKTEST;integrated security=SSPI;database=RCC"/>
</appSettings>

A web application is created and a web form is added. The connection to the databases is established in the click event of button as shown in the user interface of this application. The web.config file of this application has the aforementioned appSettings.

Code

The code for connecting to one or the other database is through the selection of a database item in the dropdownlist. Presently there are only two servers configured for connection. The code for connecting to the databases is shown as follows:

Private Sub Button2_Click(ByVal sender As Object, ByVal e As 

System.EventArgs) _ 

Handles Button2.Click
If DropDownList1.SelectedItem.ToString Is "books" Then
Dim dynSQL As New SqlClient.SqlConnection

dynSQL.ConnectionString = ConfigurationSettings.AppSettings("books") Try
dynSQL.Open()
TextBox1.Text = dynSQL.ConnectionString
If dynSQL.State = 1 Then
Label1.Text = "Server State is Open"
Else
Label1.Text = "Server State is Closed"
End If
Catch ex As SqlClient.SqlException
TextBox1.Text = ex.Message
End Try
dynSQL.Close()
ElseIf DropDownList1.SelectedItem.ToString Is "info" Then
Dim dynSQL2 As New SqlClient.SqlConnection 

dynSQL2.ConnectionString = 

ConfigurationSettings.AppSettings("info") Try
dynSQL2.Open()
TextBox1.Text = dynSQL2.ConnectionString
If dynSQL2.State = 1 Then
Label1.Text = "Server State is Open"
Else
Label1.Text = "Server State is Closed"
End If
Catch ex As SqlClient.SqlException
TextBox1.Text = ex.Message
End Try
dynSQL2.Close()
End If    
End Sub

The next two pictures shows the result of trying to connect to the databases.

However, if the servers are disconnected, or are not accessible, the SQL exception is captured and displayed in the text box as shown below.

Connection Using Dynamic Properties of the SQLConnection Control

The information stored in the web.config file can also be used by placing a 'SQLConnection' control from the 'Tools'. Since there are two server connections, two controls (see picture below) should be added to the user interface in the design plane. In the Properties window of the SQLConnection control, you need to configure the DynamicProperties in the Configuration node. By clicking the ConnectionString item of this node, a dialog box pops up asking for 'Mapping' instruction between the "key" in the configuration file and SQLConnection1.ConnectionString.

After adding the 'SQLConnection' control, the key in the web.config file can be associated with the connection as shown. For example, in the (Dynamic Properties) node,

the ConnectionString is SQLConnection2.ConnectionString, and its mapping to the web.config stored value is entered by clicking the empty space in the (Advanced) field as shown in the next picture, and picking up the relevant item. Similarly the SQLConnection1 is associated with the 'books.'

With these settings in place, the code gets much simpler, as shown below.

Private Sub Button1_Click(ByVal sender As Object, ByVal e As 

System.EventArgs)_
Handles Button1.Click
If DropDownList1.SelectedItem.ToString Is "books" Then
SqlConnection1.Open()
TextBox1.Text = SqlConnection1.ConnectionString
TextBox2.Text = SqlConnection1.State
Else
If DropDownList1.SelectedItem.ToString Is "info" Then
SqlConnection2.Open()
TextBox1.Text = SqlConnection2.ConnectionString
TextBox2.Text = SqlConnection2.State
End If
End If
End Sub

The result for one of the choices is shown for completeness.

Summary

It is possible to connect any of the SQL 2000 servers whose key/value pairs are stored in the web.config file. It is also possible to use an external file with the same key/value information for the servers. One has to make sure the login for the various databases accessed are in place for successful connection. Using dynamic properties of the SQLConnection the amount of code can be reduced.