HomeASP.NET Introduction to ASP.NET 2.0 SqlDataSource ...
Introduction to ASP.NET 2.0 SqlDataSource Control
The SqlDataSource control is one of the new controls introduced in ASP.NET 2.0. It provides developers with a declarative way of accessing data in databases for code-free retrieving and updating operations. In this article, and the next few articles, we take a deep look at the SqlDataSource control and how it works.
Contributed by Michael Youssef Rating: / 17 October 08, 2007
We will use T-SQL statements to select, insert, update and delete data from tables. We will also use stored procedures with the SqlDataSource control and we will handle OUTPUT parameters and return values along the way. At first, we need to understand how to programmatically add the SqlDataSource control to a web form. Let's begin.
As you might know, to create database-aware applications your application needs a connection object, a command object, a data reader and maybe a dataset object. In ASP.NET 2.0 the Data Source Web Controls provide us with a declarative way of accessing data in databases. There are five Data Source Web Controls in ASP.NET 2.0, namely the SqlDataSource, AccessDataSource, ObjectDataSource, XmlDataSource and the SiteMapDataSource.
In this article we discuss the SqlDataSource, which provides access to any database as long as there is a data provider, registered in the machine.config file, for it. It makes sense because the SqlDataSource control uses "ADO.NET generic data access code" to access databases. If you don't know what a data provider is, simply understand that a data provider consists of classes that are specifically designed to access certain types of data stores for performing data operations on this data. If you don't know much about ADO.NET then please consult my articles about ADO.NET with ASP.NET 2.0.
The SqlDataSource control gives you an almost code-free mechanism for accessing a database. You use declarative programming to write markup for the control; you create the control (declaratively) and tell it where to get the data and what T-SQL statements or stored procedures will be used. You do all that without writing a single line of code!
Note that the SqlDataSource Control only gets you the data. You need a way to represent the data on the web form. That's where the Data-Bound Controls come into play. We will not discuss these controls in this article, because I want to discuss them in great detail, but we will use the GridView control to display the data we get from the database. The GridView control represents the data in a table-like format. Let's take our first example; it accesses the Northwind database and retrieves data from it.
Start by creating a new ASP.NET web site by launching VS.NET 2005 and selecting File --> New --> Web Site. You will get the following dialog box.
Place the web site folder on the file system and select Visual C# as the coding language. We are not going to write any code in this article, but I'm a C# guy as you might be too. Switch to design view and drag the SqlDataSource Control to the page from the Data section of the Toolbox.
Now the SqlDataSource control is on the form and we need to configure it. You will note that there is a menu shown at the upper right corner of the control; this is called the task menu and you are going to see it a lot with other controls. It's used to configure some options of the control.
Click on Configure Data Source from the task menu of the SqlDataSource control and you will get the following dialog.
This dialog is used to choose the connection that the SqlDataSource control will use to get to the database. Since we didn't create any connections yet, click on the New Connection button to create a new connection. Note that if you have any connections in the Server Explorer they will be shown in the drop-down list. The following dialog will be shown.
This dialog is used to choose the Data Provider for the data access objects that the control creates, which depends on the Data Source. Choose Microsoft SQL Server to connect to your local SQL Server. Click on the Continue button.
The Add Connection dialog is shown. Select your server name and after that the database to which you want to connect. Now test the connection.
And when you click on the Test Connection button you will get the following message.
Now click OK, then OK to the Add Connection dialog, and you will return to the Configure Data Source dialog box -- but this time with the connection configured.
Click Next and you will get the following dialog box.
This dialog box asks you if you want to save the connection string in the web site configuration file. Simply put, a configuration file (which is named Web.Config) is used to configure ASP.NET web sites. Among those configurations is the connection string, which a connection object uses to know where the database server is and what database you want to access. You can find out more on connection strings in my articles about ADO.NET with ASP.NET 2.0; for now just click Next.
This dialog is used to build the T-SQL query used by the SqlDataSource control to retrieve the data. You have two options: you can write the T-SQL statement yourself or you can use the second option, the default option, which provides you with a tool that writes the query for you. In this example we will be using the second option which will write the T-SQL code for us. Select the Employees table from the drop-down list and select the following columns to be included in the query: EmployeeID, LastName, FirstName, Title, HomePhone.
Note that the T-SQL SELECT statement has been written in the preview section of the dialog, click on Next.
Now you can test to find out whether your query returns a result set by clicking on the TestQuery button as shown in the above screenshot. Click on finish and it's done; we now have a ready-to-use SqlDataSource object. Up to this point we didn't add the control that will use the SqlDataSource control to display the data on the page. So let's do it.
The SqlDataSource itself can't display the data on the page, it just represents the data to the page, or it's better to say that it represents data to Data-Bound Controls like the GridView control. So let's add a GridView control to the page and run it.
Drag a GridView control onto the page in the same way you did with the SqlDataSource, from the Data section of the Toolbox.
From the task menu of the GridView control select SqlDataSource1 from the drop-down list next to the Choose Data Source label. The following two screenshots represent the task menu before and after you select the SqlDataSource1 object as the data source for the GridView control.
Although that a discussion of the GridView control is beyond the scope of this article, note the difference between the first and the second screenshots. In the second screenshot you can configure the data source and you can enable Paging, Sorting and Selection. Also note that the columns have been changed to fit the returned result set of running the SELECT statement that has been set in the SqlDataSource control.
Now Let's run the page. Right click on the Default.aspx page in the Solution Explorer and select View in Browse.
The following result will be displayed in the page:
Without writing a single line of code we managed to connect to the database, execute a T-SQL statement and retrieve the results into a GridView control. So what really happened?
This is a declaration of the two controls we have used, the SqlDataSource control and the GridView control. Before we go any further let's see what other code has been written for us in the Web.Config file.
In the Web.Config file, and specifically in the <connectionStrings> section of that file, we have the connection string properties that we set using the Configure Data Source dialog of the SqlDataSource control that we added to the web page. The element <add> is used to add a new connection string to that section. You may have more than one connection string in your configuration file; it depends on your application needs, because you may need to store the database that contains your security data in a database that is separate from the application's database.
You have to give the connection string a name through the name attribute and define the connection string itself using the connectionString attribute. Also you need to provide the providerName attribute which is used by the SqlDataSource control to determine what ADO.NET Data Provider to use behind the scenes to create the connection, commands, data reader and data set objects. Again, don't worry if you don't know much about those objects because you can read about them in my ADO.NET articles.
The <SqlDataSource> element defines the SqlDataSource1 control that we have dragged on the page.
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title], [HomePhone] FROM [Employees]">
</asp:SqlDataSource>
As you can see, the ConnectionString and the SelectCommand attributes are used to define the connection string, used internally by the SqlDataSource's connection object, and the T-SQL SELECT statement, which is used internally by the SqlDataSource's Command object, respectively. Note the syntax of the ConnectionString attribute, which is in the form of "<%$ ConnectionStrings:the name of the specific connection string element used %>. The control will retrieve the connection string from the configuration file Web.Config of the website using this syntax. We can also write the connection string directly into the control's ConnectionString attribute as in the following declaration:
It's always best to place your connection strings in configuration files and access them from your class, as I explain in the ADO.NET 2.0 articles. The SelectCommand attribute's value is used by the control as the T-SQL SELECT statement that will be executed on the server. The result set returned from executing the query will be stored in a Dataset by default. I'll explain more about that in the next article. To display the returned data you need to add a GridView control and set its DataSourceID to the SqlDataSource control used as we did.
I know, there are a lot of concepts behind those controls which will be discussed in upcoming articles, so don't worry; you will soon understand everything regarding the SqlDataSource and the GridView controls.
ASP.NET 
Implement Gzip and Deflate Compression in ASP.Net
