Using DB2DataSource Component

In This Topic

The DB2DataSource data source control represents data in a DB2 database to data-bound controls. You can use the DB2DataSource control in conjunction with a data-bound control to retrieve data from a database and to display, edit, and sort data on a Web page with little or no code. This topic explains how to use the control in different ways.

Usage Basics
Load Balancing
Data Paging Overview
Automatic Data Paging
Manual Data Paging
Design Time Support
Demos

Usage Basics

To connect to a database, you must set the ConnectionString property to a valid connection string. To retrieve data from a database, set the SelectCommand property with a SQL query, you can also set the SelectCommand property to the name of a stored procedure. The SQL query that you specify can also be a parameterized query.

The DB2DataSource control retrieves data whenever the Select method is called. This method provides programmatic access to the method that is specified by SelectMethod property. The Select method is automatically called by controls that are bound to the DB2DataSource when their DataBind method is called. If you set the DataSourceID property of a data-bound control, the control automatically binds to data from the data source, as required. Setting the DataSourceID property is the recommended method for binding an DB2DataSource control to a data-bound control. Alternatively, you can use the DataSource property, but then you must explicitly call the DataBind method of the data-bound control. Some examples of data-bound controls that can use DB2DataSource are DataGrid, DetailsView, DataList, and DropDownList. You can call the Select method programmatically at any time to retrieve data from the underlying database.

You can perform data operations, such as updates, inserts, and deletes. To perform these data operations, set the appropriate command text and any associated parameters for the operation that you want to perform.

Note that DB2DataSource is available in only.

Load Balancing

If you display data on your page using a DB2DataSource, you can increase the performance of the page by using the data caching capabilities of the data source control. Caching reduces the processing load on the database servers at the expense of memory on the Web server; in most cases, this is a good trade-off. The DB2DataSource automatically caches data when the EnableCaching property is set to true and the CacheDuration property is set to the number of seconds that the cache stores data before the cache entry is discarded. You can also specify a CacheExpirationPolicy and an optional SqlCacheDependency value.

Data Paging Overview

DB2DataSource optimizes data retrieval with advanced data paging. The feature is intented to work with data-bound controls that support paginal data access (for example, DataGrid and DetailsView). In this case the database is queried for only a subset of data. The data is sorted on the database server as well. This allows applications to minimize traffic database server load.

The feature is controlled by the DataPagingMode property, which can have one of the following values:

Auto - data paging and sorting is performed automatically.
Manual - data paging and sorting is performed in a callback routine.
Disabled - no additional data processing is performed.

Automatic Data Paging

When DataPagingMode is set to Auto, DB2DataSource takes the responsibility to determine which subset of rows should be returned. This mode is applicable to CommandType=Text queries only.

The following markup demonstrates how to configure DB2DataSource component to use automatic data paging:

<%@ Register assembly="Devart.Data.DB2.Web, Version=1.6.127.0, Culture=neutral, PublicKeyToken=09af7300eec23701" namespace="Devart.Data.DB2.Web" tagprefix="cc1" %>

<cc1:DB2DataSource ID="DB2DataSource1" runat="server"
    ConnectionString="SelectCommand="SELECT * FROM dept" DataPagingMode="Auto">
</cc1:DB2DataSource>

Manual Data Paging

When DataPagingMode is set to Manual, the rows to return are chosen by a handler of the DB2DataSource.OnSelecting event. The event handler should determine the record count and prepare a DB2Command object to be executed.

Manual data paging is applicable to queries with CommandType Text and StoredProcedure.

The following markup demonstrates how to configure DB2DataSource component to use manual data paging:

<cc1:DB2DataSource ID="DB2DataSource1" runat="server"
    ConnectionString="SelectCommand="get_dept_paged" 
	SelectCommandType="StoredProcedure"
    DataPagingMode="Manual">
    <SelectParameters>
        <asp:Parameter Name="SortExpression" Type="String" />
        <asp:Parameter Name="StartIndex" Type="Int32" />
        <asp:Parameter Name="PageSize" Type="Int32" />
    </SelectParameters>
</cc1:DB2DataSource>

Given that the stored procedure has the following code:

The following event handler demonstrates how to control data paging:

protected void DB2DataSource1_Selecting(object sender, 
                                            SqlDataSourceSelectingEventArgs e) {
  DB2Command rowCountCommand = new DB2Command("SELECT count(*) FROM dept");
  rowCountCommand.Connection = new DB2Connection(DB2DataSource1.ConnectionString);
  rowCountCommand.Connection.Open();
  try {
    e.Arguments.TotalRowCount = (int)(long)rowCountCommand.ExecuteScalar();
  }
  finally {
    rowCountCommand.Connection.Close();
  }
  e.Command.Parameters["SortExpression"].Value = e.Arguments.SortExpression;
  e.Command.Parameters["StartIndex"].Value = e.Arguments.StartRowIndex;
  e.Command.Parameters["PageSize"].Value = e.Arguments.MaximumRows;
}

Visual Basic


protected sub DB2DataSource1_Selecting(ByVal sender as object, _
                                           ByVal e as SqlDataSourceSelectingEventArgs)

  Dim rowCountCommand as DB2Command = new DB2Command("SELECT count(*) FROM dept")
  rowCountCommand.Connection = new DB2Connection(DB2DataSource1.ConnectionString)
  rowCountCommand.Connection.Open()

  try
    e.Arguments.TotalRowCount = CInt(CLng(rowCountCommand.ExecuteScalar()))
  finally
    rowCountCommand.Connection.Close()
  End Try

  e.Command.Parameters["SortExpression"].Value = e.Arguments.SortExpression
  e.Command.Parameters["StartIndex"].Value = e.Arguments.StartRowIndex
  e.Command.Parameters["PageSize"].Value = e.Arguments.MaximumRows

end sub

Design Time Support

DB2DataSource component exposes its features in design time with convenient editor. In the editor you can set up connection string, adjust SELECT, INSERT, UPDATE, and DELETE statement, modify parameters, and so on. The screenshot demonstrates the Command Generator tab of the component editor, where you control how the data is inserted and updated in the table. The component editor allows you to preview the data you are working with.

Demos

dotConnect for DB2 includes a set of useful demo projects that can be used as a good starting point in ASP.NET development. The demos include:

DataSource Demo

The demo iullustrates the different paging modes described above.

LoadOnDemand Demo

This sample demonstrates the load data on demand technique with dotConnect for DB2 components. Client-side script is used to handle scrolling and to send asynchronous postback to the Web server. Server-side handler uses ObjectDataSource or DB2DataSource to populate more rows. Portion of data is loaded from DB server and displayed in the grid.

Ajax Demo

This sample demonstrates data binding and editing in an asynchronous way. All AJAX asynchronous operations are implemented using standard components only. The demo shows AJAX usage with a single table and with two tables in a master-detail relationship.

3-rd Party Components Demo

These samples demonstrate using DB2DataSource with popular 3-rd party controls such as DevExpress and Telerik grids.