The MySqlDataSource data source control represents data in a MySQL database to data-bound controls. You can use the MySqlDataSource 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 MySqlDataSource 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 MySqlDataSource 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 MySqlDataSource 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 MySqlDataSource 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 MySqlDataSource is available in Professional and Developer Editions only.

Load Balancing

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

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

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

<cc1:MySqlDataSource ID="MySqlDataSource1" runat="server"
    ConnectionString="User Id=root;Password=root;Host=localhost;Database=test;Persist Security Info=True;"
	  SelectCommand="SELECT * FROM dept" DataPagingMode="Auto">
</cc1:MySqlDataSource>

Manual Data Paging

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

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

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

<cc1:MySqlDataSource ID="MySqlDataSource1" runat="server"
    ConnectionString="User Id=root;Password=root;Host=localhost;Database=test;Persist Security Info=True;"
	  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:MySqlDataSource>

Given that the stored procedure has the following code:

CREATE PROCEDURE get_dept_paged(IN SortExpression VARCHAR(20), 
        IN StartIndex INT, IN PageSize INT)
BEGIN
  if isnull(SortExpression) or SortExpression = '' then
    set SortExpression = 'deptno';
  end if;

  set @stmt_text = concat('SELECT * FROM dept ORDER BY ', SortExpression, ' limit ?, ?');
  prepare stmt from @stmt_text;
  set @si = StartIndex, @ps = PageSize, @stmt_text = null;
  execute stmt using @si, @ps;
  deallocate prepare stmt;
END

The following event handler demonstrates how to control data paging:

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

  Dim rowCountCommand as MySqlCommand = new MySqlCommand("SELECT count(*) FROM dept")
  rowCountCommand.Connection = new MySqlConnection(MySqlDataSource1.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

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

See Also

ASP.NET Provider Model Support