HomeASP.NET Programming the ASP.NET 2.0 SqlDataSource ...
Programming the ASP.NET 2.0 SqlDataSource Control
Many of us think that Data-Source controls, like the SqlDataSource control, have been developed to work with Data-Bound controls. Yes this is correct and this is exactly the way we should be using it. But in this article, I'm going to show you that you can use the SqlDataSource control without an associated Data-Bound control like the GridView control.
Contributed by Michael Youssef Rating: / 19 October 30, 2007
Today we are going to write C# code in the Page_Load() event handler method of the Default.aspx page. It creates a SqlDataSource object and assigns appropriate values for the control's required properties. These properties are used by the SqlDataSource control to communicate with the database, and then call the appropriate methods. That will help you to understand the difference between the code that we write today and the code that was generated by Visual Studio.NET 2005 in the last article, "Introduction to the ASP.NET 2.0 SqlDataSource Control." In that article, we used the SqlDataSource control's Task Menu to make the configuration for the control. The code we write today will help you understand how the SqlDataSource control works that way, so let's start.
Create a new web site, then add a new Web Form to it and write the following code in place of the code of the Default.aspx.cs file. Note: don't add any controls to the markup.
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls;
using System.Collections; using System.Data.Common;
public partial class _Default : System.Web.UI.Page
Run the page and you will get the result shown in the following screenshot.
As you can see, we have accessed the database and retrieved data without writing any ADO.NET data access code. Let's see how we have accomplished this result.
In the previous section's code example, we used the SqlDataSource control to return and display data on an ASP.NET page, without using Data-Bound Controls such as the GridView control we used in the previous article. Let's walk through the code and discuss what we have done. We started by creating an object instance of our SqlDataSource control so we can work on its properties and methods in code. The next line of code adds the SqlDataSource1 object to the Controls collection of the page using the Add() method. You must use this technique to add controls programmatically in your code.
The SqlDataSource control is an abstraction of ADO.NET code. What do I mean by that? Okay, by using the SqlDataSource control you don't have to write ADO.NET code to connect to the database, execute a command, and then retrieve the result set using a DataReader object or a DataSet object. The SqlDataSource control accepts the connection string, to know where to get the data for you and the T-SQL Select statement that you need to execute on the server. It accepts the connection string and the T-SQL Select statement through its Properties as shown in the next 2 C# statements
SqlDataSource1.SelectCommand = "SELECT EmployeeID, FirstName, LastName, Title FROM Employees";
The SqlDataSource control's actual work, for selecting data operations, begins when you call its Select() method. Note that this method is usually called by the Data-Bound Controls when they need to display the data on an ASP.NET page and you don't have to explicitly call it, but we do that here to explain some important concepts regarding the SqlDataSource control. The SqlDataSource.Select() method returns an object that implements the IEnumerable interface. By implementing the IEnumerable interface, the contents of the Select() method's returned object type can be enumerated.
In plain English, when you call the SqlDataSource.Select() method it returns a collection of rows that you can iterate through in your code. Again, you wouldn't do that and it's the job of the Data-Bound Controls to iterate through the returned object of the Select() method, but it's good to understand how things work; it will be useful, as you will see. If you don't understand what the IEnumerable interface means to a collection of objects then I advice you to read more about C# Collections and the IEnumerable, IList, ICollection and IDictionary interfaces.
Anyway, the IEnumerable interface provides what the foreach statement needs to iterate over a collection. The returned object of the SqlDataSource.Select() method can be one of two possible objects. You can return a DataReader, which is a fast read-only forward-only mechanism for retrieving data, and you can return a DataSet, which is an in-memory representation of relational data. You can set what object is returned through assigning the SqlDataSource.DataSourceMode property to values of the SqlDataSourceMode enumeration. We will discuss this in the next section.
Using the SqlDataSourceMode enumeration makes a huge difference for us. If your SqlDataSource control is used to retrieve data for display purposes only, you should set the SqlDataSource.DataSourceMode property to SqlDataSourceMode.DataReader. If you need to retrieve data and perform operations such as updating, deleting, sorting and filtering then you would set the SqlDataSource.DataSourceMode property to SqlDataSourceMode.DataSet.
Take a look at our code again.
if (SqlDataSource1.DataSourceMode == SqlDataSourceMode.DataReader)
The if statement evaluates to true if the SqlDataSource1.DataSourceMode is equal to SqlDataSourceMode.DataReader. If it is, we iterate through the returned Data Reader object's collection of DbDataRecord objects. The returned DbDataRecord objects represents the table's rows that are returned using a Data Reader object. The else if statement evaluates to true if the SqlDataSource1.DataSourceMode is equal to SqlDataSourceMode.DataSet, which executes a different code because the returned object, in this case, is a DataView object and its collection of the database table's rows are not the same as the DataReader object's collection of database table's rows.
The DataView object has a collection of DataRowView that represents the returned rows. Note that both the DataReader and the DataView implement the IEnumerable interface and that's why we can enumerate through its collection of rows. Also note that the DataReader returned, if the SqlDataSourceMode.DataReader is used, depends on the ADO.NET Data Provider used. By default the SQL Server Data Provider is used and this means that a SqlDataReader object is the return type of the SqlDataSource.Select() method.
If we don't test for the value of the SqlDataSource.DataSourceMode property we would get an exception, shown in the next screenshot. If the SqlDataSource.DataSourceMode is set to SqlDataSourceMode.DataReader our code implements logic to manipulate a returned DataView object as shown next.
The exception is shown in the following screenshot.
And the same InvalidCastException is raised in the opposite situation, where you will have code that manipulates the DataReader object but the returned object is DataView as shown next.
The default value for the SqlDataSource.DataSourceMode is DataSet so if you tried to execute the above code without the if statement you would get the exception shown in the next screenshot.
To explain how we obtained the values of each of the returned rows I will remove the code that creates the HTML table's cells and rows from the above code. It looks like this:
if (this.SqlDataSource1.DataSourceMode == SqlDataSourceMode.DataReader)
for (int i = 0; i < record.Row.Table.Columns.Count; i++ )
{
string myField = record[i].ToString();
}
}
}
In the first foreach statement we get access to each row through an object of type DbDataRecord. The code we have written is flexible because we depend on the DbDataRecord.FieldCount property to tell us how many fields this row contains, and then using a for statement we access and retrieve the value of each field, through the indexer on the DbDataRecord, by passing the variable i to the indexer.
In the second foreach statement we have different logic to use. The for statement uses the DataRowView.Row.Table.Columns.Count property. In short, this property contains the number of columns of the table that this DataView object represents, so now we know how many fields we should access through this property.
Inside the for statement we have the same line of code as in the first foreach statement that accesses the value of the field through the indexer. This time the indexer works on another object which is a DataRowView object. Using this technique we can execute a T-SQL SELECT statement that selects any number of columns and still be able to retrieve them all.
We can also access the fields by name but we have to modify the code. Note in this case we must know in advance how many fields are returned because we will access the fields by their names as shown in the next alternative code sample.
In the next article, we are going to perform update operations using the SqlDataSource control programmatically, and then in the next few articles we are going to use Data-Bound Controls to complete all these operations with "almost" no code.
ASP.NET 
Programmatically Updating Databases using ASP.NET 2.0 SqlDataSource
