Day 1 : Database Connectivity ASP.NET/C# with SQL Server (Create Operation)

• Download source code (no EXE) - 5.8 MB
• Download source code - 9 MB

Introduction

In every language, the most important part is database connectivity. This tip will cover all the aspects of database connectivity in the form of Create Operation. This tip is useful for beginners and will help them understand the different steps in database connectivity. It will take 5 easy steps for connectivity.

Background

There can be several ways to connect to a database through C#. Here, I will cover a simple ADO.NET approach and I will cover this through Stored Procedure and Grid View. So it will be good to have a little knowledge of SQL server and ASP.NET prior to going for this. I will use Visual Studio 2012 and SQL Server 2012, but the code will be the same for all the Visual Studio and SQL Server versions.

Using the Code

Add a new project and give a name to it (In my case, the name is DatabaseConnectivity) as shown below:

Add a new Web Form with Master Page and give a name (In my case, the name is UserRegistration.aspx) and select Master Page as below:

The next step is to create a Registration Form as below and add the below code inside Content Place holder whose Id is Content3 as below:

<asp:Content ID="Content3" 
ContentPlaceHolderID="MainContent" runat="server">
    <table>
        <tr>
            <td>
                Name
            </td>
            <td>
                <asp:TextBox ID="txtName" 
                runat="server" 
                required="true"></asp:TextBox>
            </td>
        </tr> <tr>
            <td>
                Email
            </td>
            <td>
                <asp:TextBox ID="txtEmail" 
                runat="server" required="true" 
                type="Email"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                Password
            </td>
            <td>
             <asp:TextBox ID="txtPassword" 
             runat="server" required="true" 
             type="Password"></asp:TextBox>
            </td>
        </tr> <tr>
            <td>
                Confirm Password
            </td>
            <td>
      <asp:TextBox ID="txtConfirmPassword" 
      runat="server" required="true" 
      type="Password"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
               Address
            </td>
            <td>
        <asp:TextBox ID="txtAddress" 
        runat="server" required="true" 
        TextMode="MultiLine"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td></td>
            <td>
                <asp:Button ID="btnSubmit" 
                runat="server" Text="Submit" />
            </td>
        </tr>
</table>
</asp:Content>

Run the application after setting this page as a Start Page (Right click on UserRegistration.aspx and click on Set as Start page). Once run, we will get a page as below.

The next step is to generate a Button Click Event. For this, just right click on the Button and go to Properties and double click on click event (or directly double click on the button).

Once you will double click on the button, an event will generated on the code behind page as below:

protected void btnSubmit_Click(object sender, EventArgs e)
    {

    }

Whatever you want to perform when you will click on Button, you will write inside this click event. In our case, we will do database connectivity here and will save all the values in database. For this, I created a database named "Database Connectivity" and a table inside it named "tblUser" with the below script:

CREATE TABLE [dbo].[tblUser](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[Password] [nvarchar](50) NULL,
	[Address] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This script will create a table in database having identity column in ID with Primary key. I will use Stored Procedure for this, so create a stored procedure as below:

create proc spInsertUser
@Name Nvarchar(50),
@Email Nvarchar(50),
@Password Nvarchar(50),
@Address Nvarchar(50)
as
Insert into tblUser(Name,Email,Password,Address) values(@Name,@Email,@Password,@Address)

In Code behind, first retrieve all the text box values:

protected void btnSubmit_Click(object sender, EventArgs e)
 {
   string name = txtName.Text;
   string email = txtEmail.Text;
   string password = txtPassword.Text;
   string address = txtAddress.Text;
}

Now, we will connect to database and will save these values in tblUser table. For this, first add two below namespaces on page:

using System.Data;
using System.Data.SqlClient;

Now, we will save these values in database in 5 steps:

Step 1: Make a Connection

To make a connection with database, ADO.NET provides a class named SqlConnection. So, we will create an object of this class and will pass the connection string:

SqlConnection con = new SqlConnection
("Data Source=.;Initial Catalog = DatabaseConnectivity;Trusted_Connection=true;");

con is the object of SQL Connection Class. In Connection String, the meaning of different attributes are:

• Data Source: In Data Source, we will provide the Machine Name where we create the database. (.) Means Database is in your local Machine.
• Initial Catalog: Initial Catalog is the database Name (In my case, it is DatabaseConnectivity).
• Trusted_Connection: Trusted_Connection should be true if you are using window authentication while connecting to database. If you are using SQL authentication, you will have to pass userid and password.

Step 2: Open Connection

con.Open();

Step 3: Prepare Command

To prepare a command, ADO.NET gives us a class named SqlCommand which we will use as below:

SqlCommand com = new SqlCommand(); // Create a object of SqlCommand class
com.Connection = con; //Pass the connection object to Command
com.CommandType = CommandType.StoredProcedure; // We will use stored procedure.
com.CommandText = "spInsertUser"; //Stored Procedure Name

Step 4: Add Parameters If Any

Add Parameters if you have any to your command obeject as below:

com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
com.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;
com.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;
com.Parameters.Add("@Address", SqlDbType.NVarChar).Value = address;

Step 5: Execute Your Command

Execute your command as below:

com.ExecuteNonQuery();

The complete code for this is as follows:

protected void btnSubmit_Click(object sender, EventArgs e)
   {
      string name = txtName.Text;
      string email = txtEmail.Text;
      string password = txtPassword.Text;
      string address = txtAddress.Text; 
      SqlConnection con = new SqlConnection
	("Data Source=.;Initial Catalog = DatabaseConnectivity;Trusted_Connection=true;");
      SqlCommand com = new SqlCommand();

      try
        {
         
          con.Open();
            // Create a object of SqlCommand class
           com.Connection = con; //Pass the connection object to Command
           com.CommandType = CommandType.StoredProcedure; // We will use stored procedure.
           com.CommandText = "spInsertUser"; //Stored Procedure Name

           com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
           com.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;
           com.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;
           com.Parameters.Add("@Address", SqlDbType.NVarChar).Value = address;

           com.ExecuteNonQuery();
        }
        catch (Exception ex)
          {

          }
        finally
         {
           con.close();
         
         }
        }   

Run the application and fill the form and click on submit:

In my next article, I will cover how we can read values from database and show them on Grid View and different operations on gridview. Till then, there are some questions which you should figure out.

Questions

Q1: What is the difference between ExecuteNonQuery() and ExecuteScalar()?

Q2: What does ExecuteNonQuery() method return?