Export (0) Print
Expand All

SqlDataSource Class

Represents an SQL database to data-bound controls.

Namespace: System.Web.UI.WebControls
Assembly: System.Web (in system.web.dll)

public class SqlDataSource : DataSourceControl
public class SqlDataSource extends DataSourceControl
public class SqlDataSource extends DataSourceControl
Not applicable.

The SqlDataSource data source control represents data in an SQL relational database to data-bound controls. You can use the 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.

To connect to a database, you must set the ConnectionString property to a valid connection string. The 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. For information about securing connection strings, see How To: Secure Connection Strings when Using Data Source Controls.

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

The SqlDataSource 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 SqlDataSource 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 ObjectDataSource 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 SqlDataSource are DataGrid, DetailsView, DataList, and DropDownList. You can call the Select method programmatically at any time to retrieve data from the underlying database.

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

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

The SQL queries and commands that you use in the SelectCommand, UpdateCommand, InsertCommand, and 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 on how to use parameters in SQL queries with the SqlDataSource, see ASP.NET Data Source Control Parameters, Parameters with the SqlDataSource and AccessDataSource Controls, and Parameters with the SqlDataSource and AccessDataSource Controls.

By default, the SqlDataSource control works with the .NET Framework Data Provider for SQL Server, but SqlDataSource is not Microsoft SQL Server–specific. You can connect the SqlDataSource control with any database product for which there is a managed ADO.NET provider. When used with the System.Data.OleDb provider, the SqlDataSource can work with any OLE DB-compliant database. When used with the System.Data.Odbc provider, the 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 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 Data Selection Using the SqlDataSource Control.

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

The SqlDataSource provides additional capabilities, as listed in the following table.

Capability

Requirements

Caching

Set the DataSourceMode property to the DataSet value, the EnableCaching property to true, and the CacheDuration and CacheExpirationPolicy properties according to the caching behavior you want for your cached data.

Deleting

Set the DeleteCommand property to an SQL statement used to delete data. This statement is typically parameterized.

Filtering

Set the DataSourceMode property to the DataSet value. Set the FilterExpression property to a filtering expression used to filter the data when the Select method is called.

Inserting

Set the InsertCommand property to an SQL statement used to insert data. This statement is typically parameterized.

Paging

Not currently supported by the SqlDataSource, however some data-bound controls, such as GridView, support paging when you set the DataSourceMode property to the DataSet value.

Selecting

Set the SelectCommand property to an SQL statement used to retrieve data.

Sorting

Set the DataSourceMode property to DataSet.

Updating

Set the UpdateCommand property to an SQL statement used to update data. This statement is typically parameterized.

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

There is no visual rendering of the 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 SqlDataSource does not support visual features, such as the the ones that are provided by the EnableTheming or SkinID property.

