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

Open MSSQL 2008

Create the table.

Create table

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

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.

procedure

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

project

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

new item

Add Webform in the project, as shown below:

Add webform

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

webForm

Design the form as follows:

Design the form

After designing, it will display, as shown below:

Design the form

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

Add connection

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

server name

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

Copy connection string

Write the code at the click event of the button.

code

Code
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Data.SqlClient;
  9. namespace Establishing_database_connection
  10. {
  11. public partial class Application_Form : System.Web.UI.Page
  12. {
  13. protected void Page_Load(object sender, EventArgs e)
  14. {
  16. }
  18. protected void btnSubmit_Click(object sender, EventArgs e)
  19. {
  20. SqlConnection sqlCon = new SqlConnection("Data Source=PRASHANT-PC;Initial Catalog=Prashant;Integrated Security=True");
  21. try
  22. {
  23. sqlCon.Open();
  24. Response.Redirect("Application_Form.aspx?Status=SQL Connection Open/establsihed",false);
  25. }
  26. catch (Exception objEx)
  27. {
  28. Response.Redirect("Application_Form.aspx?Error=" + objEx.Message);
  29. }
  30. finally
  31. {
  32. sqlCon.Close();
  33. }
  34. }
  35. }
  36. }
Give the inputs as follows:

inputs

Output is as follows:

Output

Conclusion

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