Class SQLExtensions

Inheritance

object

SQLExtensions

Inherited Members

object.GetType()

object.MemberwiseClone()

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

Namespace: LemonEdge.Utils.Database

Assembly: LemonEdge.Utils.dll

Syntax

public static class SQLExtensions

Methods

GetEquivalentDotNetType(SQLType, bool)

Returns the equivalent .Net Core type for the specified sql type

Declaration

public static Type GetEquivalentDotNetType(this SQLType type, bool nullable)

Parameters

Type	Name	Description
SQLType	type	The sql data type
bool	nullable	Whether or not the sql type is nullable

Returns

Type	Description
Type	The equivalent .Net Core type for the specified sql type

GetEquivalentSQLTypes(Type)

Returns the sql type equivalents that can be used to store the specified dot net type

Declaration

public static IEnumerable<SQLType> GetEquivalentSQLTypes(this Type type)

Parameters

Type	Name	Description
Type	type	The dot net type to return equivelequivalentant possible sql types for

Returns

Type	Description
IEnumerable<SQLType>	The sql type equivalents that can be used to store the specified dot net type

GetFunction(AggregateFunction)

Returns the sql equivalent of the specified AggregateFunction

Declaration

public static string GetFunction(this AggregateFunction aggFunc)

Parameters

Type	Name	Description
AggregateFunction	aggFunc	The aggregate function to return the equivalent sql for

Returns

Type	Description
string	The sql equivalent of the specified AggregateFunction

GetJoinTypeSQL(RelationshipJoinType, bool)

Returns the sql equivalent for the specified join

Declaration

public static string GetJoinTypeSQL(this RelationshipJoinType join, bool useCrossApply)

Parameters

Type	Name	Description
RelationshipJoinType	join	The join to use
bool	useCrossApply	Indicates if the join should be using a cross apply or not

Returns

Type	Description
string	The sql for joining using this type of `join`

GetNextSortDirection(Order)

Returns the next sort direction to use when cycling through sorting states

Declaration

public static Order GetNextSortDirection(this Order currentSortDirection)

Parameters

Type	Name	Description
Order	currentSortDirection	The current sort direction

Returns

Type	Description
Order	Returns the next sort direction according to the following cycle: Ascending->Descending->None

GetOrder(Order)

Returns the sql equivalent of the specified order

Declaration

public static string GetOrder(this Order order)

Parameters

Type	Name	Description
Order	order	The order either ascending or descending

Returns

Type	Description
string	The sql equivalent of the specified order

IsAlphaNumerical(SQLType)

Declaration

public static bool IsAlphaNumerical(this SQLType val)

Parameters

Type	Name	Description
SQLType	val

Returns

Type	Description
bool

IsDate(SQLType)

Declaration

public static bool IsDate(this SQLType val)

Parameters

Type	Name	Description
SQLType	val

Returns

Type	Description
bool

IsNumber(SQLType)

Declaration

public static bool IsNumber(this SQLType val)

Parameters

Type	Name	Description
SQLType	val

Returns

Type	Description
bool

SQLAddSquareBrackets(string)

Declaration

public static string SQLAddSquareBrackets(this string s)

Parameters

Type	Name	Description
string	s

Returns

Type	Description
string

SQLNameWithoutDBOPrefix(string)

Returns the specified name without the xxx. prefix

Declaration

public static string SQLNameWithoutDBOPrefix(this string s)

Parameters

Type	Name	Description
string	s	The string that optionally contains a xxx. prefix

Returns

Type	Description
string	The specified name without the xxx. prefix

SQLVariableToSafeName(string, bool, bool)

Taks a string and returns it as a sql variable safe name by removing all non alphanumerical characters (excluding the underscore _) with

Declaration

public static string SQLVariableToSafeName(this string s, bool addSquareBrackets = false, bool allowSpaces = false)

Parameters

Type	Name	Description
string	s	The string to return as a sql variable safe name
bool	addSquareBrackets	Indicates if the system should add square brackets around the resulting string
bool	allowSpaces	Indicates if spaces should be allowed in the variable name

Returns

Type	Description
string

SQLVariableToSafeName(string, bool, IEnumerable<char>)

Takes a string and returns it as a sql variable safe name by removing all non alphanumerical characters with

Declaration

public static string SQLVariableToSafeName(this string s, bool addSquareBrackets = false, IEnumerable<char> validNonAlphaNumericalChars = null)

Parameters

Type	Name	Description
string	s	The string to return as a sql variable safe name
bool	addSquareBrackets	Indicates if the system should add square brackets around the resulting string
IEnumerable<char>	validNonAlphaNumericalChars	Contains a list of non alphanumerical characters that are allowed to be included such as the underscore _

Returns

Type	Description
string

ToDbType(Type)

Converts a basic .Net type to DbType

Declaration

public static DbType ToDbType(this Type val)

Parameters

Type	Name	Description
Type	val	The type

Returns

Type	Description
DbType

Exceptions

Type	Condition
ArgumentOutOfRangeException	If the type cannot be found

ToSQL(DateTimeOffset, bool)

Converts a nullable Guid into a sql parameter.

Declaration

public static string ToSQL(this DateTimeOffset value, bool treatDefaultAsNull = true)

Parameters

Type	Name	Description
DateTimeOffset	value	The Guid.
bool	treatDefaultAsNull	Whether to treat the default as null.

Returns

Type	Description
string	The SQL value.

ToSQL(Guid, bool)

Converts a nullable Guid into a sql parameter.

Declaration

public static string ToSQL(this Guid value, bool treatDefaultAsNull = true)

Parameters

Type	Name	Description
Guid	value	The Guid.
bool	treatDefaultAsNull	Whether to treat the default as null.

Returns

Type	Description
string	The SQL value.

ToSQL(DateTimeOffset?, bool)

Converts a nullable Guid into a sql parameter.

Declaration

public static string ToSQL(this DateTimeOffset? value, bool treatDefaultAsNull = true)

Parameters

Type	Name	Description
DateTimeOffset?	value	The Guid.
bool	treatDefaultAsNull	Whether to treat the default as null.

Returns

Type	Description
string	The SQL value.

ToSQL(Guid?, bool)

Converts a nullable Guid into a sql parameter.

Declaration

public static string ToSQL(this Guid? value, bool treatDefaultAsNull = true)

Parameters

Type	Name	Description
Guid?	value	The Guid.
bool	treatDefaultAsNull	Whether to treat the default as null.

Returns

Type	Description
string	The SQL value.

ToSQLQuotedString(string)

Returns the supplied string enclosed in single quotes and with any embedded quotes doubled up, as required for including a string literal in a SQL statement

Declaration

public static string ToSQLQuotedString(this string sql)

Parameters

Type	Name	Description
string	sql	The sql to be enclosed in quotes, and have single quotes replaced with two single quotes to make sure strings can't be escaped.

Returns

Type	Description
string	The supplied string enclosed in single quotes and with any embedded quotes doubled up, as required for including a string literal in a SQL statement

ToSQLStatement(SQLOperator, string, string)

Returns the sql equivalent of the specified comparison

Declaration

public static string ToSQLStatement(this SQLOperator op, string leftSideSQL, string rightSideSQL)

Parameters

Type	Name	Description
SQLOperator	op	The operation to use to compare the left and right side
string	leftSideSQL	The left side of the comparison
string	rightSideSQL	The right side of the comparison

Returns

Type	Description
string	The sql equivalent of the specified comparison

Remarks

For example:

var left = 5;
var right = 7;
var op = SQLOperator.NotEquals;
var statement = op.ToSQLStatement(left.ToString(), right.ToString());

Would result in statement holding 5 <> 7

ToSQLValue(bool)

Returns 1 or 0 as a string depending on the boolean value

Declaration

public static string ToSQLValue(this bool b)

Parameters

Type	Name	Description
bool	b

Returns

Type	Description
string	1 if `b` is true, 0 otherwise

ToSQLValue(byte[])

Returns the specified byte array formatted for sql such as 0x....

Declaration

public static string ToSQLValue(this byte[] bytes)

Parameters

Type	Name	Description
byte[]	bytes	The byte array to format for sql

Returns

Type	Description
string	The specified byte array formatted for sql such as 0x....

ToSQLValue(bool?)

Returns 1 or 0 as a string depending on the boolean value

Declaration

public static string ToSQLValue(this bool? b)

Parameters

Type	Name	Description
bool?	b

Returns

Type	Description
string	1 if `b` is true, 0 otherwise

ToSQLValue(object, bool)

Converts the specified value into a equivalent string value for sql input

Declaration

public static string ToSQLValue(this object value, bool addQuotes)

Parameters

Type	Name	Description
object	value	The value to return formatted for sql
bool	addQuotes	Indicates if quotes should be added around the value. Not needed for types that require it such as a text or datetime, the function will automatically add quotes for those

Returns

Type	Description
string	The specified value into a equivalent string value for sql input

ToSqlDBType(SQLType)

Converts the LemonEdge SQLType to the equivalent SqlDbType

Declaration

public static SqlDbType ToSqlDBType(this SQLType val)

Parameters

Type	Name	Description
SQLType	val	The value to convert

Returns

Type	Description
SqlDbType	An equivalent SqlDbType