In This Chapter Displaying Related Data in Nested DataGrid Controls A Master/Detail Display with DataList and DataGrid Controls Summary ASP.NET introduced many extremely useful server controls that can reduce development time and make it easier to create attractive Web pages with a lot less programming effort. Among these is the DataGrid control, which—for developers building pages that display and manage data—has become almost the de facto solution. However, many developers still have problems using the DataGrid control when stepping beyond the basic mode that it provides for displaying rows of data. This chapter looks particularly at displaying hierarchical data from related tables or row sets. This is common in many applications, and this chapter investigates four alternative approaches. It also looks at the specific issue of providing a master/detail display where the user can choose to show or hide the related rows.

• Hierarchical Data Format
• Datatables Get Selected Row Data
• Data Grid View In Vb

Template kartu nama coreldraw x5. Displaying Related Data in Nested DataGrid Controls Developers regularly find that they have to build pages that can display data from related tables in a data source and, at first glance, the DataGrid control doesn't seem to be able to do this. Many third-party grid controls are available for ASP.NET that are designed to provide this feature, but it's quite easy to achieve the same effect with a DataGrid control or a combination of ASP.NET list controls. The process requires that the list controls be nested so that each row within the grid that displays the parent rows contains a list control bound to the related child rows.

There are several oft-used approaches for selecting the correct set of child rows for each parent row. The following are the four most common:. Declarative nested binding to a DataSet instance—This is the simplest approach, and it requires no code to be written except that required to generate and populate the DataSet instance the first time that the page is opened. Filling nested DataGrid controls programmatically from a DataSet instance—This technique allows you to extract all the data you want in one operation, while still maintaining control over the selection of child rows, and access or modify the row contents as required. Declarative nested binding to a custom function that returns a row set—This technique combines the previous two approaches, allowing custom handling of the data when creating the row set to be combined with the simple declarative approach to performing the binding. Filling nested DataGrid controls from a DataReader instance—This is a useful technique when you need to display only a few rows. It allows you to dynamically select the child rows you want for each parent row, and it gives you full control over the content at the point where the grid is being populated.

Declarative Nested Binding to a DataSet Instance The simplest way to populate nested DataGrid controls is to use syntax that allows the child rows to be specified using declarative techniques. In other words, you specify the binding properties of the nested grid at design time, and ASP.NET fetches the rows and performs the binding to generate the output at runtime. The sample page in shows nested binding of three DataGrid controls, displaying data extracted from the Northwind sample database that is provided with SQL Server. The outer, or root, DataGrid control displays details from the Customers table, and the grid nested within it displays a list of orders (in the Order History column). However, this nested grid contains within its Details column another DataGrid control, which is bound to data extracted from the Order Details table.

Creating hierarchical data bound grid view with. How to create a hierarchical grid structure? Drop down list selected index error in row data bound event of.

The result is a hierarchical display of all three sets of related data rows. Nested DataGrid controls, using declarative data binding. Running the Examples on Your Own Server You must edit the connection string in the web.config file provided in the root folder of the examples to suit your server and environment before running this example on your own server. Alternatively, you can run all the examples online. The page starts the usual Page and Import directives: <%@Page Language='VB' EnableViewState='False'% <%@Import Namespace='System.Data'% <%@Import Namespace='System.Data.OleDb'% However, in this case you turn off viewstate for the page.

You don't intend to perform postbacks, which means that you'll only generate the data once, and you don't need to preserve the values in the grid, so there is no point in storing it in the viewstate. Declaring the DataSource Property for a Nested List Control The interesting part of Listing 4.1, and the feature that makes it work, is the way you declare the DataSource attributes for the two nested DataGrid controls. Normally, as with the root DataGrid, you specify the DataSource property for the list controls at runtime. However, when you nest list controls (as in this example), you can specify a function that returns the set of data to populate the control within the declarative definition of that control.

The following is the first DataSource attribute used in the example: DataSource='<%# CType(Container.DataItemDataRowView).CreateChildView('CustOrders')%' This statement converts the data source row that is providing the data to populate the current row of the root grid into a DataRowView instance, and then it calls its CreateChildView method. The name of a relationship between the current data source row set and the child row set must be provided, and the function returns a set of child rows that are related to the current row in the parent row set.

