ComponentOne DataObjects for .NET
Tutorial 4: Virtual Mode: Dealing with Large Datasets
DataObjects for .NET (Enterprise Edition) > DataObjects for .NET Tutorials > Tutorial 4: Virtual Mode: Dealing with Large Datasets

Visual Studio and its underlying data engine, ADO.NET, support only one mode: disconnected access to data. An ADO.NET data set is always pre-fetched in its entirety from the server to the client. Pre-fetching large data sets over the wire creates two serious problems:

DataObjects for .NET fills this gap, offering a solution to the problem of large data sets. It proves the misconceptions mentioned above wrong. It gives you the tool to achieve the best of both worlds: to have data access that is both disconnected, or no live connection is maintained on the server for particular users, and is therefore scalable, and at the same time, unlimited in data size.

To use a large data set in DataObjects for .NET is as easy as setting the DataAccessMode property to Virtual Mode.

Perhaps you are skeptical and think that it might work in a demo but never in the real world with huge tables, and so on. Well, how about a table of 2.7 million rows? This is what is shown in this tutorial, and you will see this data appearing in a TrueDBGrid with no delay, and it can be scrolled without noticeable delays.

Although it is not demonstrated in this tutorial, you can use virtual data access mode in distributed Web-based applications as well, simply by setting the DataAccessMode property to Virtual. See Tutorial 3: Creating Distributed 3-Tier Applications for more information.

We will begin by creating a new Windows Application project. In this tutorial we will create two forms.

To create Form1:

  1. Place the following components on the first form as shown in the figure.
    Number of Components Name Namespace
    1 C1SchemaDef C1SchemaDef1 C1.Data.C1SchemaDef
    1 C1DataSet C1DataSet1 C1.Data.C1DataSet
    2 C1DataTableSource C1DataTableSource1
    C1DataTableSource2    		 C1.Data.C1DataTableSource
    2 C1TrueDBGrid C1TrueDBGrid1
    C1TrueDBGrid2    		 C1.Win.C1TrueDBGrid.C1TrueDBGrid
    2 Label Label1
    Label2    		 System.Windows.Forms.Label
    1 command Button Button1 System.Windows.Forms.Button

  2. Set the Text property of each label to Fetching data.
  3. Set the Text property of Button1 to Huge Table.
  4. Select the C1SchemaDef1 control, use the smart tags to expand the C1SchemaDef Tasks menu and select Schema Designer.

    The Schema Designer opens, and the Import Wizard appears, but you can click Cancel to close the wizard.
  5. Select File | Open in the Schema Designer menu and open the schema file that was saved in Tutorial 1: Creating a Data Schema. The schema appears in the designer
  6. To enable virtual mode, we must set the DataAccessMode property for the table view objects that we need to be accessed "virtually". "Virtual", as opposed to "static", means data is fetched in increments, on demand, as opposed to fetching everything at once:
    1. In the View menu, make sure that DataSets is checked.
    2. Double-click the ProductsOrders dataset in the DataSets window to open it.
    3.   In the DataSet Editor, click the title bar of the ProductsOrderDetailsCust table view and set the DataAccessMode property to VirtualAutomatic in the Properties panel.
    4. Click the title bar of the CustOrdersDetails table view and set the DataAccessMode property to VirtualAutomatic.
      DataAccessMode = VirtualAutomatic means that the fetch is performed in increments, or segments, asynchronously in background mode while the application becomes available to the user and interactive immediately after the first segment is fetched. The user has interactive access not just to the data already fetched in background, but to the whole table, including its physical end, so the user sees data in its entirety, as if all data were transferred to the client, although the background fetch may not yet be complete. To enable this transparency, DataObjects for .NET performs the fetch on demand, when the user requests data not yet fetched from the server, in addition to the background fetch.

      DataAccessMode = VirtualAutomatic is appropriate for large tables that are big enough to make it undesirable to fetch all data at startup time (doing so would incur a long delay before the application becomes interactive), but not too big, so they can still fit in client memory. Later in this tutorial we will also use DataAccessMode = Virtual, which is intended for very large tables, so large that they do not fit in memory.
    5. Close the Schema Designer and click Yes to save the schema.
  7. Set the C1DataSet1's SchemaDef property to C1SchemaDef1 and the DataSetDef property to ProductsOrders in the property pane.
  8. Set the DataSet property for C1DataTableSource1 and C1DataTableSource2 to C1DataSet1.
  9. Set the TableView property of C1DataTableSource1 to ProductsOrderDetailsCust and set the TableView property of C1DataTableSource2 to CustOrdersDetails.
  10. Bind the C1TrueDBGrid components to the C1DataTableSource components:
    1. Set C1TrueDBGrid1's DataSource to C1DataTableSource1.
    2. Set C1TrueDBGrid2's DataSource to C1DataTableSource2.

    Note that we bind the grids to C1DataTableSource components rather than directly to a C1DataSet component. This is essential for virtual mode; it works only if you use C1DataTableSource as your data source.
  11. Add the following code to create a C1DataTableSource1_AsyncFetchComplete event handler:

    To write code in Visual Basic

    Visual Basic
    Copy Code 
    Private Sub C1DataTableSource1_AsyncFetchComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles C1DataTableSource1.AsyncFetchComplete
    Label1.Text = "Fetch complete. Record count: " + C1DataTableSource1.DataTable.Rows.Count.ToString()
