Gridview: Perform CRUD operations in Gridview control in ASP.NET(C# & Vb.NET),

In this article I will explain how to perform all crud functions using Gridview data Control i.e. insert, update, delete and select operations. In this example I will perform all CRUD operations in gridview control using Row_Command event. 

I have used Template fields to show data and update data in Gridview . I have added textbox controls in footer Template of Gridview to enter data.

Demo:

Click on Image to Enlarge it

Create table in database:

Create Table “Student_Info”  in Database “Blog” and Enter some data to display.

Column Name	DataType
Student_Id	Int
Student_name	Varchar(100)
Age	Int
Class	Varchar(100)

 Now design your web page:

Design Gridview:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" Width="380px"  AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" PageSize="7" OnPageIndexChanging="GridView1_PageIndexChanging" ShowFooter="True" OnRowCommand="GridView1_RowCommand" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">             <AlternatingRowStyle BackColor="White" ForeColor="#284775" />             <Columns>                 <asp:TemplateField HeaderText="Name">                     <EditItemTemplate>                         <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Student_name") %>'></asp:TextBox>                     </EditItemTemplate>                     <ItemTemplate>                         <asp:Label ID="Label1" runat="server" Text='<%# Bind("Student_name") %>'></asp:Label>                     </ItemTemplate>                     <FooterTemplate>                         <asp:TextBox ID="txtName" runat="server" Text='<%#Bind("Student_Name") %>'></asp:TextBox>                     </FooterTemplate>                 </asp:TemplateField>                 <asp:TemplateField HeaderText="Age">                     <EditItemTemplate>                         <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Age") %>'></asp:TextBox>                     </EditItemTemplate>                     <ItemTemplate>                         <asp:Label ID="Label2" runat="server" Text='<%# Bind("Age") %>'></asp:Label>                     </ItemTemplate>                     <FooterTemplate>                         <asp:TextBox ID="txtAge" runat="server" Text='<%#Bind("Age") %>'></asp:TextBox>                     </FooterTemplate>                 </asp:TemplateField>                 <asp:TemplateField HeaderText="Class">                     <EditItemTemplate>                         <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Class") %>'></asp:TextBox>                     </EditItemTemplate>                     <ItemTemplate>                         <asp:Label ID="Label3" runat="server" Text='<%# Bind("Class") %>'></asp:Label>                     </ItemTemplate>                     <FooterTemplate>                         <asp:TextBox ID="txtClass" runat="server" Text='<%#Bind("Class") %>'></asp:TextBox>                     </FooterTemplate>                 </asp:TemplateField>             <asp:TemplateField ShowHeader="False">                     <EditItemTemplate>                         <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" CommandArgument='<%#Bind("Student_id") %>' Text="Update"></asp:LinkButton>                         &nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" CommandArgument='<%#Bind("Student_id") %>' Text="Cancel"></asp:LinkButton>                     </EditItemTemplate>                     <ItemTemplate>                         <asp:LinkButton ID="LinkButton3" CommandArgument='<%#Bind("Student_id") %>' runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>                     </ItemTemplate>                     <FooterTemplate> <asp:LinkButton ID="LinkButton4" runat="server" CommandArgument='<%#Bind("Student_id") %>' CausesValidation="False" CommandName="Add" Text="Insert"></asp:LinkButton>                   </FooterTemplate>                 </asp:TemplateField>                 <asp:TemplateField ShowHeader="False">                     <ItemTemplate>                         <asp:LinkButton ID="LinkButton5" runat="server" CausesValidation="False" OnClientClick="javascript: return confirm('Do you really want to delete this record?')" CommandName="Delete" CommandArgument='<%#Bind("Student_id") %>' Text="Delete"></asp:LinkButton>                     </ItemTemplate>                 </asp:TemplateField>             </Columns>                        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />             <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />                   </asp:GridView>

Asp.net Code:

Using C#:

//Create Connection:     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString); //Bind grid On page load:     protected void Page_Load(object sender, EventArgs e)     {         if (!IsPostBack)         {             Fill_grid();         }     } //Bind gridview Function with database:     public void Fill_grid()     {         if (con.State == ConnectionState.Closed)         {             con.Open();         }         SqlCommand cmd = new SqlCommand("Fill_Dataset", con);         cmd.CommandType = CommandType.StoredProcedure;         SqlDataAdapter adpData = new SqlDataAdapter(cmd);         DataSet ds = new DataSet();         adpData.Fill(ds);         GridView1.DataSource = ds;         GridView1.DataBind();         con.Close();     } //Page Index in gridview     protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)     {         GridView1.PageIndex = e.NewPageIndex;         Fill_grid();     } //Insert, Update, Delete data in gridview:   protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)     {         int sid = Convert.ToInt32( e.CommandArgument.ToString());         if (con.State == ConnectionState.Closed)         {             con.Open();         }         if (e.CommandName == "Add")         {             TextBox txtname = (TextBox)GridView1.FooterRow.FindControl("txtName");             TextBox txtAge = (TextBox)GridView1.FooterRow.FindControl("txtAge");             TextBox txtClass = (TextBox)GridView1.FooterRow.FindControl("txtClass");             SqlCommand cmd = new SqlCommand();             cmd.Connection = con;             cmd.CommandText = "insert into Student_info (Student_Name, age, class) values(@name,@age,@class)";             cmd.Parameters.AddWithValue("@name", txtname.Text);             cmd.Parameters.AddWithValue("@age", txtAge.Text);             cmd.Parameters.AddWithValue("@class", txtClass.Text);             cmd.ExecuteNonQuery();             con.Close();             cmd.Dispose();             Fill_grid();         }         if (e.CommandName == "Update")         {             GridViewRow row=(GridViewRow) (((LinkButton)e.CommandSource).NamingContainer);             TextBox txtname = (TextBox)GridView1.Rows[row.RowIndex].FindControl("TextBox1");             TextBox txtAge = (TextBox)GridView1.Rows[row.RowIndex].FindControl("TextBox2");             TextBox txtClass = (TextBox)GridView1.Rows[row.RowIndex].FindControl("TextBox3");             SqlCommand cmd = new SqlCommand();             cmd.Connection = con;             cmd.CommandText = "Update Student_info set Student_Name=@name, age=@age, class=@class where Student_id=@sid";             cmd.Parameters.AddWithValue("@name", txtname.Text);             cmd.Parameters.AddWithValue("@age", txtAge.Text);             cmd.Parameters.AddWithValue("@class", txtClass.Text);             cmd.Parameters.AddWithValue("@sid", sid);             cmd.ExecuteNonQuery();             con.Close();             cmd.Dispose();             Fill_grid();             GridView1.EditIndex = -1;             Response.Write("<script> alert('Upadted Successfully');</script>");                             }         if (e.CommandName == "Delete")         {                     SqlCommand cmd = new SqlCommand();             cmd.Connection = con;             cmd.CommandText = "Delete from Student_info  where Student_id=@sid";             cmd.Parameters.AddWithValue("@sid", sid);                       cmd.ExecuteNonQuery();             con.Close();             cmd.Dispose();             Fill_grid();         }            }     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)     {     }     protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)     {         GridView1.EditIndex = -1;         Fill_grid();     }     protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)     {         GridView1.EditIndex = e.NewEditIndex;         Fill_grid();     }     protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)     {     }

Using Vb.net:

'Create Connection:     Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)     'Bind grid On page load:     Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load         If Not IsPostBack Then             Fill_grid()         End If     End Sub     'Bind gridview Function with database:     Public Sub Fill_grid()         If con.State = ConnectionState.Closed Then             con.Open()         End If         Dim cmd As New SqlCommand("Fill_Dataset", con)         cmd.CommandType = CommandType.StoredProcedure         Dim adpData As New SqlDataAdapter(cmd)         Dim ds As New DataSet()         adpData.Fill(ds)         GridView1.DataSource = ds         GridView1.DataBind()         con.Close()     End Sub     'Page Index in gridview     Protected Sub GridView1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles GridView1.PageIndexChanging         GridView1.PageIndex = e.NewPageIndex         Fill_grid()     End Sub     'Insert, Update, Delete data in gridview:     Protected Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles GridView1.RowCommand         If con.State = ConnectionState.Closed Then             con.Open()         End If         If e.CommandName = "Add" Then             Dim txtname As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtName"), TextBox)             Dim txtAge As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtAge"), TextBox)             Dim txtClass As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtClass"), TextBox)             Dim cmd As New SqlCommand()             cmd.Connection = con             cmd.CommandText = "insert into Student_info (Student_Name, age, class) values(@name,@age,@class)"             cmd.Parameters.AddWithValue("@name", txtname.Text)             cmd.Parameters.AddWithValue("@age", txtAge.Text)             cmd.Parameters.AddWithValue("@class", txtClass.Text)             cmd.ExecuteNonQuery()             con.Close()             cmd.Dispose()             Fill_grid()         End If         Dim sid As Integer = Convert.ToInt32(e.CommandArgument.ToString())         If con.State = ConnectionState.Closed Then             con.Open()         End If         If e.CommandName = "Add" Then             Dim txtname As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtName"), TextBox)             Dim txtAge As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtAge"), TextBox)             Dim txtClass As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtClass"), TextBox)             Dim cmd As New SqlCommand()             cmd.Connection = con             cmd.CommandText = "insert into Student_info (Student_Name, age, class) values(@name,@age,@class)"             cmd.Parameters.AddWithValue("@name", txtname.Text)             cmd.Parameters.AddWithValue("@age", txtAge.Text)             cmd.Parameters.AddWithValue("@class", txtClass.Text)             cmd.ExecuteNonQuery()             con.Close()             cmd.Dispose()             Fill_grid()         End If         If e.CommandName = "Update" Then             Dim row As GridViewRow = DirectCast(DirectCast(e.CommandSource, LinkButton).NamingContainer, GridViewRow)             Dim txtname As TextBox = DirectCast(GridView1.Rows(row.RowIndex).FindControl("TextBox1"), TextBox)             Dim txtAge As TextBox = DirectCast(GridView1.Rows(row.RowIndex).FindControl("TextBox2"), TextBox)             Dim txtClass As TextBox = DirectCast(GridView1.Rows(row.RowIndex).FindControl("TextBox3"), TextBox)             Dim cmd As New SqlCommand()             cmd.Connection = con             cmd.CommandText = "Update Student_info set Student_Name=@name, age=@age, class=@class where Student_id=@sid"             cmd.Parameters.AddWithValue("@name", txtname.Text)             cmd.Parameters.AddWithValue("@age", txtAge.Text)             cmd.Parameters.AddWithValue("@class", txtClass.Text)             cmd.Parameters.AddWithValue("@sid", sid)             cmd.ExecuteNonQuery()             con.Close()             cmd.Dispose()             Fill_grid()             GridView1.EditIndex = -1             Response.Write("<script> alert('Upadted Successfully');</script>")         End If         If e.CommandName = "Delete" Then             Dim cmd As New SqlCommand()             cmd.Connection = con             cmd.CommandText = "Delete from Student_info  where Student_id=@sid"             cmd.Parameters.AddWithValue("@sid", sid)             cmd.ExecuteNonQuery()             con.Close()             cmd.Dispose()             Fill_grid()         End If     End Sub     Protected Sub GridView1_RowDeleting(sender As Object, e As GridViewDeleteEventArgs) Handles GridView1.RowDeleting     End Sub     Protected Sub GridView1_RowEditing(sender As Object, e As GridViewEditEventArgs) Handles GridView1.RowEditing         GridView1.EditIndex = e.NewEditIndex         Fill_grid()     End Sub     Protected Sub GridView1_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit         GridView1.EditIndex = -1         Fill_grid()     End Sub     Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs) Handles GridView1.RowUpdating     End Sub

In this Article we have discussed how to insert, update , delete and show data in gridview. if you have any query regarding this you can leave your question in comment section.