How to connect Sql Database to ASP.Net page using C#?

The web.config file of ASP.Net web application provides a way to store the database connection string that can be accessed from any web page of the application. In the previous article we discussed about the connectionStrings element of the web.config file that enables you to declare the connection strings globally. In this sample we will discuss about the connection string required for connecting the SQL database instance of SQL Server Express Edition 2008 R2 and the C# code for establishing the connection between web page and the database.

Storing the Connection String for SQL Database

You can use the following connection string for connecting the SQL database:

view plaincopy to clipboardprint?

1. <connectionStrings>  
2.     <!-- connection string declared for connecting the web application with SQL database -->  
3.     <!-- connection string for SQL Server Express Edition 2008 R2-->  
4.     <add name="SqlConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=Northwind;User Id=sa;Password=;" providerName="System.Data.SqlClient" />  
5. </connectionStrings>  

The above code shows the usage of connectionStrings element and its add sub element that can be declared and initialized inside the web.config file. I have specified the Northwind database in the connection string with User Id = sa and a blank Password. You must specify the secure User Id and Password for the real time ASP.net web applications.

C# Code for Connecting the Web Page with SQL Database

view plaincopy to clipboardprint?

1. // create a SqlConnection object and initialize it by passing connection string.  
2. // ConfigurationManager class provides the ConnectionStrings collection type property  
3. // to access the connection string stored in the web.config file.  
4. SqlConnection myDataConnection = new SqlConnection(  
5.     ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString);  
6.   
7. // open the data connection.  
8. myDataConnection.Open();  
9.   
10. // display the connection's current state open/closed.  
11. Response.Write(string.Format("<i><b>Connection state:</b> {0}</i><br />", myDataConnection.State));  
12.   
13. // close the data connection.  
14. myDataConnection.Close();  
15.   
16. // again display the connection's current state open/closed.  
17. Response.Write(string.Format("<i><b>Connection state:</b> {0}</i>", myDataConnection.State));  
18.   
19. // dispose the connection object to release the resources.  
20. myDataConnection.Dispose();  

Namespace Required

view plaincopy to clipboardprint?

1. // import the following namespaces  
2. using System.Configuration;  
3. using System.Data.SqlClient;  

The System.Configuration namespace provides the access to the ConfiguartionManager class and the System.Data.SqlClient provides the ADO.Net Data Provider for connecting the SQL database with ASP.Net web application.

Output

It will produce the following output:

Connection state: Open
Connection state: Closed