How to: Connect to a SQL Server Database Using the SqlDataSource Control

  • Article

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

  1. 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.

  2. In the Configuration element, add a ConnectionStrings element, if one does not already exist.

  3. 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:

      name="CustomerDataConnectionString"

    • 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"
      NoteNote

      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>

  4. Save the Web.config file and close it.

To reference the SQL Server connection string from a SqlDataSource control

  1. In the page in which you want to connect to the SQL Server database, add a SqlDataSource control.

  2. 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.

See Also

Reference

SqlDataSource