The SqlDbDataSource data source control represents data in a SQL Server database to data-bound controls. You can use the SqlDbDataSource 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 SqlDbDataSource 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 SqlDbDataSource 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 SqlDbDataSource 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 SqlDbDataSource 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 SqlDbDataSource is available in Professional Edition only.

Load Balancing

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

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

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

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

Manual Data Paging

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

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

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

<cc1:SqlDbDataSource ID="SqlDbDataSource1" 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:SqlDbDataSource>

Given that the stored procedure has the following code:

CREATE PROCEDURE get_dept_paged
(
  @SortExpression VARCHAR(20),
  @StartIndex INT,
  @PageSize INT
)
AS
BEGIN
  if len(isnull(@SortExpression, ''))=0
    set @SortExpression = 'deptno'

  declare @stmt_text varchar(1024)
  set @stmt_text = 'SELECT * FROM dept ORDER BY '+ @SortExpression + 'LIMIT ' + @PageSize;

  execute @stmt_text;
END

The following event handler demonstrates how to control data paging:

protected void SqlDbDataSource1_Selecting(object sender, 
                                            SqlDataSourceSelectingEventArgs e) {
  SqlCommand rowCountCommand = new SqlCommand("SELECT count(*) FROM dept");
  rowCountCommand.Connection = new SqlConnection(SqlDbDataSource1.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;
}

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

  Dim rowCountCommand as SqlCommand = new SqlCommand("SELECT count(*) FROM dept")
  rowCountCommand.Connection = new SqlConnection(SqlDbDataSource1.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

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

See Also

ASP.NET Provider Model Support