Establishing Database Connection Between MSSQL And ASP.NET

Introduction

There are a number of articles available on the connectivity of the database. I have tried writing this article for the beginners explaining how to establish the connection between the MSSQL and ASP.net.

Database Connection

As per wiki, a database connection is the means by which a database Server and its client software communicate with each other. The term is used, whether or not the Client and the Server are on the different machines. The client uses a database connection to send the commands to and receive replies from the Server.

Now, we are going to use Microsoft Visual Studio 2010 for front end and Microsoft SQL Server 2008 (MSSQL) for the backend i.e. the database.

First, let's create the database, using Microsoft SQL Server 2008 (MSSQL)

Open MSSQL 2008



Create the table.



If you want to see how to create a database and table, using MSSQL in detail, check the blog, available at

• Creating Database and Table in the Microsoft SQL Server

Now, let’s see the establishing connection, using Microsoft Visual Studio 2010. Before moving forward, we must know the term which we are going to use.

SQLConnection

As per Csharp-station.com, a SqlConnection is an object, just like any other C# object. The SqlConnection object instantiated uses a constructor with a single argument of the type string. This argument is called a connection string.

Table 1 describes the common parts of a connection string.

Table 1. ADO.NET Connection Strings contains certain key/value pairs to specify, how to make a database connection. They include the location, name of the database and the security credentials.

Connection String Parameter Name	Description
Data Source	Identifies the Server. It can be a local machine, machine domain name or an IP Address.
Initial Catalog	Database name.
Integrated Security	Set to SSPI to make connection with the user’s Windows login
User ID	Name of the user configured in SQL Server.
Password	Password matching SQL Server user ID.

Now, we are going to see the procedure step-by-step.

Specify the project name and the project location, as shown below:



The following snapshot shows how to add a new item in the project:



Add Webform in the project, as shown below:



Following snapshot shows Webform, which has been added in the project:



Design the form as follows:



After designing, it will display, as shown below:



Now, we will see how to add the connection in our project:



Now, we will specify the Server name and the database name as follows :



Copy connection string, as shown below, and save in the Notepad for further use:



Write the code at the click event of the button.



Code

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;

namespace Establishing_database_connection
{
    public partial class Application_Form : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            SqlConnection sqlCon = new SqlConnection("Data Source=PRASHANT-PC;Initial Catalog=Prashant;Integrated Security=True");
            try
            {
                sqlCon.Open();
                Response.Redirect("Application_Form.aspx?Status=SQL Connection Open/establsihed",false);
            }
            catch (Exception objEx)
            {
                Response.Redirect("Application_Form.aspx?Error=" + objEx.Message);
            }
            finally
            {
                sqlCon.Close();
            }
        }
    }
}

Give the inputs as follows:



Output is as follows:



Conclusion

Thus, we have the database connection between MSSQL and ASP.NET