Gridview insert update delete in asp net

This asp .net tutorial describes about How to perform insert update delete using gridview in asp .net with ADO .net and without placing controls inside gridview's footer template.

1. Create Database Table

To start with Gridview Insert update delete in asp .net we need database. For this example I have created simple table named "Employee" with 4 columns "Id, Name, Email and Age" however you can use your own database Name or Table structure.
CREATE TABLE [dbo].[Employee](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[Age] [int] NULL,
 CONSTRAINT [PK_Employee] 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]
    

2. Create Form

We have created database table. Now lets create simple form to accept input from users for Gridview Insert update delete in asp .net. We will create three textboxes for user input and three buttons for Save, Update and Clear operations. Also we have used Label control named "lblAlert" to show alerts on record Save, update etc.
         
<%--//--- Label to show alerts--%>
Name
Email
Age
 
Here we have used Visible="false" inside update button to make it invisible at page load. We will only show update button during update process of gridview.
Note: To make tutorial short I have not used any validations. It is adviced to use proper validations in all pages.
Validation Controls in asp .net

3. Add connection string

To interact with database we need to add connection string in our web.confing file.
 
    
  
    
For more information about connection strings visit: Connection string example in asp net

4. Add namespaces.

Add following namespaces.
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Linq;
    

5. Add New Record

         //--- Button Save Click.
    protected void btnSave_Click(object sender, EventArgs e)
    {
        //--- Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

        //--- Insert Query.
        string cmdText = "INSERT INTO employee (Name,Email,Age) VALUES (@Name,@Email,@Age)";

        //--- Providing information to SQL command object about which query to 
        //--- execute and from where to get database connection information.
        SqlCommand cmd = new SqlCommand(cmdText, con);

        //--- Adding parameters/Values.
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
        cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(txtAge.Text));

        //--- To check current state of the connection object. If it is closed open the connection
        //--- to execute the insert query.
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }

        //--- Execute Query.
        cmd.ExecuteNonQuery();

        //--- close the connection.
        con.Close();

        //--- Clear text from textboxes
        resetControls();

        //--- Show confirmation message.
        lblAlert.Text = "Record successfully saved.";
        lblAlert.ForeColor = Color.Green;

        //--- Bind data to Gridview so that it will display updated data.
        bindAllEmployees();
    }
    
Besides Save operation we have also used two additional methods "resetControls" and "bindAllEmployees". "resetControls" method is used to clear all values from textbox controls and "bindAllEmployees" method is used to bind Gridview with latest information.

6. Bind Gridview with data "bindAllEmployees()"

         //---- Bind all employees in GridView.
    public void bindAllEmployees()
    {
        //--- Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

        //--- Select Query.
        string cmdText = "SELECT * FROM Employee order by Id desc";

        SqlDataAdapter adp = new SqlDataAdapter(cmdText, con);
        DataSet ds = new DataSet();
        adp.Fill(ds);

        grdViewTest.DataSource = ds;
        grdViewTest.DataBind();
    }
    

7. Create Gridview

          
                
                    
                    
                    
                    
                        
                            
                            
                        
                    
                
            
    
Note: We have used "CommandName" and "CommandArgument" properties of Linkbuttons inside Gridview. "Commandname" property can be used to define operations names Like Edit, Delete etc and "CommandArgument" can be used to store additional arguments. In this case we have stored Primary key of our records inside "CommandArgument". Later on we will use these properties inside Gridview's "Rowcommand" Event to distinguish which operation to perform "Edit/Delete" and also we will access primary key from "CommandArgument" property.

Note: To make tutorial short I have not used any CSS to gridview. However you can apply custom CSS to gridview: Check Style GridView and Pager with custom css in simple steps

8. Bind Gridview with data on Page_Load

Bind gridview inside page_Load event so that gridview can display existing records after page loads.
         protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            bindAllEmployees();
        }
    }
    

9. Reset Controls "resetControls()"

