|
|
|
|
|
|
|
|
|
|
|
|
|
Books |
|
|
|
|
|
|
|
Resources |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Feeds Subscription |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Simple Insert, Select, Edit, Update and Delete in Asp.Net GridView control |
|
Posted by
Moderator1
on
7/12/2007 11:24:12 AM
|
Category:
ADO.NET |
|
|
Total Views :
334040 |
|
Adding to your Favorites....
|
|
|
 |
|
|
|
|
|
|
|
Introduction |
|
|
This article explains the methods of binding Asp.Net GridView control with simple
DataSet or DataTable, and also explains the methods like Insert, Edit, Update and
Delete function in the GridView control. |
|
|
|
|
You can see most of the articles and tutorials in many websites teach you the way
to bind a GridView control to the database with some Data Source controls such as
SQLDataSource, ObjectDataSource, AccessDataSource and even XMLDatasource. But this
article focus on the other way, by binding the GridView control to the database
with the help of simple DataTable and perform adding of new records to the database
from the footer row of the GridView control. And on each row, we are going to manipulate
the records by editing, updating, cancelling and deleting functions.
Sample Scenario
For demonstration, we are going to fill an ASP.NET GridView control with data from
a database. Let us take a simple Customer Table. This customer table
contains 6
columns such as Customer Unique Code, Name of
|
|
|
|
the Customer, Gender,
City, State and Customer Type. We are going to add new records to the database and
populate it in the GridView control. Then record manipulation (edit, update and
delete) will be done in each and every column with the server controls such as TextBox
and DropDownList. In these 6 columns, we are not going to display Customer Code,
and to edit Customer Name and City columns we are going to provide TextBox, to edit
Gender and Customer Type we are going to use DropDownList. Additionally, the values
for Gender DropDownList will be filled with static values such as Male and Female,
other DropDownList for Customer Type, we will be filled dynamically with the values
from the Database.
|
|
|
Pre-requisites
Your project or website must be ASP.NET AJAX enabled website. Because we are going
to add the GridView in an UpdatePanel. So your GridView control will be look smart
without unnecessary postbacks. You need to create a Customer Table with 6 columns
for Customer Code[Code], Name[Name], Gender[Gender], City[City], State[State] and
Customer Type[Type], with your desired data types. Then create a class file in your
App_Code folder and create a Default.aspx along with code-behind file Default.aspx.cs.
|
|
Step 1.
Create Class File ‘CustomersCls.cs’
We need to create a class file to do database manipulations such as select, insert,
delete and update data in the Customer Table. So we add a class file as ‘CustomersCls.cs’
in App_Code section. Let us write five methods in the class file as follows
|
|
public void Insert(string CustomerName, string Gender, string City, string State,
string CustomerType)
{
// Write your own Insert statement blocks
}
public DataTable Fetch()
{
// Write your own Fetch statement blocks, this method should return a DataTable
}
public DataTable FetchCustomerType()
{
// Write your own Fetch statement blocks to fetch Customer Type from its
master table and this method should return a DataTable
}
public void Update(int CustomerCode, string CustomerName, string Gender, string
City, string CustomerType)
{
// Write your own Update statement blocks.
}
public void Delete(int CustomerCode)
{
// Write your own Delete statement blocks.
} |
|
|
Step 2: Make Design File ‘Default.aspx’
In the Default.aspx page, add an UpdatePanel control. Inside the UpdatePanel, add
a GridView, set AutoGenerateColumns as False. Change the ShowFooter Flag to True
and set the DataKeyNames your column name for Customer Code and Customer Type, in
our case it is Code and Type. Then click on the Smart Navigation Tag of the GridView
control, choose Add New Column and add 5 BoundField columns with DataField values
as Name, Gender, City, State and Type, plus 2 CommandField columns with one for
Edit/Update and another for Delete functions. Now your GridView control is ready.
But as first step, we need to add some new records into the database. For that we
need to place the controls in the Footer row. So we have to convert all these BoundField
columns as TemplateField columns. To do this again, click on the Smart Navigation
Tag on the GridView choose Edit Columns, the Field’s property window will open.
Select column by column from Name to Customer Type, include also Edit column, and
select ‘Convert this field into a TemplateField’. Now all the BoundField columns
will be converted to TemplateField columns except the Delete column.
|
|
Column[0] – Name
Right click on the GridView control, select Edit Template, choose column[0] – Name,
you can view a label placed in the ItemTemplate section and a TextBox placed in
the EditItemTemplate section. Add another Texbox in the FooterTemplate section and
name it as txtNewName.
Column[1] - Gender
Now again select Edit Template, choose column[1] - Gender, replace the TextBox with
a DropDownList, name it as cmbGender, add Male and Female as their ListItem values.
On the Edit DataBindings of the cmbGender, add Eval("Gender") to its selectedvalue.
Add another DropDownList in the FooterTemplate section and name it as cmbNewGender.
Column[2] –City & Column[3] - State
Add Texboxes in both column’s FooterTemplate section and name it as txtNewCity and
txtNewState respectively.
|
|
|
|
Column[4] - Type
In this column’s EditItemTemplate section, replace the TextBox with a DropDownList,
name it as cmbType. Also add another DropDownList in the FooterTemplate section
and name it as cmbNewType. Both these DropDownList’s we are going to fill with dynamic
data from database. So specify both DropDownList’s DataTextField and DataValueField
as Type.
Column[5] - Edit
Just add a link button into the FooterTemplate section, specify its CommandName
property as ‘AddNew’.
For your persual, we have provided the complete source code of the GridView control
below. The State column in our sample is read-only. So you cannot find TextBox for
that column in the EditItemTemplate section. |
|
Click here to view Source Code of the GridView Control
HyperLink
|
|
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Code,
Type" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDataBound="GridView1_RowDataBound"
OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowCommand="GridView1_RowCommand"
ShowFooter="True" OnRowDeleting="GridView1_RowDeleting">
<Columns>
<asp:TemplateField HeaderText="Name" SortExpression="Name">
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtNewName" runat="server"></asp:TextBox> </FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender">
<EditItemTemplate>
<asp:DropDownList ID="cmbGender" runat="server" SelectedValue='<%#
Eval("Gender") %>'>
<asp:ListItem Value="Male" Text="Male"></asp:ListItem>
<asp:ListItem Value="Female" Text="Female"></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("Gender") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="cmbNewGender" runat="server" >
<asp:ListItem Selected="True" Text="Male" Value="Male"></asp:ListItem>
<asp:ListItem Text="Female" Value="Female"></asp:ListItem>
</asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="txtCity" runat="server" Text='<%# Bind("City") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtNewCity" runat="server" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("City") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="State" SortExpression="State">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("State") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtNewState" runat="server" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("State") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Type">
<EditItemTemplate>
<asp:DropDownList ID="cmbType" runat="server" DataTextField="Type" DataValueField="Type">
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Eval("Type") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="cmbNewType" runat="server" DataTextField="Type"
DataValueField="Type"> </asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit" ShowHeader="False">
<EditItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True"
CommandName="Update" Text="Update"></asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False"
CommandName="Cancel" Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False"
CommandName="AddNew" Text="Add New"></asp:LinkButton>
</FooterTemplate>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
CommandName="Edit" Text="Edit"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True"
ShowHeader="True" />
</Columns>
</asp:GridView>
|
|
|
Step 3: Make Code-behind File ‘Default.aspx.cs’
Now we are going to do the code-behind part of this page. Les us explain you event
by event coding on each methods. In the code-behind page, create an instance for
the Customer class as follows
|
CustomersCls customer=new CustomersCls(); |
Then create a private method 'FillCustomerInGrid' to retrieve the
existing customer list from the database
and bind it to the GridView. The CustomersCls
class’s Fetch() method is used and it returns the data to a DataTable.
On first stage it will return empty rows. So you cannot see any header, data or even footer rows of
the GridView control. You can only see an empty space or you see only the EmptyDataText.
So you cannot add any new data from the footer row.
private void FillCustomerInGrid()
{
DataTable dtCustomer= customer.Fetch();
if (dtCustomer.Rows.Count>0)
{
GridView1.DataSource = dtCustomer;
GridView1.DataBind();
}
else
{
dtCustomer.Rows.Add(dtCustomer.NewRow());
GridView1.DataSource = dtCustomer;
GridView1.DataBind();
int TotalColumns = GridView1.Rows[0].Cells.Count;
GridView1.Rows[0].Cells.Clear();
GridView1.Rows[0].Cells.Add(new TableCell());
GridView1.Rows[0].Cells[0].ColumnSpan = TotalColumns;
GridView1.Rows[0].Cells[0].Text = "No Record Found";
}
} |
In this article, we have provided a workaround to fix this problem. Closely look
at the method FillCustomerInGrid, there is a conditional
statement to check the rows exists in DataTable or not. Now go to the else part
of the if statement, see the block of code we provided there. Simply we have added
an empty row to the DataTable. Then bind it to the GridView control. To give a professional
look to the GridView control, we do little bit more by providing ColumnSpan and
set a Text as "No Record Found", this text will be displayed if the GridView is
empty without any rows and you can see both the Header and Footer of the GridView
control.
Initialize GridView control
In the page load event, we have to call this FillCustomerInGrid method as follows,
protected void Page_Load(object sender, EventArgs e)
{
If (!IsPostBack)
{
FillCustomerInGrid();
}
} |
Fill DropDownList in GridView with dynamic values
In column[4] - Type, there are two DropDownList controls, one in the EditItemTemplate
section (cmbType) and another in FooterTemplate (cmbNewType). We have to fill both
these DropDownList controls with some dynamic data. If you look at our CustomersCls
class, we have a separate method called FetchCustomerType. In the RowDataBound event
of the GridView control insert the following code.
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
DropDownList cmbType = (DropDownList)e.Row.FindControl("cmbType");
if (cmbType != null)
{
cmbType.DataSource = customer.FetchCustomerType();
cmbType.DataBind();
cmbType.SelectedValue = GridView1.DataKeys[e.Row.RowIndex].Values[1].ToString();
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
DropDownList cmbNewType = (DropDownList)e.Row.FindControl("cmbNewType");
cmbNewType.DataSource = customer.FetchCustomerType();
cmbNewType.DataBind();
}
}
|
Previously in this article, we have set the DataKeyNames values as Code, Type. If
you see in the above code, we use one of the DataKeyNames value as the SelectedValue
for the cmbType control, this is to retain the value of the cmbType in EditMode.
The index value of Code is 0 and Type is 1. So we use as follows
|
cmbType.SelectedValue = GridView1.DataKeys[e.Row.RowIndex].Values[1].ToString(); |
So far we have initialized the GridView control with the datatable and also make
some values to be filled in the Footer DropDownList cmbNewType. Run the application, you can see the GridView only with the Footer row and data in the cmbNewType control.
Let us start to code for adding new records into the database when we click ‘Add
New’ linkbutton.
Add New Records from GridView control
Create an event for the GridView’s RowCommand and add the following code in it.
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtNewName=(TextBox)GridView1.FooterRow.FindControl("txtNewName");
DropDownList cmbNewGender = (DropDownList)GridView1.FooterRow.FindControl("cmbNewGender");
TextBox txtNewCity = (TextBox)GridView1.FooterRow.FindControl("txtNewCity");
TextBox txtNewState = (TextBox)GridView1.FooterRow.FindControl("txtNewState");
DropDownList cmbNewType = (DropDownList)GridView1.FooterRow.FindControl("cmbNewType");
customer.Insert(txtNewName.Text, cmbNewGender.SelectedValue, txtNewCity.Text, txtNewState.Text, cmbNewType.SelectedValue) ;
FillCustomerInGrid();
}
}
|
In the above code, we are declaring and finding the controls in the GridView’s footer
section and use the CustomersCls class insert method to add the new data into the
database. Then we are calling the FillCustomerInGrid method to fill the GridView
control with the newly inserted values. Now save everything and run your application.
Put some test data in the Textboxes and select some values in the DropDownLists
and click on the Add New linkbutton. You can see data inserted into the database
and listed in the GridView control.
|
|
Edit and Update in GridView
In the RowEditing event of the GridView, add the following lines of code. This will
switch a specific row of the GridView to Edit Mode.
|
|
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
FillCustomerInGrid();
} |
After the GridView swithes to Edit Mode, you can view the TextBoxes and DropDownlList
controls along with Update and Cancel linkbuttons in the Edit mode. To cancel this
action, add the following two lines of code in the GridView’s RowCancelingEdit event.
|
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs
e)
{
GridView1.EditIndex = -1;
FillCustomerInGrid();
} |
|
|
You can update the data to the customer table, by adding the following lines of
code in the GridView’s RowUpdating event.
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtName = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtName");
DropDownList cmbGender = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("cmbGender");
TextBox txtCity = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtCity");
DropDownList cmbType = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("cmbType");
customer.Update(GridView1.DataKeys[e.RowIndex].Values[0].ToString(),txtName.Text, cmbGender.SelectedValue,txtCity.Text, cmbType.SelectedValue);
GridView1.EditIndex = -1;
FillCustomerInGrid();
}
|
The above block of codes in RowUpdating event, finds the control in the GridView,
takes those values in pass it to the CustomersCls class Update method. The first
parameter GridView1.DataKeys[e.RowIndex].Values[0].ToString() will return the Code
of the Customer. That is the unique code for each customer to perform update function.
Delete in GridView
To delete a row from the customer table, add the following lines of code in the
GridView’s RowDeleting event. Here you have to pass the unique Code of customer
which is in GridView1.DataKeys[e.RowIndex].Values[0].ToString() to the Delete method
of the CustomersCls class.
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
customer.Delete(GridView1.DataKeys[e.RowIndex].Values[0].ToString());
FillCustomerInGrid();
} |
This article gives you some basic idea of inserting data into database from a GridView
control and does all database manipulations within the GridView without binding
it with any Asp.Net Data source controls.
|
|
|
Click here to view our Sample GridView with database manipulations |
|
|
You need to Login or Register to download Source Code.
|
|
|
|
|
|
|
|
|
|
|
|
| | | |