1. Onlinebuff
  2. DOT.NET Interview Questions
  3. Step By Step Select, Insert, Update and Delete using ASP.NET C# and ADO.NET

Step By Step Select, Insert, Update and Delete using ASP.NET C# and ADO.NET

Insert, Select, Update and Delete Statements

Insert, Select, Update and Delete statements these are basics of a programming because these are common coding practice which applies in development of application or a website associated with the database. Insert, Select, Update and Delete altogether also known as DML (Data Manipulation Language) statements which helps in managing the data with the database within schema objects.

What is a Database?

We will not get into more detailed explanation about the database but just a breif for undertsanding.

Database is a collection of data stored in a computer disc space or server space. In simple words it is a space where your data is been stored.

In a single database we can create any number of data tables. It's like a school with many classrooms and each classroom will have different school students.

In a single data table we create any number of columns For eg. studentID, studentName, studentAddress and in a single data table we can store data for specific topic.

Like to store student's records you can have students data table, For school accounts you can have accounts data table, For school results you can have results data table.

There are commonly used databases in our IT Zone

SQL - Microsoft SQL Server

Oracle - Oracle RDBMS or simply as Oracle

MYSQL - Open Source Freeware

MSACESS - Microsoft Access

PostgreSQL - ORDBMS

SAP - To manage customer relationship and other business operations

FoxPro - Database

IBM DB2 - IBM Database by IBM

Now in further we will try to understand about data manipulation statements i.e. (Insert, Select, Update and Delete).

About Data Manipulation Statements

Select Statement

Select statement let you to retrieve the data from the database table.

For ex :  In simple words if you want to pick up single student record, multiple student records or all students records from the table database you just need to use "Select" statement.

Example

 
Select * from 'students'

"*" - Means All

"students" - Data Table in a database.

Insert Statement

Insert statement lets you to insert new record or new data into the database table.

For ex :  In simple words if you want to add new student record then you need to use "Insert" Statement.

Example

 
Insert into student (studentID, studentName, studentAddress) values (1, "Khadak Singh", "Mulund - Mumbai");

"studentID, studentName, studentAddress" - Column Names inside student table

"student” - Table in a database.

Note: For numeric data we dont use (" ") inverted comma and for string data we use (" ") inverted comma .

Update Statement

Update Statement lets you to update existing one or more records in the database table depending on condition.

For ex:  Updating existing student records.

Example

 
Update student set studentID=2, studentName = "Khadak Sharma", studentAddress = "Parel" Where studentID = 1;

"Where studentID = 1 " - It is a condition, means update student record whose ID number is = 1.

Delete Statement

Delete statement helps to delete one or more records from the database table depending on condition.

For ex: Removing any student from a class.

Example

 
Delete From student Where studentID = 1;

"Where studentID = 1 " - It is a condition, means delete student record whose ID number is = 1.

Why Insert, Select, Update and Delete

When we have a website or an application associated with database (Sql, Oracle, Mysql and so on) then to do manipulation (i.e. Displaying, Deletion, Updating and Insertion) in the database we have to use only DML Language statements (i.e. Insert, Select, Update and Delete Statements).

Now that you have understoond about data manipulation language, now let's do step by step insert, update, delete and select using asp.net c# code with ado.net objects.

Step By Step Select, Insert Update and Delete

To demonstrate step by step insert update delete and select, we will be using a simple web application, you can choose anything web or windows depending on your requirement the coding part will remain the same.

Step 1

Create a web forms application

Step 2:

Create a Database

After creating a new website, create a new database from server explorer of visual studio or SQL server. We have shown you an example through visual studio 2010.

After creating a new database, let's add new data tables with colums like as shown in below image file.

Note : To create tables just click on your database name go to tables folder, when you choose to create a table it will ask you add columns. You can add your custom name column names along with data type. (Data Type : For string data choose - varchar(50) or above, for integer data choose - int)

Step 3

Adding connection string to web.config file.

First Right click on the database name then click on the properties (Alt + Enter).

Find the connection string then copy that connection string

