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();

CODE

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.

Note

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

CODE

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;} 
}

CODE

DB Call Example

var dbHelper = GetDBHelper();
List<AccountQueryModel> account = await dbHelper.FindAsync<AccountQueryModel>(query);

CODE

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);

CODE

DB Call Example

var dbHelper = GetDBHelper();
List<AccountQueryModel> accountList = await dbHelper.FindAsync<AccountQueryModel>(query);                               
return account;

CODE

Result

After you execute FindAsync, it retrieves data from the database and returns a list of AccountQueryModel.

Note:

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.