Introduction

Data Binding is a powerful and user-friendly feature of GridWeb Control, which offers richness without any complexity. Data stored in the database tables is fetched to DataSet and filled with data representing data tables. Using Data Binding feature, you can create Hierarchical View (Master-Child View) of interlinked data and display in the control to make it more elegant. This topic discusses creating hierarchical view sheet which means, some rows of the sheet have their "child view". When a user clicks the row's expanding button "+", the child view table of that row will be expanded down. This feature is very helpful for the developers to build a hierarchical view report. The figure below shows that effect.

 

 

Create Relations for DataTables

The task can be done with ease. For example, you use ADO.Net API and extract data from the database tables. To create hierarchical view sheet, you must design a DataSet object based on some tables and create relationship between them first. You can use the DataSet Designer of the VS.Net to create relationship between the tables. In this example, we have 3 DataTables: Customers, Orders, Order Details. Where as the sheet shows all the customers information by default. When the user clicks a customer's expanding button +, the grid will show all the orders of the particular customer. And when the user clicks an order's expanding button +, the grid will show the details of that particular order.

So we should create two relations between these three DataTables.

1. Create a foreign key on DataTable Orders, the key field is CustomerID.

 

2. Create a forengn key on DataTable Order Details, the key field is OrderID.

 

Now the DataSet Designer will look as shown below:

 

 

Bind Worksheet

Now use the Worksheets Designer to set the DataSource and DataMember for the worksheet, and configure data field binding columns. The control will automatically add a + icon for each row in the records, whose binding object (generally it is a DataRowView object) has child view(s). When you click the + icon, the record will expand to show the child view in the grid. In the example we use the Worksheets Designer to bind the worksheet to the root parent DataTable "Customers".

 

 

Customize the Child Tables Bind Columns

The Control provides an event named GridWeb.BindingChildView to let the developers to customize the child tables bind columns. In this example, we need to display the Order Details' UnitPrice field as currency format. So, we add an event handler to change the bind column's number format:

[C#]

        // Handles the BindingChildView event to set the UnitPrice column.

        private void GridWeb1_BindingChildView(Aspose.Grid.Web.GridWeb childGrid, Aspose.Grid.Web.Data.WebWorksheet childSheet)

        {

                DataView view = (DataView)childSheet.DataSource;

                if (view.Table.TableName == "Order Details")

                {

                        childSheet.BindColumns["UnitPrice"].NumberType = NumberType.Currency3;

                }

        }

 

 

[VB.NET]

        'Handles the BindingChildView event to set the UnitPrice column.

        Private Sub GridWeb1_BindingChildView(ByVal childGrid As Aspose.Grid.Web.GridWeb, ByVal childSheet As Aspose.Grid.Web.Data.WebWorksheet) Handles GridWeb1.BindingChildView

        Dim view As DataView = CType(childSheet.DataSource, DataView)

        If view.Table.TableName = "Order Details" Then

            childSheet.BindColumns("UnitPrice").NumberType = NumberType.Currency3

        End If

        End Sub

 

Load Data from Database and Binding

As you know perceiving our databinding document, we add some code to the Page_Load block to load data to the DataSet from database and bind the DataSet to the sheet in the next step. The Asppose.Grid.Web.Data.WebWorksheet Class has some useful properties. For example, The property "EnableCreateBindColumnHeader" is used whether to create the headings of the bound column within the sheet or the column headers will display the bound column names. You may set it to true or false. The properties "BindStartRow" and "BindStartColumn" specify the position in sheet of GridWeb Control to bind to the source. The property "EnableExpandChildView" is used to disable the expanded child view for the worksheet. By default it is true. The class has some useful methods too. For example, The "DataBind()" method binds a sheet with the source. The "CreateNewBindRow()" adds a new row and binds it to the data source. The "DeleteBindRow()" deletes a bound row. The "SetRowExpand()" method sets the expanded row and shows the child view content in the data binding mode. The "GetRowExpand()" method gets a Boolean value that indicates whether the row is expanded or not. In code below, The DataSet object named "dataSet21" is filled with data based on three tables and the Customers' table is additionally filtered to make it the first table in the hierarchical display. A WebWorksheet object named "sheet" is created, which clears the sheet first and then sets it linked to the data source.

[C#]

        private void Page_Load(object sender, System.EventArgs e)

        {

                // Put user code to initialize the page here

                if (!IsPostBack)

                {

                        BindWithoutInSheetHeaders();

                }

        }

 

 

        private void BindWithoutInSheetHeaders()

        {

                DemoDatabase2 db = new DemoDatabase2();

                string path = MapPath(".");                                               

                path = path.Substring(0, path.LastIndexOf("\\"));       

                path = path.Substring(0, path.LastIndexOf("\\"));       

                db.oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\\Database\\Northwind.mdb";

                try

                {

                        // Connects to database and fetches data.

                        // Customers Table.

                        db.oleDbDataAdapter1.Fill(dataSet21);

                        // Orders Table.

                        db.oleDbDataAdapter2.Fill(dataSet21);

                        // OrderDetailTable.

                        db.oleDbDataAdapter3.Fill(dataSet21);

 

 

                        // Filter data

                        dataSet21.Customers.DefaultView.RowFilter = "CustomerID<'BSAAA'";

 

 

                        WebWorksheet sheet = GridWeb1.WebWorksheets[0];

 

 

                        // Clears the sheet.

                        sheet.Cells.Clear();

 

 

                        // Disables creating in-sheet headers.

                        sheet.EnableCreateBindColumnHeader = false;

 

 

                        // Data cells begin from row 0.

                        sheet.BindStartRow = 0;

 

 

                        // Bind the sheet to the dataset.

                        sheet.DataBind();

                }

                finally

                {

                        db.oleDbConnection1.Close();

                }

        }

 

 

[VB.NET]

        Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            'Put user code to initialize the page here

            If Not IsPostBack Then

                BindWithoutInSheetHeaders()

            End If

        End Sub

 

 

        Private Sub BindWithoutInSheetHeaders()

            Dim db As DemoDatabase2 = New DemoDatabase2()

            Dim path As String = MapPath(".")

            path = path.Substring(0, path.LastIndexOf("\"))

            path = path.Substring(0, path.LastIndexOf("\"))

            db.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\Database\Northwind.mdb"

            Try

                ' Connects to database and fetches data.

                ' Customers Table.

                db.OleDbDataAdapter1.Fill(DataSet21)

                ' Orders Table.

                db.OleDbDataAdapter2.Fill(DataSet21)

                ' OrderDetailTable.

                db.OleDbDataAdapter3.Fill(DataSet21)

 

 

                ' Filter data

                DataSet21.Customers.DefaultView.RowFilter = "CustomerID<'BSAAA'"

 

 

                Dim sheet As WebWorksheet = GridWeb1.WebWorksheets(0)

 

 

                ' Clears the sheet.

                sheet.Cells.Clear()

 

 

                ' Disables creating in-sheet headers.

                sheet.EnableCreateBindColumnHeader = False

 

 

                ' Data cells begin from row 0.

                sheet.BindStartRow = 0

 

 

                ' Bind the sheet to the dataset.

                sheet.DataBind()

            Finally

                db.OleDbConnection1.Close()

            End Try

        End Sub