Class SQLWrapperInterpretor

A helper class responsible for loading a SQL Wrapper, its parameters/columns and providing all the functionality required to interpret the results

This also implements the IQueryableItem interface as this allows our query tools to requery existing sql wrappers

Inheritance

System.Object

SQLWrapperInterpretor

Implements

IQueryableItem

Inherited Members

System.Object.Equals(System.Object)

System.Object.Equals(System.Object, System.Object)

System.Object.GetHashCode()

System.Object.GetType()

System.Object.MemberwiseClone()

System.Object.ReferenceEquals(System.Object, System.Object)

System.Object.ToString()

Namespace: LemonEdge.API.Entities.Helpers

Assembly: API.dll

Syntax

public class SQLWrapperInterpretor : IQueryableItem

Constructors

SQLWrapperInterpretor(ISQLWrapper)

Creates a new sqlwrapper interpretor using the specified sql wrapper

Declaration

public SQLWrapperInterpretor(ISQLWrapper wrapper)

Parameters

Type	Name	Description
ISQLWrapper	wrapper

SQLWrapperInterpretor(Guid)

Creates a new sqlwrapper interpretor using the globally unique id of the specified sql wrapper

Declaration

public SQLWrapperInterpretor(Guid id)

Parameters

Type	Name	Description
System.Guid	id	The globally unique id of the sql wrapper to use

Properties

Columns

A list of all the columns in the schema of this sql wrapper

Declaration

public IEnumerable<ISQLTypeWrapper> Columns { get; }

Property Value

Type	Description
System.Collections.Generic.IEnumerable<ISQLTypeWrapper>

GetColumnResultCount

Returns the total count of columns in the sql wrapper

Declaration

public int GetColumnResultCount { get; }

Property Value

Type	Description
System.Int32

GetParametersCount

Returns the total count of parameters in the sql wrapper

Declaration

public int GetParametersCount { get; }

Property Value

Type	Description
System.Int32

ID

The name of this sql wrapper

Declaration

public string ID { get; }

Property Value

Type	Description
System.String

IsSP

Returns true if this sql wrapper is configured as a stored procedure

Declaration

public bool IsSP { get; }

Property Value

Type	Description
System.Boolean

Loaded

Returns true if the sql wrapper interpretor has been loaded as has the full schema of the sql wrapper

Declaration

public bool Loaded { get; }

Property Value

Type	Description
System.Boolean

Name

The name of this sql wrapper

Declaration

public string Name { get; }

Property Value

Type	Description
System.String

OpenableColumns

Returns a collection of columns that contain values that hold global ids that uniquely identify entities in the system

These entities in turn may be capable of being opened within the UI

Declaration

public IEnumerable<ISQLTypeWrapper> OpenableColumns { get; }

Property Value

Type	Description
System.Collections.Generic.IEnumerable<ISQLTypeWrapper>

Parameters

A list of all the parameters in the schema of this sql wrapper

Declaration

public IEnumerable<ISQLTypeWrapper> Parameters { get; }

Property Value

Type	Description
System.Collections.Generic.IEnumerable<ISQLTypeWrapper>

SubID

A sql wrapper does not have a sub id as a queryable item

Declaration

public string SubID { get; }

Property Value

Type	Description
System.String

Wrapper

The sql wrapper this interpretor is working with

Declaration

public ISQLWrapper Wrapper { get; }

Property Value

Type	Description
ISQLWrapper

Methods

ColumnMappableToSQLWrapperResult(ISQLTypeWrapper)

Returns true if the column can have its result stored against a ISQLWrapperResult

The ISQLWrapperResult has a limit of certain number of text/decimal/etc columns. This prevents extra column being displayed in a grid, though the results can still be written to csv/etc.

If you require all the properties you can create your own complex type to hold all the results and use that instead of the default ISQLWrapperResult

Declaration

public bool ColumnMappableToSQLWrapperResult(ISQLTypeWrapper col)

Parameters

Type	Name	Description
ISQLTypeWrapper	col	The column to check if its results can be held against a ISQLWrapperResult

Returns

Type	Description
System.Boolean	True if the column can have its result stored against a ISQLWrapperResult

ConvertValueForResult(ISQLTypeWrapper, Object)

Converts the specified value into the format expected for the specified parameter from its GetResultType(ISQLTypeWrapper)

Declaration

public object ConvertValueForResult(ISQLTypeWrapper param, object value)

Parameters

Type	Name	Description
ISQLTypeWrapper	param	The paremeter to convert the specified value into the correct result type
System.Object	value	The value to convert into a compatible type for storing against a ISQLWrapperResult

Returns

Type	Description
System.Object	The specified value into the format expected for the specified parameter from its GetResultType(ISQLTypeWrapper)

