Reading and Writing Connection String in Web.Config file using asp.net (for database connection)

In this article I will explain how to write Connection String in Web.Config file and Read the same Configuration String in .cs file to establish connection with SQL Server database.

Why should we write Connection String in Web.Config file?

                If database connection is required in a single page of website , then we write the connection string and open database connection in the same page it self.
If this database connection is required in many pages , then we have to write the same set of code in all these pages.
   
               Suppose at later stage , if database connection changes (either database or login creadentials) then we need to update it in all the pages which is time taking and error prone .So it would be better if there is a place where we can define the connection string once and reuse it in all the required pages of that website.
   
              This common place is Web.Config file.
   
Open Web.Config file and in the Configuration node add ConnectionStrings node like this

   

    <connectionStrings >
        <add name="MyConString" connectionString="Data Source=173.125.26.32;Initial Catalog=MyDb;User ID=Abc;Password=123" providerName="System.data.SqlClient"/>
    </connectionStrings>

Read Connection String in .cs file

For this we need to use the namespace
  ' using System.Configuration  '

This namespace imports all the configuration details from Web.Config file.

copy paste the following code in .cs file


  protected void Page_Load(object sender, EventArgs e)
    {
        /*Use the Connection String defined in  Web.Config file*/
        Con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConString"].ToString());
        Con.Open();


        /*CREATE COMMAND*/
        Cmd = new SqlCommand("Select * From ms_prao_mst", Con);

        /*EXECUTE IT USING EXECUTEREADER IF U ARE USING SQLREADER ELSE USE EXECUTECOMMAND*/
        Rdr = Cmd.ExecuteReader();
        try
        {
            /*LOOP TILL READ FAILS AND EXTRACT THE DATA WHICH U NEED*/
            while (Rdr.Read())
            {
                /*HERE prao_cd IS THE COLUMN NAME IN THE RESULT SET*/
                if (Rdr["prao_cd"] != DBNull.Value)
                {
                    lblText.Text = lblText.Text + " -  - " + Rdr["prao_cd"].ToString();
                }
            }
        }
        finally
        {
            if (Cmd != null)
                Cmd.Dispose();
            if (Con != null)
                Con.Close();
            if (Rdr != null)
                Rdr.Dispose();
        }
       
    }