Skip to main content

Sql

SQL Server 2016 specific features

Assembly: ServiceStack.OrmLite.dll​
View Source​
Declaration
public static class Sql : object

Fields​

VARCHAR​

View Source​
Declaration
public static string VARCHAR

EOT​

View Source​
Declaration
public const string EOT = null

Methods​

Flatten(IEnumerable)​

View Source​
Declaration
public static List<object> Flatten(IEnumerable list)
Returns​

List<System.Object>

Parameters​
TypeName
IEnumerablelist

In<T, TItem>(T, TItem[])​

View Source​
Declaration
public static bool In<T, TItem>(T value, params TItem[] list)
Returns​

System.Boolean

Parameters​
TypeName
<T>value
<TItem>[]list
Type Parameters​
  • T
  • TItem

In<T, TItem>(T, SqlExpression<TItem>)​

View Source​
Declaration
public static bool In<T, TItem>(T value, SqlExpression<TItem> query)
Returns​

System.Boolean

Parameters​
TypeName
<T>value
ServiceStack.OrmLite.SqlExpression<T>query
Type Parameters​
  • T
  • TItem

Asc<T>(T)​

View Source​
Declaration
public static string Asc<T>(T value)
Returns​

System.String

Parameters​
TypeName
<T>value
Type Parameters​
  • T

Desc<T>(T)​

View Source​
Declaration
public static string Desc<T>(T value)
Returns​

System.String

Parameters​
TypeName
<T>value
Type Parameters​
  • T

As<T>(T, Object)​

View Source​
Declaration
public static string As<T>(T value, object asValue)
Returns​

System.String

Parameters​
TypeName
<T>value
System.ObjectasValue
Type Parameters​
  • T

Sum<T>(T)​

View Source​
Declaration
public static T Sum<T>(T value)
Returns​

<T>

Parameters​
TypeName
<T>value
Type Parameters​
  • T

Sum(String)​

View Source​
Declaration
public static string Sum(string value)
Returns​

System.String

Parameters​
TypeName
System.Stringvalue

Count<T>(T)​

View Source​
Declaration
public static T Count<T>(T value)
Returns​

<T>

Parameters​
TypeName
<T>value
Type Parameters​
  • T

CountDistinct<T>(T)​

View Source​
Declaration
public static T CountDistinct<T>(T value)
Returns​

<T>

Parameters​
TypeName
<T>value
Type Parameters​
  • T

Count(String)​

View Source​
Declaration
public static string Count(string value)
Returns​

System.String

Parameters​
TypeName
System.Stringvalue

Min<T>(T)​

View Source​
Declaration
public static T Min<T>(T value)
Returns​

<T>

Parameters​
TypeName
<T>value
Type Parameters​
  • T

Min(String)​

View Source​
Declaration
public static string Min(string value)
Returns​

System.String

Parameters​
TypeName
System.Stringvalue

Max<T>(T)​

View Source​
Declaration
public static T Max<T>(T value)
Returns​

<T>

Parameters​
TypeName
<T>value
Type Parameters​
  • T

Max(String)​

View Source​
Declaration
public static string Max(string value)
Returns​

System.String

Parameters​
TypeName
System.Stringvalue

Avg<T>(T)​

View Source​
Declaration
public static T Avg<T>(T value)
Returns​

<T>

Parameters​
TypeName
<T>value
Type Parameters​
  • T

Avg(String)​

View Source​
Declaration
public static string Avg(string value)
Returns​

System.String

Parameters​
TypeName
System.Stringvalue

AllFields<T>(T)​

View Source​
Declaration
public static T AllFields<T>(T item)
Returns​

<T>

Parameters​
TypeName
<T>item
Type Parameters​
  • T

JoinAlias(String, String)​

View Source​
Declaration
public static string JoinAlias(string property, string tableAlias)
Returns​

System.String

Parameters​
TypeName
System.Stringproperty
System.StringtableAlias

TableAlias(String, String)​

View Source​
Declaration
public static string TableAlias(string property, string tableAlias)
Returns​

System.String

Parameters​
TypeName
System.Stringproperty
System.StringtableAlias

JoinAlias<T>(T, String)​

View Source​
Declaration
public static T JoinAlias<T>(T property, string tableAlias)
Returns​

<T>

Parameters​
TypeName
<T>property
System.StringtableAlias
Type Parameters​
  • T

TableAlias<T>(T, String)​

View Source​
Declaration
public static T TableAlias<T>(T property, string tableAlias)
Returns​

<T>

Parameters​
TypeName
<T>property
System.StringtableAlias
Type Parameters​
  • T

Custom(String)​

View Source​
Declaration
public static string Custom(string customSql)
Returns​

System.String

Parameters​
TypeName
System.StringcustomSql

Custom<T>(String)​

View Source​
Declaration
public static T Custom<T>(string customSql)
Returns​

<T>

Parameters​
TypeName
System.StringcustomSql
Type Parameters​
  • T

Cast(Object, String)​

View Source​
Declaration
public static string Cast(object value, string castAs)
Returns​

System.String

