Dapperextensions and reflection for Universal Search (ASP)

This article mainly introduces the use of Dapperextensions and reflection in ASP. NET to achieve a general search function, very good, with reference to solve the value, the need for friends can refer to the following

Objective

Search function is a very common function, of course, this search is not referring to full-text search, refers to the site of the background management system or the list of ERP system search function. A common practice is to search by adding several characters commonly used segments to the search bar. The code may generally be implemented like this

StringBuilder sqlstr = new StringBuilder (), if (!string. IsNullOrEmpty (Realname)) {  sqlstr.append ("and realname = @RealName");} if (age! =-1) {  sqlstr.append ("and age = @Age");} if (!string. IsNullOrEmpty (StartTime)) {  sqlstr.append ("and Createtime >= @StartTime");} if (!string. IsNullOrEmpty (EndTime)) {  sqlstr.append ("and Createtime <= @EndTime");} Mysqlparameter[] Paras = new mysqlparameter[]{      new Mysqlparameter ("@Age", age),      new Mysqlparameter ("@RealName ", Realname),      new Mysqlparameter (" @StartTime ", StartTime),      new Mysqlparameter (" @EndTime ", EndTime)    };

How do I handle this code if it encounters the following requirements?

1. Add one more query field

2. Realname needs to be changed into fuzzy query

3. Age needs to support range queries

Probably most of the program ape ideas, this is the new demand, then directly change the code, simple rude. Then in the foreground add an age range text box, the background and then add a if judgment, Realname = number directly changed to like, so easy to fix. But the demand is always changing, if a table has 50 fields, and you need to support 40 of these field queries. I think the first reaction of most people: lying trough, neuropathy! Isn't there a common way to solve this kind of search problem? I would like to say that of course, this article will use Dapperextensions and reflection to solve this problem, the most finally achieved results such as:

Dapperextensions Introduction

Dapperextensions is an extension based on Dapper, which implements CRUD operations mainly on the basis of dapper. It also provides a predicate system that allows for more complex advanced query functionality. You can also define the mapping of entity classes and tables through Classmapper.

Universal search Function implementation

1. First create an Account table and then add an account class

public class account  {public account    ()    {Age      =-1;    }    <summary>///    Account ID    ///</summary> [Mark ("Account ID")] public    int AccountId {get; set;}    <summary>///    name    ///</summary> [Mark ("name")] public    string Realname {get; set;}    //<summary>    //Age/    //</summary> [Mark ("Age")] public    int ages {get; set;}    <summary>//    Create Time//    /</summary> [Mark ("Creation Time")] public    DateTime Createtime {get ; Set }  }

2. In order to get the Chinese name of the field, we add a Markattribute class. Because of the powerful reflection function, we can dynamically get the attributes and Chinese names of each table entity class through reflection.

[AttributeUsage (Attributetargets.property, inherited = False, AllowMultiple = True)]  public class Markattribute:attribute  {public    Markattribute (string filedname, String Description = "")    { C4/>this. Filedname = Filedname;      This. Description = Description;    }    private string _filedname;    public string Filedname    {      get {return _filedname;}      set {_filedname = value;}    }    private string _description;    public string Description    {      get {return _description;}      set {_description = value;}    }  }

3. The general search idea is to abstract the search function into an object, essentially a collection of objects consisting of column names, operators, and values, so that multiple search conditions can be combined. We add a predicate class

public class Predicate {//<summary>////    </summary> public    string Columnitem {get; set;}    <summary>///Operator///    </summary> public    string Operatoritem {get; set;}    <summary>///value//    </summary> Public    object value {get; set;}  }

4. Then load the DropDownList of the foreground column name by reflecting the properties of the account class, and then add an operator to the DropDownList

var columnitems = new list<selectlistitem> ();      The properties of the class are obtained by reflection of      type T = Assembly.Load ("Searchdemo"). GetType ("SearchDemo.Models.Account");      foreach (PropertyInfo item in T.getproperties ())      {        string filedname = (item. GetCustomAttributes (typeof (Markattribute), false) [0] as Markattribute). Filedname;        Columnitems.add (New SelectListItem () {Text = filedname, Value = Item. Name});      Viewbag.columnitems = ColumnItems;      var operatoritems = new List<selectlistitem> ()      {        new SelectListItem () {Text = "equals", Value = "Eq"},        NE W SelectListItem () {text = "greater than", value = "Gt"},        new SelectListItem () {text = "greater than or equal to", Value = "Ge"},        new Selectl  Istitem () {text = "less than", value = "Lt"},        new SelectListItem () {text = "less than or equal to", value = "Le"},        new SelectListItem () {Text = "blur", Value = "Like"}      };      Viewbag.operatoritems = Operatoritems;

5. Foreground interface Implementation code

<! DOCTYPE html>
 6. Finally, the search method is implemented by dapperextensions predicates and reflections 
 

 [HttpPost] public jsonresult Search (list<predicate> predicates) {if (predicates = = null) {      Return Json (New {Error = "Please add search criteria"}); } using (var connection = sqlhelper.getconnection ()) {var PGA = new Predicategroup {Operator = Groupope Rator.        and, predicates = new list<ipredicate> ()}; foreach (var p in predicates) {var predicate = predicates.field<account> (GetExpression (P), (Operato          R) Enum.parse (typeof (Operator), P.operatoritem), p.value); Pga.        Predicates.add (predicate); } var list = connection.        Getlist<account> (PGA);      return Json (list); }} private static Expression<func<account, object>> getexpression (predicate p) {Parameterexpre      Ssion parameter = Expression.parameter (typeof (Account), "P"); Return Expression.lambda<func<account, Object>> (Expression.convert (expression.property (parameter, p. Columnitem), typeof (object)), parameter); }
 Finally, with a few simple lines of code, based on the functionality of Dapperextensions, we finally implement a common query function that can support multiple fields, multiple conditions, and multiple operators. This article is only to offer a way of thinking, there are more details not considered. For example, a combination of multiple conditions can add another logical character to join, multiple conditional combinations of nested queries, multi-table queries, and so on.