Once you have copied the connection string go to your asp.net web site solution explorer. Find the web.config file open that web.config file.

Replace the above connection string with your copied connection string.

Step 4

Add new aspx file i.e. default.aspx file to your web project.

Then

Drag and drop a gridview to the page from the tool box then do the following as shown in below.

i.e. Add - Edit Delete buttons from the command field and check on the auto generate fields. Edit link is for to choose which record to edit and delete link is for to delete the selected record.

Create a form to update and insert records into database from website as shown above i.e. Name, Address and Salary then add one hidden field and two submit buttons, name it.

Step 5

Displaying records using select statement

We will be display the records on grid view. So let’s display the records using select statement.

Open Up Connection to Database

First thing we need to open up a connection to a database. So let’s do that step by step

In default.aspx.cs file import the SQL namespaces on top .

 
using System.Data
using System.Data.SqlClient;

Now in a page load write the connection string and open up the sql connection as shown below.

 
string conn = "";
conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
SqlConnection objsqlconn = new SqlConnection(conn);
objsqlconn.Open();

Displaying records on to grid view

After opening up the sql connection, Let's write the Select statement in the sql command and the execute the sql query using sql command execute reader and append it to grid view data source as shown below.

 
objsqlconn.Open();
SqlCommand objcmd = new SqlCommand("Select * from Employee", objsqlconn);
grd_dataset.DataSource = objcmd.ExecuteReader();
grd_dataset.DataBind();

Output

Step 6

Inserting a new record using Insert Statement

To add new record to the database we will use the following form that we have created in our default.aspx file.

We will write insert statement in the button click event. So double click on a button, a button click event opens up.

First we need to open up the sql connection as same as previously we did it for select statement then in a sql command write the following insert statement as shown below.

 
string conn = "";
conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
SqlConnection objsqlconn = new SqlConnection(conn);
objsqlconn.Open();
SqlCommand objcmd = new SqlCommand("Insert into Employee(Employee_Name,Employee_Address,Employee_Salary) Values('" + txtName.Text + "','" + txtAddress.Text + "','" + txtSal.Text + "')", objsqlconn);
objcmd.ExecuteNonQuery();

Then by using the command object call the ExecuteNonQuery() method to execute query. We normally use this method while updating, deleting and inserting.

txtName.Text, txtAddress.Text and txtSal.Text are the form input name to take form input data.

So just run the website by holding Ctrl + F5 and add input data in the text box finally click on add new record button to add new record to the database.

Step 7

Updating an existing record using update statement

What we did here, we have displayed the records in the grid view screen and there itself we have provided an option for edit and delete as a link as shown below.

When a user clicks on an edit link of any selected row of data then "select" query or statement fires and it display’s the records in the following form as shown below.

So for now we will assume that user clicks on the edit link and following details displayed on the screen as shown below.

So we have just clicked on 10th record and the same data appears on the screen and we have recorded the employee ID i.e. Number = 10 in our hiddenfield. If you see our form there we have a hiddenfield of employee ID.

HiddenFields: It helps us to store the important values which we can use it in the later part of coding.

This hidden field value or employee ID, I'm going to use it in an update statement for an updating purpose.

So next step when a user clicks on update button on the form, we have to fire an update statement, so fire an update statement.

 
string conn = "";
conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
SqlConnection objsqlconn = new SqlConnection(conn);
objsqlconn.Open();
SqlCommand objcmd = new SqlCommand("Update Employee set Employee_Name='" + txtName.Text + "',Employee_Address='" + txtAddress.Text + "',Employee_Salary='" + txtSal.Text + "' where Employee_Id='" + txtEmpID.Value + "'", objsqlconn);

objcmd.ExecuteNonQuery();

Procedure is same, first we need to open up sql connection, then on sql command we to just need to write an update statement with the "Where" condition.

Note: In our "WHERE" condition we have passed hidden field value to employee id.

Step 8

Deleting an existing record using delete statement

What we did here, we have displayed all the records in the grid view screen and there itself for each record we have provided an option for edit and delete as a link as shown below.

 

