Walkthrough for creating, managing and using connection string in ADO.NET

In my previous two posts i discuss about best practices for managing connection string to connect to SQL Server in ADO.NET and different authentication modes used for connecting to SQL Server.In this post i will simply summarize the discussion from previous posts and follow the simple steps for creating and using connection string to connect to SQL Server in ADO.NET 3.5

Walk through for creating and retrieving connection string

1. Create a database in SQL Server with name 'Company ABC'.
2. Create a table in database called 'Employee',you can add any columns in it as you wish
3. To connect to the above database we can use the following connection string "Data Source=.\SQLExpress;Initial Catalog=MyCompany;Integrated Security=SSPI;Persist Security Info=false"
4. We can save that connection string in the web.config file. Instead of saving the connection string in main web.config file we will save it in external configuration file and refer external configuration file in main configuration file. Benefit of storing connection string in external configuration file is that changing the connection string in external configuration file will not restart the application as opposed to that if it was stored in main web.config file then changing the connection string would restart the application thus loosing any state information of the application
5. To save connection string in external file add new configuration file in project by right clicking the solution explorer and selecting add new item.From new item select configuration file and give it a name 'connection.config'
6. In the file add only the connectionStrings section of configuration file like that
   
   
   connectionString="Data Source=.\SQLExpress;Initial Catalog=MyCompany;Integrated Security=SSPI;Persist Security Info=false" />
   
7. In the main web.config add the reference for the external configuration file
   
8. We can retrieve the connection string from Web.config by using the WebConfigurationManager class methods. We can get ConnectionStringSettings object from WebConfigurationManager. Connection string is available as a property of
   ConnectionStringSettings . Add the following code in .cs or .vb file of your web page
   private string getConnectionString()
   {
   string connString = null;
   ConnectionStringSettings CSS = WebConfigurationManager.ConnectionStrings[testConnection];
   if (null != CSS)
   connString = CSS.ConnectionString;
   return connString;
   }
9. You can pass the retrieved connection string to SQLConnection object to open the connection with SQL Server, fetch the data and bind it with GridView.Add the following code to the code behind file of your web page
   SqlConnection sqlconn = new SqlConnection(getConnectionString());
   sqlconn.Open();
   SqlCommand comm = new SqlCommand();
   comm.CommandText = "Select * from Employee";
   comm.Connection = sqlconn;
   SqlDataReader reader = comm.ExecuteReader();
   GridView1.DataSource = reader;
   GridView1.DataBind();
   sqlconn.Close();

Conclusion

In the above post we learned about storing connection string in configuration file, retrieving connection string from configuration file and using it to get data.