For this to work, the data must be stored in such a way that the relationship between the parent and child row sets is available, and the obvious way to meet this criterion is to populate tables in an ADO.NET DataSet instance with the rows from the data source. Then you create the relationship(s) between these tables within the DataSet instance. In this example, you have three DataGrid controls, so there are three sets of data rows in the DataSet instance that you use to populate them: data extracted from the Customers, Orders, and Order Details tables in the Northwind database. The DataSet instance that contains these rows also contains two relationships ( DataRelation objects), named CustOrders and OrdersODetails (see ). The structure of the DataSet instance for the nested DataGrid control example. The first of these relationships is used to create the row set for the data source of the second DataGrid control ( id='dgr2') that displays details (such as the delivery address) of each order for the current customer.

The second relationship is used to create the row set for the data source of the third DataGrid control ( id='dgr3'), which displays the individual lines for each order: DataSource='<%# CType(Container.DataItemDataRowView).CreateChildView('OrdersODetails')%' When you subsequently bind the root DataGrid control to its data source, the nested grids will automatically be populated with the matching sets of child rows. Populating a DataSet Instance and Adding Relationships The code in the sample page is responsible for creating the DataSet instance and adding the relationships between the tables to it. Listing 4.2 declares a page-level variable to hold the DataSet instance and then calls a separate routine named FillDataSet in the PageLoad event handler to fill it with the data and relationships required. When you have the DataSet instance, you bind it to the root DataGrid, specify which table it should draw its data from, and call the DataBind method to initiate the process of binding all three DataGrid objects. Listing 4.2—The Page-Level Variable and the Code in the PageLoad Event Handler ' variable to hold reference to DataSet across routines Dim oDataSet As DataSet Sub PageLoad 'fill the data set with some rows from database FillDataSet('c%') ' bind the data to the grid for display dgr1.DataSource = oDataSet dgr1.DataMember = 'Customers' dgr1.DataBind End Sub Listing 4.3 shows the FillDataSet routine that is used to populate the DataSet instance. This routine receives a String object that contains the full or partial match for the customer ID whose orders you want to list. (In Listing 4.2, it is set to 'c%' to extract order details for all customers whose ID starts with c.) Using this ID, you can build the SQL statements you require to extract the appropriate sets of rows from the Customers, Orders, and Order Details tables in the database.

You have to join the Products table in the third SQL statement to get the name of the product because the Order Details table only contains a foreign key to the rows in this table—not the product name. Using Stored Procedures You could use stored procedures to extract the rows, of course, but the aim of this example is to demonstrate binding techniques for the server controls, so you use SQL statements to avoid unnecessary complexity. Next, the connection string is extracted from web.config, and you can create a Connection instance and a new empty DataSet instance. Then, within the Try.Catch construct, you create a DataAdapter instance, open the connection, and fill the three tables—changing the CommandText property of DataAdapter to the appropriate SQL statement as you go.

Creating and Adding the DataRelation Instances When the tables are filled, you can create and add the relationships you need between them. You create a new DataRelation object by specifying the name you want to assign to it, the column in the parent table that contains the key to match to the child table, and the column in the child table that contains this value as a foreign key. Shows the relationships between the Northwind database tables that are used in this example, as well as the primary key and foreign keys in each table. The relationships between the tables used in this example. The ItemDataBound Event Handler for the Customers Table DataGrid Control The root DataGrid control, which displays data from the Customers table, will execute the routine named BindOrdersGrid for each row it contains.

The task here is to create a row set containing just the appropriate matching child rows from the Orders table and then bind that row set to the nested DataGrid control within each Customers row. Along the way, after you've created the child row set, you can play with it by changing the values and the output generated by the DataGrid control. The BindOrdersGrid routine is shown in Listing 4.4. In it, you first test what type of item the event is occurring for—it could be a row containing data, a header row, a footer row, or a separator row. (All these types of row can be declared using templates or attributes of the DataGrid control, and the ItemDataBound event occurs for them all when present.) Also, notice that the code tests for an AlternatingItemRow instance. Even if you only define an element or use a BoundColumn control, the event is raised alternately as an Item row type and an AlternatingItem row type. Testing the Row Type in ItemDataBound Event Handlers A common mistake when handling the ItemDataBound event and the ItemCreated event is to fail to properly establish the type of row that each event is being raised for before trying to access the contents.

For example, if a row contains a Label control when in 'normal' mode and a TextBox control when in 'edit' mode, you must determine the row type before trying to access the Label or TextBox control. If the row type is ListItemType.Item or ListItemType.AlternatingItem, you can only access the Label control. If it is ListItemType.EditItem, you can only access the TextBox control.

