Gridview Crud: insert edit update delete in asp.net c# using editable gridview


Article Author : Tauseef Ahmad, Article Posted on : 8/4/2017 , Article Category : Asp.net


Download full source code here: Gridview Crud: insert edit update delete in asp.net c# using editable gridview
please share this article to help others :

Here i have explained with an example and attached code that how to perform CRUD operation CRUD is also called Insert Read Update and Delete  data in database in editable gridview using asp.net c#.

i am explaining with very easy and step by step process with screenshots and code provided that select insert edit update and delete record in gridview using Asp.net c#.

DataBase Section:

Step 1. Open SQL Server and Create Database with Table (student).

i created database with name TestDemoDB.

 



 

Step 2.  now create table with name student. To make ID filed primary key convert (Is Identity) to yes in column property as mention in screenshot.

 

 

Visual Studio Section:

Step 1.  Now open visual studio ( i used vs 2013 ) and create new empty website just see the screenshot.

 

now give name to website and click OK.

 

 

Step 2.  now copy and add the following code in web.config file.

 

<connectionStrings>
    <add name="conString" connectionString="Data Source=tauseef;database=TestDemoDB; Integrated Security=true"/>
  </connectionStrings >

 

in connectionstring give your datasource name as i gave my own and you give your datasource name and also in database give your database name.

in case your sql server need sql authentication (userid and password ) than just use the following code other wise in window authentication use above code.

<connectionStrings>
    <add name="SqlConn" connectionString="Data Source=Tauseef;Initial Catalog=TestDemoDB;User Id=sa;Password=password" providerName="System.Data.SqlClient" />
  </connectionStrings>

 

Step 3. Now add new file (aspx) 

 

give name to page.

 

 

HTML Section:

Add the following code to aspx page this code is used for front end where user see data in gridview and  enter data.

The HTML Markup consists of an ASP.Net GridView with multiple event handlers assigned.

The GridView has a CommandField column which will display the command buttons i.e. Edit, Update, Cancel and Delete.

Below the GridView there’s a Form which will allow us to insert data to the SQL Server database table.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="ID"
OnRowDataBound="OnRowDataBound" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit"
OnRowUpdating="OnRowUpdating" OnRowDeleting="OnRowDeleting"  EmptyDataText="No records has been added.">
<Columns>
    <asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
        <ItemTemplate>
            <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
        </EditItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Degree" ItemStyle-Width="150">
        <ItemTemplate>
            <asp:Label ID="lblDegree" runat="server" Text='<%# Eval("Degree") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="txtDegree" runat="server" Text='<%# Eval("Degree") %>'></asp:TextBox>
        </EditItemTemplate>
    </asp:TemplateField>
    <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="150"/>
</Columns>
</asp:GridView>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
<tr>
    <td style="width: 150px">
        Name:<br />
        <asp:TextBox ID="txtName" runat="server" Width="140" />
    </td>
    <td style="width: 150px">
        Degree:<br />
        <asp:TextBox ID="txtDegree" runat="server" Width="140" />
    </td>
    <td style="width: 100px">
        <asp:Button ID="btnSave" runat="server" Text="Add" OnClick="btnSave_Click" />
    </td>
</tr>
</table>

 

Namspaces:

import the following namspaces.

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

 

Bindi the gridview with Records from sql Databse table.

here write a method to populate gridview from sql database table inside the page load event of page.

 

// getting connection from webconfig file..
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
// page load event... 
  protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            
            this.BindGrid();
        }
    }  
// method for populating gridview from sql database table...
  private void BindGrid()
    {
       
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT ID, Name, Degree  FROM student"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                }
            }
        }
    }

 

when you run the page grid look like this becuase there is no data in sql table and i have set EmptyDataText to display message when no records are present.

Inserting Records to Gridview:

The following event handler is executed when the Add Button is clicked. The name and the degree values are fetched from their respective TextBoxes and then passed to the query for insert data. when data successfully inserted in sql table than gridview is populated again. see the screenshot.

 

 protected void btnSave_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand("insert into student(Name,Degree)Values('" + txtName.Text + "','" + txtDegree.Text + "')", con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        txtName.Text = "";
        txtDegree.Text = ""; 
        this.BindGrid();
    }

 

 

Editing  GridView records:

 

When the Edit Button is clicked, the GridView’s OnRowEditing event handler is triggered. Here simply the EditIndex of the GridView is updated with the Row Index of the GridView Row to be edited.

  protected void OnRowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        this.BindGrid();
    }

 

Update Gridview records:

 

When the Update Button is clicked, the GridView’s OnRowUpdating event handler is triggered.

ID which is the primary key is fetched from the DataKey property of GridView while the Name and Degree fields are fetched from their respective TextBoxes and are passed to query.

 protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow row = GridView1.Rows[e.RowIndex];
        int Id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
        string studentname = (row.FindControl("txtName") as TextBox).Text;
        string degree = (row.FindControl("txtDegree") as TextBox).Text;
        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand("update student set Name ='" + studentname + "',Degree='" + degree + "'where ID =" +Id, con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
      GridView1.EditIndex = -1;
        this.BindGrid();
    }

 

Cancel Edit:

When the Cancel Button is clicked, the GridView’s OnRowCancelingEdit event handler is triggered. Here the EditIndex is set to -1 and the GridView is populated with data.

 protected void OnRowCancelingEdit(object sender, EventArgs e)
    {
        GridView1.EditIndex = -1;
        this.BindGrid();
    }

when you click edit button record is visible in textboxes inside gridview for editing and update and delete button becomes visible see screenshot.

 

Deleting Gridview Record:

When the Delete Button is clicked, the GridView’s OnRowDeleting event handler is triggered.

ID which is the primary key is fetched from the DataKey property of GridView and is passed to the query for deleting record.

 protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int Id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
        SqlConnection con = new SqlConnection(strConnString);
        con.Open();
        SqlCommand cmd = new SqlCommand("delete from student where ID=" + Id, con);
        cmd.ExecuteNonQuery();
        con.Close();
        this.BindGrid();

 

}

 

Display Confirmation Message before deleting:

show display message to user before delete record.

for this I have made use of OnRowDataBound event handler where I have first determined the Delete Button and then I have attach the JavaScript Confirm to its client side Click event handler. see screenshot.

 protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != GridView1.EditIndex)
        {
            (e.Row.Cells[2].Controls[2] as LinkButton).Attributes["onclick"] = "return confirm('Are you sure to delete this record?');";
        }
    }

 

Thats it, This is complete CRUD operation using Editable gridview in asp.net c#. Download source code with database backup at the top. 



Related Article


Add your valuable Comments


Success! Comment added successfully.

Name is required

Email is a required field Invalid Email
Message is required

Comments


{{cmt.Item1}}


{{cmt.Item2}}

{{cmt.Item3.slice(0,9)}}


ADS

Tags:

gridview crud operation editable insert edit update delete sql query store procedure