ASP. NET 3.5 core programming learning notes (19): Data source components and sqldatasource controls

The data source control represents several named data views. Each view can manage a data set. The data is associated with the data source control and managed through SQL operations (select, insert, delete, and count) to achieve sorting and paging. There are two data source controls: Table-based data source and hierarchical data source. The following table briefly describes the data source controls:

Note that the sqldatasource class is not only for SQL Server, but can also be connected to any ado. net provider that can manage relational data. The following table lists hierarchical data source controls:

Note: Data source controls are not displayed on the page. They are implemented as controls to "exist in declaration mode" (instantiated during request processing ), as the original part of the aspx source code, you can access the view status of the page.

Data Source view

A named view is represented by a data source view object, that is, an instance of the performanceview class. These classes represent custom data views. You can set sorting, filtering, and other data operations. The performanceview class is the base class of all view classes associated with the data source controls. The number of views in the data source control depends on the connection string and the features and Implementation of the underlying data source. In ASP. NET 2.0 and later versions, the built-in data source only supports one view, that is, the default view. The following table lists the attributes of the performanceview class:

The main methods of the performanceview class are as follows:

All data source view objects can obtain data through the select method. This method returns the objects that implement the ienumerable interface. The actual object type depends on the data source control type and attribute settings.

Interaction with data-bound controls

The interaction process between data source controls and data binding controls in ASP. NET 2.0 and later versions is as follows:

Most ASP. Net controls fully perceive the potential functions of the data source control and connect to the underlying data warehouse through the idatasource interface. The official website only requires that the control be used as the data source to implement this interface. Once the control obtains the data source view object, it can call its properties and methods to execute the required tasks.

Hierarchical Data Source view

The table-based data source control usually has only one named view. The view supported by the hierarchical data source control can be any level of data in the data source. The hierarchical data source control and table data source control are consistent with the standard for the programming interface for data binding. However, the two controls have different view classes. hierarchicalperformanceview is used for hierarchical data source controls. This class has only one method, that is, select, this method returns an enumerated hierarchical data object.

Sqldatasource Control

This control represents a connection to relational data storage. It can be an SQL Server, Oracle, or a data source accessed through an OLE DB or open database connection.

We can establish a connection to data storage through two main attributes (connectionstring represents the connection string and providername represents the namespace of the ADO. Net hosting provider. The default value of the providername attribute is system. Data. sqlclient. For ole db providers, you can use the system. Data. oledb string.

The widget can be obtained through the data adapter or the command object. The obtained data may be stored in dataset or read by the data reader.

Sample Code:

<asp:SqlDataSource runat="server" ID="MySqlSource" 
                 ProviderName='<%$ ConnectionStrings:LocalNWind.ProviderName %>' 
                 ConnectionString='<%$ ConnectionStrings:LocalNWind %>'                 SelectCommand="Select * From employees"                 UpdateCommand="Update employees Set lastname=@lname"                  DeleteCommand="Delete From employees Where employeeid=@TheEmp "                  FilterExpression="employeeid > 3">　　　　　　　　　 <!-- parameters go here --></asp:SqlDataSource>
<asp:DataGrid runat="server" ID="grid" DataSourceID="MySqlSource" />

The command attribute contains a string that represents an SQL command. The command can contain parameters.

The filtering function is enabled only when the datasourcemode is dataset.

The data operations supported by the View class associated with sqldatasource are provided by several sets of attributes, as shown in the following table:

Many attributes in the table are reflected in the actual View class attributes.

The delete, insert, select, and update Methods of sqldatasource only encapsulate the corresponding methods of the underlying data source View class. There are also related events corresponding to these methods, which appear in pairs, such as: deleting/deleted, inserting/inserted, selecting/selected, updating/updated, this is triggered before and after the preceding method is called. The filtering operation sends a notification through the filtering event.

Declarative Parameters

Each Command has its own parameter set, that is, an instance of the parametercollection collection class. The following table lists several parameter types supported by ASP. NET:

Each parameter class has a name attribute and a set of attributes for its function and implementation.

Sample Code:

<asp:SqlDataSource runat="server" ID="MySource"
        ConnectionString='<%$ ConnectionStrings:LocalNWind %>'
        SelectCommand="Select * From employees where employeeid > @MinID" >
<SelectParameters>
<asp:ControlParameter Name="MinID" ControlId="EmpID" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>

The query contains a placeholder named @ minid. The data source control automatically fills in the placeholder with the information returned by the controlparameter object. The value of the parameter depends on the given property value of the given control. The property name is specified by propertyname, and the Control ID is specified by controlid. In the preceding example, the text property value of the empid control is used to match the value of @ minid.

Conflict Detection

When multiple users use the same page, if the current record is changed and other users want to change it, how does one update or delete the record?

Sqldatasource uses the conflictdetection attribute to check whether the update and delete operations are performed. This attribute is declared as the conflictoptions Enumeration type and the default value is overwritechange. When selecting this value, we must explicitly add additional clauses for the command to check whether the value to be changed for this field is consistent with that originally read.

Example:

Update employees Set firstname=@firstname
Where employeeid=@employeeid and firstname=@original_firstname

If you use the compareallvalues option, you must handle the deleted or updated event of the data source control and check the number of affected rows. If an operation does not affect any records, a conflict may occur.

Sample Code:

Void onupdated (Object sender, sqldatasourcestatuseventargs E)
{
// Affectrows indicates that no row is affected, probably because of a conflict.
If (E. affectrows = 0)
{
......
}
}

Cache

To reduce the number of queries executed between sending and sending requests, the data source control can cache the result set within a certain period of time. If the data is cached, the Select method obtains the data from the cache instead of the underlying database. If the cache expires, the Select method generates new data from the base and updates the cache. The cache behavior of the sqldatasource control is controlled by the attributes in the following table:

 

Only one cache item is created for different combinations of selectcommand, connectionstring, and selectparameters. You can use the cachekeydependecy attribute of the data source control to control cache items.

Accessdatasource Control

This control is connected to the ACCESS database and inherits from sqldatasource. It provides simple and more targeted programming interfaces.