Query Helpers

Query helpers are a list of classes which contain methods used for generating SQL queries. This topic lists all query helpers and how to use them.

TableColumn

The TableColumn class represents a column to be selected in the SQL query.

Example

List<TableColumn> columns = new List<TableColumn>();
columns.Add(new TableColumn {ColumnName = "ITEMID", TableAlias = "A"})

Property	Description
string TableAlias	Alias used for the table
bool SortDescending	Specifies the sort direction of the column
string ColumnName	Name of the column
bool ExcludeExternally	If true, the column will be excluded from the ExternalColumnGenerator
bool IsCustomExternalColumn	Specifies if the column is custom, i. e. the column does not exist in the table and will use the CustomText property instead of the ColumnName
string CustomText	Custom text used when the column is external (ex. SUM(COLUMN) )
bool IsNull	Specifies if the column should check for null value using the ISNULL sql method
string NullValue	If IsNull is true, specifies the replacement value of the object
string ColumnAlias	Alias used for the column
string AggregateFunction	The aggregate function to be added to the column (ex. "SUM")
bool AggregateExtenally	If true, the AggregateFunction will be added to the column in the ExternalColumnGenerator

Condition

The Condition class represent a condition to be checked in the WHERE clause of the SQL query.

Property	Description
string Operator	Specifies the operator between multiple conditions (AND, OR)
string ConditionValue	Specifies the condition to be verified

Example

List<Condition> conditions = new List<Condition>();
conditions.Add(new Condition {Operator = "AND", ConditionValue = "A.DELETED = 0 "})

Join

The Join class represents a SQL join between two tables.

Property	Description
string JoinType	Represents the type of join
string Table	Represents the name of the table to be joined
string Condition	Represents the condition on which the join is made
string TableAlias	Represents the alias of the table

Example

List<Join> joins = new List<Join>();
joins.Add(new Join 
{
	Condition = "RIT.ITEMID = I.ITEMID",
	JoinType = "LEFT OUTER",
	Table = "RETAILITEM",
	TableAlias = "RIT"
});

ColumnPopulation

The ColumnPopulation enum can be used to group multiple sets of columns and define which columns will be included in each group. This is used to reduce the number of unnecessary columns for certain operations.

For example, if we have a dictionary with two groups, one just for the ID of an object and the other for the whole object, we can get only the ID column in the query.

private static Dictionary<ColumnPopulation, List<TableColumn>> SelectionColumns
{
	get
	{
		selectionColumns.Add(ColumnPopulation.IDOnly, new List<TableColumn>
		{
		new TableColumn {ColumnName = "ITEMID", TableAlias = "A"}

		});
		selectionColumns.Add(ColumnPopulation.DataEntity, new List<TableColumn>
			{
			new TableColumn {ColumnName = "ITEMID", TableAlias = "A"},
			new TableColumn {ColumnName = "ITEMNAME", TableAlias = "A"},
			new TableColumn {ColumnName = "VARIANTNAME", TableAlias = "A"}
		});
			
		return selectionColumns;
	}
}

After we define our column groups, we can simply access them with SelectionColumn[ColumnPopulation.IDOnly] and use them for generating the query.

How to use QueryHelpers

After we have defined the columns, joins and/or conditions, we can use the QueryPartGenerator to generate the query and execute it.

cmd.CommandText = string.Format(QueryTemplates.BaseQuery("RETAILITEM", "A"),
				QueryPartGenerator.InternalColumnGenerator(columns),
				QueryPartGenerator.JoinGenerator(joins),
				QueryPartGenerator.ConditionGenerator(conditions),
				string.Empty);

The QueryTemplates class contains a set of SQL select templates which can be used to start building the query, such as BaseQuery, UnionQuery, PagingQuery, InternalQuery. The templates contain placeholders "{0}" which will be replaced by the string.Format method with the generated query parts.

The QueryPartGenerator class contains methods for generating internal or external columns, conditions, joins and group by queries.