EnsureHasStandardParams(ISQLWrapper, IEnumerable<ISQLTypeWrapper>, IEntityUpdater, UserInfo, IReadOnlyCache, Action<ISQLTypeWrapper>)

Ensures that for a given sql wrapper that it contains all the standard parameters that should be included with all sql wrappers. These are:

• Account: The account the function should run under.
• Canvas: The canvas the function should run within
• Team: The team the function should run under the permissions of.
• As Of: The as of date to run the entire query as of

Declaration

public static Task EnsureHasStandardParams(ISQLWrapper wrapper, IEnumerable<ISQLTypeWrapper> wrapperTypes, IEntityUpdater updater, UserInfo user, IReadOnlyCache cache, Action<ISQLTypeWrapper> addNewWrapper)

Parameters

Type	Name	Description
ISQLWrapper	wrapper	The sql wrapper to ensure it has all the standard parameters of
System.Collections.Generic.IEnumerable<ISQLTypeWrapper>	wrapperTypes	The list of existing parameters for this sql wrapper
IEntityUpdater	updater	The current context that we can add changes to for creating/updating the standard parameters of
UserInfo	user	The current logged on user
IReadOnlyCache	cache	A local cache
System.Action<ISQLTypeWrapper>	addNewWrapper	A function that returns a new ISQLTypeWrapper for creating the standard parameters when required

Returns

Type	Description
System.Threading.Tasks.Task	A task indicating the completion of this operation

GetColumn(String)

Returns the column definition given the specified binding against a property on ISQLWrapperResult

Declaration

public ISQLTypeWrapper GetColumn(string binding)

Parameters

Type	Name	Description
System.String	binding	A name of a property on ISQLWrapperResult that we want the column defintion of

Returns

Type	Description
ISQLTypeWrapper	The column definition given the specified binding against a property on ISQLWrapperResult

GetColumnBinding(ISQLTypeWrapper)

Returns the binding the system will use to bind the value of this column to a property on the ISQLWrapperResult

Declaration

public string GetColumnBinding(ISQLTypeWrapper typeWrapper)

Parameters

Type	Name	Description
ISQLTypeWrapper	typeWrapper	The column which we want the binding for the values it holds being stored against a ISQLWrapperResult

Returns

Type	Description
System.String	The binding the system will use to bind the value of this column to a property on the ISQLWrapperResult

GetColumnName(SQLWrapperInterpretor.ResultType)

Returns the type of property used for storing the specified type on the ISQLWrapperResult

Declaration

public static string GetColumnName(SQLWrapperInterpretor.ResultType type)

Parameters

Type	Name	Description
SQLWrapperInterpretor.ResultType	type	The type of data

Returns

Type	Description
System.String	The type of property used for storing the specified type on the ISQLWrapperResult

GetFields(IReadOnlyCache)

Returns all columns that can be queried from this queryable item

Declaration

public Task<IEnumerable<IQueryableFieldInfo>> GetFields(IReadOnlyCache cache)

Parameters

Type	Name	Description
IReadOnlyCache	cache	A local context cache

Returns

Type	Description
System.Threading.Tasks.Task<System.Collections.Generic.IEnumerable<IQueryableFieldInfo>>	All columns that can be queried from this queryable item

GetFrameworkResultType(SQLWrapperInterpretor.ResultType)

Returns the .net type used to store the specified resulttype which is held against the default ISQLWrapperResult type

Declaration

public static Type GetFrameworkResultType(SQLWrapperInterpretor.ResultType type)

Parameters

Type	Name	Description
SQLWrapperInterpretor.ResultType	type	The result type to return an equivelant .net type used to store it with

Returns

Type	Description
System.Type	The .net type used to store the specified resulttype which is held against the default ISQLWrapperResult type

GetParams(IReadOnlyCache)

Returns all parameters that need to be used in order to call this sql wrapper

Declaration

public Task<IEnumerable<IQueryableParamInfo>> GetParams(IReadOnlyCache cache)

Parameters

Type	Name	Description
IReadOnlyCache	cache	A local context cache

Returns

Type	Description
System.Threading.Tasks.Task<System.Collections.Generic.IEnumerable<IQueryableParamInfo>>	All parameters that need to be used in order to call this sql wrapper

GetRelatedItemType(ISQLTypeWrapper)

Returns the entitydescriptor describing the type of globally unique id that the specified column holds

Declaration

public EntityDescriptor GetRelatedItemType(ISQLTypeWrapper col)

Parameters

Type	Name	Description
ISQLTypeWrapper	col	The column which holds a unique id

Returns

Type	Description
EntityDescriptor	The entitydescriptor describing the type of globally unique id that the specified column holds

GetResultType(ISQLTypeWrapper)

Returns the type that the result value for the specified value will be stored against on the default ISQLWrapperResult complex type

Declaration

public static SQLWrapperInterpretor.ResultType GetResultType(ISQLTypeWrapper col)

