Most Most IMP Add Update Delete in Gridview Asp.net

Most Most IMP Add Update Delete in Gridview Asp.net

Add Update Delete in Gridview Asp.net

Add Update Delete in Gridview Asp.net

This article is about simple way to Add Update and Delete records using asp.net GridView using Dataset or Datatable. We shall update the database table naming (Download Demo)

"UserTable". 

Schema for UserTable is as follows

Note:I have used SqlServer 2008

For this article we are going to fill GridView by data from database.

We are going to make perform following operations on data using GridView

1. Add New record into database; here data will directly be added into database table
2. Update Records into database, using edit link.
3. Delete Records using delete link

To make Add Update Delete in Gridview more user friendly; make sure your website is Ajax Enabled and Gridview is put under UpdatePanel. This will avoid unnecessary 

postback and Gridview control will look smart.

Before we develop GridView, lets work out main functions for database operations. Create a Class File naming "ManageUsers" and add following functions one by one

Fetch Data from Database.

view plainprint?

1. public DataTable Fetch()  
2.     {  
3.         string sql = "Select * From UserTable";  
4.         SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);  
5.         DataTable dt = new DataTable();  
6.         da.Fill(dt);  
7.         return dt;  
8.         // Write your own Logic for fetching data, this method should return a DataTable  
9.     }  

Update Data into Database

view plainprint?

1. public void Update(int id, string FirstName, string LastName, string EmailAddress, string LoginId, string Password, string StartDate, string EndDate)  
2.     {  
3.         string sql = "UPDATE UserTable SET [First Name] = '"+ FirstName + "',[Last Name] = '" + LastName + "',[Login Id] = '" + LoginId +"' ,[Password] = '" + Password   
4.   
5. + "'";  
6.              sql += ",[Start Date] = '" + StartDate +"',[End Date] = '" + EndDate +"',[Email Address] = '" + EmailAddress + "' WHERE Id=" + id;  
7.   
8.         SqlConnection conn = new SqlConnection(cnstr);  
9.         conn.Open();  
10.         SqlCommand cmd = new SqlCommand(sql, conn);  
11.         cmd.ExecuteNonQuery();  
12.         conn.Close();  
13.         conn.Dispose();  
14.   
15.     }  

Insert new records into Database

view plainprint?

1. public void Insert(string FirstName, string LastName, string EmailAddress, string LoginId, string Password, string StartDate, string EndDate)  
2.     {  
3.        string sql = "INSERT INTO UserTable ([First Name],[Last Name],[Login Id],[Password],[Start Date],[End Date],[Email Address]) ";  
4.        sql +=" VALUES ('"+ FirstName +"','" + LastName + "','" + LoginId +"','" + Password + "','" + StartDate + "','" + EndDate + "','" + EmailAddress + "')";  
5.              
6.         SqlConnection conn = new SqlConnection(cnstr);  
7.         conn.Open();  
8.         SqlCommand cmd = new SqlCommand(sql, conn);  
9.         cmd.ExecuteNonQuery();  
10.         conn.Close();  
11.         conn.Dispose();  
12.     }  

Delete records from Database

view plainprint?

1. public void Delete(int id)  
2.     {  
3.         string sql = "DELETE FROM UserTable WHERE Id=" + id;  
4.         SqlConnection conn = new SqlConnection(cnstr);  
5.         conn.Open();  
6.         SqlCommand cmd = new SqlCommand(sql, conn);  
7.         cmd.ExecuteNonQuery();  
8.         conn.Close();  
9.         conn.Dispose();  
10.         // Write your own Delete statement blocks.  
11.     }  

In above code cnstr is the connection string for our 

database.

Now we shall make changes in GridView (Note: Make sure GridView is added on page and under updatepanel) (Download Demo)

Once we add gridview inside updatepanel, do following things

