Connection Strings in web.config configuration file - Part 3

Suggested Videos
Part 1 - What is ADO.NET
Part 2 - SQLConnection in ADO.NET

In this video session we will learn about
1. Storing connection strings  in a configuration file. For example, web.config for an asp.net web application and app.config for windows application
2. Reading the connection strings from web.config and app.config files.
3. Disadvantages of storing connection strings in application code.
4. Advantages of storing connection string in configuration files - web.config and app.config.



In Part 2, of this video series, we have discussed about the SqlConnection object. SqlConnection object uses connection string. The connection strings were hard coded in the examples that we worked with in Part 2. 

There are 2 issues with hard coding the connection strings in application code
1. For some reason, if we want to point our application to a different database server, we will have to change the application code. If you change application code, the application requires a re-build and a re-deployment which is a time waster.
2. All the pages that has the connection string hard coded needs to change. This adds to the maintenance overhead and is also error prone.

In real time, we may point our applications from time to time, from Development database to testing database to UAT database.

Because of these issues, the best practice is to store the connection in the configuration file, from which all the pages can read and use it. This way we have only one place to change, and we don't have to re-build and re-deploy our application. This saves a lot of time.

In an asp.net web application, the configuration strings can be stored in web.config file, as shown below. Give a meaningful name to your connection string. Since we are working with sql server, the provider name is System.Data.SqlClient.
<connectionStrings>
  <add name="DatabaseConnectionString"
        connectionString="data source=.; database=Sample_Test_DB; Integrated Security=SSPI"
        providerName="System.Data.SqlClient" />
</connectionStrings>



How to read the connection string from web.config file?
Use the ConnectionStrings property of the ConfigurationManager class to retrieve the connection string value from web.config. ConfigurationManager class is present in System.Configuration namespace.
protected void Page_Load(object sender, EventArgs e)
{
    string ConnectionString = ConfigurationManager.co .ConnectionStrings["DatabaseConnectionString"].ConnectionString;
    using (SqlConnection connection = new SqlConnection( ConnectionString ))
    {
        SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);
        connection.Open();
        GridView1.DataSource = cmd.ExecuteReader();
        GridView1.DataBind();
    }
}

The configuration file in a windows application is App.config. Storing connection strings in App.config is similar to web.config. The same ConfigurationManager class can be used to read connection string from App.config file. The example below, shows how to read connection strings from App.config file, and bind the data to a DataGridview control in a windows application.
private void Form1_Load(object sender, EventArgs e)
{
    string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
    using (SqlConnection connection = new SqlConnection( ConnectionString ))
    {
        SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);
        connection.Open();
        BindingSource source = new BindingSource();
        source.DataSource = cmd.ExecuteReader();
        dataGridView1.DataSource = source;
    }
}

12 comments:

  1. sir,i am using visual studio 2008 and i was trying to store connection string into web config file but i am getting error because i don't know where to place that code in config file.sir please i want you to send me solution at ashishsen1990@gmail.com.

    thank you

    Ashish Kumar Sen

    ReplyDelete
  2. Hi,

    In the above tutorial you have written code to retrieve connectionstring from App.Config file. In the code block you have stored the connectionstring data retrieved from app.config file to a string variable connectionstring. When you create a connection object you have used attrib/value pairs to pass in the constructor. Then what is the use of storing the data retrieved from app.config.

    The Code should be

    using (SqlConnection connection = new SqlConnection(ConnectionString ))

    Instead of

    using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI"))

    I think I am correct.

    Thanks and Regards.

    ReplyDelete
    Replies
    1. Hi, You are right. That is a typo. Thank you very much for pointing it out.

      Delete
  3. Hi,

    I am getting error when I am trying to open the connection.

    using (SqlConnection con = new SqlConnection("data source=.; database=LocalDB; integrated security=SSPI"))

    The above code is not working for me.
    Please help me.

    Thank you!

    ReplyDelete
    Replies
    1. what is the error that you are getting ?

      Delete
    2. You should mention then name of the database in connection string. This might be causing error.

      Delete
  4. Hi..
    I am getting error when I am trying to open the connection.
    The system cannot find the file specified

    An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code


    Please Help!!

    Thank you

    ReplyDelete
  5. Sir can we write like this
    string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ToString();


    instead of
    string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

    Can you please explain what is the difference between the two?

    ReplyDelete
    Replies
    1. 2nd one is correct .. bcoz ur pointing to the ConnectionString element in the Web.config file.. where it contains the information about the database,server name etc that you are pointing to ..

      Delete
  6. what is the use of BindingSource class

    ReplyDelete
  7. also wirte
    BindingSource.DataSource=cmd.ExecuteReader();

    ReplyDelete

It would be great if you can help share these free resources