The PgSqlDataTable component is one of the most effective tools in the dotConnect for PostgreSQL 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 PgSqlDataTable features.

• Introduction
• Data Fetch Control Overview
• Traditional Fetch Mode
• Sequential Fetch Mode
• Paginal Fetch Mode
• Pure Asynchronous Fetch
• Query-based Master-detail Relationship
• Deferred Column Data Read
• Flexible Data Binding

Introduction

The PgSqlDataTable component can be used both as a standalone data processing unit and in conjunction with GUI controls like grids. The behavior of the PgSqlDataTable 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 PgSqlDataTable component supports server-specific data types. It is used by PgSqlDataSet 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 PgSqlDataTable 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 PgSqlDataTable members are involved into the fetch control:

• Open and Close methods for simply opening and closing whole datasets or its parts
• Active property to control the dataset state in design time
• StartRecord, MaxRecords properties to retrive limited resultset
• FetchAll property to control whether PgSqlDataTable requests all records at once or just the amount requested by a visual component
• NonBlocking property, which determines whether data fetch process blocks current thread
• Fill method to open whole dataset
• FillPage method to open a part of a dataset
• BeginFill, EndFill, SuspendFill, CancelFill methods to perform fetch operations asynchronously

This list demonstrates that PgSqlDataTable 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 PgSqlDataTable component, just invoke the Open method (or set Active property to true). Make sure that the component is configured as follows:

• FetchAll property is set to true
• MaxRecords property is set to 0, which indicates that all records should be requested

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(PgSqlDataTable myDataTable, PgSqlCommand 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 PgSqlDataTable, ByVal myCommand As PgSqlCommand)
  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 PgSqlDataTable 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 PgSqlDataTable component has two ways to limit the range:

• By providing arguments to the FillPage method
• By setting the StartRecord and MaxRecords properties and calling the Open method

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:

• Set MaxRecords to a positive integer
• Set FetchAll to false
• Set NonBlocking to true

static void UseDataTable(PgSqlDataTable myDataTable, PgSqlConnection 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", ((PgSqlDataTable)sender).RecordCount));
}

static void myDataTable_FetchFinished(object sender, EventArgs e) {

  Console.Write("All records are fetched");
}

Private Shared Sub UseDataTable(ByVal myDataTable As PgSqlDataTable, ByVal myConnection As PgSqlConnection)
  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, PgSqlDataTable).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:

• BeginFill initiates data fetch in a background thread. Returns immediately after the server starts to send first pieces of data.
• EndFill finishes the previously started data fetch. The EndFill method reads out the resultset if it is not fetched entirely yet.
• SuspendFill pauses the previously started data fetch. You can resume it later with the BeginFill or EndFill methods.
• CancelFill terminates the previously started data fetch.

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

void UseDataTable(PgSqlDataTable myDataTable, PgSqlConnection 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 PgSqlDataTable, ByVal myConnection As PgSqlConnection)
  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

In addition to classic master-detail relationship, the PgSqlDataTable 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 PgSqlDataTable 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 PgSqlDataTable. You can implement a master-detail relationship, but you cannot implement master-detail-subdetail relationship.

PgSqlConnection connection = new PgSqlConnection("User Id=postgres;Password=postgres;Host=db;Port=5435;" +
"Database=test;Persist Security Info=True;");
connection.Open();
PgSqlDataTable deptTable = new PgSqlDataTable("SELECT * FROM dept", connection);
PgSqlDataTable empTable = new PgSqlDataTable("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 PgSqlConnection("User Id=postgres;Password=postgres;Host=db;Port=5435;"& _
"Database=test;Persist Security Info=True;")
connection.Open()
Dim deptTable As New PgSqlDataTable("SELECT * FROM dept", connection)
Dim empTable As New PgSqlDataTable("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 PgSqlDataTable 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(PgSqlDataTable myDataTable, PgSqlConnection 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 PgSqlDataTable, ByVal myConnection As PgSqlConnection)
  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

The PgSqlDataTable 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 PgSqlDataTable. 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 PgSqlDataTable 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 PostgreSQL  | Asynchronous Execution  | InterForm Technology  | PgSqlDataTable Reference  | ParentDataRelation Reference