|
|
|
|
|
Parent-Child Data Manipulations with GridView and DetailsView control |
|
Posted by
Moderator1
on
10/17/2007 2:22:50 AM
|
Category:
ADO.NET |
|
|
Total Views :
21255 |
|
Adding to your Favorites....
|
|
|
 |
|
|
|
|
|
Introduction |
|
This article explains the concepts of Parent-Child relationships Data Manipulations with
the Asp.Net Data-bound controls. This is achieved by using the Asp.Net GridView
control and DetailsView control to perform various database activities such as Add,
Edit, Update and Delete operations.
|
|
|
Master-child or Parent-child relationship is a very common use of data structure
in now day’s application. This makes the data to be more organized and present the
data in an efficient way for the viewers. And Asp.Net data bound controls are rich
enough to achieve this relationship in a simpler fashion with minimal coding.
Sample Scenario
For demonstration purpose, we are going to use a Customer Table in our sample database.
We are going to take distinct value from the Customer Type column and use it as
master information and listed it in a GridView control. When the Customer Type is
selected, its corresponding child records (i.e. related records) will be binded
in the DetailsView control, where we are going to perform Add, Edit, Update and
Delete operations.
|
Disclaimer: Master-Child data manipulation has to be done from
two database tables with some foreign key constraints. For simplification and education
purpose, we have designed only one table named as ‘Customers’ and used it in this
article. It is up to the readers or viewers or anyone of this article to design
your own Master-Child or Parent-Child table structures.
|
|
|
Pre-requisites
Make your project or website as an ASP.NET AJAX enabled website. Because we are going
to add the GridView and DetailsView control in an UpdatePanel. So both the
control will be work nicely without any unnecessary postbacks. We assume that you
have created a Customer Table with 6 columns for Customer Code[Cus_Code – Identity
column & Primary Key], Name[Cus_Name], Gender[Cus_Gender], City[Cus_City], State[Cus_State]
and Customer Type[Cus_Type], with your desired data types. For demonstration purpose,
we filled this table with some data. The main criteria are the Customer Type column
must have some identical values such as Retailer and Wholesaler. 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 FetchByCustomerType(string CustomerType)
{
// Write your own block of statement to fetch records from the Customer
table based on the
// Customer Type, this method should return a DataTable.
}
public DataTable FetchOnlyCustomerType()
{
// This block of statement fetches the distinct value of Customer Type from
the Customers Table.
string sql = "Select distinct Cus_Type from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public void Update(int CustomerCode, string CustomerName, string Gender, string
City, string State, 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. Set the DataKeyNames as “Cus_Type”.
Add new BoundField column in the GridView and set is DataField as “Cus_Type”. Then
add a “Select” CommandField. So the GridView control source code will look as follows
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Cus_Type"
OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:BoundField DataField="Cus_Type" HeaderText="Customer
Type" SortExpression="Cus_Type"
/>
<asp:CommandField HeaderText="Select"
ShowHeader="True" ShowSelectButton="True"
/>
</Columns>
</asp:GridView>
|
|
|
|
|
Now drag and drop a DetailsView control into the UpdatePanel. Now set the properties
such as AllowPaging as “True”, AutoGenerateDeleteButton as "True", AutoGenerateEditButton
as "True", AutoGenerateInsertButton as "True", AutoGenerateRows as "False" and DataKeyNames
as "Cus_Code". Now we have to add BoundFields for Customer Code, Customer Name,
City, State and Customer Type columns, and for the Gender column we have to add
a TemplateField column. In that TemplateField column, InsertItemTemplate and EditItemTemplate
section, drop a DropDownList control, named cmbSex and add two items in the list such has Male and Female. In the ItemTemplate section, add a label control and set
its Text property to Cus_Gender. Now the DetailsView is ready and its source code
will look like
|
|
|
|
|
Click here to view Source Code of the DetailsView Control
HyperLink
|
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
AllowPaging="True" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True"
AutoGenerateInsertButton="True" AutoGenerateRows="False" DataKeyNames="Cus_Code"
OnDataBound="DetailsView1_DataBound" OnItemCommand="DetailsView1_ItemCommand" OnItemDeleting="DetailsView1_ItemDeleting"
OnItemInserting="DetailsView1_ItemInserting" OnItemUpdating="DetailsView1_ItemUpdating"
OnModeChanging="DetailsView1_ModeChanging" OnPageIndexChanging="DetailsView1_PageIndexChanging">
<EmptyDataTemplate>
No Records Found
</EmptyDataTemplate>
<Fields>
<asp:BoundField DataField="Cus_Code" HeaderText="Customer
Code" ReadOnly="True" SortExpression="Cus_Code" />
<asp:BoundField DataField="Cus_Name" HeaderText="Name"
SortExpression="Cus_Name" />
<asp:TemplateField HeaderText="Gender" SortExpression="Cus_Gender">
<EditItemTemplate>
<asp:DropDownList ID="cmbSex" runat="server" SelectedValue='<%#
Eval("Cus_Gender") %>'>
<asp:ListItem Value="M">Male</asp:ListItem>
<asp:ListItem Value="F">Female</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="cmbSex" runat="server">
<asp:ListItem Selected="True" Value="M">Male</asp:ListItem>
<asp:ListItem Value="F">Female</asp:ListItem>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Cus_Gender")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Cus_City" HeaderText="City" SortExpression="Cus_City"
/>
<asp:BoundField DataField="Cus_State" HeaderText="State" SortExpression="Cus_State"
/>
<asp:BoundField DataField="Cus_Type" HeaderText="Type" ReadOnly="True"
SortExpression="Cus_Type" />
</Fields>
</asp:DetailsView>
|
|
Step 3: Make Code-behind File ‘Default.aspx.cs’
Next we are going to do the code-behind part of this project. 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(); |
Initialize GridView control
In the page load event, we have to initialize the GridView control by calling the
FetchOnlyCustomerType method as follows,
protected void Page_Load(object sender, EventArgs e)
{
If (!IsPostBack)
{
GridView1.DataSource = customer. FetchOnlyCustomerType();
GridView1.DataBind();
}
} |
Now if you run the application, you can view the GridView control listing the Customer
Type values distinctly. Next we have to write down the RowCommand event to fetch
the child information when particular Customer Type is selected. The GridView’s
RowCommand event will be as follows
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Select"))
{
int RowIndex=Convert.ToInt16(e.CommandArgument.ToString());
ViewState["CustomerType"] = GridView1.DataKeys[RowIndex].Value.ToString();
BindDetailsView();
}
} |
In the above code, we are storing the CustomerType value in the ViewState field
for future use. Then we are calling a private method BindDetailsView. This method
binds the data to the DetailsView control by using the FetchByCustomerType method
in the CustomersCls class and its codes are as follows
private void BindDetailsView()
{
DetailsView1.DataSource = customer.FetchByCustomerType(ViewState["CustomerType"].ToString());
DetailsView1.DataBind();
}
|
Now run the application. GridView control will shown Customer Type information.
When you click on the select link of the GridView control, all the related records
for that Customer Type will be populated in the DetailsView control. Thus maintaining
Master-Child relationships is done between GridView and DetailsView control.
Let us start performing some database manipulations such as adding new records,
edit, and update and delete existing records in the DetailsView control. Adding
new records and editing existing records in a DetailsView control can be achieved
by changing the DetailsView to different modes. The modes of DetailsView control
are Insert, Edit and ReadOnly. Before performing any database manipulation in the
DetailsView, we have to switch the control to its appropriate mode from its ItemCommand
event. The code block of changing mode is as follows
protected void DetailsView1_ItemCommand(object sender, DetailsViewCommandEventArgs
e)
{
if (e.CommandName.Equals("New"))
{
DetailsView1.ChangeMode(DetailsViewMode.Insert);
BindDetailsView();
}
else if (e.CommandName.Equals("Cancel"))
{
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);
BindDetailsView();
}
else if (e.CommandName.Equals("Edit"))
{
DetailsView1.ChangeMode(DetailsViewMode.Edit);
BindDetailsView();
}
}
|
Add New Records from DetailsView control
To add new records from the DetailsView control, first we have to switch the DetailsVeiw
to Insert mode. When switching to Insert mode, the entire fields of the DetailsView
control will be changed to TextBox controls except the Gender’s (cmbSex) DropDownList
control since it is placed inside the TemplateField. We should not allow the users
to enter data in the Customer Code TextBox which is Identity Primary Key column
and also we should restrict the Customer Type TextBox since it should be either
Retailer or Wholesaler. As we have stored this value in the ViewState, there is
no need for the user to enter this value again. To achieve this, we have to make
both these as ReadOnly fields and it can be done by writing some code in the DataBound
event of the DetailsView control as follows.
protected void DetailsView1_DataBound(object sender, EventArgs e)
{
if (DetailsView1.CurrentMode == DetailsViewMode.Insert)
{
TextBox TextBox1 = (TextBox)DetailsView1.Rows[0].Cells[1].Controls[0];
TextBox1.Text = "Autogenerate Code";
TextBox1.ReadOnly = true;
TextBox TextBox2 = (TextBox)DetailsView1.Rows[5].Cells[1].Controls[0];
TextBox2.Text = this.ViewState["CustomerType"].ToString();
TextBox2.ReadOnly = true;
}
}
|
Then we have to create an event ItemInserting for the DetailsView control and put
the code as follows to insert new data into the Customer table.
protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs
e)
{
TextBox TextBox2 = (TextBox)DetailsView1.Rows[1].Cells[1].Controls[0];//Name
DropDownList cmbSex = (DropDownList)DetailsView1.FindControl("cmbSex");//Gender
TextBox TextBox4 = (TextBox)DetailsView1.Rows[3].Cells[1].Controls[0];//City
TextBox TextBox5 = (TextBox)DetailsView1.Rows[4].Cells[1].Controls[0];//State
TextBox TextBox6 = (TextBox)DetailsView1.Rows[5].Cells[1].Controls[0];//Customer
Type
customer.Insert(TextBox2.Text, cmbSex.SelectedValue, TextBox4.Text, TextBox5.Text,
TextBox6.Text);
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);
BindDetailsView();
} |
Edit and Update in GridView
When you click on the Edit link in the DetailsView, the control will automatically
switch to edit mode. This is done by the ItemCommand event of the DetailsView control.
Let us concentrate on updating the new values into the database from the DetailsView
control. Updating records in a DetailsView is same as Inserting records from it.
Use DetailsView’s ItemUpdating to perform Update operation as follows.
protected void DetailsView1_ItemUpdating(object sender, DetailsViewInsertEventArgs
e)
{
TextBox TextBox2 = (TextBox)DetailsView1.Rows[1].Cells[1].Controls[0];//Name
DropDownList cmbSex = (DropDownList)DetailsView1.FindControl("cmbSex");//Gender
TextBox TextBox4 = (TextBox)DetailsView1.Rows[3].Cells[1].Controls[0];//City
TextBox TextBox5 = (TextBox)DetailsView1.Rows[4].Cells[1].Controls[0];//State
customer.Update(DetailsView1.DataKey.Value,TextBox2.Text, cmbSex.SelectedValue,
TextBox4.Text, TextBox5.Text);
DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);
BindDetailsView();
} |
The above block of codes in ItemUpdating event, finds the control in the DetailsView,
takes those values and pass it to the CustomersCls class Update method. The first
parameter DetailsView1.DataKey.Value will return the Unique Code of the Customer
record. That is the unique code for each customer to perform update function. Along
with that we are passing the Name, Gender, City and State to perform update function.
|
Delete in DetailsView control
Deleting a row from the customer table is easy as usual. We have to add the following
lines of code in the DetailsView's ItemDeleting event. Here you have to pass the unique Code of customer
which is in DetailsView1.DataKey.Value to the Delete method
of the CustomersCls class.
|
|
protected void DetailsView1_ItemDeleting(object sender, DetailsViewDeleteEventArgs
e)
{
customer.Delete(DetailsView1.DataKey.Value);
BindDetailsView();
} |
|
Paging in the DetailsView control
To add paging in the DetailsView control, add the following lines of code in the
PageIndexChanging event of the DetailsView.
|
protected void DetailsView1_PageIndexChanging(object sender, DetailsViewPageEventArgs
e)
{
DetailsView1.PageIndex = e.NewPageIndex;
BindDetailsView();
} |
|
Disclaimer: Master-Child data manipulation has to be done from two
database tables with some foreign key constraints. For simplification and education
purpose, we have designed only one table named as ‘Customers’ and used it in this
article. It is up to the readers or viewers or anyone of this article to design
your own Master-Child or Parent-Child table structures.
|
|
|
|
Click here to view our Master-child Sample Application |
|
|
|
|
|
|
|
| | | | |