Query scripting

Helper functions for working with SQL query text.

Format

Value formatter class for SQL queries.

Energy.Query.Format format;
format = new Energy.Query.Format();
// or use global default which is obviously not recommended
format = Energy.Query.Format.Global; 

Text

Format object value as TEXT.

Null values will be represented as “NULL”.

public string Text(string value)

Format object value as TEXT.

When nullify parameter is set to true, null values will be represented as “NULL” instead of “’’”.

string Text(string value, bool nullify)

Format object value as TEXT with limited length.

string Text(string text, int limit)

Unicode

Format as Unicode TEXT.

Null values will be represented as “NULL”.

string Unicode(string value)

string Unicode(object value)

Format as Unicode TEXT.

When nullify parameter is set to true, null values will be represented as “NULL” instead of “’’”.

string Unicode(string value, bool nullify)

string Unicode(object value, bool nullify)

Number

Format as NUMBER.

Real numbers are represented with dot “.” as decimal point separator.

string Number(object value)

string Number(object value, bool nullify)

Integer

Format as INTEGER.

string Integer(int number)

Take only integer part of number.

string Integer(double number)

string Integer(decimal number)

Format as INTEGER.

Returns “1” for true and “0” for false.

string Integer(bool number)

Date

Format as DATE.

Represents date as quoted date string using “YYYY-MM-DD” format.

string Date(DateTime value)

string Date(object value)

Time

Format as TIME.

Uses 24h “hh:mm:ss” format.

Milliseconds will be used if present.

string Time(DateTime value)

string Time(object value)

Stamp

Format as DATETIME.

Uses by default “YYYY-MM-DD hh:mm:ss” format or “YYYY-MM-DDThh:mm:ss” depending on settings.

string Stamp(DateTime value)

string Stamp(object value)

Type

Definition

Represents SQL database type definition from a string like “NVARCHAR(20) NOT NULL”.

class Energy.Query.Type.Definition
{
    /// <summary>
    /// Represents type name.
    /// Example "VARCHAR".
    /// </summary>
    public string Type;

    /// <summary>
    /// Represents type parameter string. 
    /// Example: "(9,2)".
    /// </summary>
    public string Parameter;

    /// <summary>
    /// Represents default option.
    /// Example: "DEFAULT ''"
    /// </summary>
    public string Default;

    /// <summary>
    /// Represents nullable.
    /// Example "NOT NULL";
    /// </summary>
    public string Null;
}

Simplify

Simplify type.

IsNumeric

Check if SQL type is numeric.

bool Energy.Query.Type.IsNumeric(string type)

IsNullable

Check if SQL type is nullable.

bool Energy.Query.Type.IsNullable(string type)

Parameter

Support class for parametrized queries.

Bag

Represents list of parameters and their values. Use it to define parameters for parametrized query and to parse it.

Energy.Query.Parameter.Bag bag = new Energy.Query.Parameter.Bag();
bag.Set("param1", "value1");
bag.Set("param2", "value2");
string query = "INSERT INTO table1 ( column1 , column2 ) VALUES ( @param1 , @param2 )";
string result = bag.Parse(query);
// expect "INSERT INTO table1 ( column1 , column2 ) VALUES ( 'value1' , 'value2' )";

This class also offers several options which may be set for proper parameter parsing process.

Explicit

Parameters must be explicitly defined.

NullAsZero

Parse null values as numeric zero.

Unicode

Use N prefix for all non empty texts (Unicode).

UnknownAsEmpty

Parse unknown parameters as empty texts. Does not apply to parameters with names with leading @@ (double at sign).

UnknownAsNull

Parse unknown parameters as NULL. Does not apply to parameters with names with leading @@ (double at sign).

bag.Unicode = true;
bag.UnknownAsNull = true;

Template

Support for query templates.

ConvertToParameterizedQuery

Convert SQL query template which uses angle brackets to parameterized query which uses at sign to define parameters.

string template = @"
INSERT INTO [dbo].[Orders] 
    ([number]
    ,[order date]
    )
VALUES
    (<number , nvarchar(35),>
    ,<order date , date,>
    )
GO
";
result = Energy.Query.Parameter.Template.ConvertToParameterizedQuery(template);
// expect "INSERT INTO [dbo].[Orders] ([number],[order date]) VALUES (@number,@order_date)"

Quote

Quote string value using apostrophe (’) as quotation mark. Function will return “NULL” if value is null.Exchange

string Energy.Query.Text.Quote(string value)

Quote string value using specified quotation mark. Use apostrophe (’) for values and quotes (”) for database object names. Function will return “NULL” if value is null.

string Energy.Query.Text.Quote(string value, char quote)
second)

Quote string value using specified quotation mark. Use apostrophe (’) for values and quotes (”) for database object names. Specify text to be returned when value is null or pass null to use default “NULL”.

string Energy.Query.Text.Quote(string value, char quote, string nullText)

Quote string value using specified quotation mark. You might use square parenthesis ([]) to use T-SQL style quotation for database object names. Function will return “NULL” if value is null.

string Energy.Query.Text.Quote(string value, string quote)

Quote string value using specified quotation mark. You might use square parenthesis ([]) to use T-SQL style quotation for database object names. Specify text to be returned when value is null or pass null to use default “NULL”.

string Energy.Query.Text.Quote(string value, string quote, string nullText)

Strip

Strip quotation from a value.

Return null if nullText parameter is specified and value equals to it.

string Energy.Query.Text.Strip(string value, string quote, string nullText)

Includes support for apostrophes, quotation marks, square brackets or unquoted values.

string Energy.Query.Text.Strip(string value, string quote, string nullText)

Definition

Represents SQL database type definition from a string like “NVARCHAR(20) NOT NULL”.

Energy.Query.Definition def = Energy.Query.Type.ExtractTypeDefinition("VARCHAR(20) NULL DEFAULT ''");

Type

Represents type name. Example “VARCHAR”.

Simple

Represents simplified type name.

Parameter

Represents type parameter string. Example: “(9,2)”.

Size

Size option.

ExtractTypeDefinition

Extract SQL data type for declaration string like “VARCHAR(50)”

Energy.Query.Definition def = Energy.Query.Type.ExtractTypeDefinition("VARCHAR(50)");