ASP.NET Tutorial: Using web.config to store and use connection strings
Use web.config to store & use connection string for Databases instead of making copies of it every time you use it. Here is quick tutorial to use web.config
ASP.NET is a server side scripting language from Microsoft. It works with the .NET framework so can be coded in C#, VB.
Now in many projects it is needed to connect to the database i.e. MSSQL Server. And typing the connection string each and every time the connection has to be made is very time consuming task.
Storing the Connection string in web.config file is the solution for the problem. And here is the guide for using web.config to store and use connection strings and use it whenever it is required.
The initial 3 steps include the creation of a basic ASP.NET project. You can skip these if you are making changes to your project or you already have a sample project to work on.
Step 1: Create a new Project
Click on File and then click on New Project to create a new ASP.NET project.
Name it as per your requirement. I am taking name as DatabaseDemo for the project.
Step 2: Add some aspx pages to the project.
Step 3: Add a new project to this existing project
the existing project will act as a database access layer to access data from database. Say the layer is named as the DataLayer
Step 4: open web.config
file & add following XML code to the file
<connectionStrings>
<add
name="conn"
connectionString="server: mssql_sample_server;database:sample_db;User ID=userName;Password=password"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
name: this attribute will act as identifier for the connection string when used in the code. You can store various conncetionStrings with different names.
connectionString: this attribute in above code holds the connection string, which will be used later to make connection to databases. Place your connection string over there.
providerName: This attribute is used to inform that which type of data provider is used.
Step 5: Now you want to use the connectionString
So to do so add a reference of System.Configuration to the project. Now after adding the reference; add the using string to the code file where you want to use that string.
Add reference of System.Configuration in Class Library
using System.Configuration;
Now you can retrieve the connectionString by following code:
string strcon = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
Now as we have created a separate project to handle the database actions; there we will create a class with static method to get the SqlConnection object whenever we want to make connection to the database. And this class will be having access to the System.Configuration for the connection strings.
Here we have named the class as DBUtility.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DataLayer
{
public class DBUtility
{
public static SqlConnection GetConnection()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
return conn;
}
}
}
Now we can create a reference of DataLayer project in our main project DatabaseDemo.
And then calling the Static method of the DBUtility class directly to create the SqlConnection object.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DataLayer;
using System.Data;
using System.Data.SqlClient;
namespace DatabaseDemo
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection conn = null;
conn = DBUtility.GetConnection();
if (conn != null)
{
Response.Write("Connection to Databse is successful");
}
else
{
Response.Write("Database Connection Failed");
}
}
}
}
Now we are done…We have successfully created and used ConnectionString in web.config.