So when a user clicks on the delete link of selected row, on delete link event args we have fired delete statement with following delete code as shown below.

 
string conn = "";
conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
SqlConnection objsqlconn = new SqlConnection(conn);
objsqlconn.Open();
SqlCommand objcmd = new SqlCommand("Delete from Employee Where Employee_ID='" + _empID + "'", objsqlconn);
objcmd.ExecuteNonQuery();

 
protected void grd_dataset_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int index = Convert.ToInt32(e.RowIndex);
        int _empID = 0;

        _empID = Convert.ToInt16(grd_dataset.Rows[index].Cells[2].Text);
        if (_empID != 0)
        {

            string conn = "";
           
            conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
            SqlConnection objsqlconn = new SqlConnection(conn);
            try
            {

                    objsqlconn.Open();
                    SqlCommand objcmd = new SqlCommand("Delete from Employee Where Employee_ID='" + _empID + "'", objsqlconn);
                    objcmd.ExecuteNonQuery();
                    ClearAll();
                    Response.Redirect("Default.aspx");
                



            }
            catch (Exception ex)
            {
                Response.Write(ex.Message.ToString());
            }
            finally
            {
                objsqlconn.Close();
            }
          
        }
    }

Note : _empID - we got from the gridview columns 

Delete Procedure is same as did it for update and insert, first we need to open up sql connection, then on sql command we to just need to write a delete statement with the "Where" condition.

So my technical friend I hope that you have understood about insert, update, select and delete using asp.net c# and ado.net class object. If you have any doubts regarding this topic you can add your valuable comments. If you like this article kindly share it with your friends. Happy Coding

Author: Gurunatha Dogi

Gurunatha Dogi

Gurunatha Dogi is a software engineer by profession and founder of Onlinebuff.com, Onlinebuff is a tech blog which covers topics on .NET Fundamentals, Csharp, Asp.Net, PHP, MYSQL, SQL Server and lots more..... read more

Comments