The same kind of logic applies to a row that is in 'selected' mode, in which case the row type is ListItemType.SelectedItem. The next step is to get a reference to the DataGrid control within the current row. The event handler is executed once for each row in the root DataGrid control. It receives a DataGridItemEventArgs instance that contains more details of the event and a reference to the current row in the DataGrid control as a DataGridItem object. This object has a whole range of properties that can be used to set the style of the row, such as the background and foreground colors, borders, font, text alignment, and so on. However, the properties and methods you're usually most interested in when accessing or manipulating the contents of a row are those shown in Table 4.1. Table 4.1 Commonly Used Properties and Methods of the DataGridItem Object Property or Method Description Cells Gets a collection of the table cells in the current row as TableCell objects.

Attributes Enables attributes to be added to or removed from the HTML elements that are generated for the current row. Controls Returns a collection of all the child controls for the current row. DataItem Returns a reference to the source data row as a DataRowView object. DataSetIndex Returns the index of the current row within the bound data source. EnableViewState Specifies whether the controls in the current row will persist their viewstates within the page. ItemIndex Returns the index of the current row within the Items collection of the DataGrid control. ItemType Returns a value from the ListItemType enumeration that indicates the current row type.

FindControl(' id') Returns a reference to a control within the row, given its ID, or Nothing if the control is not found. HasControls Returns True if the current row contains any server controls, or False if not.

You can use the FindControl method to locate the DataGrid control we're looking for in the current row. You have to cast the result to the correct type on return because the FindControl method returns the reference as a generic Object type: Dim oGrid As DataGrid = CType(e.Item.FindControl('dgr2'), DataGrid) Then you generate the set of child rows that match the current row by creating a filtered DataView instance on the Customers table, which will only contain the row for the current customer.

You do this by extracting the ID of the current customer from the DataKeys collection of the DataGrid control, using the ItemIndex property of the DataGridItem instance passed to the routine (refer to Table 4.1). Then, to limit the rows that are displayed in the DataGrid control, you set the RowFilter property of the default DataView instance of the Customers table, as shown in this section of the code: Dim sKey As String = dgr1.DataKeys(e.Item.ItemIndex) Dim oView, oChildView As DataView oView = oDataSet.Tables('Customers').DefaultView oView.RowFilter = 'CustomerID = ' & sKey & ' Now you can use the CreateChildView method of the first (and only) row in the DataView instance to create the set of related child rows from the Orders table.

You do this the same way as in the previous declarative binding example, specifying the name of the DataRelation instance that links the two tables in the DataSet instance: oChildView = oView(0).CreateChildView( oDataSet.Relations('CustOrders')) At this point, you can perform any actions you want to carry out on the source data or on the DataGrid row and its contents. In this example, you want to hide any rows that have not yet shipped. You can do this by simply deleting them from the child DataView instance. The ItemDataBound Event Handler for the Orders Table DataGrid Control The second DataGrid control, which displays data from the Orders table, will execute the routine named BindOrderItemsGrid for each row as it is bound to its data source. Listing 4.5 shows this routine in full.

Much of this listing is similar to the BindOrdersGrid routine in Listing 4.4. The differences are summarized individually in this section.

Creating Custom Functions to Return a DataReader Instance Of course, there's no reason you can't create custom functions that return row sets as open DataReader instances rather than as DataView instances. If you did this, you could avoid handling the ItemDataBound event and instead use the same declarative approach as in the preceding example.

As you can see, the four examples in this chapter are designed to give you a taste of the possible combinations of techniques. They by no means cover the complete set of permutations. The Changes to the Server-Side Code in This Example Using a DataReader instance instead of a DataSet instance requires an almost complete change to the server-side code in the page. Listing 4.7 shows the PageLoad event handler, which binds the root DataGrid control to its data source, and the three functions that return DataReader instances. The first of these is used to generate the row set containing a list of customers that is bound to the root DataGrid control in the PageLoad event handler.

Other Approaches to Accessing the Data and Performing Nested Data Binding With the four approaches described in this chapter, you can create the row set to populate the nested DataGrid control in whatever way you want. The last example uses simple SQL statements with a DataReader instance, but you could equally well use any stored procedure to generate the required results and massage these results as in earlier examples to get exactly the row set you want. Likewise, you could build a row set from an XML document or using custom code to add the rows and columns directly. You could also combine the use of DataSet instances and DataReader instances, or you could use a HashTable instance, an ArrayList instance, or whatever data source suits the list controls you are using in the page.

Hierarchical Data Format

And while this chapter's examples use DataGrid controls, the same techniques work with various combinations of other list controls—such as Repeater, DataList, ListBox, and CheckBoxList controls.

The control displays rows of data in a grid (an HTML table), displaying one data row per grid row. This walkthrough shows you how to extend the functionality of the control so that individual grid rows can display data from a related data table. In the walkthrough, you show related data in a nested control — a control inside the grid row of the parent control. In this walkthrough, you will see how to create a control that displays a list of customers where each row of the control includes another control that displays the orders for that customer. Both controls use controls to retrieve the data from the data source. Tasks illustrated in this walkthrough include the following;.

