ASP.NET data source

The data source

A data sourse control interacts with data-bound controls and hides the complex process of co-editing data. T hese are tools that provide data to data bound controls and support execution such as insertion, deletion, and update operations.

Each data sourse control wraps up a special data provider-related database, XML file, or custom class, and helps:

• Manage connections
• Select the data
• Manage presentation aspects such as pedding, caching, and more
• Manipulate the data

There are ASP.NET data sourse controls available in the ASP.NET, from SQL servers, ODBC, or OLE DB servers, from XML files, and from business objects.

Based on data types, these controls can be divided into two categories:

• Layered data sourse controls
• Table-based data sourse control

The data sourse control for layering data is:

• XMLDataSource - It allows XML files and strings to be bound with or without pattern information.
• SiteMapDataSource - It allows you to bind a provider that provides site map information.

The data source control used as table data is:

Data source control	Describe
SqlDataSource	It represents the connection to ADO.NET data provider that returns SQL data, including data sources available through OLEDB and QDBC.
ObjectDataSource	It allows you to bind a custom .Net business object that returns data
LinqdataSource	It allows the results of Linq-t0-SQL queries to be bound. (Supported only ASP.NET 3.5)
AccessDataSource	It represents a connection to the Microsoft Access database.

Data Source view

A Data source view is an object of a DataSourceView class that represents a custom view of data designed for different data operations such as sorting, filtering, and so on.

The DataSourceView class is used as the basic class for all data source view classes and defines the performance of the data source control.

The following table provides the properties of the DataSourceView class:

Property	Describe
CanDelete	Indicates whether to allow the deletion of potential data sources.
CanInsert	Indicates whether to allow the insertion of potential data sources.
CanPage	Indicates whether to allow pedding of potential data sources.
CanRetrieveTotalRowCount	Indicates whether the total row information is available.
CanSort	Indicates whether the data can be sorted.
CanUpdate	Indicates whether updates are allowed on potential data sources.
Events	Gets a list of event handles represented by the data source view.
Name	The name of the view.

The following table provides a way for the DataSourceView class:

Method	Describe
CanExecute	Determines whether the specified command can be executed.
ExecuteCommand	Execute the specified command.
ExecuteDelete	Perform a delete operation on the list of data represented by the DataSourceView object.
ExecuteInsert	Perform an insert operation on the list of data represented by the DataSourceView object.
ExecuteSelect	Get a list of data from a potential data store.
ExecuteUpdate	Perform an update operation on the list of data represented by the DataSourceView object.
Delete	Perform a delete operation on the data associated with the view.
Insert	Perform an insert operation on the data associated with the view.
Select	Returns the data being queried.
Update	Perform an update operation on the data that is contacted to the view.
OnDataSourceViewChanged	The DataSourceViewChanged event is presented.
RaiseUnsupportedCapabilitiesError	Called by the RaiseUnsupportedCapabilitiesError method to compare the capabilities required for ExecuteSelect operations and the capabilities supported by the view.

SqlDataSource control

SqlDataSource controls represent connections to related databases such as SQL Server or Oracle databases, or accessable data through OLEDB or Open Database Connectivity (ODBC). T he data connection is done through two important properties, ConnectionString and ProviderName.

The following snippets provide 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 different data operations on potential data depends on the different properties (property sets) of the data source control.

The following table provides a set of properties for the related SqlDataSource control, which provides the programming interface for the control:

The property group	Describe
DeleteCommand, DeleteParameters, DeleteCommandType	Gets or sets SQL statements, parameters, and types of rows to be deleted from potential data.
FilterExpression, FilterParameters	Gets and sets data filter strings and parameters.
InsertCommand, InsertParameters, InsertCommandType	Gets or sets SQL statements, parameters, and types of rows inserted into potential data.
SelectCommand, SelectParameters, SelectCommandType	Gets or sets SQL statements, parameters, and the types of rows retrieved in potential data.
SortParameterName	Gets or sets the name of an input parameter that is used by the procedure stored by the command to sort the data.
UpdateCommand, UpdateParameters, UpdateCommandType	Gets or sets SQL statements, parameters, and types of rows to update in potential data.

The following snippets show the data source controls that can be used for data operations:

<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>

ObjectDataSource control

ObjectDataSource controls enable user-defined classes to connect the output of their methods to the data bound control. T he programming interface for this class is almost identical to the SqlDataSource control.

Here are two important aspects of binding customer objects:

• A binding class should have a default constructor, it should be stateless, and it should have a way to map to selection, update, insert, and delete meaning.
• Objects must update one item at a time, and batch operations are not supported.

Let's use this control directly in an example. T he student class is one that is used with a data source object. T his class has three properties: a student id, name, and city. I t has a default constructor and a GetStudents method for retrieving data.

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 line to a data source object and retrieve the data:

• Create a new web page.
• Add a class to solution Explorer's project by right-clicking .cs, add a class template, and put the above code inside.
• The build method allows the application to use references to the class.
• Place a data source control object in a web page form.
• Configure data source by selecting an object.

• Select a data method for different data operations. I n this example, there is only one method.

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

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

• Run the project, which retrieves the hard-coded ancestors in the student class.

AccessDataSource control

The AccessDataSource control represents a connection to the Access database. I t is based on sqlDataSource controls and provides a simpler programming interface. T he following snippets provide the basic syntax of 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 for read-only mode. H owever, it can also be used to perform insert, update, or delete operations. T his is done ADO.NET set of commands and parameters.

Updates are problematic for ASP.NET access databases within an application because the Access database is plain text and the default ASP.NET application account may have permission to write database files.