Parameters​
TypeName
System.Objectvalue
System.StringcastAs

IsJson(String)​

Tests whether a string contains valid JSON. ISJSON does not check the uniqueness of keys at the same level.

View Source​
Declaration
public static bool? IsJson(string expression)
Returns​

System.Nullable<System.Boolean>: Returns True if the string contains valid JSON; otherwise, returns False. Returns null if expression is null. ISJSON does not check the uniqueness of keys at the same level.

Parameters​
TypeNameDescription
System.StringexpressionThe string to test.

ISJSON does not check the uniqueness of keys at the same level. |

JsonValue<T>(String, String)​

Extracts a scalar value from a JSON string.

View Source​
Declaration
public static T JsonValue<T>(string expression, string path)
Returns​

<T>: Returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression. If the value is greater than 4000 characters: <br /><br /> <ul> <li>In lax mode, <strong>JSON_VALUE</strong> returns null.</li> <li>In strict mode, <strong>JSON_VALUE</strong> returns an error.</li> </ul> <br /> If you have to return scalar values greater than 4000 characters, use <strong>OPENJSON</strong> instead of <strong>JSON_VALUE</strong>. For more info, see OPENJSON (Transact-SQL).

Parameters​
TypeNameDescription
System.Stringexpression

An expression. Typically the name of a variable or a column that contains JSON text.<br /><br /> If <strong>JSON_VALUE</strong> finds JSON that is not valid in expression before it finds the value identified by <em>path</em>, the function returns an error. If <strong>JSON_VALUE</strong> doesn't find the value identified by <em>path</em>, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in <em>expression</em>.

| | System.String | path | A JSON path that specifies the property to extract. For more info, see JSON Path Expressions (SQL Server).<br /><br /> In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of <em>path</em>.<br /><br /> If the format of path isn't valid, <strong>JSON_VALUE</strong> returns an error.<br /><br />

|

Type Parameters​
  • T

JsonValue(String, String)​

Extracts a scalar value from a JSON string.

View Source​
Declaration
public static string JsonValue(string expression, string path)
Returns​

System.String: Returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression. If the value is greater than 4000 characters: <br /><br /> <ul> <li>In lax mode, <strong>JSON_VALUE</strong> returns null.</li> <li>In strict mode, <strong>JSON_VALUE</strong> returns an error.</li> </ul> <br /> If you have to return scalar values greater than 4000 characters, use <strong>OPENJSON</strong> instead of <strong>JSON_VALUE</strong>. For more info, see OPENJSON (Transact-SQL).

Parameters​
TypeNameDescription
System.Stringexpression

An expression. Typically the name of a variable or a column that contains JSON text.<br /><br /> If <strong>JSON_VALUE</strong> finds JSON that is not valid in expression before it finds the value identified by <em>path</em>, the function returns an error. If <strong>JSON_VALUE</strong> doesn't find the value identified by <em>path</em>, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in <em>expression</em>.

| | System.String | path | A JSON path that specifies the property to extract. For more info, see JSON Path Expressions (SQL Server).<br /><br /> In SQL Server 2017 and in Azure SQL Database, you can provide a variable as the value of <em>path</em>.<br /><br /> If the format of path isn't valid, <strong>JSON_VALUE</strong> returns an error.<br /><br />

|

JsonQuery(String)​

Extracts an object or an array from a JSON string.<br /><br /> To extract a scalar value from a JSON string instead of an object or an array, see JSON_VALUE(Transact-SQL). For info about the differences between <strong>JSON_VALUE</strong> and <strong>JSON_QUERY</strong>, see Compare JSON_VALUE and JSON_QUERY.

View Source​
Declaration
public static string JsonQuery(string expression)
Returns​

System.String: Returns a JSON fragment of type T. The collation of the returned value is the same as the collation of the input expression.<br /><br /> If the value is not an object or an array: <ul> <li>In lax mode, <strong>JSON_QUERY</strong> returns null.</li> <li>In strict mode, <strong>JSON_QUERY</strong> returns an error.</li> </ul>

Parameters​
TypeNameDescription
System.Stringexpression

An expression. Typically the name of a variable or a column that contains JSON text.<br /><br /> If <strong>JSON_QUERY</strong> finds JSON that is not valid in <em>expression</em> before it finds the value identified by <em>path</em>, the function returns an error. If <strong>JSON_QUERY</strong> doesn't find the value identified by <em>path</em>, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in <em>expression</em>.

|

JsonQuery<T>(String)​

View Source​
Declaration
public static T JsonQuery<T>(string expression)
Returns​

<T>

Parameters​
TypeName
System.Stringexpression
Type Parameters​
  • T

JsonQuery(String, String)​

View Source​
Declaration
public static string JsonQuery(string expression, string path)
Returns​

System.String

Parameters​
TypeName
System.Stringexpression
System.Stringpath

JsonQuery<T>(String, String)​

View Source​
Declaration
public static T JsonQuery<T>(string expression, string path)
Returns​

<T>

Parameters​
TypeName
System.Stringexpression
System.Stringpath
Type Parameters​
  • T