Click here to Skip to main content
13,259,539 members (53,403 online)
Click here to Skip to main content
Add your own
alternative version

Stats

103.6K views
3.8K downloads
18 bookmarked
Posted 12 Oct 2015

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

, 29 Mar 2016
Rate this:
Please Sign up or sign in to vote.
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:

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?

License

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

Share

About the Author

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

You may also be interested in...

Pro
Pro

Comments and Discussions

 
Questiontable not updated Pin
Member 134466554-Oct-17 21:53
memberMember 134466554-Oct-17 21:53 
QuestionData not insert Pin
Member 1304746922-Mar-17 2:40
memberMember 1304746922-Mar-17 2:40 
QuestionNice Article.... Pin
Member 1295789119-Jan-17 1:54
memberMember 1295789119-Jan-17 1:54 
QuestionRegister OK but cannot Login! Pin
Bui Tan Duoc20-Aug-16 5:55
professionalBui Tan Duoc20-Aug-16 5:55 
AnswerRe: Register OK but cannot Login! Pin
Bui Tan Duoc20-Aug-16 6:44
professionalBui Tan Duoc20-Aug-16 6:44 
GeneralMy vote of 5 Pin
Member 1259004117-Jun-16 8:05
memberMember 1259004117-Jun-16 8:05 
Questiondatabase not updating Pin
Member 1204142116-Apr-16 21:38
memberMember 1204142116-Apr-16 21:38 
AnswerRe: database not updating Pin
VijayRana17-Apr-16 3:18
professionalVijayRana17-Apr-16 3:18 
GeneralRe: database not updating Pin
Member 1252652616-May-16 5:08
memberMember 1252652616-May-16 5:08 
GeneralRe: database not updating Pin
VijayRana16-May-16 5:25
professionalVijayRana16-May-16 5:25 
PraiseGood article Pin
Member 1117728631-Mar-16 21:08
memberMember 1117728631-Mar-16 21:08 
Praiseconnectivity Pin
Member 1222239922-Dec-15 0:15
memberMember 1222239922-Dec-15 0:15 
GeneralMy vote of 5 Pin
Member 1205273215-Oct-15 3:42
memberMember 1205273215-Oct-15 3:42 
Questioncon scope Pin
BaseDeDatos14-Oct-15 8:49
memberBaseDeDatos14-Oct-15 8:49 
Questionconcise Pin
Dgmarious13-Oct-15 12:32
professionalDgmarious13-Oct-15 12:32 
Generalit is very handy Pin
Southmountain13-Oct-15 4:56
memberSouthmountain13-Oct-15 4:56 
SuggestionForgot close the connection Pin
Anil000713-Oct-15 3:09
memberAnil000713-Oct-15 3:09 
GeneralRe: Forgot close the connection Pin
Vijay Rana AON13-Oct-15 3:11
professionalVijay Rana AON13-Oct-15 3:11 
GeneralRe: Forgot close the connection Pin
HaBiX14-Oct-15 6:56
memberHaBiX14-Oct-15 6:56 
GeneralRe: Forgot close the connection Pin
ahagel13-Oct-15 19:50
memberahagel13-Oct-15 19:50 
AnswerMessage Closed Pin
12-Oct-15 8:39
memberserkan198712-Oct-15 8:39 
GeneralNice description Pin
Member 1196008512-Oct-15 6:06
memberMember 1196008512-Oct-15 6: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 30 Mar 2016
Article Copyright 2015 by VijayRana
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid