How to: Connect to a SQL Server Database Using the SqlDataSource Control
You can connect to a Microsoft SQL Server database using the SqlDataSource control. To do so, you need a connection string and access rights to a SQL Server database. You can then use the SqlDataSource control to provide data to any data-bound control that supports the DataSourceID property, such as the GridView control.
To configure a connection string for SQL Server in the Web.config file
Open the Web.config file in the root directory of your ASP.NET application. If you do not have a Web.config file, create one.
Create an add element as a child of the ConnectionStrings element, defining the following attributes:
name Set the value to the name that you want to use to reference the connection string, as in the following example:
connectionString Assign a connection string with the location of your SQL Server, and authentication information, if applicable. Your connection string might look like the following:
connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind"
If you are connecting to SQL Server with Windows integrated authentication, you will need to ensure that the identity of your Web application has the appropriate access to the SQL Server database. For information on determining the identity of your Web application, see ASP.NET Impersonation.
providerName Assign the value "System.Data.SqlClient", which specifies that ASP.NET should use the ADO.NET provider System.Data.SqlClient when making a connection with this connection string.
The connection string configuration will be similar to the following:
<connectionStrings> <add name="CustomerDataConnectionString" connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind" providerName="System.Data.SqlClient" /> </connectionStrings>
Save the Web.config file and close it.
To reference the SQL Server connection string from a SqlDataSource control
In the page in which you want to connect to the SQL Server database, add a SqlDataSource control.
In the SqlDataSource control, set the following properties:
SelectCommand Set to a SQL select statement for retrieving data, as in the following example:
SelectCommand="Select CustomerID, CompanyName From Customers"
ConnectionString Set to the name of the connection string that you created in the Web.config file in the format
<%$ ConnectionStrings:connection string name
The following example shows a SqlDataSource control configured to connect to an SQL Server database.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CustomerDataConnectionString %>" SelectCommand="SELECT CustomerID, CompanyName FROM Customers" />
You can now bind other controls, such as the GridView control, to the SqlDataSource control.