Asp.Net Connection String

 

Introduction

You define database connections in terms of a string that specifies the properties of the connection. These properties usually include the location of the database and security information for accessing the database. Different databases support different properties and therefore require different connection strings.

In this article you will get to know :

1. How to create connection string?

Step 1.
Open Notepad.

Step 2.
And Save it using file name as "string.udl". Make sure its extension is .udl.

Step 3.
Close this File.

Step 4.
Open the file you have just saved.

Step 5.
Go to Provider Tab. And make sure "Microsoft Jet 4.0 OLE DB Provider" is selected. Then Hit Next.

Step 6.
Choose your database. In my case it is "Datable.mdb".

Step 7.
Enter password if it is password protected else leave it blank.

Step 8.
Click on "Test connection" Button to make sure connection is successfully establised with database or not. 

Step 9.
Hit OK Button.

Step 10.
Right Click on "string.udl" file and open it with notepad or any other text editor.

 

2. Where to add connection string in web.config file?

Although you can hard-code connection strings into your Asp.Net web pages, this approach can lead to a difficult-to-manage solution. It is not unusual for data sources to be move, redefined, or upgraded after an Asp.Net application has been released. For example, you might intially deploy a small Asp.Net website with an access database. As the usage and number of visitors to the site increases, you might decide to upgrade to Sql Server. If you have hard-coded all the connections into every page to use the access database, you will have to replace every occurence with the new connection details for the Sql Server Database. However, if you have used the Asp.Net web.config file to store connection strings, and your web pages have retrieved the details from the Asp.Net web.config file at the run time, your solution will be more manageable because you will need only to change the connection details in the Asp.Net web.config file.

Follow the following steps to add connection string in web.config file:

Step 1.

Open your web.config File.

Step 2.

Do not use appsettings in web.config. Instead use the connectionStrings section in web.config.

Step 3.

Locate <configuration> tab and put the connection string between them like below.

<connectionStrings>
    <add name="database_con" connectionString="Data Source=MY-PC\SQLEXPRESS;Initial Catalog=DB1;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

You have successfully placed your connection string.

In above config file database_con is a connection string. It is separated from source code because you can change the config file any time and don’t need to compile the source code.

NOTE* : Always store the connection string in a config file. It's not any harder once you get used to it and you will benefit from it as it is much easier to change connection string properties when your application is in production.

3. How to get or read connection string from web.config file?

If you store a connection string in the connectionStrings section of the Asp.Net web.config file, you can retrieve the string at run time by using the ConfigurationManager class. This class provides a ConnectionString collection that enumerates the connection strings added to the connectionStrings section of the Asp.Net web.config file.

string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

Remember to add reference to the System.Configuration component. Then include the namespace ("using System.Configuration;" in C# and "imports System.Configuration" in VB.Net) in your code to get access to the ConfigurationManager class.

The following example shows how to retrieve connection strings at run time and how to use them to open a database connection:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
 
    {
 
        string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
 
        SqlConnection conn = new SqlConnection(connStr);
 
        conn.Open();
 
        SqlCommand command = new SqlCommand("SELECT CategoryName FROM Category", conn);
 
        SqlDataReader reader = command.ExecuteReader();
 
        while (reader.Read())
 
        {
 
            dlistCategory.Items.Add(reader[0].ToString());
 
        }
     }
  }
Give your Valuable Comments.
comments powered by Disqus
 
Follow me on twitter
About Me
Harpreet Singh
I began programming with C++ when i was 17. Then at the middle of my study .NET came. Then I began to read C# and VB.NET. By the time i learnt SQL and ASP.NET, and developed some websites such as news portals that are active now. Currently i am running my own Software company.
More Articles