WisDom. net ----- dynamic query design

1. Why do we need to dynamically query the WisDom. net Development Framework Design (Framework Design. We use the Ado.net object framework to operate databases. I believe that we all know that the linq to entities feature does not implement dynamic queries. However, in reality, there are many combinations of system list pages, input filtering conditions, and sorting conditions. So what should we do? It's in WisDom. does DataInfo write an IF statement for processing in each case, so that the display will result in code bloated and cause great maintenance difficulties. Entity SQL is used here to solve this problem. 2. what is Entity SQL is similar to SQL's storage-independent query language. Using Entity SQL, Entity Data can be queried as an object or as a table. Entity SQL should be considered in the following cases: when the query must be dynamically constructed at runtime. When you want to define a query as part of the model definition. 3. wisDom. net. The following example shows how to use Entity SQL to retrieve object objects by page. queryConfig query configuration class. 2. TableName table names, such as filter conditions and sorting conditions, are defined. Familiar with SQL statements 3. copy the code of the object returned by the Entitylist query // <summary> // obtain the object framework by PAGE /// </summary> /// <typeparam name = "T"> </ typeparam> /// <param name = "TableName"> table name </param> /// <param name = "queryConfig"> </param> /// <param name = "Entitylist"> </param> // <param name = "allPage"> </param> // <param name = "CurrPage"> </param>/ // <returns> </returns> public WisDomError GetEntityByQueryConfig <T> (smartoaEntities oaEntities, String TableName, QueryConfig queryConfig, out List <T> Entitylist, out int allPage, out int CurrPage) {allPage = 1; CurrPage = queryConfig. currPage; Entitylist = new List <T> (); // construct Esql StringBuilder filter = new StringBuilder (); foreach (Fitler f in queryConfig. fitlerlist) {filter. append (f. toString () + "");} string SortBys = string. empty; if (queryConfig. sortType! = Null) {SortBys = queryConfig. sortType. toString ();} string esql = String. format ("SELECT * FROM {2} where 1 = 1 {0} {1}", filter. toString (), SortBys, TableName); var query = oaEntities. executeStoreQuery <T> (esql, null); List <T> EntityInfoList = query. toList (); // calculate the total number of pages int allCount = EntityInfoList. count; if (allCount % queryConfig. pageSize = 0) {allPage = allCount/queryConfig. pageSize;} else {AllPage = (allCount/queryConfig. pageSize + 1);} Entitylist = (from c in EntityInfoList select c ). skip (CurrPage-1) * queryConfig. pageSize ). take (queryConfig. pageSize ). toList (); CurrPage = queryConfig. currPage; return Wiserr;} copy code 4. queryConfig design description only one column of sorting and copying code // <summary> /// query configuration /// </summary> public class QueryConfig {// <summary>/ // current page // </summary> public int CurrPage; /// <su Mmary> /// number of entries displayed on each page /// </summary> public int PageSize; public SortBy sortType; /// <summary> /// filter condition /// </summary> public List <Fitler> fitlerlist; /// <summary> // ToString () /// </summary> /// <returns> </returns> public override string ToString () {StringBuilder sb = new StringBuilder (); foreach (Fitler f in fitlerlist) {sb. append (f. toString () + "");} return sb. toString () ;}} public class Fitler {/// <summary> /// field to be filtered // </summary> public string FitlerField {get; set ;} /// <summary> /// filter type /// </summary> public FitlerType fitlertype {get; set ;} /// <summary> /// filter value /// </summary> public string FilerValue {get; set;} public override string ToString () {string returnback = string. empty; switch (fitlertype) {case FitlerType. equal: returnback = string. format ("and {0} = '{ 1} '", FitlerField, FilerValue); break; case FitlerType. greater: returnback = string. format ("and {0}> '{1}'", FitlerField, FilerValue); break; case FitlerType. greaterEqual: returnback = string. format ("and {0 }>=' {1} '", FitlerField, FilerValue); break; case FitlerType. less: returnback = string. format ("and {0} <'{1}'", FitlerField, FilerValue); break; case FitlerType. lessEqual: returnback = string. fo Rmat ("and {0} <= '{1}'", FitlerField, FilerValue); break; case FitlerType. like: returnback = string. format ("and {0} like '% {1} %'", FitlerField, FilerValue); break; default: break;} return returnback ;}} public class SortBy {public string SortName {get; set;} public DBSort sorttype {get; set;} public override string ToString () {string RetStr = string. empty; if (! String. isNullOrEmpty (SortName) {switch (sorttype) {case DBSort. DESC: RetStr = string. format ("Order by {0} {1}", SortName, "desc"); break; case DBSort. ASC: RetStr = string. format ("Order by {0} {1}", SortName, "asc"); break ;}} return RetStr ;}}