SQL Connection: Connecting to Multiple Databases

As businesses may have more than one SQL Server holding their data, it is of interest to know the technique of connecting to mulitple servers through saving connection information in the configuration of the application. In fact, it is a recurring question in several forums. This tutorial addresses this question in detail.

Contributed by
Rating: 4 stars4 stars4 stars4 stars4 stars / 28
August 22, 2005
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

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 C#:
string strConn=ConfigurationSettings.AppSettings["ConnectionString"];


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

Saving multiple connections in Web.config

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>
Procedure to connect to multiple servers

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
Results

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.

blog comments powered by Disqus
MS SQL SERVER ARTICLES

- Windows Azure Media Services Launched by Mic...
- Windows Server 8 Cloud Backup Beta Released
- Idera Announces SQL Compliance Manager 3.6
- Idera SQL Doctor 3.0 and MS SQL Changes
- Microsoft Cuts Windows Azure Compute and Sto...
- Express5800 to Mesh with SQL Server 2012
- Microsoft Azure Outage
- Windows Azure Server Supported by RealCloud ...
- Idera Releases SQL Diagnostic Manager v7.1
- MS SQL Sever 2012 Launch, New Idera Release
- OpenText Azure Cloud Solution, Geminaire Raa...
- Melissa Data Releases MatchUp Tool for SQL S...
- Glovia`s G2 ERP Solution to Support SQL Serv...
- Upgrade Assistant for SQL Server 2012 Releas...
- Azure Update Features Several New Improvemen...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 11 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials