JavaScript Editor jscript editor     Web designer 

Main Page

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:

      В CopyCode imageCopy Code
    • 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:

      В CopyCode imageCopy Code
      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:

    В CopyCode imageCopy Code
      <add name="CustomerDataConnectionString" 
        connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind"
        providerName="System.Data.SqlClient" />
  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:

      В CopyCode imageCopy Code
      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.

    В CopyCode imageCopy Code
      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



JavaScript Editor jscript editor     Web designer