Filtering Based on Business Objects (lists)

Document directory

• Typical Implementation of assembled SQL statements
• Cache Policy
• Filter Business Objects
• Page implementation

Code download: http://www.tracefact.net/sourcecode/filterSorting.rar

Introduction to filtering based on Business Objects

You may be too familiar with SQL statements, or you may have transitioned from Asp to Asp. Net, but the concept of Asp has not changed much. The result is that most of the logic of the application is handed over to the database, and the. Net Framework provides us with flexible and powerful data control capabilities. For example, when we need to filter data, we think of the "Where" clause instead of the List <T>. findAll (); when we need to sort data, we think of the "Order By" clause instead of the List <T>. sort (); when we need to paging data, we think of stored procedures rather than List <T>. getRange ().

Of course, it is very efficient to let the database do this in many cases, especially when the data volume is large. However, when the data volume is small, all data is retrieved at a time and then cached on the server. Subsequent sorting, filtering, and paging requests will only be cached, which will greatly improve the efficiency.

No method is absolutely good or absolutely not good, and there is a scope of application. This article will introduce the same thing. When the data volume is very large, we may not only want the database to use the Where clause for filtering, then, only the data entries to be displayed on the current page are returned.

This article only proposes another solution to the same problem, and determines when to use it based on the situation.

Are you still assembling SQL statements? Traditional data filtering methods

Filtering data should be the most common operation. Let's take the Orders order table of the NorthWind database as an example. What do you do if we need to filter it by any combination of years, months, and days? I think it should be like this:

1. Create three drop-down boxes on the page to select the year, month, and day.
2. When you access the page for the first time, all data is displayed. At this time, an access to the database will return all data, such as "Select * From Orders" in SQL statements ".
3. Select any one of the year, month, and day to generate a PostBack.
4. Assemble an SQL statement based on the user's choice, such as "Where Year (OrderDate) = @ Date and Month (OrderDate) = @ Month ".
5. The SQL statement is sent to the database, and the database returns the query result, which is displayed on the user interface.
6. This is repeated.

As you can see, in the above mode, in order to display different data according to the user's choice, each user's operation requires an access to the database, let's take a look at the specific implementation.

Typical Implementation of assembled SQL statements

First, create a page (SqlApproach. aspx) and place three DropDownList controls, a GridView control, and an ObjectDataSource control on the page, as shown in:

In the App_Code folder, create the Business Object Order (in Order. cs) based on the Orders table ).

Public class Order
{
Private int orderId; // order Id
Private string customerId; // user ID
Private DateTime orderDate; // The order date.
Private string country; // country

Public int OrderId {
Get {return orderId ;}
Set {orderId = value ;}
}

Public string CustomerId
{
Get {return customerId ;}
Set {customerId = value ;}
}

Public DateTime OrderDate
{
Get {return orderDate ;}
Set {orderDate = value ;}
}

Public string Country
{
Get {return country ;}
Set {country = value ;}
}
}

For the Set (List, or row set) data, we use List <Order> to store. Next, create an OrderManager. cs file in the App_Code directory to return the result set from the database and fill the list. It usually contains code similar to this:

Public class OrderManager
{
// Obtain List objects based on SQL statements
Public static List <Order> GetList (string query)
{
List <Order> list = null;
SqlDataReader reader = ExecuteReader (query );

If (reader. HasRows ){
List = new List <Order> ();
While (reader. Read ()){
List. Add (GetItem (reader ));
}
}

Reader. Close ();

Return list;
}

// Obtain a SqlDataReader object
Private static SqlDataReader ExecuteReader (string query)
{
String connString = ConfigurationManager. ConnectionStrings ["NorthwindConnectionString"]. ConnectionString;
SqlConnection conn = new SqlConnection (connString );
SqlCommand cmd = new SqlCommand (query, conn );
Conn. Open ();
SqlDataReader reader = cmd. ExecuteReader (CommandBehavior. CloseConnection );

Return reader;
}

// Obtain an Order object from a row
Private static Order GetItem (IDataRecord record)
{
Order item = new Order ();
Item. OrderId = Convert. ToInt32 (record ["orderId"]);
Item. CustomerId = record ["CustomerId"]. ToString ();
Item. OrderDate = Convert. ToDateTime (record ["OrderDate"]);
Item. Country = record ["ShipCountry"]. ToString ();
Return item;
}
}

