How to add a database connection string to your web config file

A database connection string is a line of code that allows an application to connect to a database. It is composed of variables that specify connection information such as: the name of the database, the type of the database, the server in which the database resides, the amount of time that the connection will remain open, and in some cases the user id and password required to access the database.

Database connection strings may be coded in several places in a Web Application. They can be declared in the code of each class that requires connectivity, or they can be declared in the web config file. The advantage of declaring them in the web config file is that it provides a central location where the string can be made available to all your pages and classes. Therefore, you only have to code them once, and if you need to alter them in the future, you will only need to change them in one place.

Here is an example of a connection string in the web config file. It is configured to connect to SQL Server's AdventureWorks database. If you use this code, make sure you replace the text YOURCOMPUTERNAME with the name of your computer.

<connectionStrings>

    <add name="AdventureWorksConnectionString" connectionString="Data Source=YOURCOMPUTERNAME\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

Next, here is how to access the connection string from your code. Let's suppose you have a class in your Data tier project called "Connect" and you would like to access the connection string from the web config file.

1) Add a reference to your data tier project for the System.configuration component.



2) Add a "using" statement to your "Connect" class for the System.Configuration namespace.

using System.Configuration;

3) Retrieve the connection string from the web config file using the ConfigurationManagement class:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Configuration;

namespace DataLayer

{

    public class Connect

    {

        private static string

            strConn = ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString;

    }

}

4) Add a "using" statement to your "Connect" class for the System.Data.SqlClient and System.data namespaces.

using System.Data.SqlClient;

using System.Data;

5) Add a method to your "Connect" class to test the connection string.

        /// 

        /// This method uses the connection string to connect to the database

        ///        

        /// bool

        public static bool CheckConnection()

        {

            bool blnConnected = false;

            //Initialize a connenction object using the SqlConnection class.

            //Note that we are passing the connection string to the class.

            SqlConnection sqlConn = new SqlConnection(strConn.ToString());    

            //Wrap any attempt to open a database connection in a try catch finally statement.

            try

            {

                //Open the database connection

                sqlConn.Open();

                //Check if the database connection is open

                if (sqlConn.State == ConnectionState.Open)

                {

                    blnConnected = true;

                }

            }

            catch (Exception ex)

            {

                throw ex;               

            }

            finally

            {

                //It is always a good idea to close your database connection after you are done

                //using it, and the finally statement is a perfect place to do this.

                if (sqlConn.State != ConnectionState.Closed)

                {

                    sqlConn.Close();

                }

            }

            return blnConnected;

        }