Connect to a SQL Server database in a Microsoft Visual Studio Web site project. Use the control to manage data access. Display data returned from the database in the control. Create a with nested controls to be displayed by the control. Dynamically customize the display for each row based on run-time conditions. Optionally, use data caching with the control to reduce requests made to the database. Open Visual Studio or Visual Web Developer Express.

In the File menu, click New Web Site. The New Web Site dialog box is displayed. Under Installed Templates, click Visual Basic or Visual C# and then select ASP.NET Empty Web Site. In the Web location box, select File System, and then enter the name of the folder where you want to keep the pages for your Web site. For example, type the folder name C: WebSites NestedGridView. Visual Studio creates a Web site project that includes a Web.config file.

If your Web site does not have an AppData folder, in Solution Explorer, right-click the Web site name, select Add ASP.NET Folder, and then click AppData. Copy Northwind.mdf from the following folder to your AppData folder: C: SQL Server 2000 Sample Databases. If your Web site does not have a Default.aspx file, create one by performing the following steps:. In Solution Explorer, right-click the project name and then click Add New Item. In the Add New Item dialog box, under Installed Templates select Visual C# or Visual Basic.

Select Web Form, enter Default.aspx in the Name box, and then click Add. If Default.aspx is not open, open it. Switch to Design view. In the Toolbox, from the Data group, drag a control onto the page. If the SqlDataSource Tasks smart tag does not appear, right-click the control and then click Show Smart Tag. In the SqlDataSource Tasks panel, click Configure Data Source. The Configure Data Source wizard appears.

Datatables Get Selected Row Data

Click New Connection. The Add Connection dialog box appears. If the Data source list does not display Microsoft SQL Server Database File (SqlClient), click Change, and in the Change Data Source dialog box, select Microsoft SQL Server Database File. If the Choose Data Source dialog box appears instead of the Connection Properties dialog box, in the Data source list, select Microsoft SQL Server Database File. In the Data provider list, make sure.NET Framework Data Provider for SQL Server is selected, and then click Continue. In the Add Connection dialog box, click Browse, navigate to the AppData folder of the project you are working on, and select the Northwind.mdf file. In the Choose Your Data Connection wizard step, click Next.

In the Save the Connection String to the Application Configuration File step, make sure that the Yes, save this connection as check box is selected. Name the connection NorthwindConnectionString and then click Next. In the Configure the Select Statement step, select Specify columns from a table or view. In the Name list, select Customers. In the Columns list, select the CustomerID and CompanyName columns. Click Next.

Click Finish. Ensure that you are in Design view for the Default.aspx page. In the Toolbox, from the Data group, drag a GridView control onto the page. Click the smart tag to show the GridView Tasks panel.

Data Grid View In Vb

In the Choose Data Source list, select the control that you added previously ( SqlDataSource1). Select the Enable Paging check box. In the Properties window, expand the property for the control, and then set the property to.

This will align the text in the grid row at the top of the cells, because the nested grid will display multiple lines. Ensure that you are in Design view for the Default.aspx page.

Click the control, and then click the smart tag. In the GridView Tasks panel, click Edit Columns. The Fields dialog box appears. In the Available fields panel, select TemplateField, and then click Add. In the TemplateField properties panel, set HeaderText to Orders. In the GridView Tasks panel, click Edit Templates. In Template Editing Mode panel, from the Display list, select ItemTemplate.

In the Toolbox, from the Data group, drag a SqlDataSource control onto the page to the editable area of the element. In the SqlDataSource Tasks panel, click Configure Data Source.

The Configure Data Source wizard appears. For the Choose Your Data Connection step, select the NorthwindConnectionString option that you created earlier in this walkthrough and then click Next. In the Configure the Select Statement step, select Specify columns from a table or view. In the Name list, select Orders. In the Columns panel, select the OrderID and OrderDate columns. Click WHERE to add a parameter for the SELECT statement.

In the Add WHERE Clause window, select CustomerID in the Column list. Select None in the Source list. This creates a select parameter that you will set to the CustomerID value for each row that is bound to the parent control.

Click Next. Click Finish. In the Toolbox, from the Data group, drag a control to the editable area of the. Click the GridView smart tag, and then in the Choose Data Source list, select the name of the nested control ( SqlDataSource2).

Right-click the parent control ( GridView1) and then click Show Smart Tag. In the GridView Tasks panel, click End Template Editing.