The above code is easy to understand: the GetList () method accepts a query statement and returns the List <Order> List. Within the GetList () method, the ExecuteReader () method is called. The ExecuteReader () method creates and returns a SqlDataReader object based on the passed query statement query, which is used to read data returned by the database. In the While statement, the GetItem () method is called, which creates an Order object based on each data row. Finally, add the Order object to the List <Order> List and return to the List.

It can be seen that the method we need to call on the page is the GetList (query) method. Let's take a look at the main code of the page file SqlFilter. aspx:

<Asp: ObjectDataSource ID = "objdsOrderList" runat = "server" SelectMethod = "GetList"
TypeName = "OrderManager" OnSelecting = "objdsOrderList_Selecting">
<SelectParameters>
<Asp: Parameter Name = "query" Type = "string"/>
</SelectParameters>
</Asp: ObjectDataSource>

ObjectDataSource uses GetList as SelectCommand. The ObjectDataSource ID is used for the performanceid of the GridView.

Now let's continue to look at what the post-code of SqlFilter. aspx is usually like (when the Text of DropDownList is set to "all", its Value is "0 "):

Public partial class SqlApproach: System. Web. UI. Page
{
Public int Year {
Get {return Convert. ToInt32 (ddlYear. SelectedValue );}
}

Public int Month {
Get {return Convert. ToInt32 (ddlMonth. SelectedValue );}
}

Public int Day {
Get {return Convert. ToInt32 (ddlDay. SelectedValue );}
}

// Obtain the query statement
Public string QuerySql
{
Get
{
Int year = Year;
Int month = Month;
Int day = Day;

List <string> sqlList = new List <string> ();
String subSql = string. Empty;

If (year! = 0)
SqlList. Add (String. Format ("Year (OrderDate) = {0}", year ));

If (month! = 0)
SqlList. Add (String. Format ("Month (OrderDate) = {0}", month ));

If (day! = 0)
SqlList. Add (String. Format ("Day (OrderDate) = {0}", day ));

If (sqlList. Count> 0) // if any drop-down box is selected, the SQL statement is assembled.
{
String [] list = sqlList. ToArray ();
SubSql = "Where (" + String. Join ("and", list) + ")";
}
// Return the assembled SQL statement
Return "Select CustomerId, ShipCountry, OrderDate, OrderId From Orders" + subSql;
}
}

// Page loading events
Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{
AppendListItem (ddlMonth, 12); // a total of 12 months
AppendListItem (ddlDay, 31); // The default value is 31 days.
}
}

// Month change
Protected void ddlMonth_SelectedIndexChanged (object sender, EventArgs e ){
GvOrderList. DataBind ();
}

// Year change
Protected void ddlYear_SelectedIndexChanged (object sender, EventArgs e ){
GvOrderList. DataBind ();
}

// Daily change
Protected void ddlDay_SelectedIndexChanged (object sender, EventArgs e ){
GvOrderList. DataBind ();
}

// Add a project to the list
Protected void AppendListItem (ListControl list, int end ){
For (int I = 1; I <= end; I ++ ){
List. Items. Add (new ListItem (I. ToString ()));
}
}

// The PostBack of each list calls gvOrderList. DataBind (), and then triggers
// Then, the OrderManager. GetList (query) method is called to return data from the database.
Protected void objdsOrderList_Selecting (object sender, ObjectDataSourceSelectingEventArgs e ){
E. InputParameters ["query"] = QuerySql;
}
}

This Code uses the Year, Month, and Day attributes to obtain the DropDownList values of the Year, Month, and Day respectively. The main logic is included in QuerySql attributes. It assembles SQL statements based on the status of the three lists. Finally, in the Selecting event of ObjectDataSource, pass the QuerySql method to get the list object and display it on the page.

NOTE: To make the code simple, I didn't process special dates like. Even if this date is used as the query condition, only an empty list is returned and no program error occurs. As this is only a sample program, I think it is acceptable.

Filtering Based on Business Objects

After learning about the traditional screening based on assembled SQL statements, let's take a look at what object-based filtering is like and how to improve performance.

