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
- Add New record into database; here data will directly be added into database table
- Update Records into database, using edit link.
- 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.
public DataTable Fetch()
{
string sql = "Select * From UserTable";
SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
// Write your own Logic for fetching data, this method should return a DataTable
}
Update Data into Database
public void Update(int id, string FirstName, string LastName, string EmailAddress, string LoginId, string Password, string StartDate, string EndDate)
{
string sql = "UPDATE UserTable SET [First Name] = '"+ FirstName + "',[Last Name] = '" + LastName + "',[Login Id] = '" + LoginId +"' ,[Password] = '" + Password
+ "'";
sql += ",[Start Date] = '" + StartDate +"',[End Date] = '" + EndDate +"',[Email Address] = '" + EmailAddress + "' WHERE Id=" + id;
SqlConnection conn = new SqlConnection(cnstr);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
Insert new records into Database
public void Insert(string FirstName, string LastName, string EmailAddress, string LoginId, string Password, string StartDate, string EndDate)
{
string sql = "INSERT INTO UserTable ([First Name],[Last Name],[Login Id],[Password],[Start Date],[End Date],[Email Address]) ";
sql +=" VALUES ('"+ FirstName +"','" + LastName + "','" + LoginId +"','" + Password + "','" + StartDate + "','" + EndDate + "','" + EmailAddress + "')";
SqlConnection conn = new SqlConnection(cnstr);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
Delete records from Database
public void Delete(int id)
{
string sql = "DELETE FROM UserTable WHERE Id=" + id;
SqlConnection conn = new SqlConnection(cnstr);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
// Write your own Delete statement blocks.
}
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
- set AutoGenerateColumns as False.
- Change the ShowFooter Flag to True
- 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]
- 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.
- 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.
- 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’.
Source of the Gridview control changes to following, check we have added Calender Extender for date selection. (Download Demo)
Add New
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.
private void BindCustomers()
{
DataTable CustomerTable = customer.Fetch();
if (CustomerTable.Rows.Count > 0)
{
gridUserManagement.DataSource = CustomerTable;
gridUserManagement.DataBind();
}
else
{
CustomerTable.Rows.Add(CustomerTable.NewRow());
gridUserManagement.DataSource = CustomerTable;
gridUserManagement.DataBind();
int TotalColumns = gridUserManagement.Rows[0].Cells.Count;
gridUserManagement.Rows[0].Cells.Clear();
gridUserManagement.Rows[0].Cells.Add(new TableCell());
gridUserManagement.Rows[0].Cells[0].ColumnSpan = TotalColumns;
gridUserManagement.Rows[0].Cells[0].Text = "No Record Found";
}
}
Initializing the GridView control on Page load:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindCustomers();
}
}
GridView RowEditing Event
protected void gridUserManagement_RowEditing(object sender, GridViewEditEventArgs e)
{
gridUserManagement.EditIndex = e.NewEditIndex;
BindCustomers();
}
GridView RowCancelingEdit Event
protected void gridUserManagement_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gridUserManagement.EditIndex = -1;
BindCustomers();
}
Updating Records (GridView RowUpdating Event): Update the data to the UserTable, by adding the following lines of code in the GridView’s RowUpdating event
protected void gridUserManagement_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtNewFirstName = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox2");
TextBox txtNewLastName = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox3");
TextBox txtNewEmailAddress = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox4");
TextBox txtNewLoginId = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox5");
TextBox txtNewPassword = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox6");
TextBox txtNewStartDate = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox8");
TextBox txtNewEndDate = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox9");
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);
gridUserManagement.EditIndex = -1;
BindCustomers();
}
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:
protected void gridUserManagement_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
customer.Delete(Convert.ToInt32(gridUserManagement.DataKeys[e.RowIndex].Values[0].ToString()));
BindCustomers();
}
Add New Records from GridView control
protected void gridUserManagement_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtNewFirstName = (TextBox)gridUserManagement.FooterRow.FindControl("NewFirstName");
TextBox txtNewLastName = (TextBox)gridUserManagement.FooterRow.FindControl("NewLastName");
TextBox txtNewEmailAddress = (TextBox)gridUserManagement.FooterRow.FindControl("NewEmailAddress");
TextBox txtNewLoginId = (TextBox)gridUserManagement.FooterRow.FindControl("NewLoginId");
TextBox txtNewPassword = (TextBox)gridUserManagement.FooterRow.FindControl("NewPassword");
TextBox txtNewStartDate = (TextBox)gridUserManagement.FooterRow.FindControl("NewStartDate");
TextBox txtNewEndDate = (TextBox)gridUserManagement.FooterRow.FindControl("NewEndDate");
customer.Insert(txtNewFirstName.Text, txtNewLastName.Text, txtNewEmailAddress.Text, txtNewLoginId.Text, txtNewPassword.Text, txtNewStartDate.Text, txtNewEndDate.Text);
BindCustomers();
}
}
This is how we can develop a simple GridView control in Asp.Net with Add Update Delete functionality.
Read more...