End Sub

    To write code in C#

    C#
    Copy Code 
    private void c1DataTableSource1_AsyncFetchComplete(object sender, System.EventArgs e) 
{
    label1.Text = "Fetch complete. Record count: " + c1DataTableSource1.DataTable.Rows.Count.ToString();
}
  12. Add the following code to create a C1DataTableSource2_AsyncFetchComplete event handler:

    To write code in Visual Basic

    Visual Basic
    Copy Code 
    Private Sub C1DataTableSource2_AsyncFetchComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles C1DataTableSource2.AsyncFetchComplete 
    Label2.Text = "Fetch complete. Record count: " + C1DataTableSource2.DataTable.Rows.Count.ToString()
End Sub

    To write code in C#

    C#
    Copy Code 
    private void c1DataTableSource2_AsyncFetchComplete(object sender, System.EventArgs e) 
{
    label2.Text = "Fetch complete. Record count: " + c1DataTableSource2.DataTable.Rows.Count.ToString();
}

This completes the Form1 set up, where we used DataAccessMode = VirtualAutomatic.

Now let's add a second form to the project where we show a very large table with 2.7 million rows. For such huge data amounts we must use DataAccessMode = Virtual.

In this part of the tutorial we need a SQL Server connection. Up until now a Microsoft Access sample database was enough. MS Access, being a desktop database, does not provide sufficient query optimization, so it cannot be effectively used in DataObjects for .NET virtual mode. We will use the SQL Server sample Northwind database included in the standard SQL Server installation.

Note: If you are running the pre-built tutorial projects included in DataObjects for .NET installation, you will need to change the connection string in this tutorial to point to your SQL Server instance.

Obviously, we do not want to physically fill 2.7 million rows in your SQL Server database, so we will simulate a huge table by creating a composite table with self-joins, repeating the same tables, Order Details and Products, several times to create the desired effect:


Order Details →(∞-1) Products →(1-∞) Order Details →(∞-1) Products →(1-∞) Order Details

