A data source control interacts with data-bound controls and hides the complex data binding process. These are tools that provide data to data bound controls and support the execution of operations such as insert, delete, and update.
Each data source control wraps a special data provider associated with a database, XML file, or custom class, and helps:
Manage connections
Select data
Manage presentation aspects like paging, caching, etc.
Manipulate data
There are many data source controls available in ASP.NET for obtaining data from a SQL server, ODBC or OLE DB server, from XML files, and from business objects.
Based on data type, these controls can be divided into two categories:
Layered data source control
Table-based data source control
The data source controls for hierarchical data are:
XMLDataSource - It allows binding XML files and strings with or without schema information.
SiteMapDataSource - It allows binding a provider that provides sitemap information.
The data source controls used for tabular data are:
| Data source control | describe |
|---|---|
| SqlDataSource | It represents a connection to an ADO.NET data provider that returns SQL data, including data sources available through OLEDB and QDBC. |
| ObjectDataSource | It allows binding a custom .Net business object that returns data |
| LinqdataSource | It allows binding the results of Linq-t0-SQL queries. (Only supported by ASP.NET 3.5) |
| AccessDataSource | It represents a connection to a Microsoft Access database. |
Data source view is an object of the DataSourceView class, which represents a custom data view designed for different data operations such as sorting, filtering, etc.
The DataSourceView class is used as the base 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 deletion of potential data sources is allowed. |
| CanInsert | Indicates whether to allow insertion of potential data sources. |
| CanPage | Indicates whether paging of potential data sources is allowed. |
| CanRetrieveTotalRowCount | Indicates whether the total row information can be obtained. |
| CanSort | Indicates whether the data can be sorted. |
| CanUpdate | Indicates whether updates are allowed on the potential data source. |
| Events | Gets a list of event handlers represented by the data source view. |
| Name | The name of the view. |
The following table provides the methods of the DataSourceView class:
| method | describe |
|---|---|
| CanExecute | Determine whether the specified command can be executed. |
| ExecuteCommand | Execute the specified command. |
| ExecuteDelete | Performs a delete operation on the data list represented by the DataSourceView object. |
| ExecuteInsert | Performs an insert operation on the data list represented by a DataSourceView object. |
| ExecuteSelect | Get a list of data from the underlying data store. |
| ExecuteUpdate | Performs an update operation on the list of data represented by the DataSourceView object. |
| Delete | Performs a delete operation on the data associated with the view. |
| Insert | Performs an insert operation on the data associated with the view. |
| Select | Returns the queried data. |
| Update | Performs an update operation on the data associated with the view. |
| OnDataSourceViewChanged | Raise the DataSourceViewChanged event. |
| RaiseUnsupportedCapabilitiesError | Called by the RaiseUnsupportedCapabilitiesError method to compare the capabilities required by the ExecuteSelect operation with the capabilities supported by the view. |
The SqlDataSource control represents a connection to a related database such as a SQL Server or Oracle database, or to accessible data via OLEDB or Open Database Connectivity (ODBC). Data connection is completed 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 different data operations on the underlying data depends on different properties (property sets) of the data source control.
The following table provides the related property set of the SqlDataSource control, which provides the control's programming interface:
| attribute group | describe |
|---|---|
| DeleteCommand,DeleteParameters,DeleteCommandType | Gets or sets the SQL statement, parameters, and type of rows to delete in the underlying data. |
| FilterExpression,FilterParameters | Gets and sets data filtering strings and parameters. |
| InsertCommand,InsertParameters,InsertCommandType | Gets or sets the SQL statement, parameters, and type of rows inserted into the underlying data. |
| SelectCommand,SelectParameters,SelectCommandType | Gets or sets the SQL statement, parameters, and type of rows retrieved from the underlying data. |
| SortParameterName | Gets or sets the name of an input parameter that will be used by the command stored procedure to sort the data. |
| UpdateCommand,UpdateParameters,UpdateCommandType | Gets or sets the SQL statement, parameters, and type of rows updated in the underlying data. |
The following code snippet shows the data source control that can be used to perform 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>The ObjectDataSource control enables user-defined classes to connect the output of their methods to data bound controls. The programming interface of this class is almost the same as the SqlDataSource control.
The following are two important aspects of binding customer objects:
A bindable class should have a default constructor, be stateless, and have methods that map to select, update, insert, and delete semantics.
Objects must be updated one item at a time, batch operations are not supported.
Let's jump right into an example of using this control. The student class is a class used with a data source object. This class has three attributes: a student id, name, and city. It has a default constructor and a GetStudents method to retrieve 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, "MH 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 wire to a data source object and retrieve the data:
Create a new web page.
Add a class (Student.cs) to the Solution Explorer project by right-clicking it, adding a class template, and placing the above code inside.
Establish methods so that an application can use a reference to the class.
Place a data source control object in the web form.
Configure the data source by selecting the object.
Choose data methods for different data operations. In this example, there is only one method.
Place a data bound control such as a grid view on the page and select the data source object as a potential data source.
At this stage, the design view should look like this:
Running the project, it retrieves the hard-coded ancestor in the students class.
The AccessDataSource control represents a connection to an Access database. It is based on the SqlDataSource control and provides a simpler programming interface. The following code snippet provides 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 in read-only mode. However, it can also be used to perform insert, update, or delete operations. This is accomplished using ADO.NET commands and parameter sets.
Updates are problematic for Access databases within ASP.NET applications because the Access database is a plain text and the default ASP.NET application account may have write permissions to the database files.