1. set AutoGenerateColumns as False.
2. Change the ShowFooter Flag to True
3. set the DataKeyNames your column name for Id. (This field can have multiple values depending on requirement, we are going to use Id, as it is primary key of our datatable. These values are available in GridView events. We can access them using.GridView.DataKeys[e.RowIndex].Values[0]
4. Smart Navigation Tag of the GridView control, choose Add New Column
   
   
   Now add 8 BoundField columns with DataField values as "Id","First Name","Last Name","Email Address", "Login Id", "Password", "Start Date", "End Date"; also add 2 CommandField columns with one for Edit/Update and another for Delete functions. Now we can see our Grid View control is ready. Above data fields are column names of the DataTable we are using. (To bind correct columns, our datatable column names and gridview bound column's "DataField" should match.
5. We shall also give facility to add new records, for that we will put controls in Footer row. To do this we need to convert all above BoundField columns to template field columns. Click on the Smart Navigation Tag on the GridView choose Edit Columns, the Field’s property window will open.  Select column by column from Id, include also Edit column, and select ‘Convert this field into a TemplateField’
   
   
   Except "delete" column all the BoundField columns are converted in to Template Field Column.
6. Now one by one we will controls to Footer of the GridView. Right click on the GridView control, select Edit Template.
   
   Column[0] – Id: select Edit Template, choose column[0] – Id, you can view a label placed in the ItemTemplate section and a TextBox placed in the EditItemTemplate section (Id is our Primary key and it should not be editable). In edit Item Section template, put Label. Do not add anything in Footer Template.
   
   Column[1] - First Name 
   Now again select Edit Template, choose column[1] - First Name, Add another TextBox in the FooterTemplate section and name it as NewFirstName.  
   
   Column[2] - Last Name 
   Choose column[2] - Last Name, Add another TextBox in the FooterTemplate section and name it as NewLastName.  
   
   Column[3] - Email Address 
   Choose column[3] - Email Address, Add another TextBox in the FooterTemplate section and name it as NewEmailAddress.  
   
   Column[4] - Login Id 
   Choose column[4] - Login Id, Add another TextBox in the FooterTemplate section and name it as NewLoginId.  
   
   Column[5] - Password 
   Choose column[5] - Password, Add another TextBox in the FooterTemplate section and name it as NewPassword.  
   
   Column[6] - Start Date 
   Choose column[6] - Start Date, Add another TextBox in the FooterTemplate section and name it as NewStartDate. (Check following source view, we have added, Calender Extender of Ajax Toolkit) 
   
   Column[7] - End Date 
   Choose column[7] - End Date, Add another TextBox in the FooterTemplate section and name it as NewEndDate.  (Check following source view, we have added, Calender Extender of Ajax Toolkit) 
   
   Column[8] - Edit 
   Just add a link button into the FooterTemplate section, specify its CommandName property as ‘AddNew’.
7. 
   Source of the Gridview control changes to following, check we have added Calender Extender for date selection. (Download Demo)
   
   
   view plainprint?

   1. <asp:gridview autogeneratecolumns="False" datakeynames="Id" enablemodelvalidation="True" id="gridUserManagement" onrowcancelingedit="gridUserManagement_RowCancelingEdit" onrowcommand="gridUserManagement_RowCommand" onrowdeleting="gridUserManagement_RowDeleting" onrowediting="gridUserManagement_RowEditing" onrowupdating="gridUserManagement_RowUpdating" runat="server" showfooter="True">  
   2.         <columns>  
   3.             <asp:templatefield headertext="Id" sortexpression="Id">  
   4.                 <edititemtemplate>  
   5.                     <asp:label id="TextBox1" runat="server" text="<%# Bind("Id") %>"></asp:label>  
   6.                 </edititemtemplate>  
   7.                 <itemtemplate>  
   8.                     <asp:label id="Label1" runat="server" text="<%# Bind("Id") %>"></asp:label>  
   9.                 </itemtemplate>  
   10.             </asp:templatefield>  
   11.             <asp:templatefield headertext="First Name">  
   12.                 <edititemtemplate>  
   13.                     <asp:textbox id="TextBox2" runat="server" text="<%# Bind("[First Name]") %>"></asp:textbox>  
   14.                 </edititemtemplate>  
   15.                 <footertemplate>  
   16.                     <asp:textbox id="NewFirstName" runat="server"></asp:textbox>  
   17.                 </footertemplate>  
   18.                 <itemtemplate>  
   19.                     <asp:label id="Label2" runat="server" text="<%# Bind("[First Name]") %>"></asp:label>  
   20.                 </itemtemplate>  
   21.             </asp:templatefield>  
   22.             <asp:templatefield headertext="Last Name">  
   23.                 <edititemtemplate>  
   24.                     <asp:textbox id="TextBox3" runat="server" text="<%# Bind("[Last Name]") %>"></asp:textbox>  
   25.                 </edititemtemplate>  
   26.                 <footertemplate>  
   27.                     <asp:textbox id="NewLastName" runat="server"></asp:textbox>  
   28.                 </footertemplate>  
   29.                 <itemtemplate>  
   30.                     <asp:label id="Label3" runat="server" text="<%# Bind("[Last Name]") %>"></asp:label>  
   31.                 </itemtemplate>  
   32.             </asp:templatefield>  
   33.             <asp:templatefield headertext="Email Address">  
   34.                 <edititemtemplate>  
   35.                     <asp:textbox id="TextBox4" runat="server" text="<%# Bind("[Email Address]") %>"></asp:textbox>  
   36.                 </edititemtemplate>  
   37.                 <footertemplate>  
   38.                     <asp:textbox id="NewEmailAddress" runat="server"></asp:textbox>  
   39.                 </footertemplate>  
   40.                 <itemtemplate>  
   41.                     <asp:label id="Label4" runat="server" text="<%# Bind("[Email Address]") %>"></asp:label>  
   42.                 </itemtemplate>  
   43.             </asp:templatefield>  
   44.             <asp:templatefield headertext="Login Id">  
   45.                 <edititemtemplate>  
   46.                     <asp:textbox id="TextBox5" runat="server" text="<%# Bind("[Login Id]") %>"></asp:textbox>  
   47.                 </edititemtemplate>  
   48.                 <footertemplate>  
   49.                     <asp:textbox id="NewLoginId" runat="server"></asp:textbox>  
   50.                 </footertemplate>  
   51.                 <itemtemplate>  
   52.                     <asp:label id="Label5" runat="server" text="<%# Bind("[Login Id]") %>"></asp:label>  
   53.                 </itemtemplate>  
   54.             </asp:templatefield>  
   55.             <asp:templatefield headertext="Password">  
   56.                 <edititemtemplate>  
   57.                     <asp:textbox id="TextBox6" runat="server" text="<%# Bind("Password") %>"></asp:textbox>  
   58.                 </edititemtemplate>  
   59.                 <footertemplate>  
   60.                     <asp:textbox id="NewPassword" runat="server"></asp:textbox>  
   61.                 </footertemplate>  
   62.                 <itemtemplate>  
   63.                     <asp:label id="Label6" runat="server" text="<%# Bind("Password") %>"></asp:label>  
   64.                 </itemtemplate>  
   65.             </asp:templatefield>  
   66.             <asp:templatefield headertext="Start Date">  
   67.                 <edititemtemplate>  
   68.                     <asp:textbox id="TextBox8" runat="server" text="<%# Bind("[Start Date]") %>"></asp:textbox>  
   69.                     <cc1:calendarextender format="dd-MMM-yyyy" id="CalendarExtender1" runat="server" targetcontrolid="TextBox8"></cc1:calendarextender>  
   70.                 </edititemtemplate>  
   71.                 <footertemplate>  
   72.                     <asp:textbox id="NewStartDate" runat="server"></asp:textbox>  
   73.                     <cc1:calendarextender format="dd-MMM-yyyy" id="CalendarExtender4" runat="server" targetcontrolid="NewStartDate"></cc1:calendarextender>  
   74.                 </footertemplate>  
   75.                 <itemtemplate>  
   76.                     <asp:label id="Label8" runat="server" text="<%# Bind("[Start Date]") %>"></asp:label>  
   77.                 </itemtemplate>  
   78.             </asp:templatefield>  
   79.             <asp:templatefield headertext="End Date">  
   80.                 <edititemtemplate>  
   81.                     <asp:textbox id="TextBox9" runat="server" text="<%# Bind("[End Date]") %>"></asp:textbox>  
   82.                     <cc1:calendarextender format="dd-MMM-yyyy" id="CalendarExtender2" runat="server" targetcontrolid="TextBox9"></cc1:calendarextender>  
   83.                 </edititemtemplate>  
   84.                 <footertemplate>  
   85.                     <asp:textbox id="NewEndDate" runat="server"></asp:textbox>  
   86.                     <cc1:calendarextender format="dd-MMM-yyyy" id="CalendarExtender3" runat="server" targetcontrolid="NewEndDate"></cc1:calendarextender>  
   87.                 </footertemplate>  
   88.                 <itemtemplate>  
   89.                     <asp:label id="Label9" runat="server" text="<%# Bind("[End Date]") %>"></asp:label>  
   90.                 </itemtemplate>  
   91.             </asp:templatefield>  
   92.             <asp:templatefield showheader="False">  
   93.                 <edititemtemplate>  
   94.                     <asp:linkbutton causesvalidation="True" commandname="Update" id="LinkButton1" runat="server" text="Update"></asp:linkbutton>  
   95.                      <asp:linkbutton causesvalidation="False" commandname="Cancel" id="LinkButton2" runat="server" text="Cancel"></asp:linkbutton>  
   96.                 </edititemtemplate>  
   97.                 <footertemplate>  
   98.                     <asp:linkbutton commandname="AddNew" id="AddNew" runat="server">Add New</asp:linkbutton>  
   99.                 </footertemplate>  
   100.                 <itemtemplate>  
   101.                     <asp:linkbutton causesvalidation="False" commandname="Edit" id="LinkButton1" runat="server" text="Edit"></asp:linkbutton>  
   102.                 </itemtemplate>  
   103.             </asp:templatefield>  
   104.             <asp:commandfield showdeletebutton="True">  
   105.         </asp:commandfield></columns>  
   106.         </asp:gridview>  
8. Now we shall develop code in code behind of web page i.e. in C#. We have already developed ManageUsers Class in start. We will develop code for handing Edit,Update and Insert operations of GridView. Add events as shown in following image.
   
   
   
   
   
   Now check out following code for each event we have added for GridView.
   
   Create object of the Class "ManageUsers"; and write function to Bind Customer details to the GridView.
   view plainprint?

   1. private void BindCustomers()  
   2.     {  
   3.         DataTable CustomerTable = customer.Fetch();  
   4.   
   5.         if (CustomerTable.Rows.Count > 0)  
   6.         {  
   7.             gridUserManagement.DataSource = CustomerTable;  
   8.             gridUserManagement.DataBind();  
   9.         }  
   10.         else  
   11.         {  
   12.             CustomerTable.Rows.Add(CustomerTable.NewRow());  
   13.             gridUserManagement.DataSource = CustomerTable;  
   14.             gridUserManagement.DataBind();  
   15.   
   16.             int TotalColumns = gridUserManagement.Rows[0].Cells.Count;  
   17.             gridUserManagement.Rows[0].Cells.Clear();  
   18.             gridUserManagement.Rows[0].Cells.Add(new TableCell());  
   19.             gridUserManagement.Rows[0].Cells[0].ColumnSpan = TotalColumns;  
   20.             gridUserManagement.Rows[0].Cells[0].Text = "No Record Found";  
   21.         }  
   22.     }  
   
   Initializing the GridView control on Page load:
   
   view plainprint?

   1. protected void Page_Load(object sender, EventArgs e)  
   2.     {  
   3.         if (!IsPostBack)  
   4.         {  
   5.             BindCustomers();  
   6.         }  
   7.     }  
   
   GridView RowEditing Event
   
   view plainprint?

   1. protected void gridUserManagement_RowEditing(object sender, GridViewEditEventArgs e)  
   2.     {  
   3.         gridUserManagement.EditIndex = e.NewEditIndex;  
   4.         BindCustomers();   
   5.     }  
   
   GridView RowCancelingEdit Event
   
   view plainprint?

   1. protected void gridUserManagement_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)  
   2.     {  
   3.         gridUserManagement.EditIndex = -1;  
   4.         BindCustomers();   
   5.     }  
   
   Updating Records (GridView RowUpdating Event): Update the data to the UserTable, by adding the following lines of code in the GridView’s RowUpdating event
   
   view plainprint?

   1. protected void gridUserManagement_RowUpdating(object sender, GridViewUpdateEventArgs e)  
   2.     {  
   3.         TextBox txtNewFirstName = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox2");  
   4.         TextBox txtNewLastName = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox3");  
   5.         TextBox txtNewEmailAddress = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox4");  
   6.         TextBox txtNewLoginId = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox5");  
   7.         TextBox txtNewPassword = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox6");  
   8.         TextBox txtNewStartDate = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox8");  
   9.         TextBox txtNewEndDate = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox9");  
   10.   
   11.         customer.Update(Convert.ToInt32(gridUserManagement.DataKeys[e.RowIndex].Values[0].ToString()), txtNewFirstName.Text, txtNewLastName.Text, txtNewEmailAddress.Text, txtNewLoginId.Text, txtNewPassword.Text, txtNewStartDate.Text, txtNewEndDate.Text);  
   12.         gridUserManagement.EditIndex = -1;  
   13.         BindCustomers();   
   14.     }  
   The above block of codes in RowUpdating event, finds the control in the GridView, takes those values in pass it to the ManageUsers class Update method. The first parameter GridView1.DataKeys[e.RowIndex].Values[0].ToString() will return the Id of the Customer. That is the unique id for each customer to perform update function.
   
   Delete In GridView: 
   
   view plainprint?

   1. protected void gridUserManagement_RowDeleting(object sender, GridViewDeleteEventArgs e)  
   2.     {  
   3.         customer.Delete(Convert.ToInt32(gridUserManagement.DataKeys[e.RowIndex].Values[0].ToString()));  
   4.         BindCustomers();   
   5.     }  
   
   Add New Records from GridView control
   
   
   view plainprint?

   1. protected void gridUserManagement_RowCommand(object sender, GridViewCommandEventArgs e)  
   2.     {  
   3.         if (e.CommandName.Equals("AddNew"))  
   4.         {  
   5.             TextBox txtNewFirstName = (TextBox)gridUserManagement.FooterRow.FindControl("NewFirstName");  
   6.             TextBox txtNewLastName = (TextBox)gridUserManagement.FooterRow.FindControl("NewLastName");  
   7.             TextBox txtNewEmailAddress = (TextBox)gridUserManagement.FooterRow.FindControl("NewEmailAddress");  
   8.             TextBox txtNewLoginId = (TextBox)gridUserManagement.FooterRow.FindControl("NewLoginId");  
   9.             TextBox txtNewPassword = (TextBox)gridUserManagement.FooterRow.FindControl("NewPassword");  
   10.             TextBox txtNewStartDate = (TextBox)gridUserManagement.FooterRow.FindControl("NewStartDate");  
   11.             TextBox txtNewEndDate = (TextBox)gridUserManagement.FooterRow.FindControl("NewEndDate");  
   12.   
   13.             customer.Insert(txtNewFirstName.Text, txtNewLastName.Text, txtNewEmailAddress.Text, txtNewLoginId.Text, txtNewPassword.Text, txtNewStartDate.Text, txtNewEndDate.Text);  
   14.             BindCustomers();  
   15.         }   
   16.     }  

This is how we can develop a simple GridView control in Asp.Net with Add Update Delete functionality.

Download Demo Code here --> Add Update Delete in Gridview Asp.net