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 this, you need a connection string and access rights to a SQL Server database. Then, you can use the SqlDataSource control to provide data to any data-bound control that supports the DataSourceID property, such as the GridView control.
To connect to a SQL Server database using the SqlDataSource control
In Microsoft Visual Studio, open a Web site, add an ASP.NET page (Web Form), and switch to Design view.
From the Data group in the Toolbox, drag a SqlDataSource control onto the page.
On the SqlDataSource Tasks shortcut menu, click Configure Data Source.
Note
If the SqlDataSource Tasks shortcut menu is not displayed, right-click the SqlDataSource control, and then click Configure Data Source.
.png "Add SQL data source")
Click New Connection.
If the Choose Data Source dialog box appears, click Microsoft SQL Server, and then click Continue.
.png "Choose data connection")
In the Add Connection dialog box, if the Data source box does not contain Microsoft SQL Server (SqlClient), do the following:
In the Add Connection dialog box, click Change.
In the Change Data Source dialog box, click Microsoft SQL Server, and then click OK.
The setting in the Data source box defaults to the type of data source that you have created most recently, which might not be a SQL Server connection.
In the Server name box, enter the name for your SQL Server database, and then under Logon to the server, enter the logon credentials.
- For the logon credentials, select the option that is appropriate for accessing and running the SQL Server database (either by using Microsoft Windows integrated security or by providing a specific ID and password) and, if it is required, enter a user name and password.
In the Select or enter a database name list, enter a valid database on the server, such as Northwind.
.png "Click New Connection")
Optionally, click Test connection to verify that your connection works.
Click OK.
The new connection is selected in the Configure Data Source - <Datasourcename> dialog box.
Click Next.
Select Yes, save this connection as, and enter a name for your connection for when it is stored in the Web.config file, and then click Next.
.png "Save connection string.")
Select the database table, view, or stored procedure from which to retrieve results or specify your own SQL statement.
.png "Configure data source")
Optionally, click WHERE to supply search criteria and ORDER BY to specify sort order.
If you want to support insert, update, and delete operations, click Advanced, and then select the option to generate INSERT, UPDATE, and DELETE statements for your SqlDataSource control. You can also specify whether you want the commands to use optimistic concurrency checks to determine whether the data has been modified before an update or delete operation is performed.
To test your query, click Next, and then click Test Query.
Click Finish.
You can now drag a data-bound control, such as a GridView control, onto the page and specify your SqlDataSource control as the data source.