{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
A quick way to change connection between local and production databases
Summary
This article shows a quick and simple way to switch between local and production databases. Also discusses the connection strings.
 
Table of Contents

Connection Strings

Figure 1: Interpretation of a Connection String

Multiple Connection Strings

Code Listing 1. A whole bunch of connection strings in the web.config

Multiple Environments

Code Listing 2. Switching between production and local database

 

If your ASP.NET application has a database in the backend, you will typically be switching between at least a couple of databases:

  • A test database on a server. Most likely you will be connecting to this db for all your testing and development needs.
  • A local database. It is more than likely that you have the database replicated on your own laptop or desktop. While there are some humongous production databases, most of the databases don’t make a dent on the local hard disk.
  • A production database. This is, of course, the db that the outside world connects to.

As a developer, you will be connecting to all these three databases from Visual Studio on a regular basis (maybe not as often when it comes to the production database).

Connection Strings

Your ASP.NET program connects to a particular database by using the connection string information provided in the web.config file. The connection information is placed inside <connectionStrings> element:

<connectionStrings>
</connectionStrings>

Within the connectionStrings element, you add information about a single database.

<add name="MyDB"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
/>

In the example above, the connection to a db is named MyDB. You will use this name in the .Net code to obtain this connection string. In this case, the providerName shows that it is an SQL Server database. And finally, the connectionString attribute provides details of where my database server is and how to connect to it.

As shown in Figure 1, a connection string contains all the necessary information to go to a particular database server and to the database of interest on that server.

Figure 1: Interpretation of a Connection String

Figure 1: Interpretation of a Connection String

Multiple Connection Strings

An ASP.NET application could be accessing different databases in different pages (or for the same page, if it needs to get data from different databases). For example, all the sales data might be in one database; all the HR information might be in another.

Sometimes even though the data is in the same database, you might have different names for the connection strings. This might be done to make the connection string name be recognized differently in different contexts. Or, some third-party control is expecting the connection string name to be named a certain way.

So, in a typical ASP.NET application, you will have a whole bunch of connection strings, some pointing to the same database and some to different databases.

Code Listing 1. A whole bunch of connection strings in the web.config

<connectionStrings>

  <add name="MyDB"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

  <add name="MyDB2"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer2;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

  <remove name="MyDB3"/>
  <add name="MyDB3"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

  <add name="MyDB4"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

</connectionStrings>

Multiple Environments

All the connection strings shown in Listing 1 are used by the application at some point (say in the production environment). However, now we want a whole different set of connection strings when we are working locally (either these databases are on our laptop or they are on some QA server).

There are few different ways to do that:

- Code some logic. For example, have a series of connection strings like MyDBProd, MyDBTest, etc. And have another element in the web.config that tells whether this is Prod or Test environment, etc. In the code connect to the right database depending on this switch. This is work and extra code and logic.

- Another approach is to have different web.config files for different environments and overwrite the web.config file according to the environment you want to connect to.

- Perhaps the easiest is the following – have all the connection strings in the same web.config but commented out. Uncomment only the ones you need.

For example:

Code Listing 2. Switching between production and local database

<!--

<connectionStrings>

  <add name="MyDB"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

  <add name="MyDB2"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer2;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

</connectionStrings>


<connectionStrings>

  <add name="MyDB"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=(local);Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

  <add name="MyDB2"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=(local);Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

</connectionStrings>


-->

<connectionStrings>

  <add name="MyDB"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

  <add name="MyDB2"
      providerName="System.Data.SqlClient"
      connectionString="Data Source=MyDBServer2;Initial Catalog=MyDatabase;Integrated Security=True;User ID=aUser ;Password=aPword"
  />

</connectionStrings>

As you can see in Listing 2, there are connection strings for the production environment and the local environment. The names of these connection strings are the same, however, you will see the data source is different: MyDBServer vs. (local).

Outside the comments, I have the production connection strings since I needed production connection strings at that time. If I want local db, then I will copy-paste the local connection strings.

This way, all the information is in one single web.config file. This can be checked into the source control (with the production connection strings outside comments).

Bookmark and Share This

More Articles With Similar Tags
icon-fusion-sqlserver-on-start-menu.jpg
This article talks about using SQL Server in a virtual environment. Here the operating system installed is Windows Vista Ultimate on a Mac using VMware Fusion. The DBMS version used is SQL Server 2008. Adventure Works database is installed on top of this in the virtual environment and tested by issuing queries and creating database diagrams inside SQL Server Management Studio. Simple testing is also done with SQL Server Business Intelligence Development Studio on Windows virtual machine.
Sometimes you need to execute a stored procedure from the query window to view the results in a quick and dirty way. This article shows how to execute a stored procedure that's already there in the db with output parameters.
icon-fusion-sqlserver-successful-installation.jpg
This article goes through installing SQL Server on Windows virtual machine. This Windows virtual machine has been created with VMware Fusion. The versions used here are SQL Server 2008 and Windows Vista Ultimate. Several screenshots of SQL Server installation on a virtual machine are also included.
About  Contact  Privacy Policy  Site Map