Export (0) Print
Expand All

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

  1. In Microsoft Visual Studio, open a Web site, add an ASP.NET page (Web Form), and switch to Design view.

  2. From the Data group in the Toolbox, drag a SqlDataSource control onto the page.

  3. On the SqlDataSource Tasks shortcut menu, click Configure Data Source - <Datasourcename>.

    • If the SqlDataSource Tasks shortcut menu is not displayed, right-click the T:System.Web.UI.WebControls.SqlDataSource control, and then click Configure Data Source.

  4. Click New Connection.

    • If the Choose Data Source dialog box appears, click Microsoft SQL Server, and then click Continue.

  5. In the Add Connection dialog box, click Change.

  6. In the Change Data Source dialog box, click Microsoft SQL Server, and then click OK.

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

  8. In the Select or enter a database name list, enter a valid database on the server, such as Northwind.

  9. Optionally, click Test connection to verify that your connection works.

  10. Click OK.

    Notice that your new connection is selected in the Configure Data Source - <Datasourcename> dialog box.

  11. Click Next.

  12. Select Yes, save this connection as, and enter a name for your connection for when it is stored in the application configuration file, and then click Next.

  13. Select the database table, view, or stored procedure from which to retrieve results or specify your own SQL statement.

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

  15. To test your query, click Next, and then click Test Query.

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

Community Additions

ADD
Show:
© 2014 Microsoft