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

Load Balancing

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

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

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

<cc1:SQLiteDataSource ID="SQLiteDataSource1" runat="server"
    ConnectionString="Data Source=D:\TestApplication\database.db;"
    SelectCommand="SELECT * FROM dept" DataPagingMode="Auto">
</cc1:SQLiteDataSource>

Manual Data Paging

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

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

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

<cc1:SQLiteDataSource ID="SQLiteDataSource1" runat="server"
    ConnectionString="Data Source=D:\TestApplication\database.db;"
    OnSelecting="SQLiteDataSource1_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:SQLiteDataSource>

Given that the stored procedure has the following code:

CREATE OR REPLACE FUNCTION test.get_dept_paged(startindex integer, pagesize integer)
  RETURNS SETOF test.dept AS '
DECLARE
  rc test.dept%ROWTYPE;
BEGIN
  FOR rc IN SELECT * FROM test.dept LIMIT PageSize OFFSET StartIndex LOOP
    RETURN NEXT rc;
  END LOOP;
END;'
LANGUAGE 'plpgsql' VOLATILE;

The following event handler demonstrates how to control data paging:

protected void SQLiteDataSource1_Selecting(object sender, 
                                            SqlDataSourceSelectingEventArgs e) {
  SQLiteCommand rowCountCommand = new SQLiteCommand("SELECT count(*) FROM dept");
  rowCountCommand.Connection = new SQLiteConnection(SQLiteDataSource1.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 SQLiteDataSource1_Selecting(ByVal sender as object, _
                                           ByVal e as SqlDataSourceSelectingEventArgs)

  Dim rowCountCommand as SQLiteCommand = new SQLiteCommand("SELECT count(*) FROM dept")
  rowCountCommand.Connection = new SQLiteConnection(SQLiteDataSource1.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

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

See Also

ASP.NET Provider Model Support