To create Form2:

  1. Select Project | Add Windows Form. The Add New Item dialog box appears.
    1. Choose Windows Form from the Templates pane.
    2. Name the form Form2 and click Add.
  2. Place the following components on the form as shown in the figure.
    Number of Components Name Namespace
    1 C1SchemaDef c1SchemaDef1 C1.Data.C1SchemaDef
    1 C1DataSet c1DataSet1 C1.Data.C1DataSet
    1 C1DataTableSource C1DataTableSource1 C1.Data.C1DataTableSource
    1 C1TrueDBGrid c1TrueDBGrid1 C1.Win.C1TrueDBGrid.C1TrueDBGrid

  3. As in other tutorials, we start with the schema created in Tutorial 1: Creating a Data Schema. Select the C1SchemaDef1 control, click the smart tag to open the C1SchemaDef Tasks menu, and select Schema Designer.

    The Schema Designer opens, and the Import Wizard appears, but you can click Cancel to close the wizard.
  4. Select File | Open in the Schema Designer menu and open the schema file that was saved in Tutorial 1: Creating a Data Schema. The schema will appear in the designer.
  5. Right-click the Tables window and select Add | Composite table from the context menu.
  6. In the newly added node, change the default name CompositeTable to HugeTable.
  7. Select the Diagram tab in the Composite Table Editor and drag-and-drop the following tables from the Tables window to the Composite Table Editor, arranging them as in the image below:
    • Order Details – drag and drop this table three times.
    • Products – drag and drop this table two times.

  8. In the resulting diagram, delete the following redundant relations by selecting the relation and pressing the Delete key (or right-click the relation and select Remove from the context menu):
    • Products – Order Details_2
    • Products_1 – Order Details
  9. Invert the first (Products – Order Details) and third (Products_1 – Order Details_1) relations by selecting the relation and choosing Invert from the context menu, so that the resulting diagram becomes:

    Order Details →(∞-1) Products →(1-∞) Order Details →(∞-1) Products →(1-∞) Order Details
  10. Define composite table fields by checking the check boxes for the following fields:
    Table Field
    Order Details OrderID
    ProductID
    Products ProductName
    Order Details_1 OrderID
    Products_1 ProductName
    Order Details_2 OrderID
  11. If necessary, select View | DataSets and then right-click the DataSets window and select Add in the context menu
  12. For the newly created data set node, change the default name DataSet to TheHugeOne.
  13. Drag the HugeTable node from the Tables window and drop it in the DataSet Editor. This creates a data set consisting of a single table, HugeTable.
  14. Set the DataAccessMode property of the HugeTable table view to Virtual.
  15. Now we must change the database connection from MS Access to SQL Server in order to use the SQL Server sample Northwind database included in the standard SQL Server installation. 
    1. If necessary, select View | Connections and then double-click the existing Connection node in the Connections window. The Connection Editor opens.
    2. Click the ellipsis button to the right of the Connection string to open the standard OLE DB Data Link Properties dialog box.
    3.   Click the Provider tab and select Microsoft OLE DB Provider for SQL Server.
    4. Click the Connection tab and select your SQL Server server name; enter your login information, if necessary; and select the Northwind database.
    5. Click OK to close the Data Link Properties dialog box.
    6.   Close the Schema Designer and click Yes to save the schema.
  16. Set the C1DataSet1's SchemaDef property to c1SchemaDef1 and the DataSetDef property to TheHugeOne in the Properties window.
  17. Set the C1DataTableSource's DataSet property to C1DataSet1 and the TableView property to HugeTable.
  18. Set the C1TrueDBGrid1's DataSource to C1DataTableSource1 to bind the grid to the C1DataTableSource.

    Note that we bind the grid to the C1DataTableSource components rather than directly to the C1DataSet component. This is essential for virtual mode; it works only if you use C1DataTableSource as your data source.
  19. To activate Form2, switch to Form1 and double-click Button1 to switch to code view and add the Button1_Click event. Add the following code to the Button1_Click event in Form1:

    To write code in Visual Basic

    Visual Basic
    Copy Code 
    Dim form2 As Form2
form2 = New Form2()
form2.ShowDialog(Me)

    To write code in C#

    C#
    Copy Code 
    Form2 form2 = new Form2();
form2.ShowDialog(this);

Run the program and observe the following:

 

 

Copyright © GrapeCity, Inc. All rights reserved.

Product Support Forum | Print this page