DBHelper
The DBHelper function helps you fetch the required documents from the database based on Query and FilterConditions, and fields that you require from the documents that fulfill the conditions. You can get the instance of IDBHelper by calling the method GetDBHelper() and it provides you with the following methods:
Methods
Task<List<T>> FindAsync<T>(Query query); Task<List<T>> FindAsync<T>(string entityName, Expression<Func<T, bool>> filterCondition, int? limit, params string[] fields); Task<List<T>> FindAsync<T>(string entityName, Expression<Func<T, bool>> filterCondition, params string[] fields); DBStatisticsInfo GetDBStatistics();
The first method helps you fetch the documents based on a Query, which comprises of the EntityName(Name of the collection), list of FilterConditions, Fields to fetch, and Limit of results to retrieve.
The GetDBStatistics() provides you the information on the total number of queries and the time taken for each query during callback execution.
GetDBStatistics() method should be used only when you are debugging or troubleshooting the issue. Once the debugging/troubleshooting is done, you must remove it from your callback code.
Definitions
public class Query
{
ExpressionOperator ExpressionOperator = ExpressionOperator.AND;
public string EntityName { get; set; }
public List<FilterCondition> Conditions { get; set; }
public Expression Criteria {get; set;}
public string[] Fields { get; set; }
public int? Limit { get; set; }
}
public class FilterCondition
{
public string FieldName { get; set; }
public ConditionOperator ComparisonOperator { get; set; }
public object Value { get; set; }
}
public class Expression
{
public ExpressionOperator ExpressionOperator { get; set; }
public List<FilterCondition> Conditions { get; }
public List<Expression> Filters { get; }
}
ComparisonOperator :
EqualTo,
GreaterThan,
GreaterThanOrEqualTo,
In,
LessThan,
LessThanOrEqualTo,
NotEqualTo,
NotIn,
Contains
- EntityName is the name of the collection you want to fetch the records from, or the records of the entity that you want to query upon.
- Conditions are a list of FilterConditions, which compare a field or a column’s value with a specified Value.
- Fields are the columns of the entity you want in your query results.
- Limit is the number of records you want to limit your result to.
The following are a few examples of different Query Models: The following are a few examples of executing queries using DBHelper. The examples are for your reference only and may not execute as is.
Simple Query Example
Query query = new Query()
{
EntityName = "Account",
Fields = new string[]{ "Id", "Name", "Type" },
Limit = 5,
Conditions = new List<FilterCondition>()
{
new FilterCondition()
{
ComparisonOperator = ConditionOperator.EqualTo,
FieldName = "Type", Value = "Ship-To"
}
}
};
public class AccountQueryModel
{
public string Id {get; set;}
public string Name {get; set;}
public string Type {get; set;}
}
DB Call Example
var dbHelper = GetDBHelper(); List<AccountQueryModel> account = await dbHelper.FindAsync<AccountQueryModel>(query);
Output
After you execute FindAsync, it retrieves data from the database and returns a list of AccountQueryModel.
Complex Query Example
Query query = new Query()
{
EntityName = "Account",
Fields = new string[]{ "Id", "Name", "Type","BillingCity__c", "BillingCountryCode__c" },
Limit = 2,
Criteria = new Expression(ExpressionOperator.AND)
};
FilterCondition nestedConditionOne = new FilterCondition()
{
ComparisonOperator = ConditionOperator.EqualTo,
FieldName = "Type",
Value = "Sold-To"
};
query.Criteria.AddCondition(nestedConditionOne);
Expression complexExpression = new Expression(ExpressionOperator.OR);
FilterCondition complexExpressionConditionOne = new FilterCondition()
{
ComparisonOperator = ConditionOperator.In,
FieldName = "BillingCity__c",
Value = new string[] { "BEDFORD PARK", "FREMONT" }
};
FilterCondition complexExpressionConditionTwo = new FilterCondition()
{
ComparisonOperator = ConditionOperator.EqualTo,
FieldName = "BillingCountryCode__c",
Value = "US"
};
complexExpression.AddCondition(complexExpressionConditionOne);
complexExpression.AddCondition(complexExpressionConditionTwo);
query.Criteria.AddFilter(complexExpression);
DB Call Example
var dbHelper = GetDBHelper(); List<AccountQueryModel> accountList = await dbHelper.FindAsync<AccountQueryModel>(query); return account;
Result
After you execute FindAsync, it retrieves data from the database and returns a list of AccountQueryModel.
For better performance, consider the following scenarios when executing database queries.
-
Apply more filters to narrow down the search results.
-
Fetch only the required columns or fields.
-
Do not execute queries inside for loop.
-
Create appropriate indexes based on the fields used in the query. You can create indexes on an entity using datasync UI.
