Parent-Child Data Manipulations with GridView and DetailsView control

from:
http://www.aspdotnetcodes.com/Parent_Child_GridView_DetailsView.aspx

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 : 6890 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 [Show full code..] <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.

转载于:https://www.cnblogs.com/aspxphpjsprb/archive/2008/01/26/1053942.html