1. Create three drop-down boxes on the page to select the year, month, and day.
2. When you access the page for the first time, all data is displayed. At this time, an access to the database will return all data, such as "Select * From Orders" in SQL statements ".
3. Cache all the returned data (which has been converted to a List <Order> business object.
4. Filter the List <Order> in the cache based on the user's choice, return the filtering result, and display it on the page.
5. After this repetition, each user request will only be directed to the cache.

Cache Policy

Because cache is used here, it is necessary to discuss the cache. We know that there are three types of Cache: OutputCache, data Cache based on data source controls, and object Cache based on System. Web. Caching. Cache. Among the three caches, The OutputCache and data cache can also apply the SqlCacheDependency cache expiration Policy. SqlCacheDependency simply means that when the database data changes, it depends on this database (table) the cache automatically expires. SqlCacheDependency is divided into two different policies: Polling-based pull mechanism of SQL Server2000 and Notification-based push mechanism of SQL Server2005. When you apply System. Web. Caching. Cache, you cannot apply the SqlCacheDependency expiration policy. You can only apply an expiration policy that is changed based on a file or another Cache project.

NOTE: The Polling mechanism of SQL Server means Asp. net processes perform this access to the database process at regular intervals. Because the interval is fixed, it is called round robin (the access time is in milliseconds and can be stored on the Web. config ). When a round robin finds that the data is different from the data in the previous access, the cache dependent on the data immediately expires. The Notification mechanism of SQL Server2005 is Asp. net only does its own thing, does not ask the database process, and when the database data changes, the SQL Server 2005 process actively notifies Asp. net Process, tell it that the data has changed, and then Asp. net to expire the cache. Therefore, the efficiency of using the SQL Server2005 notification mechanism is much higher.
This article does not describe how to enable SqlDependency. You can refer to related books.

When I mention caching, you may feel that I have used caching for filtering business objects, but I have no way to assemble SQL statements. It is unfair to compare them, now, I want to make a comparison of the performance of the two tables in their respective application cache (SqlDependency uses the polling mechanism of SqlServer 2000 ):

Cache name	Filter Based on assembled SQL statements	Filtering Based on Business Objects
OutputCache VaryByParam = "*" Duration = "600"	It makes sense when there are few options in the drop-down box. During the cache validity period, the database will not be accessed. However, when there are many options, a large number of pages will be cached. During the first visit, you still need to access the database and cache multiple page results. The effect is not good enough. When database data is changed, the cache does not expire.	It doesn't make sense because the business object is read from the cache. When database data is changed, the cache does not expire.
OutputCache VaryByParam = "*" Duration = "999999" SqlDependency = "Northwind: Orders"	Same as above, but the cache will expire when data changes.	It doesn't make sense. When the database data changes, the page cache will expire. The page cache requires that the data be reloaded, but the reloaded data still comes from the cached object. The result is that even if the database changes, the page shows that the results remain unchanged.
ObjectDataSource EnableCaching = "true" CacheDuration = "600"	The drop-down list is invalid within the cache validity period. During the Cache validity period, the DataBind () method of the GridView does not allow the data source to re-read the data (the data source does not trigger the Selecting event). In other words, the data source does not call the GetList (query) method, therefore, the list function is invalid.	The effect is the same as that of the assembled SQL statement. The list is invalid.
ObjectDataSource EnableCaching = "true" CacheDuration = "infinite" SqlDependency = "Northwind: Orders"	The list is invalid, which has the same effect as the above. The difference is that the cache expires when the database is changed (the first access after the database is invalid, the list is valid ).	The list is invalid, just like the assembled SQL method. The difference is that SqlDependency also fails, because when the database data changes, the data cache expires and the data source reads the data again, but the data still comes from the cache.
Catch Insert ("fullList", List <Order>)	Basically not feasible (Cache each returned result, the effect is basically equivalent to all returned results, and it is very troublesome)	The object in this article is to apply this method to cache.

Obviously, the method used in this article is: when the database data changes, the cache cannot expire immediately. There are two solutions: one is to use Cache. the Insert () overload method sets the automatic expiration time of the cache (the cache advantage is not obvious when the time is set to short, and the data changes after the time is set to long cannot be instantly reflected ); another is to use Cache when adding, deleting, and modifying databases. remove () Manually Remove the cache (which is easy to omit ).

This article does not describe how to use the Cache. The above shows you how to use the Cache, as long as you know that you can use the Cache. Insert (key, value) method to add the Cache. Finally, when we use Cache. Insert (key, value) to Insert a Cache, although no expiration time is set, when the server memory is insufficient, the Cache will still be removed.

Filter Business Objects

Filtering Based on Business Objects is actually filtering based on List <Order> (of course, your business object may not be a List <Order>). The idea seems simple, we first get all the lists through an overloaded GetList () method and apply the cache in this GetList () method. Traverse the business object, select a qualified project, add the qualified project to the new list, and return to the new list.

// Retrieve all lists
Public static List <Order> GetList (){
List <Order> list = HttpContext. Current. Cache ["fullList"] as List <Order>;

If (list = null ){
List = GetList ("Select OrderId, CustomerId, ShipCountry, OrderDate From Orders ");
// Add the cache and never expire (you can manually set the cache to expire when deleting or updating the cache)
HttpContext. Current. Cache. Insert ("fullList", list );
}

Return list;
}

// Filter the list based on the list of all projects, including year, month, and day.
Public static List <Order> GetList (List <Order> fullList, int year, int month, int day)
{
List <Order> list = null;
Bool canAdd; // indicates whether the current project meets the added conditions.

If (fullList! = Null)
{
List = new List <Order> ();

Foreach (Order item in fullList)
{
CanAdd = true;

If (year! = 0 & year! = Item. Date. Year)
CanAdd = false;

If (month! = 0 & month! = Item. Date. Month)
CanAdd = false;

If (day! = 0 & day! = Item. Date. Day)
CanAdd = false;

If (canAdd = true) // if all the conditions are met, add them to the list.
List. Add (item );
}
}

Return list;
}

The GetList () method without parameters above calls the GetList (query) method without caching, returns all the lists, and then adds them to the cache. If there is cache, the data in the cache is directly used. In the GetList (fullList, year, month, day) method, the List (all lists) passed in is filtered by year, month, and day.

Use List <T>. FindAll (Predicate <T> match) for filtering

Although the above method can complete the task, it is not good enough. Why?

1. We tightly coupled the filtering conditions (year, month, and day) to the GetList () method. If you want to add other columns in the future, such as country filtering, then our method signature needs to be changed (add country), and all the places that call the GetList () method need to be modified.
2. Code is not reused. filtering by year, month, or day is a common task. We should encapsulate this part and filter Other Business Objects in the future, this code can be reused.

Actually, these problems. net Framework has been designed for us, and provides a FindAll (Predicate <T> match) method on the List <T> for filtering, predicate <T> encapsulates filtering rules. Predicate <T> is a generic delegate, which means that the match parameter is a method that returns the bool type. In FindAll (), this method is called.

Public delegate bool Predicate <T> (T obj );

NOTE: I have seen such a sentence: What is the difference between Librariy and Framework? The answer is: we call the Librariy method, but the Framework calls our method (of course we will also call the Framework ). It can be seen that the Framework is highly scalable and flexible. In many places, we can integrate our code into the Framework.

Now let's take a look at how to define methods that meet the Predicate <T> delegate. If we write the method inside the OrderManager class, we can write it like this:

// Main logic for Data Filtering
Public bool MatchRule (Order item)
{
If (year! = 0 & year! = Item. Date. Year)
Return false;

If (month! = 0 & month! = Item. Date. Month)
Return false;

If (day! = 0 & day! = Item. Date. Day)
Return false;

Return true;
}

In fact, you can find that there is no place to pass the year, month, and day parameters, because Predicate <T> (T obj) requires only one parameter, which is an Order-type item. Therefore, we need to encapsulate this method in a simple way, so that we can pass the year, month, and day parameters. Before encapsulation, we should consider that filtering by year, month, or day is a common operation. We need to reuse the code.

First, we define an interface. This interface only requires that the Date attribute of the DateTime type be returned. For all classes that implement this interface, you should be able to use our filtering method (an object without a date obviously cannot be filtered by year, month, or day ).

Public interface IDate
{
DateTime Date {get ;}
}

In this case, our Order class should also be modified to implement this interface. We only need it to return the orderDate field:

Public class Order: IDate
{
//...
Public DateTime Date
{
Get {return orderDate ;}
}
}

Next, define the classes that can be used for filtering and create a DateFilter. cs file:

// A generic class used to filter the list by year, month, or day. The base class
Public class DateFilter <T> where T: IDate
{
Private int year;
Private int month;
Private int day;

Public DateFilter (int year, int month, int day)
{
This. year = year;
This. month = month;
This. day = day;
}
// Easy-to-use Constructor
Public DateFilter (DateTime date): this (date. Year, date. Month, date. Day ){}
Public DateFilter (int year, int month): this (year, month, 0 ){}
Public DateFilter (int year): this (year, 0, 0 ){}
Public DateFilter (): this (0, 0, 0 ){}

// Main logic for Data Filtering
Public virtual bool MatchRule (T item)
{
If (year! = 0 & year! = Item. Date. Year)
Return false;

If (month! = 0 & month! = Item. Date. Month)
Return false;

If (day! = 0 & day! = Item. Date. Day)
Return false;

Return true;
}
}

As you can see, the Predicate <T> delegate type method MatchRule is almost the same as the previous one. The only difference is that it is changed to a virtual method to overwrite it in the subclass, to filter more columns (attributes. It is worth noting that this generic class uses constraints. We require that the type parameter T must implement the IDate interface.

In fact, this class is usually used as a base class (or can be used directly, rather than an abstract class). Now let's look at how to expand it if we want to filter Country as well:

// You can add a filter to a country.
Public class OrderFilter: DateFilter <Order>
{
Private string country;

Public OrderFilter (int year, int month, int day, string country)
: Base (year, month, day) // call the base class Constructor
{
This. country = country;
}

Public override bool MatchRule (Order item)
{
// Obtain the comparison result of the base class about the date.
Bool result = base. MatchRule (item );

If (result = false) // if the date is not met, false is returned directly.
Return false;

// Continue the country comparison
If (String. IsNullOrEmpty (country) | string. Compare (item. Country, country, true) = 0)
{
Return true;
} Else
{
Return false;
}
}
}

Page implementation

Now we add a new method for the OrderManager class, and use the OrderFilter we created above to see what it looks like. It only calls the FindAll () method on fullList, the custom DateFilter is passed and the result is returned:

// Obtain the list object and use filter as the filter condition.
Public static List <Order> GetList (List <Order> fullList, DateFilter <Order> filter)
{
List <Order> list = null;
If (fullList! = Null)
{
List = fullList. FindAll (new Predicate <Order> (filter. MatchRule ));
}
Return list;
}

The layout on the ObjFilter. aspx page is almost the same as that on the assembled SQL. The attributes of the ObjectDataSource control have some changes:

<Asp: ObjectDataSource ID = "objdsOrderList" runat = "server" SelectMethod = "GetList"
TypeName = "OrderManager" OnSelecting = "objdsOrderList_Selecting">
<SelectParameters>
<Asp: Parameter Name = "fullList" Type = "Object"/>
<Asp: Parameter Name = "filter" Type = "Object"/>
</SelectParameters>
</Asp: ObjectDataSource>

Call the newly overloaded GetList () method. Then let's take a look at how to set the Selecting event of ObjectDataSource on the CodeBehind file:

// Attribute to obtain the objects used for filtering
Public DateFilter <Order> Filter {
Get {
DateFilter <Order> filter = new OrderFilter (Year, Month, Day, Country );
Return filter;
}
}

// Set parameters
Protected void objdsOrderList_Selecting (object sender, ObjectDataSourceSelectingEventArgs e)
{
E. InputParameters ["fullList"] = OrderManager. GetList (); // retrieve all lists
E. InputParameters ["filter"] = Filter;
}

Note that the code for obtaining the Year, Month, and Day attributes and the SelectedIndexChanged Event code of DropDownList are omitted to save space.

Event Probe

Okay, now all our work has been completed. Let's test how to reduce the database dependency in this way. You can open the SQL Server event Profiler (SQL Server Profiler under SQL Server ). Select "file" --> "new" --> "trace" --> to log on. As shown in the following figure:

Select the "event" tab, as shown in the following figure:

Remove stored procedures, security reviews, sessions from selected events on the right, keep T-SQL, and we only monitor it. Then we can see that, for every access to the database, we can see here:

Click the "eraser" icon above to clear the list. Then we open the SqlFilter. aspx file first, and we can see that each operation on the List, whether it is paging or filtering, will perform a query operation on the database. Then, click "eraser" to clear the list and open the ObjFilter. in the aspx file, you can see that after the first access to the database, subsequent operations, whether paging or filtering, no longer constitute a dependency on the database.

Summary

In this article, we mainly discuss how to filter business objects. I first proposed a mindset for many people: Hand over operations to the database. The typical process of this operation is listed later. In this article, it is called "filtering based on assembled SQL", and then the code demonstration is given.

In the second part, we discussed in detail how to filter business objects-caching objects on the server to support requests. Like the first half, we first learned the process, learned the cache policy, and then implemented the code. Finally, we used the event probe provided by SQL Server to track the status of database requests in two cases.

Thank you for reading this article. I hope this article will help you!