This method is used to clear all the input values from textboxes and reset form to its initial state.
        //--- Reset Form input fields.
    public void resetControls()
    {
        txtAge.Text = string.Empty;
        txtEmail.Text = string.Empty;
        txtName.Text = string.Empty;

        //--- Show Save button.
        btnSave.Visible = true;

        //--- Hide Update button.
        btnUpdate.Visible = false;

        //--- Reset selected value from View state.
        ViewState["selectedRec"] = string.Empty;
    } 
    

9. Edit and Delete

Here we have used "OnRowCommand" Event of gridview to perform Edit and Delete operations.
       protected void grdViewTest_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Edt")
        {
            //--- Get primarry key value of the selected record.
            int Id = Convert.ToInt32(e.CommandArgument);

            //--- Set id in viewstate so that we can use in update process.
            ViewState["selectedRec"] = Id.ToString();

            //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

            //======= Parameterized select Query.
            string cmdText = "SELECT * FROM employee WHERE Id=@Id";

            //====== Providning information to SQL command object about which query to 
            //====== execute and from where to get database connection information.
            SqlCommand cmd = new SqlCommand(cmdText, con);

            //===== Adding parameters/Values.
            cmd.Parameters.AddWithValue("@Id", Id);

            //===== To check current state of the connection object. If it is closed open the connection
            //===== to execute the insert query.
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            //===== Execute Query.
            SqlDataReader dr = cmd.ExecuteReader();

            //===== Check if data present.
            if (dr.HasRows)
            {
                //===== Read data from datareader.
                dr.Read();

                //===== Bind values to textboxes.
                txtName.Text = dr["Name"].ToString();
                txtEmail.Text = dr["Email"].ToString();
                txtAge.Text = dr["Age"].ToString();

                //==== Store primary key of the selected record in ViewState for future reference.
                //==== This will help us when we write update method.
                ViewState["selectedRec"] = dr["id"].ToString();

            }
            dr.Dispose();

            //===== close the connection.
            con.Close();

            //==== Show Update button and hide Save button.
            btnSave.Visible = false;
            btnUpdate.Visible = true;



        }
        else if (e.CommandName == "Del")
        {
            //--- Get primarry key value of the selected record.
            int Id = Convert.ToInt32(e.CommandArgument);

            //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

            //======= Delete Query.
            string cmdText = "DELETE FROM employee WHERE Id=@Id";

            //====== Providning information to SQL command object about which query to 
            //====== execute and from where to get database connection information.
            SqlCommand cmd = new SqlCommand(cmdText, con);

            //===== Adding parameters/Values.
            cmd.Parameters.AddWithValue("@Id", Id);

            //===== To check current state of the connection object. If it is closed open the connection
            //===== to execute the insert query.
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            //===== Execute Query.
            cmd.ExecuteNonQuery();

            //===== close the connection.
            con.Close();

            //===== Bind data to FormView so that FormView will display updated data.
            bindAllEmployees();
        }
    }
    

10. Update Record.

          //--- Button Update Click
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        //--- Get primary key value of the selected record from viewstate.
        int Id = Convert.ToInt32(ViewState["selectedRec"]);

        //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

        //======= Insert Query.
        string cmdText = "UPDATE employee SET Name=@Name,Email=@Email,Age=@Age WHERE Id=@Id";

        //====== Providning information to SQL command object about which query to 
        //====== execute and from where to get database connection information.
        SqlCommand cmd = new SqlCommand(cmdText, con);

        //===== Adding parameters/Values.
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
        cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(txtAge.Text));

        cmd.Parameters.AddWithValue("@Id", Id);

        //===== To check current state of the connection object. If it is closed open the connection
        //===== to execute the insert query.
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }

        //===== Execute Query.
        cmd.ExecuteNonQuery();

        //===== close the connection.
        con.Close();

        //===== Bind data to FormView so that FormView will display updated data.
        bindAllEmployees();

        //--- Reset controls.
        resetControls();

        //--- Show confirmation message.
        lblAlert.Text = "Record successfully updated.";
        lblAlert.ForeColor = Color.Green;
    }
    

Insert update delete using gridview in asp .net with ADO .net Demo:

 
About Us | Terms of Use | Privacy Policy | Disclaimer | Contact Us Copyright © 2012-2024 CodingFusion
50+ C# Programs for beginners to practice