TopicLocation
How to: Connect to an ODBC Database Using the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Display Ads From a Database Using the AdRotator Web Server ControlBuilding ASP .NET Web Applications
How to: Allow Users to Delete Items in DataList Web Server ControlsBuilding ASP .NET Web Applications
How to: Connect to an Oracle Database Using the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Bind to Data in a Templated ControlBuilding ASP .NET Web Applications
How to: Enable Filtering for the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Secure Connection Strings When Using Data Source ControlsBuilding ASP .NET Web Applications
How to: Connect to an Access Database Using the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Connect to a SQL Server Database Using the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Access SQL Server as a Local UserBuilding ASP .NET Web Applications
How to: Access SQL Server Using a Mapped Windows Domain UserBuilding ASP .NET Web Applications
How to: Connect to an ODBC Database Using the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Display Ads From a Database Using the AdRotator Web Server ControlBuilding ASP .NET Web Applications
How to: Allow Users to Delete Items in DataList Web Server ControlsBuilding ASP .NET Web Applications
How to: Connect to an Oracle Database Using the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Bind to Data in a Templated ControlBuilding ASP .NET Web Applications
How to: Enable Filtering for the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Secure Connection Strings When Using Data Source ControlsBuilding ASP .NET Web Applications
How to: Connect to an Access Database Using the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Connect to a SQL Server Database Using the SqlDataSource ControlBuilding ASP .NET Web Applications
How to: Access SQL Server as a Local UserBuilding ASP .NET Web Applications
How to: Access SQL Server Using a Mapped Windows Domain UserBuilding ASP .NET Web Applications
Walkthrough: Simple Sorting for the GridView Web Server ControlBuilding ASP .NET Web Applications in Visual Studio
Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server ControlBuilding ASP .NET Web Applications in Visual Studio
How to: Customize Controls for Editing in the GridView Web Server ControlBuilding ASP .NET Web Applications in Visual Studio
How to: Connect to an Access Database Using the SqlDataSource Control (Visual Studio)Building ASP .NET Web Applications in Visual Studio
How to: Connect to an Oracle Database Using the SqlDataSource Control (Visual Studio)Building ASP .NET Web Applications in Visual Studio
Walkthrough: Displaying Hierarchical Data in a TreeView ControlBuilding ASP .NET Web Applications in Visual Studio
Walkthrough: Basic Data Access in Web PagesBuilding ASP .NET Web Applications in Visual Studio
Walkthrough: Displaying and Formatting Data with the DataList Web Server ControlBuilding ASP .NET Web Applications in Visual Studio
How to: Connect to a SQL Server Database Using the SqlDataSource Control (Visual Studio)Building ASP .NET Web Applications in Visual Studio
How To: Secure Connection Strings when Using Data Source Controls (Visual Studio)Building ASP .NET Web Applications in Visual Studio
Walkthrough: Displaying Formatted Data in Web Pages with the FormView Web Server ControlBuilding ASP .NET Web Applications in Visual Studio
How to: Add Repeater Web Server Controls to a Web Forms Page (Visual Studio)Building ASP .NET Web Applications in Visual Studio
Walkthrough: Displaying a Drop-Down List While Editing in the GridView Web Server ControlBuilding ASP .NET Web Applications in Visual Studio
How to: Enable Filtering for SqlDataSource Controls (Visual Studio)Building ASP .NET Web Applications in Visual Studio
Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server ControlBuilding ASP .NET Web Applications in Visual Studio
How to: Bind to Data in a Templated Control in Visual StudioBuilding ASP .NET Web Applications in Visual Studio
Walkthrough: Creating Master/Detail Web Pages in Visual StudioBuilding ASP .NET Web Applications in Visual Studio
How to: Connect to an ODBC Database Using the SqlDataSource Control (Visual Studio)Building ASP .NET Web Applications in Visual Studio
Walkthrough: Displaying a Drop-Down List While Editing in the GridView Web Server ControlBuilding ASP .NET Web Applications in Visual Studio

This section contains four code examples:

  • The first code example demonstrates how to display data from SQL Server in a GridView control using declarative syntax.

  • The second code example demonstrates how to display data from an ODBC-compliant database in a GridView control using declarative syntax.

  • The third code example demonstrates how to display and update data in a GridView control.

  • The fourth code example demonstrates how to display and update data in a DropDownList control.

The following code example demonstrates how to use a SqlDataSource control declaratively to retrieve data from SQL Server and display it in a GridView control.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataReader"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT FirstName, LastName, Title FROM Employees">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          DataSourceID="SqlDataSource1">
      </asp:GridView>

    </form>
  </body>
</html>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataReader"
          ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
          SelectCommand="SELECT FirstName, LastName, Title FROM Employees">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          DataSourceID="SqlDataSource1">
      </asp:GridView>

    </form>
  </body>
</html>

The following code example demonstrates how to use a SqlDataSource control declaratively to retrieve data from an ODBC-compliant database and display it in a GridView control. The ProviderName property is the name of the .NET Framework Data Provider for ODBC, which is System.Data.Odbc.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >

  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <!-- This example uses a Northwind database that is hosted by an ODBC-compliant
         database. To run this sample, create an ODBC DSN to any database that hosts
         the Northwind database, including Microsoft SQL Server or Microsoft Access,
         change the name of the DSN in the ConnectionString, and view the page.
    -->
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ProviderName="System.Data.Odbc"
          DataSourceMode="DataReader"
          ConnectionString="dsn=myodbc3dsn;"
          SelectCommand="SELECT FirstName, LastName, Title FROM Employees">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          DataSourceID="SqlDataSource1">
      </asp:GridView>

    </form>
  </body>
