ADO.NET: SqlDataSource Control

The SqlDataSource control provides you with a quick and easy way to access database data.  Most of the time you don't even have to write a single line of code.  Now this can a be a good thing and a bad thing, depends on how you look at things.  Most of the configuration and querying is performed via a configuration wizard.

Here are the steps to creating a SqlDataSource:

1. In a ASP.NET project/web site drag a SqlDataSource control in under "Data" in the "Toolbox" tab into a .aspx page.

 

After you dragged the control it should look like this on your .aspx page

2. Click on "SqlDataSource1" control, then click on the ">" sign, a slide out menu appears.  Select "Configure Data Source".

The "Configure Data Source" wizard will pop up.

3. Click on the "New Connection" button, the "Add Connection" wizard will pop up. Select or type in your "Server name".  If you have a local instance of SQL Server, then you can type in "(local)" in the "Server name" field.  Leave the authentication method under "Log on to server" as "Windows Authentication"

The database in your SQL Server should be automatically populated, once you input the server name of database server in the "Server name" field.  Type in, or select the "Northwind" database.  You can test the connection by clicking on "Test Connection".  Once the connection is successful, click on the "OK" button.

 

4.  In the next step of the wizard a new connection has been created, and the connection string displayed.  Click "Next"

5.  In the next step the wizard will ask if you want to store connection string in the Web.config file.  Leave the checkbox checked, then you can change the name of the connection string in the text field.  Click "Next"

 

6.  At this point you have two options, either you can write your own SQL statement or stored procedures or specify the columns in a database table.  I always choose the first option which is "Specify a custom SQL statement or stored procedure" because in the next screen you will be given more options, and have the ability to work with more than one table.  You can still work with a single table if you choose this option, so it gives you more flexibility. Click "Next"

7.  You will be given the choice to create a custom SQL statement or to select a stored procedure.  In this example we will be building a custom Sql statement. So click on the "Query Builder" button.

8. The "Add Table" wizard will pop up.  Select the "Categories" and "Products" table.  Then click on the "Add" button and then the "Close" button.

9.  The selected tables populates the Query Builder wizard, as you can see the table relationships have been transferred over.

10.  Select the "CategoryName" and "Description" in the "Categories" table.  Then the "ProductName" in the "Products" table.  The SQL select statement will change dynamically as you select a new column from the table window.

11.  Click on the "Execute Query" button to execute the query and see the results.  Once you are happy with the results, click the "OK" button.

12.  The select statement from "Query Builder" populates the "SELECT" tab in "Custom SQL Statement or Stored Procedures" wizard.

13. Click "Next", then click "Finish".  You now have the SqlDataSource control configured

The next step is to bind the data in the SqlDataSource control to a UI data control.  Lets use the GridView control.

1.  Drag the GridView control under "Data" in the "Toolbox" into a .aspx page.

It should look like this when you have both controls on the page.

2. At this point you can either bind the data from the SqlDataSource1 control to the GridView1 control via a wizard or write a little bit of code in the .aspx code behind page.  In this example since we have been using wizards the whole time I will use the wizard.  I will write a new blog on how to bind data programmatically in the future.  To bind the data to the GridView control via the wizard, click on the ">" button on the GridView1 control.

3.In the "Choose Data Source:" drop down list, select "SqlDataSource1"

Accept the default settings, then click on anywhere in on the page outside of the "GridView Tasks" wizard.

4.  Save the .aspx page, and then press F5 to run the web application.  The GridView control will display the data in the browser.

Once again the SqlDataSource control is meant for beginners, and in situations where you need something quick and you can throwaway.  Such as a proof of concept in a client meeting, with Entity Framework and LINQ around, SqlDataSource is really not suitable for a full blown application anymore.

Source Code: SqlDataSourceWS.zip, click on "File" -> "Download" to download the .zip file