Customizing SQL query formats

Problem Overview

Working with EasyQuery, you can use different DB systems, such as MS SQL Server, My SQL, Oracle, etc. All these systems support SQL,
but can use slightly different syntax for table joins, different quoting symbol or different formats for some particular types of values (date/time, boolean and so on).

Sometimes you need to define some special requirements, for example, you may want to make all columns in generated query have clear and readable titles.

All such requirement can be controlled by special Formats property exposed in SqlQueryBuilder and EqServiceProviderDb classes.

Default Formats

There is SetDefaultFormats method in EasyQuery which gives an opportunity to set formats for the most popular DB systems into defaults. The primary parameter for this method is enumeration FormatType. These are possible values:

MsAccess

MsSqlServer

Oracle

MySQL

Example for MVC projects:

eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);

Formatting date/time values

You can set different Date/Time Formats, according to settings you are used to in your country, or even according to your practice.

If you need to format the date/time values in result SQL, then you can do it via Formats.DateFormat and Formats.DateTimeFormat properties of a DbQuery object. Additionally, you may specify if date/time values should be quoted via QuoteTime property.

Column aliases formatting

Sometimes it is necessary to make user-friendly column aliases (since they can be used as column headers when showing query result in some grid).
In other cases, there can be a requirement to use alphanumeric names for column aliases.
Formats has few sub-properties which allow you to control such things:

Never - EasyQuery will not add column aliases. This option is useful only when you generate a filter string for one table/data set.

IfNecessary - default value. Column aliases will be added only if it's necessary: either there is an ambiguity between columns from different tables or the aliases is necessary for other statement clauses.

Always - aliases will be generated for all columns

QuoteColumnAlias : bool
If true - all column aliases will be taken into quotes whether it's necessary or not.

AlphaAlias : bool
Specifies whether column aliases should be alphanumeric only. So no spaces or other symbols in alias names.

Here is an example of code for (MVC project) which turns on aliases for all columns and allows to use more "friendly" aliases (with spaces and other symbols):