In this topic:

• Introduction

• Data Connections

• Performing Data Operations

• Data Provider

• Caching

• Additional Features

• Data Source View

• Declarative Syntax

Introduction

The System.Web.UI.WebControls.SqlDataSource data source control represents data in an SQL relational database to data-bound controls. You can use the System.Web.UI.WebControls.SqlDataSource control in conjunction with a data-bound control to retrieve data from a relational database and to display, edit, and sort data on a Web page with little or no code.

Data Connections

To connect to a database, you must set the SqlDataSource.ConnectionString property to a valid connection string. The System.Web.UI.WebControls.SqlDataSource can support any SQL relational database that can be connected to using an ADO.NET provider, such as the SqlClient, OleDb, Odbc, or OracleClient provider. For information about how to secure connection strings, see How To: Secure Connection Strings when Using Data Source Controls.

To retrieve data from an underlying database, set the SqlDataSource.SelectCommand property with an SQL query. If the database that the System.Web.UI.WebControls.SqlDataSource is associated with supports stored procedures, you can set the SqlDataSource.SelectCommand property to the name of a stored procedure. The SQL query that you specify can also be a parameterized query. You can add System.Web.UI.WebControls.Parameter objects that are associated with a parameterized query to the SqlDataSource.SelectParameters collection. For more information about parameterized SQL queries and their syntax, see Using Parameters with Data Source Controls.

The System.Web.UI.WebControls.SqlDataSource control retrieves data whenever the SqlDataSource.Select(System.Web.UI.DataSourceSelectArguments) method is called. This method provides programmatic access to the method that is specified by ObjectDataSource.SelectMethod property. The SqlDataSource.Select(System.Web.UI.DataSourceSelectArguments) method is automatically called by controls that are bound to the System.Web.UI.WebControls.SqlDataSource when their BaseDataBoundControl.DataBind method is called. If you set the BaseDataBoundControl.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 System.Web.UI.WebControls.ObjectDataSource control to a data-bound control. Alternatively, you can use the DataSource property, but then you must explicitly call the BaseDataBoundControl.DataBind method of the data-bound control. Some examples of data-bound controls that can use System.Web.UI.WebControls.SqlDataSource are System.Web.UI.WebControls.DataGrid, System.Web.UI.WebControls.DetailsView, System.Web.UI.WebControls.DataList, and System.Web.UI.WebControls.DropDownList. You can call the SqlDataSource.Select(System.Web.UI.DataSourceSelectArguments) method programmatically at any time to retrieve data from the underlying database.

In declarative and programmatic ASP.NET scenarios, you can set the DataBoundControl.DataSourceID property of the data-bound control to the ID of the System.Web.UI.WebControls.SqlDataSource control. You can also assign an instance of the System.Web.UI.WebControls.SqlDataSource class to the BaseDataBoundControl.DataSource property of the data-bound control. For more information about binding data-bound control to data source controls, see ASP.NET Data Access Overview.

Performing Data Operations

Depending on the capabilities of the underlying database product and the configuration of the instance of the System.Web.UI.WebControls.SqlDataSource class, 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. For example, for an update operation, set the SqlDataSource.UpdateCommand property to an SQL string or the name of a stored procedure and add any required parameters to the SqlDataSource.UpdateParameters collection. The update is performed when the SqlDataSource.Update method is called, either explicitly by your code or automatically by a data-bound control. The same general pattern is followed for SqlDataSource.Delete and SqlDataSource.Insert operations.

The SQL queries and commands that you use in the SqlDataSource.SelectCommand, SqlDataSource.UpdateCommand, SqlDataSource.InsertCommand, and SqlDataSource.DeleteCommand properties can be parameterized. This means that the query or command can use placeholders instead of literal values and bind the placeholders to application or user-defined variables. You can bind parameters in SQL queries to Session variables, values that are passed on the query string for a Web Forms page, the property values of other server controls, and more. For more information about how to use parameters in SQL queries with the System.Web.UI.WebControls.SqlDataSource, see Using Parameters with Data Source Controls and Using Parameters with the SqlDataSource Control.

Data Provider

By default, the System.Web.UI.WebControls.SqlDataSource control works with the .NET Framework Data Provider for SQL Server, but System.Web.UI.WebControls.SqlDataSource is not Microsoft SQL Server–specific. You can connect the System.Web.UI.WebControls.SqlDataSource control with any database product for which there is a managed ADO.NET provider. When used with the System.Data.OleDb provider, the System.Web.UI.WebControls.SqlDataSource can work with any OLE DB-compliant database. When used with the System.Data.Odbc provider, the System.Web.UI.WebControls.SqlDataSource can be used with any ODBC driver and database, including IBM DB2, MySQL, and PostgreSQL. When used with the System.Data.OracleClient provider, the System.Web.UI.WebControls.SqlDataSource can work with Oracle 8.1.7 databases and later. The list of allowable providers is registered in the DbProviderFactories section of the configuration file, either in the Machine.config or Web.config file. For more information, see Selecting Data using the SqlDataSource Control.

Caching

If you display data on your page using a System.Web.UI.WebControls.SqlDataSource control, 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 System.Web.UI.WebControls.SqlDataSource automatically caches data when the SqlDataSource.EnableCaching property is set to true and the SqlDataSource.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 SqlDataSource.CacheExpirationPolicy and an optional SqlDataSource.SqlCacheDependency value.

Additional Features

The System.Web.UI.WebControls.SqlDataSource provides additional capabilities, as listed in the following table.

Data Source View

As with all data source controls, the System.Web.UI.WebControls.SqlDataSource control is associated with a data source view class. The System.Web.UI.WebControls.SqlDataSource control has only one associated System.Web.UI.WebControls.SqlDataSourceView, and it is always named Table.

There is no visual rendering of the System.Web.UI.WebControls.SqlDataSource control; it is implemented as a control so that you can create it declaratively and, optionally, to allow it to participate in state management. As a result, the System.Web.UI.WebControls.SqlDataSource does not support visual features, such as the ones that are provided by the System.Web.UI.DataSourceControl.EnableTheming or System.Web.UI.DataSourceControl.SkinID property.

Declarative Syntax

<asp:SqlDataSource
    CacheDuration="string|