64x64
By Nyan Win Luck on 2016-04-28
I've some error! Asp.net runtime error! Only content control are allowed directly in a contrnt page that contains content controls! This error I'm writing sqldatasource to appear dropdownlist
64x64
By Nawab on 2016-02-25
i am looking for a event handling.
64x64
By Desai chandni on 2015-08-26
very help full & simple to a explain
64x64
By RAGAVAN on 2015-08-19
very useful
64x64
By Rahul on 2015-08-18
owesum teaching keep going u r bestway explaination
64x64
By Safuvan on 2015-08-17
tanx alot....its so helpful....easy nd simple
64x64
By Waqas khan on 2015-02-21
Thank you brother its very helpful....
64x64
By Sagar on 2015-02-13
thanks....
64x64
By Naqi Zaidi on 2015-01-26
how to connnection with Step By Step Select, Insert, Update and Delete using ASP.NET C# with Microsoft SQL Server
64x64
By Navid on 2015-01-14
Nice article, plz provide Oracle 9i and ASP.net 2012 ADD EDIT DELETE application sample too
64x64
By Rudra on 2015-01-13
This article is really useful to me..thanks for showing it step by step which is easy to understand..can you help me for insert update and deleting only images from database with validation.????(no limit on size of image)
64x64
By Rudra on 2015-01-13
This article is really useful to me..thanks for showing it step by step which is easy to understand..can you help me for insert update and deleting only images from database with validation.????(no limit on size of image)
64x64
By Dataset does not exist in current context on 2015-01-01
please help me out and send me the solution i have am performing this project but it shows error on dataset. help me asap grd_dataset.DataSource
64x64
By Vidyashree on 2014-12-27
i m new to asp.net give me solution CREATE DATABASE permission denied in database 'master'. Cannot attach the file 'C:\Documents and Settings\user\My Documents\ado.mdf' as database 'ado'.
64x64
By Prajakta on 2014-12-22
It was of a great help..thanks..good article.
64x64
By Sagar on 2014-12-10
great job dude helped a lot
64x64
By Sanket on 2014-11-30
Thanks it is very usefull for me
64x64
By Aditi Patel on 2014-11-26
Such a helpful info using very easy and understandable language. Really very nice. Thanks.
64x64
By Rahul on 2014-10-20
Thanxxxxx............
64x64
By MANOJ Kashyap on 2014-10-19
hello nice
64x64
By Hetvy on 2014-10-15
This is very useful for me thank u but plz say how 2 create db in SQL with query & how 2 fetch any single record on output with query. Plz reply me.thank you
64x64
By Swathy mohanan on 2014-10-09
good
64x64
By Nikhhil on 2014-09-29
hello nice
64x64
By Andy Weil on 2014-09-25
The article you submitted are really very useful for and i want to step by step article like How to create Trigger and many more. So, i request to you please provide that type information and is it possible to find your article in a pdf form.
64x64
By Jayaprasad on 2014-09-19
thank u
64x64
By Kaushik on 2014-09-08
than x lots
64x64
By Narasingh on 2014-09-04
sir,,,u really superbb,,,i m beginner...this article very nice ,,,,plss,,,give more article...and pls write for beginners for easy undstndng
64x64
By Alok on 2014-08-22
nic .....
64x64
By Keshawa on 2014-08-17
Very much Helpful. Can you please describe the code of clicking "Edit".
64x64
By Jayesh Kotadiya on 2014-08-08
How to Set HiddenFiled and selected index change row cell???
64x64
By Viral Khunt on 2014-08-03
Thanks lot of... U help me??? my project Mycollege ERP system Software
64x64
By Sachin Kumar on 2014-07-20
Please hep me to store bulk excel data in database in asp.net using c#, And please let me know how to make connection to store excel data in database
64x64
By Sethsaren on 2014-07-20
thank
64x64
By Nuux ali ibraahim on 2014-07-03
so thankz for softerware engineer
64x64
By Bs123 on 2014-06-26
thnks sir
64x64
By Roshni pashine on 2014-06-25
nice coding.but plz can provide more specific details how to create forms
64x64
By Srinivas on 2014-06-03
nice coding
64x64
By Joanna on 2014-05-23
need update statement thank you
64x64
By Jaydeep on 2014-05-20
thank brother for coding insert update and deleting
64x64
By Nasemaeeb on 2014-05-14
i want update controls
64x64
By Vijay goyal on 2014-05-12
Thanks for providing a good tutorials. Sir Please give me some information about Ado.net like Execute Non Query, sqlDatareader ,Data set and so on.
64x64
By Admin on 2014-04-23
For mysql connections check this my new article Mysql Connection CLick Here http://www.onlinebuff.com/article_step-by-step-to-upload-an-image-and-store-in-database-using-php_40.html
64x64
By Admin on 2014-04-23
Hi #D.ANBARASAN, Please check the code i have re-written the delete. I have written delete code in grid view row deletion click event. Thank you for your comments
64x64
By D.ANBARASAN on 2014-04-17
where i'll write the delete coding,i can't understand...plz ans this question...
64x64
By D.ANBARASAN on 2014-04-17
where i'll write the delete coding,i can't understand...plz ans this question...
64x64
By Monil Thakor on 2014-04-02
Than Q nyc article bro.
64x64
By Nitin on 2014-03-28
Very nice code .....
64x64
By Amit Gupta on 2014-03-27
Plz send me how to make gridview in database.
64x64
By Sathya on 2014-03-25
It's very useful for me. But i need mysql connection codings
64x64
By EmiJames on 2013-11-27
where i'll write the delete coding,i can't understand...plz ans this question...
64x64
By Pranav on 2013-11-26
how to insert database
64x64
By Pramod on 2013-10-16
very nice & its a very helpful for anyone... Thanking you...
64x64
By Mohammed on 2013-10-04
ya.simple and super
64x64
By Porknbeans on 2013-10-03
what is grd_dataset?
64x64
By Vagat singh on 2013-09-29
Very very best site for understanding the basic concepts with example.

Add a Comment