dotConnect for DB2 Documentation
In This Topic
    DB2DataTable Advanced Features
    In This Topic

    The DB2DataTable component is one of the most effective tools in the dotConnect for DB2 arsenal. It combines connection to server, command object and data storage in single component with really comprehensive capabilities. In addition, it can accomplish data access tasks in both connected and disconnected models. This topic describes how you can use some of the DB2DataTable features.

    Introduction

    The DB2DataTable component can be used both as a standalone data processing unit and in conjunction with GUI controls like grids. The behavior of the DB2DataTable component can be described in a declaratively way with properties, which means that it is very convenient to build and test the application in design time.

    The DB2DataTable component supports server-specific data types. It is used by DB2DataSet components to build the schema with server-specific features. However, this does not break compatibility with common data access techniques.

    Data Fetch Control Overview

    The DB2DataTable component provides rich set of data fetch mechanisms. Besides the traditional mode where selected data is retrieved at once, it supports sequential, paginal, and asynchronous fetch modes. The following DB2DataTable members are involved into the fetch control:

    This list demonstrates that DB2DataTable has all flavors of data access mechanisms. The component members mentioned in the list can be used in different combinations. The following sections of the topic describe some combinations, their effect and problems that can be solved best with chosen fetch mode.

    Traditional Fetch Mode

    The usual approach to requesting data is to transfer the whole resultset to client side in single operation. The advantages of this approach are obvious: the entire resultset is available once fetched, the application does not depend on network stability, there are no delays during navigation through large datasets. On the other hand, the disadvantages of this approach are obvious as well: long initial timeout, possibly excessive memory consumption, higher requirements to network connection speed.

    To perform traditional fetch with the DB2DataTable component, just invoke the Open method (or set Active property to true). Make sure that the component is configured as follows:

    With these conditions, the Open method will return only when the whole dataset is transferred to client side. However, you can set the NonBlocking property to true, which will make the method return immediately. To capture the end moment of the operation in a non-blocking fetch, watch for the SyncRoot property or handle the FetchFinished event.

    Another way is to call the Fill method, which ignores all of the mentioned properties and just fetches the whole resultset.

    static void UseDataTable(DB2DataTable myDataTable, DB2Command myCommand)
    {
      myCommand.CommandText = "SELECT * FROM Test.Dept";
      myDataTable.SelectCommand = myCommand;
      myDataTable.FetchAll = true;
      try
      {
        myDataTable.Active = true;
        foreach(DataRow myRow in myDataTable.Rows)
        {
          foreach(DataColumn myCol in myDataTable.Columns)
          {
            Console.Write(myRow[myCol]+"\t");
          }
          Console.WriteLine();
        }
      }
      finally
      {
        myDataTable.Active = false;
      }
    }
    
    
    Public Sub UseDataTable(ByVal myDataTable As DB2DataTable, ByVal myCommand As DB2Command)
      myCommand.CommandText = "SELECT * FROM Test.Dept"
      myDataTable.SelectCommand = myCommand
      myDataTable.FetchAll = True
      Try
        myDataTable.Active = True
        Dim myRow As DataRow
        Dim myCol As DataColumn
        For Each myRow In myDataTable.Rows
          For Each myCol In myDataTable.Columns
            Console.Write(myRow(myCol) & Chr(9))
          Next myCol
          Console.WriteLine()
        Next myRow
      Finally
        myDataTable.Active = False
      End Try
    End Sub
    
    

    Sequential Fetch Mode

    A more sophisticated approach is to transfer to client only rows that are necessary for rendering or updating. When client needs to fetch more records (for example, user scrolls a grid), another request to server is performed. In contrast to the traditional fetch mode, this way the initial timeout is much shorter, there is no redundant memory consumption, and possibly slow connection would not freeze the application too much. However, sequential mode assumes that you have stable network, so that client does not have to wait every time a new portion of the resultset is requested.

    To perform sequential fetch, set the FetchAll property to false, MaxRecords property to 0, and call Open. Note that the NonBlocking property does not affect the sequential fetch, and that the Fill method ignores the FetchAll property as well.

    To improve behavior of sequential mode, the DB2DataTable component provides two additional properties, QueryRecordCount and RecordCount. The QueryRecordCount property determines whether to perform additional roundtrip to server to find out how many rows are there in the resultset. If the QueryRecordCount property is set to true, the RecordCount property contains the number of rows, otherwise it contains number of rows currently fetched to the client.

    Paginal Fetch Mode

    When you know for sure which subset of rows the application actually needs, you can switch to paginal mode that allows selecting rows range explicitly. In this mode it is up to you to determine which and how many rows should be retrieved. The DB2DataTable component has two ways to limit the range:

    While the first way is self-sufficient and is not affected by any property, the second way still can be modified by the FetchAll and NonBlocking properties (see above for explanation). For example, the following assignments will cause the Open method return before a subrange of rows is entirely fetched:

    static void UseDataTable(DB2DataTable myDataTable, DB2Connection myConnection) {
    
      myDataTable.Connection = myConnection;
      myDataTable.SelectCommand = myConnection.CreateCommand();
      myDataTable.SelectCommand.CommandText = "SELECT * FROM Dept";
      myDataTable.FetchAll = false;
      myDataTable.NonBlocking = true;
      myDataTable.StartRecord = 10;
      myDataTable.MaxRecords = 5;
    
      myDataTable.RowFetched += new EventHandler(myDataTable_RowFetched);
      myDataTable.FetchFinished += new EventHandler(myDataTable_FetchFinished);
      myDataTable.Open();
    }
    
    static void myDataTable_RowFetched(object sender, EventArgs e) {
    
      Console.Write(string.Format("{0} rows are fetched", ((DB2DataTable)sender).RecordCount));
    }
    
    static void myDataTable_FetchFinished(object sender, EventArgs e) {
    
      Console.Write("All records are fetched");
    }
    
    
    Private Shared Sub UseDataTable(ByVal myDataTable As DB2DataTable, ByVal myConnection As DB2Connection)
      myDataTable.Connection = myConnection
      myDataTable.SelectCommand = myConnection.CreateCommand
      myDataTable.SelectCommand.CommandText = "SELECT * FROM Dept"
      myDataTable.FetchAll = False
      myDataTable.NonBlocking = True
      myDataTable.StartRecord = 10
      myDataTable.MaxRecords = 5
      AddHandler myDataTable.RowFetched, New EventHandler(AddressOf myDataTable_RowFetched)
      AddHandler myDataTable.FetchFinished, New EventHandler(AddressOf myDataTable_FetchFinished)
      myDataTable.Open()
    End Sub
    
    Private Shared Sub myDataTable_RowFetched(ByVal sender As Object, ByVal e As EventArgs)
      Console.Write(String.Format("{0} rows are fetched", DirectCast(sender, DB2DataTable).RecordCount))
    End Sub
    
    Private Shared Sub myDataTable_FetchFinished(ByVal sender As Object, ByVal e As EventArgs)
      Console.Write("All records are fetched")
    End Sub
    
    

    Pure Asynchronous Fetch

    The traditional and paginal fetch modes provide capability of transparent asynchronous fill operations with the NonBlocking property set to true. You can control the asynchronous fetch entirely using the following set of methods:

    For more information about the asynchronous operations in DB2DataTable please refer to topic Asynchronous Execution. The topic describes some useful techniques and provides more examples on the subject.

    void UseDataTable(DB2DataTable myDataTable, DB2Connection myConnection) {
    
      myDataTable.Connection = myConnection;
      myDataTable.SelectCommand = myConnection.CreateCommand();
      myDataTable.SelectCommand.CommandText = "SELECT * FROM Dept";
    
      IAsyncResult aRes = myDataTable.BeginFill(null, null);
    
      Console.Write("Fetch in process");
    
      Thread.Sleep(100);
      myDataTable.SuspendFill(true);
      Console.Write("Fetch is stopped");
    
      myDataTable.EndFill(aRes);
      Console.Write("All records are fetched");
    }
    
    
    Private Sub UseDataTable(ByVal myDataTable As DB2DataTable, ByVal myConnection As DB2Connection)
      myDataTable.Connection = myConnection
      myDataTable.SelectCommand = myConnection.CreateCommand
      myDataTable.SelectCommand.CommandText = "SELECT * FROM Dept"
      Dim result1 As IAsyncResult = myDataTable.BeginFill(Nothing, Nothing)
      Console.Write("Fetch in process")
      Thread.Sleep(100)
      myDataTable.SuspendFill(True)
      Console.Write("Fetch is stopped")
      myDataTable.EndFill(result1)
      Console.Write("All records are fetched")
    End Sub
    
    

    Query-based Master-detail Relationship

    This subsection is applicable only for full .NET Framework.

    In addition to classic master-detail relationship, the DB2DataTable component is capable of establishing a relationship where details data is not cached on the client. Every time the current position in the master table is changed, the client DB2DataTable requests an appropriate portion of data from the server. This goal is achieved using the ParentDataRelation class. The query-based master-detail relationship is designed to improve performance of the application and leverage network load.

    Note that multi-level master-detail relationships are not supported in DB2DataTable. You can implement a master-detail relationship, but you cannot implement master-detail-subdetail relationship.

    DB2Connection connection = new DB2Connection("User Id=root;Host=localhost;Database=Test");
    connection.Open();
    DB2DataTable deptTable = new DB2DataTable("SELECT * FROM dept", connection);
    DB2DataTable empTable = new DB2DataTable("SELECT * FROM emp", connection);
    empTable.ParentRelation.ParentTable = deptTable;
    empTable.ParentRelation.ParentColumnNames = new string[] { "deptno" };
    empTable.ParentRelation.ChildColumnNames = new string[] { "deptno" };
    deptTable.Owner = this;
    empTable.Owner = this;
    deptTable.Open();
    empTable.Open();
    deptDataGrid.DataSource = deptTable;
    empDataGrid.DataSource = empTable;
    
    
    Dim connection As New DB2Connection("User Id=root;Host=localhost;Database=Test")
    connection.Open()
    Dim deptTable As New DB2DataTable("SELECT * FROM dept", connection)
    Dim empTable As New DB2DataTable("SELECT * FROM emp", connection)
    empTable.ParentRelation.ParentTable = deptTable
    empTable.ParentRelation.ParentColumnNames = New String() {"deptno"}
    empTable.ParentRelation.ChildColumnNames = New String() {"deptno"}
    deptTable.Owner = Me
    empTable.Owner = Me
    deptTable.Open()
    empTable.Open()
    deptDataGrid.DataSource = deptTable
    empDataGrid.DataSource = empTable
    
    

    Deferred Column Data Read

    The DB2DataTable component is designed to avoid redundant data transfer. One of the tools you can use for this purpose is partial column read. The main point of the technique is having a full dataset schema on client side, but requesting only a subrange of columns. When you decide that a row really needs the rest of the columns, you invoke the ReadComplete method for the chosen row. This can be illustrated with a photo database, where initially you fetch only a short description of a photo, and the image itself is transferred only if the user requests it.

    static void UseDataTable(DB2DataTable myDataTable, DB2Connection myConnection) {
    
      myDataTable.Connection = myConnection;
      myDataTable.SelectCommand = myConnection.CreateCommand();
      myDataTable.SelectCommand.CommandText = "SELECT DEPTNO FROM Dept";
    
      System.Data.DataColumn column_DEPTNO = new System.Data.DataColumn();
      System.Data.DataColumn column_DNAME = new System.Data.DataColumn();
      System.Data.DataColumn column_LOC = new System.Data.DataColumn();
      column_DEPTNO.ColumnName = "DEPTNO";
      column_DEPTNO.DataType = typeof(int);
      column_LOC.ColumnName = "LOC";
      column_LOC.DataType = typeof(string);
      column_DNAME.ColumnName = "DNAME";
      column_DNAME.DataType = typeof(string);
      myDataTable.Columns.AddRange(new System.Data.DataColumn[] {column_DEPTNO, column_DNAME, column_LOC});
    
      myDataTable.FetchAll = true;
      myDataTable.Open();
      Console.Write("DEPTNO column is loaded");
      myDataTable.ReadComplete(myDataTable.Rows[0]);
      Console.Write("DEPTNO and LOC columns are loaded");
    }
    
    
    Private Shared Sub UseDataTable(ByVal myDataTable As DB2DataTable, ByVal myConnection As DB2Connection)
      myDataTable.Connection = myConnection
      myDataTable.SelectCommand = myConnection.CreateCommand
      myDataTable.SelectCommand.CommandText = "SELECT DEPTNO FROM Dept"
      Dim column_DEPTNO As New DataColumn
      Dim column_DNAME As New DataColumn
      Dim column_LOC As New DataColumn
      column_DEPTNO.ColumnName = "DEPTNO"
      column_DEPTNO.DataType = GetType(Integer)
      column_DNAME.ColumnName = "DNAME"
      column_DNAME.DataType = GetType(String)
      column_LOC.ColumnName = "LOC"
      column_LOC.DataType = GetType(String)
      myDataTable.Columns.AddRange(New DataColumn() {column_DEPTNO, column_DNAME, column_LOC})
    
      myDataTable.FetchAll = True
      myDataTable.Open()
      Console.Write("DEPTNO column is loaded")
      myDataTable.ReadComplete(myDataTable.Rows.Item(0))
      Console.Write("DEPTNO and LOC columns are loaded")
    End Sub
    
    

    Flexible Data Binding

    This subsection is applicable only for full .NET Framework.

    The DB2DataTable component is designed to provide comprehensive data binding capabilities. It supports the InterForm technology, which means that you can bind controls on another form to the DB2DataTable. The component even allows updating underlying datasets in design time with data binding (when the CachedUpdates property is set to false). In addition, the QueryRecordCount property enables informing GUI controls about real quantity of rows in a resultset. These features make DB2DataTable component the best choice for both design time and run time data processing.

    See Also

    General Concepts in Database Application Development  | Developing Database Applications with dotConnect for DB2  | Asynchronous Execution  | InterForm Technology  | DB2DataTable Reference  | ParentDataRelation Reference