Tutorial - Using the CriteriaQuery API
Follow these steps to accomplish common tasks using the CodeTrigger CriteriaQuery API. These code fragments assume you have already got a working basic project either build from scratch or using one of the wizards, and you wish to use some more advanced queries
in your application.
Please note that error handling, try catch blocks and message outs have been stripped from these samples to make them more concise.
Tutorial Updated: 2016-11-23
/*************************************************************************
define some constants to avoid clutter
**************************************************************************/
static string fmt = "yyyy-MM-dd";
static Func<object, string> dateformatter = (x => ((DateTime)x).ToString(fmt));
|
|
/****************************************************************************************************************************************
[Between] : Load a collection of BOOrder records from the database with OrderDate 'Between' date1 and date 2 (non inclusive)
****************************************************************************************************************************************/
public int LoadRecordsByDateRange1(bool checkCount, DateTime fromDate, DateTime toDate)
{
int maxResults = 200;
//........
var criteria = new Criteria<BOOrders>()
.Add(Expression.Between("OrderDate", fromDate, toDate, dateformatter));
IList<BOOrders> boOrderss = null;
int resultCount = criteria.Count();
//if too many results, inform user and exit
if (checkCount && (resultCount > maxResults))
{
//SetUserMessage("Your current filter settings will return " + resultCount.ToString() + " records.");
return -1;
}
//else get the full results and do something with it
boOrderss = criteria.List<BOOrders>();
//gridView1.DataSource = boOrderss;
//gridView1.DataBind();
return boOrderss.Count;
}
|
/****************************************************************************************************************************************
[Lte] & [Gte] : Load collection with OrderDate Greater than or equal to ('
>= ') date1 and Less than or equal to ('
<= ') date 2
****************************************************************************************************************************************/
public int LoadRecordsByDateRange2(bool checkCount, DateTime fromDate, DateTime toDate)
{
int maxResults = 200;
//........
var criteria = new Criteria<BOOrders>()
.Add(Expression.Gte("OrderDate", fromDate, dateformatter))
.Add(Expression.Lte("OrderDate", toDate, dateformatter));
IList<BOOrders> boOrderss = null;
int resultCount = criteria.Count();
//if too many results, inform user and exit
if (checkCount && (resultCount > maxResults))
{
//SetUserMessage("Your current filter settings will return " + resultCount.ToString() + " records.");
return -1;
}
//else get the full results and do something with it
boOrderss = criteria.List<BOOrders>();
//gridView1.DataSource = boOrderss;
//gridView1.DataBind();
return boOrderss.Count;
}
|
/****************************************************************************************************************************************
[OrderBy] & [Eq] & [Or]: Load an ordered collection using 'Eq' and 'Or' clauses
****************************************************************************************************************************************/
public int LoadRecordsByDateRange3(bool checkCount, DateTime fromDate, DateTime toDate)
{
int maxResults = 300;
//........
IList<object> listObjs = new List<object>();
listObjs.Add(new Criteria<BOOrders>().Add(Expression.Eq("ShipCountry", "Germany")));
listObjs.Add(new Criteria<BOOrders>().Add(Expression.Eq("ShipCountry", "USA")));
var criteria = new Criteria<BOOrders>(Expression.Or(listObjs.ToArray()))
.Add(Expression.Gte("OrderDate", fromDate, dateformatter))
.Add(Expression.Lte("OrderDate", toDate, dateformatter))
.Add(OrderBy.Asc("ShipName"));
IList<BOOrders> boOrderss = null;
int resultCount = criteria.Count();
//if too many results, inform user and exit
if (checkCount && (resultCount > maxResults))
{
//SetUserMessage("Your current filter settings will return " + resultCount.ToString() + " records.");
return -1;
}
//else get the full results and do something with it
boOrderss = criteria.List<BOOrders>();
//gridView1.DataSource = boOrderss;
//gridView1.DataBind();
return boOrderss.Count;
}
|
/****************************************************************************************************************************************
[In] & [NotNull]: Load a collection using 'In' and 'NotNull' clauses
****************************************************************************************************************************************/
public int LoadRecordsByDateRange4(bool checkCount, DateTime fromDate, DateTime toDate)
{
int maxResults = 300;
//........
var criteria = new Criteria<BOOrders>()
.Add(Expression.Gte("OrderDate", fromDate, dateformatter))
.Add(Expression.Lte("OrderDate", toDate, dateformatter))
.Add(Expression.In("ShipCountry", "'Germany', 'USA'"))
.Add(Expression.NotNull("ShipRegion"));
IList<BOOrders> boOrderss = null;
int resultCount = criteria.Count();
//if too many results, inform user and exit
if (checkCount && (resultCount > maxResults))
{
//SetUserMessage("Your current filter settings will return " + resultCount.ToString() + " records.");
return -1;
}
//else get the full results and do something with it
boOrderss = criteria.List<BOOrders>();
//gridView1.DataSource = boOrderss;
//gridView1.DataBind();
return boOrderss.Count;
}
|
/****************************************************************************************************************************************
[Like] & [Null] & [NotEq]: Load a collection using 'Like' and 'Null' and 'NotEq' clauses
****************************************************************************************************************************************/
public int LoadRecordsByDateRange5(bool checkCount, DateTime fromDate, DateTime toDate)
{
int maxResults = 300;
//........
var criteria = new Criteria<BOOrders>()
.Add(Expression.Gte("OrderDate", fromDate, dateformatter))
.Add(Expression.Lte("OrderDate", toDate, dateformatter))
.Add(Expression.Like("ShipCountry", "Ger"))
.Add(Expression.NotEq("ShipCity", "Stuttgart"))
.Add(Expression.Null("ShipRegion"));
IList<BOOrders> boOrderss = null;
int resultCount = criteria.Count();
//if too many results, inform user and exit
if (checkCount && (resultCount > maxResults))
{
//SetUserMessage("Your current filter settings will return " + resultCount.ToString() + " records.");
return -1;
}
//else get the full results and do something with it
boOrderss = criteria.List<BOOrders>();
//gridView1.DataSource = boOrderss;
//gridView1.DataBind();
return boOrderss.Count;
}
|
/****************************************************************************************************************************************
[Projection] & [InSubQuery]: Load a collection using 'Projection' and 'InSubQuery' clauses
****************************************************************************************************************************************/
public int LoadRecordsByDateRange6(bool checkCount, DateTime fromDate, DateTime toDate)
{
int maxResults = 300;
//........
//declare sub query, with projection of OrderID (so it only returns a list of relevant values of that field)
var orderIdRange = new Criteria<BOOrders>()
.Add(Expression.Between("OrderID", 10302, 10312))
.Add(new Projection("OrderID"));
//add the sub query to the main query (with the relevant joining field name)
var criteria = new Criteria<BOOrders>()
.Add(Expression.Gte("OrderDate", fromDate, dateformatter))
.Add(Expression.Lte("OrderDate", toDate, dateformatter))
.Add(Expression.InSubQuery("OrderID", orderIdRange));
IList<BOOrders> boOrderss = null;
int resultCount = criteria.Count();
//if too many results, inform user and exit
if (checkCount && (resultCount > maxResults))
{
//SetUserMessage("Your current filter settings will return " + resultCount.ToString() + " records.");
return -1;
}
//else get the full results and do something with it
boOrderss = criteria.List<BOOrders>();
//gridView1.DataSource = boOrderss;
//gridView1.DataBind();
return boOrderss.Count;
}
|
/****************************************************************************************************************************************
[Projection]: Load a collection of a subset of fields using 'Projection'
****************************************************************************************************************************************/
public int LoadRecordsByDateRange7(bool checkCount, DateTime fromDate, DateTime toDate)
{
//........
var criteria = new Criteria<BOOrders>()
.Add(Expression.Gte("OrderDate", fromDate, dateformatter))
.Add(Expression.Lte("OrderDate", toDate, dateformatter))
.Add(new Projection("OrderID"))
.Add(new Projection("ShippedDate"));
//get the results and do something with it
IList<int> orderIds = criteria.List<IList<object>>()[0].Select((x) => (int)x).ToList();
IList<DateTime?> shippedDates = criteria.List<IList<object>>()[1].Select((x) => (DateTime?)x).ToList();
return orderIds.Count;
}
|