ASP.NET - Data Sources

A data source control interacts with the data-bound controls and hides the complex data binding processes. These are the tools that provide data to the data bound controls and support execution of operations like insertions, deletions, sorting, and updates.

Each data source control wraps a particular data provider-relational databases, XML documents, or custom classes and helps in:

• Managing connection
• Selecting data
• Managing presentation aspects like paging, caching, etc.
• Manipulating data

There are many data source controls available in ASP.NET for accessing data from SQL Server, from ODBC or OLE DB servers, from XML files, and from business objects.

Based on type of data, these controls could be divided into two categories:

• Hierarchical data source controls
• Table-based data source controls

The data source controls used for hierarchical data are:

• XMLDataSource - It allows binding to XML files and strings with or without schema information.

• SiteMapDataSource - It allows binding to a provider that supplies site map information.

The data source controls used for tabular data are:

Data Source Views

Data source views are objects of the DataSourceView class. Which represent a customized view of data for different data operations such as sorting, filtering, etc.

The DataSourceView class serves as the base class for all data source view classes, which define the capabilities of data source controls.

The following table provides the properties of the DataSourceView class:

The following table provides the methods of the DataSourceView class:

The SqlDataSource Control

The SqlDataSource control represents a connection to a relational database such as SQL Server or Oracle database, or data accessible through OLEDB or Open Database Connectivity (ODBC). Connection to data is made through two important properties ConnectionString and ProviderName.

The following code snippet provides the basic syntax of the control:

<asp:SqlDataSource runat="server" ID="MySqlSource"
   ProviderName='<%$ ConnectionStrings:LocalNWind.ProviderName  %>'
   ConnectionString='<%$ ConnectionStrings:LocalNWind %>'
   SelectionCommand= "SELECT * FROM EMPLOYEES" />

<asp:GridView ID="GridView1" runat="server" DataSourceID="MySqlSource" />

Configuring various data operations on the underlying data depends upon the various properties (property groups) of the data source control.

The following table provides the related sets of properties of the SqlDataSource control, which provides the programming interface of the control:

The following code snippet shows a data source control enabled for data manipulation:

<asp:SqlDataSource runat="server" ID= "MySqlSource"
   ProviderName='<%$ ConnectionStrings:LocalNWind.ProviderName  %>'
   ConnectionString=' <%$ ConnectionStrings:LocalNWind %>'
   SelectCommand= "SELECT * FROM EMPLOYEES"
   UpdateCommand= "UPDATE EMPLOYEES SET LASTNAME=@lame"
   DeleteCommand= "DELETE FROM EMPLOYEES WHERE EMPLOYEEID=@eid"
   FilterExpression= "EMPLOYEEID > 10">
   .....
   .....
</asp:SqlDataSource>

The ObjectDataSource Control

The ObjectDataSource Control enables user-defined classes to associate the output of their methods to data bound controls. The programming interface of this class is almost same as the SqlDataSource control.

Following are two important aspects of binding business objects:

• The bindable class should have a default constructor, it should be stateless, and have methods that can be mapped to select, update, insert, and delete semantics.

• The object must update one item at a time, batch operations are not supported.

Let us go directly to an example to work with this control. The student class is the class to be used with an object data source. This class has three properties: a student id, name, and city. It has a default constructor and a GetStudents method for retrieving data.

The student class:

public class Student
{
   public int StudentID { get; set; }
   public string Name { get; set; }
   public string City { get; set; }
   
   public Student()
   { }
   
   public DataSet GetStudents()
   {
      DataSet ds = new DataSet();
      DataTable dt = new DataTable("Students");
      
      dt.Columns.Add("StudentID", typeof(System.Int32));
      dt.Columns.Add("StudentName", typeof(System.String));
      dt.Columns.Add("StudentCity", typeof(System.String));
      dt.Rows.Add(new object[] { 1, "M. H. Kabir", "Calcutta" });
      dt.Rows.Add(new object[] { 2, "Ayan J. Sarkar", "Calcutta" });
      ds.Tables.Add(dt);
      
      return ds;
   }
}

Take the following steps to bind the object with an object data source and retrieve data:

• Create a new web site.

• Add a class (Students.cs) to it by right clicking the project from the Solution Explorer, adding a class template, and placing the above code in it.

• Build the solution so that the application can use the reference to the class.

• Place an object data source control in the web form.

• Configure the data source by selecting the object.

• Select a data method(s) for different operations on data. In this example, there is only one method.

• Place a data bound control such as grid view on the page and select the object data source as its underlying data source.

• At this stage, the design view should look like the following:

• Run the project, it retrieves the hard coded tuples from the students class.

The AccessDataSource Control

The AccessDataSource control represents a connection to an Access database. It is based on the SqlDataSource control and provides simpler programming interface. The following code snippet provides the basic syntax for the data source:

<asp:AccessDataSource ID="AccessDataSource1 runat="server" 
   DataFile="~/App_Data/ASPDotNetStepByStep.mdb" SelectCommand="SELECT * FROM  [DotNetReferences]">
</asp:AccessDataSource>

The AccessDataSource control opens the database in read-only mode. However, it can also be used for performing insert, update, or delete operations. This is done using the ADO.NET commands and parameter collection.

Updates are problematic for Access databases from within an ASP.NET application because an Access database is a plain file and the default account of the ASP.NET application might not have the permission to write to the database file.