The SqlDataSource control can be used to retrieve data from a database with little or no code.

The SqlDataSource control can work with any database that has an associated ADO.NET provider, including Microsoft SQL Server, Oracle, ODBC, or OLE DB databases such as Microsoft Access.

The database you use will dictate the syntax of the SQL statements you configure the SqlDataSource to use, and whether you can use more advanced database features such as stored procedures. However, the data source control operates the same for all databases.

To retrieve data from a database using the SqlDataSource control, you need to set at least the following properties:

The following sections describe these properties in more detail.

Set the ProviderName property to the name of the ADO.NET provider associated with the type of database in which the data is stored. The list of allowable providers is registered in the DbProviderFactories section of the configuration file, either in the Machine.config or Web.config file.

By default, the SqlDataSource control uses the System.Data.SqlClient ADO.NET provider, which corresponds to Microsoft SQL Server™. Therefore, if you are connecting to a SQL Server™ database, you do not need to specify a provider. When connectiong to other data sources, however, you need to explicitly specify the appropriate provider, such as System.Data.OracleClient, System.Data.Odbc, or System.Data.OleDb.

NOTE: The value for the ProviderName property cannot be set to an unmanaged ADO provider, such as SQLOLEDB or MSDAORA.

Set the ConnectionString property to a connection string used for a specific database.

Note, however, that setting the ConnectionString property of a SqlDataSource control in the ASP.NET page is not advisable, as the connection string is stored as plain text and may be subject to unauthorized scrutiny. Moreover, particularly for larger sites, any change in the connection string may mean modifying the code for a significant volume of pages.

For security as well as maintainability, it is recommended that connection strings are stored in the connectionStrings element in the application’s configuration file. You can then reference the stored connection string using a connection expression as in the following example:

<asp:SqlDataSource id="SqlDataSource1" runat="server" 
   ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
   SelectCommand="SELECT * FROM [Categories]" />

For additional security, you can encrypt the contents of the <connectionStrings> configuration section. For more information, see Securing Connection Strings When Using Data Source Controls.