Using OracleDataSource Component

The OracleDataSource data source control represents data in an Oracle database to data-bound controls. You can use the OracleDataSource 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

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 OracleDataSource 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 OracleDataSource 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 OracleDataSource 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 OracleDataSource 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.

Load Balancing

If you display data on your page using a OracleDataSource, 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 OracleDataSource 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

OracleDataSource 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, OracleDataSource 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 OracleDataSource component to use automatic data paging: 

<cc1:OracleDataSource ID="OracleDataSource1" runat="server"
    ConnectionString="
	  User Id=scott;Password=tiger;Server=ORA;Persist Security Info=True;"
    SelectCommand="SELECT * FROM dept" DataPagingMode="Auto">
</cc1:OracleDataSource>

Manual Data Paging

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

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

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

<cc1:OracleDataSource ID="OracleDataSource1" runat="server"
    ConnectionString="
	 User Id=scott;Password=tiger;Server=ORA;Persist Security Info=True;"
    OnSelecting="OracleDataSource1_Selecting" 
	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:OracleDataSource>

Given that the stored procedure has the following code: 

CREATE OR REPLACE PACKAGE TEST.get_dept AS
  TYPE TCursor IS REF CURSOR;
  FUNCTION get_dept_paged(SortExpression IN VARCHAR2, 
           StartIndex IN INT, PageSize IN INT) RETURN TCursor;
END;
/

CREATE OR REPLACE PACKAGE BODY TEST.get_dept AS
  FUNCTION get_dept_paged(SortExpression IN VARCHAR2, 
       StartIndex IN INT, PageSize IN INT) RETURN TCursor
  AS
    stmt VARCHAR2(2000);
    cur TCursor;
    SortExp VARCHAR2(20);
  BEGIN
    IF SortExpression IS NULL OR SortExpression = '' THEN
      SortExp := 'DEPTNO';
    ELSE
      SortExp := SortExpression;
    END IF;
    stmt := 'select DEPTNO, DNAME, LOC from (SELECT Q1.*, ROWNUM ORADIRECT_QUERY_ROWNUM 
                                   FROM  dept Q1 order by ' || SortExp || ') Q 
                                   WHERE Q.ORADIRECT_QUERY_ROWNUM > '|| StartIndex ||' 
                                   AND Q.ORADIRECT_QUERY_ROWNUM <= ' || (StartIndex + PageSize);
    OPEN cur FOR stmt;
    RETURN cur;
  END;
END;
/

The following event handler demonstrates how to control data paging:

[C#] 
protected void OracleDataSource1_Selecting(object sender, 
                                            SqlDataSourceSelectingEventArgs e) {
  OracleCommand rowCountCommand = new OracleCommand("SELECT count(*) FROM dept");
  rowCountCommand.Connection = new OracleConnection(OracleDataSource1.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 OracleDataSource1_Selecting(ByVal sender as object, _
                                           ByVal e as SqlDataSourceSelectingEventArgs)

  Dim rowCountCommand as OracleCommand = new OracleCommand("SELECT count(*) FROM dept")
  rowCountCommand.Connection = new OracleConnection(OracleDataSource1.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

OracleDataSource 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.

OracleDataSource editor

Demos

dotConnect for Oracle 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 Oracle components. Client-side script is used to handle scrolling and to send asynchronous postback to the Web server. Server-side handler uses ObjectDataSource or OracleDataSource 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 OracleDataSource with popular 3-rd party controls such as DevExpress and Telerik grids.

dotConnect for Oracle
Buy