Parameters

Type	Name	Description
ISQLTypeWrapper	col	The column to retrieve the type used for storing its result

Returns

Type	Description
SQLWrapperInterpretor.ResultType	The type that the result value for the specified value will be stored against on the default ISQLWrapperResult complex type

GetResultValue(ISQLWrapperResult, ISQLTypeWrapper)

Given a result, and a column from the sqlwrapper, this will return the actual value of the result

Declaration

public object GetResultValue(ISQLWrapperResult result, ISQLTypeWrapper col)

Parameters

Type	Name	Description
ISQLWrapperResult	result	A result holding values retrieved from executing this SQLWrapper
ISQLTypeWrapper	col	The column to return the value for

Returns

Type	Description
System.Object	Returns the value held against a result for the specified column

GetResultValue(ISQLWrapperResult, String)

Given a result, and the name of a column (from the sqlwrapper schema of the column name stored on Name), this will return the actual value of the result

Declaration

public object GetResultValue(ISQLWrapperResult result, string columnName)

Parameters

Type	Name	Description
ISQLWrapperResult	result	A result holding values retrieved from executing this SQLWrapper
System.String	columnName	The Name of a column to return the value for

Returns

Type	Description
System.Object	Returns the value held against a result for the specified column

GetResultValue<T>(ISQLWrapperResult, ISQLTypeWrapper)

Given a result, and a column from the sqlwrapper, this will return the actual value of the result

Declaration

public T GetResultValue<T>(ISQLWrapperResult result, ISQLTypeWrapper col)

Parameters

Type	Name	Description
ISQLWrapperResult	result	A result holding values retrieved from executing this SQLWrapper
ISQLTypeWrapper	col	The column to return the value for

Returns

Type	Description
T	Returns the value held against a result for the specified column

Type Parameters

Name	Description
T	The type of the value that should be returned for this property

GetResultValue<T>(ISQLWrapperResult, String)

Given a result, and the name of a column (from the sqlwrapper schema of the column name stored on Name), this will return the actual value of the result

Declaration

public T GetResultValue<T>(ISQLWrapperResult result, string columnName)

Parameters

Type	Name	Description
ISQLWrapperResult	result	A result holding values retrieved from executing this SQLWrapper
System.String	columnName	The Name of a column to return the value for

Returns

Type	Description
T	Returns the value held against a result for the specified column

Type Parameters

Name	Description
T	The type of the value that should be returned for this property

GetSQLToSelectFrom(Int64, IEnumerable<(IQueryableParamInfo Param, String SQLValue)>)

Returns the sql function/table to call to execute this sql wrapper

Declaration

public string GetSQLToSelectFrom(long accountID, IEnumerable<(IQueryableParamInfo Param, string SQLValue)> sqlParams)

Parameters

Type	Name	Description
System.Int64	accountID	The account id this sql is being generated for
System.Collections.Generic.IEnumerable<System.ValueTuple<IQueryableParamInfo, System.String>>	sqlParams	The valuesof any sql parameters used to call this sql wrapper

Returns

Type	Description
System.String	The sql function/table to call to return the data for this sql wrapper

Load(IEntityRetriever)

Loads all the parameters and column information for the sql wrapper for this interpretor

Uses a context instead of a cache for when the wrapper may have changes, or is being modified by the user

Declaration

public Task Load(IEntityRetriever retriever)

Parameters

Type	Name	Description
IEntityRetriever	retriever	A context to load the sql wrapper schema with

Returns

Type	Description
System.Threading.Tasks.Task	A task indicating the completion of the operation

Load(IReadOnlyCache)

Loads all the parameters and column information for the sql wrapper for this interpretor

Uses a cache instead of a context for when the wrapper does not have changes and just needs a quick method to load and typically execute/display results

Declaration

public Task Load(IReadOnlyCache cache)

Parameters

Type	Name	Description
IReadOnlyCache	cache	A local cache to load the sql wrapper schema from

Returns

Type	Description
System.Threading.Tasks.Task	A task indicating the completion of the operation

Reload(IEntityRetriever)

Forces a loads of all the parameters and column information for the sql wrapper for this interpretor

Uses a context instead of a cache for when the wrapper may have changes, or is being modified by the user

Declaration

public Task Reload(IEntityRetriever retriever)

Parameters

Type	Name	Description
IEntityRetriever	retriever	A context to load the sql wrapper schema with

Returns

Type	Description
System.Threading.Tasks.Task	A task indicating the completion of the operation

Implements

IQueryableItem

Extension Methods

MiscExtensions.SetIfNotEqual<T, P>(T, Expression<Func<T, P>>, P)

ReflectionExtensions.ClearEventInvocations(Object, String)

StringExtensions.ToCSVFormatString(Object, Type)

SQLExtensions.ToSQLValue(Object, Boolean)