Home   |   QuickStart Welcome   |   ASP.NET   |   Web Services   |   How Do I...?   
  |   I want my samples in...      

ASP.NET 2.0 Quickstart Tutorials

SqlDataSource

The SqlDataSource control is a data source control that represents a connection to an ADO.NET SQL database provider, such as SQL, OLEDB, ODBC, or Oracle. Note that a separate AccessDataSource control is implemented in ASP.NET, although it is possible to connect to Access with SqlDataSource as well (using the Microsoft Jet 4.0 OLEDB provider). Like all data source controls, the SqlDataSource control can be declaratively bound to any data-bound control that supports the DataSourceID property. SqlDataSource is also self-describing about its capabilities (select, insert, update, delete, sort) so that data-bound controls can provide automatic behavior when a capability is present.

SqlDataSource is intended to replace the ADO.NET code you would normally write in a page to create a connection and command to query a database. Because the data queries are specified directly as properties of the data source control, this is sometimes called a two-tier model, because the data queries are still maintained in page code. For this reason, the SqlDataSource control is usually aimed at small hobbyist or personal sites that do not require a fully encapsulated data middle-tier object. Another section of this tutorial discusses the ObjectDataSource control, which is targetted at larger enterprises with a need for middle-tier encapsulation of database queries.

The example below shows a GridView control associated to a SqlDataSource control. The SelectCommand property defines the SQL select statement to execute when the GridView requests the data from SqlDataSource.

VB GridView-SqlDataSource
Run Sample View Source

The SqlDataSource control is not limited to only connecting to Microsoft™ SQL Server database. It can actually connect to any managed ADO.NET provider configured as a System.Data.Common.DbProviderFactory. By default, there are four providers included in the .NET Framework machine.config:
<configuration>
  <system.data>
    <DbProviderFactories>
      <add name="Odbc Data Provider" invariant="System.Data.Odbc" type="System.Data.Odbc.OdbcFactory, ..." />
      <add name="OleDb Data Provider" invariant="System.Data.OleDb" type="System.Data.OleDb.OleDbFactory, ..." />
      <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" type="System.Data.OracleClient.OracleClientFactory, ..." />
      <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" type="System.Data.SqlClient.SqlClientFactory, ..." />
    </DbProviderFactories>
  </system.data>
</configuration>
The SqlDataSource ProviderName property can be set to the invariant name of any valid provider factory (it defaults to System.Data.SqlClient). Note that if you change the provider name, you will need to ensure the ConnectionString and SelectCommand properties use the correct syntax for the chosen provider.

The SelectCommand property of SqlDataSource can also be set to a stored procedure name instead of SQL command text. To enable this, set the SelectCommandType property to "StoredProcedure". The following example demonstrates the SqlDataSource control configured to select data from a stored procedure in the Northwind sample database.

VB GridView-SqlDataSource (Stored Procedure)
Run Sample View Source

By default, the SqlDataSource control returns a DataView from a DataSet object that contains the query results. You can configure the SqlDataSource control to return data as a DataReader instead by setting the SqlDataSourceMode property to "DataReader". Using a DataReader is generally more performant than a DataSet when you just need forward-only, read-only access to the data. However, note that the sorting capability of SqlDataSource will be disabled in this mode. The following example demonstrates the DataReader mode of SqlDataSource.

VB GridView-SqlDataSource (DataReader)
Run Sample View Source

The SqlDataSource control supports Update operations when its UpdateCommand property is set and Delete operations when its DeleteCommand property is set to a valid update or delete command or stored procedure. The UpdateCommand or DeleteCommand should contain parameter placeholders for each value that will be passed by the GridView control (more on this below). You can also specify an UpdateParameters or DeleteParameters collection to set properties for each parameter, such as the parameter data type, input/output direction, or default value. These collections are discussed in more detail in subsequent topics.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:Pubs %>"
  SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors]"
  UpdateCommand="UPDATE [authors] SET [au_lname] = @au_lname, [au_fname] = @au_fname, [state] = @state WHERE [au_id] = @original_au_id"
  DeleteCommand="DELETE FROM [authors] WHERE [au_id] = @original_au_id"/>
Notice the naming convention for parameters in the Update statement assigned to UpdateCommand. The automatic capability of GridView relies on this naming convention in order to work. Parameters that correspond to new values (entered by the user while the GridView row is in edit mode) are named the same as the associated field value returned by the SelectCommand. Parameters that correspond to the old values of the row (before the row was placed in edit mode), are given an "original_" prefix.

By default, the SqlDataSource re-formats parameter names from the Keys dictionary passed by a data-bound control with the "original_" prefix, in order to differentiate them from the new values. The prefix applied to Keys parameters can be customized by setting the SqlDataSource OldValuesParameterFormatString property to a valid .NET Framwork format string. It is set to "original_{0}" by default, but if you wanted to remove the formatting (for example, if you know your keys and new values parameter names never conflict), you could set this to "{0}".

VB GridView Updating
Run Sample View Source

A common scenario in data-driven page is the ability to filter data in a report. For example, suppose the user could select from a set of field values in a DropDownList to filter the report grid to only display rows with a matching field value. In ASP.NET v1.x, you would have needed to perform the following steps in code:
  1. Cancel databinding in Page_Load if the request is a postback
  2. Handle SelectedIndexChanged event
  3. Add DropDownList SelectedValue to command�s Parameters collection
  4. Execute the command and call DataBind
In ASP.NET 2.0, this code is eliminated through the use of declarative Data Parameter objects. A data parameter allows external values to be declaratively associated with data source operations. These parameters are usually associated with a variable in a command expression or property, for example a parameter in a SQL statement or stored procedure for SqlDataSource. Data source controls expose parameter collection properties that can contain parameter objects for each supported data operation. For example:
<asp:DropDownList ID="DropDownList1" ... runat="server"/>
...
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  ConnectionString="<%$ ConnectionStrings:Pubs %>"
  SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @state">
  <SelectParameters>
    <asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />
  </SelectParameters>
</asp:SqlDataSource>
The following example demonstrates a ControlParameter used to obtain a parameter value from a DropDownList control on the page:

VB Filter By DropDownList
Run Sample View Source

SqlDataSource also support the Insert operation, when its InsertCommand property is set. You can optionally define parameters for insert using the InsertParameters collection. The following example demonstrates a SqlDataSource control configured to insert data.

VB Master-Details Insert
Run Sample View Source

For more information and examples that demonstrate the use of the SqlDataSource control, refer to the Performing Data Access section.