Click here to Skip to main content
15,867,568 members
Articles / Web Development / HTML
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.22/5 (27 votes)
29 Mar 2016CPOL3 min read 228.4K   6.4K   20   24
CRUD operation in ASP.NET/C# with SQL Server for beginners

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:

Image 1

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:

Image 2

Image 3

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.NET
<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.

Image 4

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).

Image 5

Image 6

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

C#
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:

SQL
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:

SQL
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:

C#
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:

C#
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:

C#
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

C#
con.Open();

Step 3: Prepare Command

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

C#
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:

C#
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:

C#
com.ExecuteNonQuery();

The complete code for this is as follows:

C#
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:

Image 7

Image 8

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?

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
Hi Myself Vijay having around 7 years of experience on Microsoft Technologies.

Comments and Discussions

 
Questiondata base connectivity issue at remote Sql server Pin
santosh singh _anu22-Jul-21 21:58
santosh singh _anu22-Jul-21 21:58 
Questionstored procedure Pin
Member 1363949523-Jan-18 7:26
Member 1363949523-Jan-18 7:26 
Questiontable not updated Pin
Member 134466554-Oct-17 20:53
Member 134466554-Oct-17 20:53 
QuestionData not insert Pin
Member 1304746922-Mar-17 1:40
Member 1304746922-Mar-17 1:40 
QuestionNice Article.... Pin
Member 1295789119-Jan-17 0:54
Member 1295789119-Jan-17 0:54 
QuestionRegister OK but cannot Login! Pin
Bui Tan Duoc20-Aug-16 4:55
professionalBui Tan Duoc20-Aug-16 4:55 
AnswerRe: Register OK but cannot Login! Pin
Bui Tan Duoc20-Aug-16 5:44
professionalBui Tan Duoc20-Aug-16 5:44 
GeneralMy vote of 5 Pin
Member 1259004117-Jun-16 7:05
Member 1259004117-Jun-16 7:05 
Questiondatabase not updating Pin
Member 1204142116-Apr-16 20:38
Member 1204142116-Apr-16 20:38 
AnswerRe: database not updating Pin
VijayRana17-Apr-16 2:18
professionalVijayRana17-Apr-16 2:18 
Is there any error. Can you please send me database connectivity part.
GeneralRe: database not updating Pin
Member 1252652616-May-16 4:08
Member 1252652616-May-16 4:08 
GeneralRe: database not updating Pin
VijayRana16-May-16 4:25
professionalVijayRana16-May-16 4:25 
PraiseGood article Pin
PR_Chaudhary31-Mar-16 20:08
PR_Chaudhary31-Mar-16 20:08 
Praiseconnectivity Pin
Member 1222239921-Dec-15 23:15
Member 1222239921-Dec-15 23:15 
GeneralMy vote of 5 Pin
Member 1205273215-Oct-15 2:42
Member 1205273215-Oct-15 2:42 
Questioncon scope Pin
BaseDeDatos14-Oct-15 7:49
BaseDeDatos14-Oct-15 7:49 
Questionconcise Pin
Dgmarious13-Oct-15 11:32
professionalDgmarious13-Oct-15 11:32 
Generalit is very handy Pin
Southmountain13-Oct-15 3:56
Southmountain13-Oct-15 3:56 
SuggestionForgot close the connection Pin
Anil Sharma198313-Oct-15 2:09
professionalAnil Sharma198313-Oct-15 2:09 
GeneralRe: Forgot close the connection Pin
VijayRana13-Oct-15 2:11
professionalVijayRana13-Oct-15 2:11 
GeneralRe: Forgot close the connection Pin
HaBiX14-Oct-15 5:56
HaBiX14-Oct-15 5:56 
GeneralRe: Forgot close the connection Pin
ahagel13-Oct-15 18:50
professionalahagel13-Oct-15 18:50 
GeneralNice description Pin
Member 1196008512-Oct-15 5:06
Member 1196008512-Oct-15 5:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.