Click here to download the source code for this lesson.

In the prior lesson Using the ASP.NET GridView control to query a SQL Server database I showed you how to connect to and pull data from a database table and display it in a GridView.  In that lesson I wrote C# code to do the data retrieval and data binding to the GridView server control.  When you write code to perform these types of operations you have the ultimate flexibility and control over how your program behaves, however there are situations where you may want to connect to interact with a database without writing any code.  This is where the SqlDataSource data control comes in.  With the SqlDataSource server control, you can connect to a database and retrieve or update data without writing any C# code.  Here are some benefits of the SqlDataSource control and reasons to use it versus writing your own C# code:

• Use it in situations when you want to connect to a database directly from your web form and run queries.  If you are working in an N-Tier design and you have back-end libraries or web services that integrate with your database, you probably want to avoid this control and you may not be able to use it.  For example, in many government and commercial implementations the web server is usually in a DMZ without the ability to connect to the database.
• Use it in situations when you want to implement filtering, sorting and paging without any code.  This is one of the primary benefits of the SqlDataSource control.
• Use it in situations when you want to query and update database data without writing any code.  This is a rapid application development characteristic of the SqlDataSource control which makes Visual Web Developer 2008 and Visual Studio 2008 truly agile integrated development environments.
• You can pass parameters to queries or stored procedures by binding to TextBoxes and other server controls to solicit input data from the end user.  For example, you can implement a table search and ask the user to type in a last name that will be used in the query Select statement.  The SqlDataSource control handles all the parameter binding.

As you can see, this is a powerful control and can help make your C# development experience more productive, but cannot be used in every situation so you will have to determine whether or not it is feasible for you.  In this lesson and in the next few lessons I will explore how to use the SqlDataSource control to make the GridView control do many useful tasks such as sorting and paging.

Let me get started by talking about what the SqlDataSource control actually is.  It is a server control, just like the TextBox and Label controls, however it is not a visual control.  In other words, you don’t see it on a web page.  It works in the background on the server when your .aspx page runs to perform integration with a database.  You first have to drag the control from the Data group of controls in the Toolbox onto your web form.  After you do that, you configure some of the properties of the control and then you can associate it to your GridView using one line of code in a Button click event handler method that we will explore later in this lesson.

After you drag the SqlDataSource control onto your web form, change the ID property to something more meaningful.  In my example I changed it to CSharpDataSource.  Next set the ConnectionString property.  You can paste in any valid database connection string.  In my example I put an SQL Server 2008 connection string to my csharpuniversity database.  Next you have to set the SelectQuery property.  This property contains a popup window that lets you enter valid SQL query statements.  Finally once you have all of these properties configured, you can add a Button to your web form and create a click event handler by double clicking on the Button in design view.  Then put the following line of code in the click event method (my method is named btnCustomers_Click).

gvCustomers.DataSourceID = "CSharpDataSource";

Notice that I used the DataSourceID property of the GridView control gvCustomers.  I assigned the ID of my SqlDataSource control on my web form which is CSharpDataSource.  That line of code basically tells .NET to take the SqlDataSource control and bind it to the GridView.  If you don’t do this, nothing will happen when your program runs and you won’t see any data.  If you need to use the SqlDataSource in a scenario where you don’t want the user to have to click on a Button in order to retrieve data from the database (e.g. you want to show some data when the page loads), you don’t need to write any code, you can simply set the DataSourceID property value in the Design view of your web form using a point and click method.  Either way the SqlDataSource control is essentially connecting to the database, running a query and then storing the results of the query in a DataSet.  Then, because you bound the SqlDataSource to the GridView, the DataSet result data will be displayed on the web page.  Works like magic doesn’t it?  Play around with the SqlDataSource and see if it fits well in your environment.  Click here to watch an example video where I setup and use the SqlDataSource control to retrieve data and show it in a GridView control.