Insert, Delete, Update in GridView in ASP.Net using C#

Insert, Delete, Update in GridView in ASP.Net using C#

SP.NET GridView is a very common and useful control. Here, I’m explaining how to work with GridView control in ASP.NET, like how to insert, delete and update record in GridView control. Follow bellow steps to know how to work with GridView in ASP.NET. Step 1:- Create Database in SqlServer and create a table within this database. For demonstration here I have created database named “Demo” and table named “GridView” CREATE DATABASE DEMO GO USE DEMO GO CREATE TABLE GridView ( id INT PRIMARY KEY IDENTITY, name VARCHAR (50) NOT NULL, age INT NOT NULL, salary FLOAT NOT NULL, country VARCHAR(50) NOT NULL, city VARCHAR(50) NOT NULL, photopath VARCHAR(500) NULL ) GO Note: Here “id” column is auto increment and primary key.  Step 2:- Add connection string in your application’s web.config file and change name and connectionString according your SQL Server configuration as following. <configuration>      <connectionStrings>                 <add name="dbconnection" providerName="System.Data.SqlClient"                     connectionString="Data Source=.;Initial Catalog=demo; User Id=xyz; password=123456" />                 </connectionStrings> </configuration> Step 3:- Drag & drop GridView control from Toolbox on “.aspx” page and write down the following line of code within <asp:GridView> section or simple copy the following line of code and put where you want to display GridVIew. <div>         <asp:GridView ID="GridView1" runat="server" ShowHeaderWhenEmpty="True"             AutoGenerateColumns="False" onrowdeleting="RowDeleting"             OnRowCancelingEdit="cancelRecord" OnRowEditing="editRecord"             OnRowUpdating="updateRecord" CellPadding="4"             EnableModelValidation="True" GridLines="None" Width="1297px"             ForeColor="#333333" >             <RowStyle HorizontalAlign="Center" />             <AlternatingRowStyle BackColor="White" />             <EditRowStyle BackColor="#7C6F57" />             <FooterStyle BackColor="#1C5E55" ForeColor="White" Font-Bold="True" />             <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />             <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />             <RowStyle BackColor="#E3EAEB" />             <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />             <Columns>             <asp:TemplateField>             <HeaderTemplate>Id</HeaderTemplate>             <ItemTemplate>             <asp:Label ID ="lblId" runat="server" Text='<%#Bind("id")%>'></asp:Label>             </ItemTemplate>             </asp:TemplateField>             <asp:TemplateField>             <HeaderTemplate>Name</HeaderTemplate>             <ItemTemplate>             <asp:Label ID ="lblName" runat="server" Text='<%#Bind("name") %>'></asp:Label>             </ItemTemplate>             <EditItemTemplate>             <asp:TextBox ID="txtName" runat="server" Text='<%#Bind("name") %>' MaxLength="50"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtName" runat="server" Text="*" ToolTip="Enter name"ControlToValidate="txtName"></asp:RequiredFieldValidator>             <asp:RegularExpressionValidator ID="revtxtName" runat="server" Text="*" ToolTip="Enter alphabate " ControlToValidate="txtName" ValidationExpression="^[a-zA-Z'.\s]{1,40}$"></asp:RegularExpressionValidator>                         </EditItemTemplate>             <FooterTemplate>             <asp:TextBox ID="txtNewName" runat="server" MaxLength="50"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtNewName" runat="server" Text="*" ToolTip="Enter name"ControlToValidate="txtNewName"></asp:RequiredFieldValidator>             <asp:RegularExpressionValidator ID="revtxtNewName" runat="server" Text="*" ToolTip="Enter alphabate " ControlToValidate="txtNewName" ValidationExpression="^[a-zA-Z'.\s]{1,40}$"></asp:RegularExpressionValidator>                        </FooterTemplate>             </asp:TemplateField>                         <asp:TemplateField>             <HeaderTemplate>Age</HeaderTemplate>             <ItemTemplate>             <asp:Label ID="lblAge" runat ="server" Text='<%#Bind("age") %>'></asp:Label>             </ItemTemplate>             <EditItemTemplate>             <asp:TextBox ID ="txtAge" runat="server" Text='<%#Bind("age") %>' MaxLength="2"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtAge" runat="server" Text="*" ToolTip="Enter age"ControlToValidate="txtAge"></asp:RequiredFieldValidator>             <asp:RegularExpressionValidator ID="revtxtAge" runat="server" Text="*" ToolTip="Enter numeric value" ControlToValidate="txtAge" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>                        </EditItemTemplate>             <FooterTemplate>             <asp:TextBox ID="txtNewAge" runat="server" MaxLength="2"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtNewAge" runat="server" Text="*" ToolTip="Enter age"ControlToValidate="txtNewAge"></asp:RequiredFieldValidator>             <asp:RegularExpressionValidator ID="revNewtxtAge" runat="server" Text="*" ToolTip="Enter numeric value" ControlToValidate="txtNewAge" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>             </FooterTemplate>             </asp:TemplateField>             <asp:TemplateField>             <HeaderTemplate>Salary</HeaderTemplate>             <ItemTemplate>             <asp:Label ID = "lblSalary" runat="server" Text='<%#Bind("salary") %>'></asp:Label>             </ItemTemplate>             <EditItemTemplate>             <asp:TextBox ID="txtSalary" runat="server" Text='<%#Bind("salary") %>'  MaxLength="10"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtSalary" runat="server" Text="*"  ToolTip="Enter salary"ControlToValidate="txtSalary"></asp:RequiredFieldValidator>             <asp:RegularExpressionValidator ID="revtxtSalary" runat="server" Text="*" ToolTip="Enter numeric value" ControlToValidate="txtSalary" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>                        </EditItemTemplate>             <FooterTemplate>             <asp:TextBox ID="txtNewSalary" runat="server" MaxLength="10"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtNewSalary" runat="server" Text="*"  ToolTip="Enter salary" ControlToValidate="txtNewSalary"></asp:RequiredFieldValidator>             <asp:RegularExpressionValidator ID="revtxtNewSalary" runat="server" Text="*" ToolTip="Enter numeric value" ControlToValidate="txtNewSalary" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>             </FooterTemplate>             </asp:TemplateField>             <asp:TemplateField>             <HeaderTemplate>Country</HeaderTemplate>             <ItemTemplate>             <asp:Label ID = "lblCountry" runat="server" Text='<%#Bind("country") %>'></asp:Label>             </ItemTemplate>             <EditItemTemplate>             <asp:TextBox ID="txtCountry" runat="server" Text='<%#Bind("country") %>' MaxLength="20"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtCountry" runat="server" Text="*" ToolTip="Enter country"ControlToValidate="txtCountry"></asp:RequiredFieldValidator>                      </EditItemTemplate>             <FooterTemplate>             <asp:TextBox ID="txtNewCountry" runat="server" MaxLength="20"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtNewCountry" runat="server" Text="*" ToolTip="Enter country" ControlToValidate="txtNewCountry"></asp:RequiredFieldValidator>             </FooterTemplate>             </asp:TemplateField>             <asp:TemplateField>             <HeaderTemplate>City</HeaderTemplate>             <ItemTemplate>             <asp:Label ID = "lblCity" runat="server" Text='<%#Bind("city") %>'></asp:Label>             </ItemTemplate>             <EditItemTemplate>             <asp:TextBox ID="txtCity" runat="server" Text='<%#Bind("city") %>' MaxLength="20"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtCity" runat="server" Text="*" ToolTip="Enter city"ControlToValidate="txtCity"></asp:RequiredFieldValidator>             </EditItemTemplate>             <FooterTemplate>             <asp:TextBox ID="txtNewCity" runat="server" MaxLength="20"></asp:TextBox>             <asp:RequiredFieldValidator ID="rfvtxtNewCity" runat="server" Text="*" ToolTip="Enter city"ControlToValidate="txtNewCity"></asp:RequiredFieldValidator>             </FooterTemplate>             </asp:TemplateField>             <asp:TemplateField>             <HeaderTemplate>Photo</HeaderTemplate>             <ItemTemplate>             <asp:Image ID="imgPhoto" Width="100px" Height="100px" runat="server" text="Photo"ImageUrl='<%#Bind("photopath") %>' />             </ItemTemplate>             <EditItemTemplate>             <asp:FileUpload ID="fuPhoto" runat="server" ToolTip="select Employee Photo"/>             <asp:RegularExpressionValidator ID="revfuPhoto" runat="server" Text="*" ToolTip="Image formate only" ControlToValidate="fuPhoto" ValidationExpression="[a-zA-Z0_9].*\b(.jpeg|.JPEG|.jpg|.JPG|.jpe|.JPE|.png|.PNG|.mpp|.MPP|.gif|.GIF)\b"></asp:RegularExpressionValidator>             </EditItemTemplate>             <FooterTemplate>             <asp:FileUpload ID="fuNewPhoto" runat="server" ToolTip="select Employee Photo"/>             <asp:RequiredFieldValidator ID="rfvfuNewPhoto" runat="server" ErrorMessage="*"ToolTip="Select Photo" ControlToValidate="fuNewPhoto"></asp:RequiredFieldValidator>             <asp:RegularExpressionValidator ID="revfuNewPhoto" runat="server" Text="*" ToolTip="Image formate only" ControlToValidate="fuNewPhoto" ValidationExpression="[a-zA-Z0_9].*\b(.jpeg|.JPEG|.jpg|.JPG|.jpe|.JPE|.png|.PNG|.mpp|.MPP|.gif|.GIF)\b"></asp:RegularExpressionValidator>             </FooterTemplate>             </asp:TemplateField>             <asp:TemplateField>             <HeaderTemplate>Operation</HeaderTemplate>             <ItemTemplate>             <asp:Button ID="btnEdit" runat="server" CommandName="Edit" Text="Edit" />             <asp:Button ID="btnDelete" runat="server" CommandName="Delete" Text="Delete"CausesValidation="true" OnClientClick="return confirm('Are you sure?')" />             </ItemTemplate>             <EditItemTemplate>             <asp:Button ID="btnUpdate" runat="server" CommandName="Update" Text="Update" />             <asp:Button ID="btnCancel" runat="server" CommandName="Cancel" Text="Cancel"CausesValidation="false" />             </EditItemTemplate>             <FooterTemplate>             <asp:Button ID="btnNewInsert" runat="server" Text="Insert" OnClick="InsertNewRecord"/>             <asp:Button ID="btnNewCancel" runat="server" Text="Cancel" OnClick="AddNewCancel"CausesValidation="false" />             </FooterTemplate>                     </asp:TemplateField>                        </Columns>             <EmptyDataTemplate>                       No record available                                 </EmptyDataTemplate>                </asp:GridView>         <br />         <asp:Button ID="btnAdd" runat="server" Text="Add New Record" OnClick="AddNewRecord" />       </div> Step 4:- Create one class within App_Code folder. Here I have given this class named is GlobalClass and write following line of code. using System.Data; using System.Data.SqlClient; public class GlobalClass {     public static SqlDataAdapter adap;     public static DataTable dt;     // Stored image path before updating the record     public static string imgEditPath; } Step 5:- Create one folder, named Images, where we have stored employees photos see in following image. Step 6:- Write down following line of code in “.cs” page which related to your .aspx page (e.g. if your .aspx page name is default.aspx then your .csfile is default.aspx.cs). using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Web.UI.WebControls; using System.IO; public partial class _Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         if (!IsPostBack)         {             // Call FillGridView Method             FillGridView();         }     }     /// <summary>     /// Fill record into GridView     /// </summary>     public void FillGridView()     {         try         {             string cnString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;             SqlConnection con = new SqlConnection(cnString);             GlobalClass.adap = new SqlDataAdapter("select * from gridview", con);             SqlCommandBuilder bui = new SqlCommandBuilder(GlobalClass.adap);             GlobalClass.dt = new DataTable();             GlobalClass.adap.Fill(GlobalClass.dt);             GridView1.DataSource = GlobalClass.dt;             GridView1.DataBind();         }         catch         {             Response.Write("<script> alert('Connection String Error...') </script>");         }     }     /// <summary>     /// Edit record     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void editRecord(object sender, GridViewEditEventArgs e)     {         // Get the image path for remove old image after update record         Image imgEditPhoto = GridView1.Rows[e.NewEditIndex].FindControl("imgPhoto") as Image;         GlobalClass.imgEditPath = imgEditPhoto.ImageUrl;         // Get the current row index for edit record         GridView1.EditIndex = e.NewEditIndex;         FillGridView();     }     /// <summary>     /// Cancel the operation (e.g. edit)     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void cancelRecord(object sender, GridViewCancelEditEventArgs e)     {         GridView1.EditIndex = -1;         FillGridView();     }     /// <summary>     /// Add new row into DataTable if no record found in Table     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void AddNewRecord(object sender, EventArgs e)     {         try         {             if (GlobalClass.dt.Rows.Count > 0)             {                 GridView1.EditIndex = -1;                 GridView1.ShowFooter = true;                 FillGridView();             }             else             {                 GridView1.ShowFooter = true;                 DataRow dr = GlobalClass.dt.NewRow();                 dr["name"] = "0";                 dr["age"] = 0;                 dr["salary"] = 0;                 dr["country"] = "0";                 dr["city"] = "0";                 dr["photopath"] = "0";                 GlobalClass.dt.Rows.Add(dr);                 GridView1.DataSource = GlobalClass.dt;                 GridView1.DataBind();                 GridView1.Rows[0].Visible = false;             }         }         catch         {             Response.Write("<script> alert('Row not added in DataTable...') </script>");         }     }     /// <summary>     /// Cancel new added record     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void AddNewCancel(object sender, EventArgs e)     {         GridView1.ShowFooter = false;         FillGridView();     }     /// <summary>     /// Insert New Record     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void InsertNewRecord(object sender, EventArgs e)     {         try         {             string strName = GlobalClass.dt.Rows[0]["name"].ToString();             if (strName == "0")             {                 GlobalClass.dt.Rows[0].Delete();                 GlobalClass.adap.Update(GlobalClass.dt);             }             TextBox txtName = GridView1.FooterRow.FindControl("txtNewName") as TextBox;             TextBox txtAge = GridView1.FooterRow.FindControl("txtNewAge") as TextBox;             TextBox txtSalary = GridView1.FooterRow.FindControl("txtNewSalary") as TextBox;             TextBox txtCountry = GridView1.FooterRow.FindControl("txtNewCountry") as TextBox;             TextBox txtCity = GridView1.FooterRow.FindControl("txtNewCity") as TextBox;             FileUpload fuPhoto = GridView1.FooterRow.FindControl("fuNewPhoto") as FileUpload;             Guid FileName = Guid.NewGuid();             fuPhoto.SaveAs(Server.MapPath("~/Images/" + FileName + ".png"));             DataRow dr = GlobalClass.dt.NewRow();             dr["name"] = txtName.Text.Trim();             dr["age"] = txtAge.Text.Trim();             dr["salary"] = txtSalary.Text.Trim();             dr["country"] = txtCountry.Text.Trim();             dr["city"] = txtCity.Text.Trim();             dr["photopath"] = "~/Images/" + FileName + ".png";             GlobalClass.dt.Rows.Add(dr);             GlobalClass.adap.Update(GlobalClass.dt);             GridView1.ShowFooter = false;             FillGridView();         }         catch         {             Response.Write("<script> alert('Record not added...') </script>");         }     }     /// <summary>     /// Update the record     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void updateRecord(object sender, GridViewUpdateEventArgs e)     {         try         {             TextBox txtName = GridView1.Rows[e.RowIndex].FindControl("txtName") as TextBox;             TextBox txtAge = GridView1.Rows[e.RowIndex].FindControl("txtAge") as TextBox;             TextBox txtSalary = GridView1.Rows[e.RowIndex].FindControl("txtSalary") as TextBox;             TextBox txtCountry = GridView1.Rows[e.RowIndex].FindControl("txtCountry") as TextBox;             TextBox txtCity = GridView1.Rows[e.RowIndex].FindControl("txtCity") as TextBox;             FileUpload fuPhoto = GridView1.Rows[e.RowIndex].FindControl("fuPhoto") as FileUpload;             Guid FileName = Guid.NewGuid();             if (fuPhoto.FileName != "")             {                 fuPhoto.SaveAs(Server.MapPath("~/Images/" + FileName + ".png"));                 GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["photopath"] = "~/Images/" + FileName + ".png";                 File.Delete(Server.MapPath(GlobalClass.imgEditPath));             }             GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["name"] = txtName.Text.Trim();             GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["age"] =Convert.ToInt32(txtAge.Text.Trim());             GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["salary"] =Convert.ToInt32(txtSalary.Text.Trim());             GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["country"] = txtCountry.Text.Trim();             GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["city"] = txtCity.Text.Trim();             GlobalClass.adap.Update(GlobalClass.dt);                GridView1.EditIndex = -1;             FillGridView();         }         catch         {             Response.Write("<script> alert('Record updation fail...') </script>");         }     }     /// <summary>     /// Delete Record     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void RowDeleting(object sender, GridViewDeleteEventArgs e)     {         try         {             GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex].Delete();             GlobalClass.adap.Update(GlobalClass.dt);             // Get the image path for removing deleted's record image from server folder             Image imgPhoto = GridView1.Rows[e.RowIndex].FindControl("imgPhoto") as Image;             File.Delete(Server.MapPath(imgPhoto.ImageUrl));             FillGridView();         }         catch         {             Response.Write("<script> alert('Record not deleted...') </script>");         }     } } Step 7:- Now, save and build you application and execute. Your application output display as following. It’s your application first screen. Because there is not data in table so it displace message “No record available”. Step 8:- For inserting data, click on button “Add New Record”. Now fill the data. Every controls have validation for prohibited wrong input.   After filling data, your GridView looking as following.  Here I have inserted three records. Note: - Id are generated automatic for every new record. Step 9:- For updating records click button “Edit” and change TextBox Data and you can select new photo of employee. For example in place of age“28”, updated with “30” and in place of salary “50000”, updated with “55000”, then click button “Update”. Through button “Cancel” you return previous stage. Step 10:- For record deletion, click on button “Delete”. Before deleting record, confirmation alert message are popup. Here I have deleted 3rdrecord (e.g. Andrew Deniel). After deleting record your GridView looks as following.