Using the SqlDataSource Control

Learning Resources
 

Using the SqlDataSource Control


Basics

The SqlDataSource control enables you to use a Web server control to access data that is located in a relational database. This can include Microsoft SQL Server and Oracle databases, as well as OLE DB and ODBC data sources. You can use the SqlDataSource control with data-bound controls such as the GridView, FormView, and DetailsView controls to display and manipulate data on an ASP.NET Web page, using little or no code.

The SqlDataSource control uses ADO.NET classes to interact with any database supported by ADO.NET. This includes Microsoft SQL Server (using the System.Data.SqlClient provider), System.Data.OleDb, System.Data.Odbc, and Oracle (using the System.Data.OracleClient provider). Using a SqlDataSource control allows you to access and manipulate data in an ASP.NET page without using ADO.NET classes directly. You provide a connection string to connect to your database and define the SQL statements or stored procedures that work with your data. At run time, the SqlDataSource control automatically opens the database connection, executes the SQL statement or stored procedure, returns the selected data (if any), and then closes the connection.

Connecting the SqlDataSource Control to a Data Source
When you configure a SqlDataSource control, you set the ProviderName property to the type of database (the default is System.Data.SqlClient) and the ConnectionString property to a connection string that includes information required to connect to the database. The contents of a connection string differ depending on what type of database the data source control is accessing. For example, the SqlDataSource control requires a server name, database (catalog) name, and information about how to authenticate the user when connecting to a SQL Server. For information on valid connection strings, see the ConnectionString property topics for the SqlConnection, OracleConnection, OleDbConnection, and OdbcConnection classes.

Instead of setting connection strings at design time as property settings in the SqlDataSource control, you can store them centrally as part of your application's configuration settings using the connectionStrings configuration element. This enables you to manage connection strings independently of your ASP.NET code, including encrypting them using Protected Configuration. The following example shows a connection to the SQL Server Northwind sample database using a connection string stored in the connectionStrings configuration element named MyNorthwind.

An C# example

<%@ Page language="C#" %>




 
    ASP.NET Example


   


                id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataReader"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT LastName FROM Employees">
     

                id="ListBox1"
          runat="server"
          DataTextField="LastName"
          DataSourceID="SqlDataSource1">
     

   
 

 

--MSDN
 For Support