The SqlDataTable component is one of the most effective tools in the dotConnect for SQL Server 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 SqlDataTable 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 SqlDataTable component can be used both as a standalone data processing unit and in conjunction with GUI controls like grids. The behavior of the SqlDataTable 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 SqlDataTable component supports server-specific data types. It is used by SqlDataSet 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 SqlDataTable 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 SqlDataTable 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 SqlDataTable 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 SqlDataTable 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 SqlDataTable 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(SqlDataTable myDataTable, SqlCommand myCommand)
{
  myCommand.CommandText = "SELECT * FROM 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 SqlDataTable, ByVal myCommand As SqlCommand)
  myCommand.CommandText = "SELECT * FROM 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 SqlDataTable 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 SqlDataTable 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(SqlDataTable myDataTable, SqlConnection 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", ((SqlDataTable)sender).RecordCount));
}

static void myDataTable_FetchFinished(object sender, EventArgs e) {

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

Private Shared Sub UseDataTable(ByVal myDataTable As SqlDataTable, ByVal myConnection As SqlConnection)
  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, SqlDataTable).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 SqlDataTable please refer to topic Asynchronous Execution. The topic describes some useful techniques and provides more examples on the subject.

void UseDataTable(SqlDataTable myDataTable, SqlConnection 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 SqlDataTable, ByVal myConnection As SqlConnection)
  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 SqlDataTable 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 SqlDataTable 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 SqlDataTable. You can implement a master-detail relationship, but you cannot implement master-detail-subdetail relationship.

SqlConnection connection = new SqlConnection("User Id=root;Host=localhost;Database=Test");
connection.Open();
SqlDataTable deptTable = new SqlDataTable("SELECT * FROM dept", connection);
SqlDataTable empTable = new SqlDataTable("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 SqlConnection("User Id=root;Host=localhost;Database=Test")
connection.Open()
Dim deptTable As New SqlDataTable("SELECT * FROM dept", connection)
Dim empTable As New SqlDataTable("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 SqlDataTable 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(SqlDataTable myDataTable, SqlConnection 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 SqlDataTable, ByVal myConnection As SqlConnection)
  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 SqlDataTable 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 SqlDataTable. 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 SqlDataTable 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 SQL Server  | Asynchronous Execution  | InterForm Technology  | SqlDataTable Reference  | ParentDataRelation Reference