Gridview is the most basic and important control to display data in dot net based application. I have been using this control from very long time in many of my projects it may be windows or web based applications. Many of you will also agree that this is “bread and butter” of any applications. But we must always take care when such developer
friendly control must be used. There also other controls like Repeaters,Datalist, Formview etc which also does same job as gridview.
So what makes gridview “The Gridview”
Very important difference is flexibility of gridview i.e. built in support for sorting, caching, paging or editing. Now these listed features are very much required, as we list data user may want to sort it or edit it and of course paging is also necessary to show large amount of data divided into pages. Yeah these features can also be implemented using other data controls but you need to write your own code.
And when to use Gridview
Gridview can be used when we need to quickly list data from database and not much concern about how grid data looks, then Gridview is way to go. And also when we want to use in-built features of the control like sorting, caching paging etc.
Let see how to use gridview in ASP.NET application to insert/update/delete records using Gridview events. There are four important events in gridview which can be used to perform basic data operations insert/update/delete which listed below.
- OnRowEditing
- OnRowCancelingEdit
- OnRowUpdating
- OnRowDeleting
I will briefly explain what each event does and then put step-by-step code list.
- Onrowediting – This event is raised when a row’s Edit button is clicked. This event can be used to make gridview ready for editing i.e. edit mode.
- OnRowCancelingEdit – This event is raised when a row’s Cancel button is clicked. When we want turn back to normal mode cancel button can be used.
- Onrowupdating – This event is raised when a row’s Update button is clicked. As name suggest it is used update the existing record. This event can be used to make actual changes or inserting new record in to database.
- Onrowdeleting – This event is raised when a row’s Delete button is clicked. Yes this helps to delete the record.
Now let see how to add and perform different actions using above mentioned events of gridview.I have done very basic coding to make it more understandable.
Step 1. Add gridview control on the page. On aspx page I added following code to add the gridview. And also included all the required events.
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”false”
DataKeyNames=”CategoryID“
onrowcancelingedit=”GridView1_RowCancelingEdit”
onrowdeleting=”GridView1_RowDeleting” onrowediting=”GridView1_RowEditing”
onrowupdating=”GridView1_RowUpdating”
>
<Columns>
<asp:CommandField HeaderText=”Edit-Update” ShowEditButton=”true” />
<asp:BoundField DataField=”CategoryID“ HeaderText=”Category ID” ReadOnly=”true”/>
<asp:BoundField DataField=”CategoryName“ HeaderText=”Category Name” />
<asp:BoundField DataField=”Description” HeaderText=”Description” />
<asp:CommandField HeaderText=”Delete” ShowDeleteButton=”true” />
</Columns>
</asp:GridView>
Note: AutoGenerateColumns property is false i.e. it will not generate columns which are basically corresponding field name of binded datatable. As it will not automatically genetrate columns we must define all columns which we want to display. Here <asp:BoundField> is columns name which we want to display and DataField is the actual column name in the table and HeaderText is Display name of the column.
And there is <asp:CommandField which is where we will show our buttons (Edit/Cancel and Delete) inside Gridview.We have set ShowEditButton and ShowDeleteButton to true this will show buttons on each row.
Step2.Add the insert button which will help us to add new record.
<asp:Button ID=”btnInsert“ runat=”server” Text=”Insert” onclick=”btnInsert_Click“ />
Step3. Let’s bind the data to gridview. Here I am using Northwind sample database to list the data on gridview. To do this I have created loadData() function to bind the gridview and binded it on Page Load event. Below is the code listing of the same.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
loadData();
}
}
protected void loadData()
{
SqlConnection conn = new
SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[“ConnString”].ConnectionString);
string query = “SELECT CategoryID, CategoryName,Description FROM Categories”;
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
conn.Close();
}
Step4. Now add logic to insert new record and update the existing record. First we will see how to insert new record.
protected void btnInsert_Click(object sender, EventArgs e)
{
SqlConnection conn = newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[“ConnString“].ConnectionString);
string query = “SELECT CategoryID, CategoryName, Description FROM Categories”;
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow dr = dt.NewRow();
dt.Rows.InsertAt(dr,0);
GridView1.EditIndex = 0;
GridView1.DataSource = dt;
GridView1.DataBind();
((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text = “Insert”;
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
loadData();
}
Note: On insert button click event we bind data again to gridview as we did on pageload but now we will include blank column on gridview to insert new record. To do that we simply add new row to datatable. This can be done using DataRow object of DataTable. DataRow sr = dt.NewRow() this creates the column at specified position in the datatable. In our case, we are adding it at top i.e. first row (dt.Rows.InsertAt(dr, 0)) . And next GridView1.EditIndex =0 sets the gridview in editmode. And last line in the above code listing sets the name of Button in out GridView to Insert. This I will explain in the later section why I did that. And RowEditing event is triggered if user clicks on edit button on gridview and sets in edit mode.
Now quickly see how to insert new record. Below is code listing performs the new insert action. I have used RowUpdating event of Gridview to insert and this same event is used to perform update on existing records.
Here logic is to check which button is clicked by user is it insert button or update button. If its insert it will perform insert action which is “if” block and if its update then “else” block will be executed.
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
if (((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text == “Insert”)
{
SqlConnection conn = new
SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[“ConnString”].ConnectionString);
string query = “INSERT INTO Categories (CategoryName, Description) VALUES(@name,@desc)”;
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(“@name”, SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[2].Controls[0]).Text;
cmd.Parameters.Add(“@desc“, SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[3].Controls[0]).Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
else
{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[“ConnString”].ConnectionString);
string query = “UPDATE Categories SET CategoryName = @name, Description = @desc WHERE CategoryID=@cat_id “;
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(“@name”, SqlDbType.NVarChar,15).Value
= ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
cmd.Parameters.Add(“@desc“, SqlDbType.VarChar).Value
= ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text;
cmd.Parameters.Add(“@cat_id“, SqlDbType.Int).Value
= Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
GridView1.EditIndex = -1;
loadData();
}
To Cancel the editing of the record RowCancelingEdit event is used.On this event Gridview is set back to normal mode. This is shown below.
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{ GridView1.EditIndex = -1;
loadData();
}
Note: EditIndex is a property of gridview of integer datatype. If property value is zero then it sets the gridviews first row in editmode and if it’s -1 it turns it back to normal mode.
Step5. Last step is delete the record. This is also similar to what I have done it before, here RowDeleting event is used to perform the delete action.
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlConnection conn = newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[“ConnString”].ConnectionString);
string query = “DELETE FROM Categories WHERE CategoryID=@cat_id “;
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(“@cat_id“, SqlDbType.Int).Value
= Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
loadData();
}
That’s it this was the basic insert/update/delete actions using in built events of the gridview.I will also include other built in features of the gridview in subsequent posts. Sample project can be downloaded from the below links.
Download Sample Project:
https://app.box.com/s/ej8jlzc61knyrndb524e
Note: Project uses AdventureWorks sample database which can be downloaded from-
S&P Infotech Blog 
Pingback: Create Gridview from csv file in C sharp (C#) - Techxpress.in