</html>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >

  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <!-- This example uses a Northwind database that is hosted by an ODBC-compliant
         database. To run this sample, create an ODBC DSN to any database that hosts
         the Northwind database, including Microsoft SQL Server or Microsoft Access,
         change the name of the DSN in the ConnectionString, and view the page.
    -->
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ProviderName="System.Data.Odbc"
          DataSourceMode="DataReader"
          ConnectionString="dsn=myodbc3dsn;"
          SelectCommand="SELECT FirstName, LastName, Title FROM Employees">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          DataSourceID="SqlDataSource1">
      </asp:GridView>

    </form>
  </body>
</html>

The following code example demonstrates a common display and update scenario with the GridView control. As with the previous examples, data from the Northwind database is displayed in the GridView control. Additionally, because an UpdateCommand property is specified and the AutoGenerateEditButton property is set to true, you can edit and update the records with no additional code. The GridView control automatically handles adding parameters to the UpdateParameters collection and calls the Update method when the Update button in the GridView control is clicked.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataSet"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees"
          UpdateCommand="Update Employees SET FirstName=@FirstName,LastName=@LastName,Title=@Title WHERE EmployeeID=@EmployeeID">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          AutoGenerateColumns="False"
          DataKeyNames="EmployeeID"
          AutoGenerateEditButton="True"
          DataSourceID="SqlDataSource1">
          <columns>
              <asp:BoundField HeaderText="First Name" DataField="FirstName" />
              <asp:BoundField HeaderText="Last Name" DataField="LastName" />
              <asp:BoundField HeaderText="Title" DataField="Title" />
          </columns>

      </asp:GridView>

    </form>
  </body>
</html>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataSet"
          ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
          SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees"
          UpdateCommand="Update Employees SET FirstName=@FirstName,LastName=@LastName,Title=@Title WHERE EmployeeID=@EmployeeID">
      </asp:SqlDataSource>

      <asp:GridView
          id="GridView1"
          runat="server"
          AutoGenerateColumns="False"
          DataKeyNames="EmployeeID"
          AutoGenerateEditButton="True"
          DataSourceID="SqlDataSource1">
          <Columns>
              <asp:BoundField HeaderText="First Name" DataField="FirstName" />
              <asp:BoundField HeaderText="Last Name" DataField="LastName" />
              <asp:BoundField HeaderText="Title" DataField="Title" />
          </Columns>

      </asp:GridView>

    </form>
  </body>
</html>

The following code example demonstrates a common display and update scenario with the DropDownList and TextBox controls. The DropDownList control does not automatically add update parameters to the UpdateParameters collection nor call the Update method, so you must do so. The update parameters are specified declaratively and you can add an event handler to perform the Update operation when an event is raised.

Security noteSecurity Note:

This example has a text box that accepts user input, which is a potential security threat. By default, ASP.NET Web pages validate that user input does not include script or HTML elements. For more information, see Script Exploits Overview (Visual Studio).

<%@Page  Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
 private void On_Click(Object source, EventArgs e) {
    try {
        SqlDataSource1.Update();
    }
    catch (Exception except) {
        // Handle the Exception.
    }

    Label2.Text="The record was updated successfully!";
 }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
          UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID">
          <UpdateParameters>
              <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
              <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="LastName"
          DataValueField="EmployeeID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br />
      <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
        AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

    </form>
  </body>
</html>

<%@Page  Language="VJ#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
 private void On_Click(Object source, System.EventArgs e)
 {
    try {
       SqlDataSource1.Update();
    }
    catch (Exception except) {
        // Handle the Exception.
    }
    Label2.set_Text("The record was updated successfully!");
 } //On_Click
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
          SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
          UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID">
          <UpdateParameters>
              <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
              <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="LastName"
          DataValueField="EmployeeID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br />
      <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
        AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

    </form>
  </body>
</html>

System.Object
   System.Web.UI.Control
     System.Web.UI.DataSourceControl
      System.Web.UI.WebControls.SqlDataSource
         System.Web.UI.WebControls.AccessDataSource

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Windows 98, Windows Server 2000 SP4, Windows Server 2003, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP SP2, Windows XP Starter Edition

The Microsoft .NET Framework 3.0 is supported on Windows Vista, Microsoft Windows XP SP2, and Windows Server 2003 SP1.

.NET Framework

Supported in: 3.0, 2.0

Community Additions

ADD
Show:
© 2014 Microsoft