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

object

SQLWrapperInterpretor

Implements

ISQLWrapperInterpretor

IQueryableItem

Inherited Members

object.GetType()

object.MemberwiseClone()

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

Namespace: LemonEdge.API.Entities.Helpers

Assembly: LemonEdge.API.dll

Syntax

public class SQLWrapperInterpretor : ISQLWrapperInterpretor, 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	The sql wrapper to use

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
Guid	id	The globally unique id of the sql wrapper to use

Properties

Chains

All chained queries from this one

Declaration

public IReadOnlyDictionary<ISQLWrapperChain, IEnumerable<ISQLWrapperChainLink>> Chains { get; }

Property Value

Type	Description
IReadOnlyDictionary<ISQLWrapperChain, IEnumerable<ISQLWrapperChainLink>>

Columns

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

Declaration

public IEnumerable<ISQLTypeWrapper> Columns { get; }

Property Value

Type	Description
IEnumerable<ISQLTypeWrapper>

DefaultOpenableColumn

Declaration

public ISQLTypeWrapper DefaultOpenableColumn { get; }

Property Value

Type	Description
ISQLTypeWrapper

GetColumnResultCount

Returns the total count of columns in the sql wrapper

Declaration

public int GetColumnResultCount { get; }

Property Value

Type	Description
int

GetParametersCount

Returns the total count of parameters in the sql wrapper

Declaration

public int GetParametersCount { get; }

Property Value

Type	Description
int

HasPostProcessCalculation

Declaration

public bool HasPostProcessCalculation { get; }

Property Value

Type	Description
bool

ID

The name of this sql wrapper

Declaration

public string ID { get; }

Property Value

Type	Description
string

IsSP

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

Declaration

public bool IsSP { get; }

Property Value

Type	Description
bool

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
bool

LoadedChains

Returns true if the sql wrapper interprator has loaded all chain info

Declaration

public bool LoadedChains { get; }

Property Value

Type	Description
bool

Name

The name of this sql wrapper

Declaration

public string Name { get; }

Property Value

Type	Description
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
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
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
string

UseCore

Declaration

public bool UseCore { get; }

Property Value

Type	Description
bool

Wrapper

The sql wrapper this interpretor is working with

Declaration

public ISQLWrapper Wrapper { get; }

Property Value

Type	Description
ISQLWrapper

Methods

AddTableStructure(DataSet, string)

Declaration

public DataTable AddTableStructure(DataSet set, string tableName)

Parameters

Type	Name	Description
DataSet	set
string	tableName

Returns

Type	Description
DataTable

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
bool	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 static object ConvertValueForResult(ISQLTypeWrapper param, object value)

Parameters

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

Returns

Type	Description
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.
• User: The user 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
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
Action<ISQLTypeWrapper>	addNewWrapper	A function that returns a new ISQLTypeWrapper for creating the standard parameters when required

Returns

Type	Description
Task	A task indicating the completion of this operation

FillResults(DataTable, IReader)

Declaration

public Task FillResults(DataTable table, IReader csv)

Parameters

Type	Name	Description
DataTable	table
IReader	csv

Returns

Type	Description
Task

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
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
string	The binding the system will use to bind the value of this column to a property on the ISQLWrapperResult

GetColumnName(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
string	The type of property used for storing the specified type on the ISQLWrapperResult

GetCustomInitSQLForSP(long, IEnumerable<(IQueryableParamInfo Param, string SQLValue)>)

Declaration

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

Parameters

Type	Name	Description
long	accountID
IEnumerable<(IQueryableParamInfo Param, string SQLValue)>	sqlParams

Returns

Type	Description
string

GetDefaultParameterValue(ISQLTypeWrapper, IEntityRetriever, IReadOnlyCache, UserInfo, IServiceContext)

Declaration

public Task<object> GetDefaultParameterValue(ISQLTypeWrapper parameter, IEntityRetriever cn, IReadOnlyCache cache, UserInfo user, IServiceContext context)

Parameters

Type	Name	Description
ISQLTypeWrapper	parameter
IEntityRetriever	cn
IReadOnlyCache	cache
UserInfo	user
IServiceContext	context

Returns

Type	Description
Task<object>

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
Task<IEnumerable<IQueryableFieldInfo>>	All columns that can be queried from this queryable item

GetFrameworkResultType(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
Type	The .net type used to store the specified resulttype which is held against the default ISQLWrapperResult type

GetParameterBindings(IEnumerable<ISQLTypeWrapper>)

Declaration

public static Dictionary<Guid, string> GetParameterBindings(IEnumerable<ISQLTypeWrapper> parameters)

Parameters

Type	Name	Description
IEnumerable<ISQLTypeWrapper>	parameters

Returns

Type	Description
Dictionary<Guid, string>

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
Task<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

GetResultType(Type)

Declaration

public static SQLWrapperInterpretor.ResultType GetResultType(Type type)

Parameters

Type	Name	Description
Type	type

Returns

Type	Description
SQLWrapperInterpretor.ResultType

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
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
string	columnName	The Name of a column to return the value for

Returns

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

GetResultValue(DataRow, ISQLTypeWrapper)

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

Declaration

public object GetResultValue(DataRow result, ISQLTypeWrapper col)

Parameters

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

Returns

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

GetResultValue(DataRow, 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(DataRow result, string columnName)

Parameters

Type	Name	Description
DataRow	result	A result holding values retrieved from executing this SQLWrapper
string	columnName	The Name of a column to return the value for

Returns

Type	Description
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
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

GetResultValue<T>(DataRow, ISQLTypeWrapper)

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

Declaration

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

Parameters

Type	Name	Description
DataRow	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

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

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

Declaration

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

Parameters

Type	Name	Description
long	accountID	The account id this sql is being generated for
IEnumerable<(IQueryableParamInfo Param, string SQLValue)>	sqlParams	The valuesof any sql parameters used to call this sql wrapper
IEnumerable<string>	selectedColumnsForCheck

Returns

Type	Description
string	The sql function/table to call to return the data for this sql wrapper

HasColumn(string)

Declaration

public bool HasColumn(string binding)

Parameters

Type	Name	Description
string	binding

Returns

Type	Description
bool

HasResultColumn(string)

Declaration

public bool HasResultColumn(string columnName)

Parameters

Type	Name	Description
string	columnName

Returns

Type	Description
bool

IsCoreParameter(string)

Declaration

public static bool IsCoreParameter(string paramName)

Parameters

Type	Name	Description
string	paramName

Returns

Type	Description
bool

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
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
Task	A task indicating the completion of the operation

LoadChains(IReadOnlyCache)

Loads all the chain info for this sql wrapper

Declaration

public Task LoadChains(IReadOnlyCache cache)

Parameters

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

Returns

Type	Description
Task	A task indicating the completion of the operation

MapResult(ISQLWrapperResult, object)

Given a result this will map all the values to the appropriate properties in the given class

Declaration

public object MapResult(ISQLWrapperResult result, object newResult)

Parameters

Type	Name	Description
ISQLWrapperResult	result
object	newResult

Returns

Type	Description
object

MapResultForJson(ISQLWrapperResult)

Given a result this will map all the values to the appropriate properties creating a dictionary that can be serialized to JSON

Declaration

public Dictionary<string, object> MapResultForJson(ISQLWrapperResult result)

Parameters

Type	Name	Description
ISQLWrapperResult	result	A result holding values retrieved from executing this SQLWrapper

Returns

Type	Description
Dictionary<string, object>	A dictionary mapping all the values to their column names

MapResult<T>(ISQLWrapperResult, T)

Declaration

public T MapResult<T>(ISQLWrapperResult result, T newResult)

Parameters

Type	Name	Description
ISQLWrapperResult	result
T	newResult

Returns

Type	Description
T

Type Parameters

Name	Description
T

MapToOnlyColumns(IEnumerable<string>)

Declaration

public void MapToOnlyColumns(IEnumerable<string> columns)

Parameters

Type	Name	Description
IEnumerable<string>	columns

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
Task	A task indicating the completion of the operation

SetResult(ISQLTypeWrapper, object, object, string)

Sets the value of column after converting the type if it doesn't match the column's property type.

Declaration

public void SetResult(ISQLTypeWrapper column, object value, object item, string propNameToSet = null)

Parameters

Type	Name	Description
ISQLTypeWrapper	column
object	value
object	item
string	propNameToSet

TryGetResultType(Type, out ResultType)

Declaration

public static bool TryGetResultType(Type type, out SQLWrapperInterpretor.ResultType resultType)

Parameters

Type	Name	Description
Type	type
SQLWrapperInterpretor.ResultType	resultType

Returns

Type	Description
bool

Implements

ISQLWrapperInterpretor

IQueryableItem

Extension Methods

LinqExtensions.AsArray<T>(T)

LinqExtensions.ToArrayOfOne<T>(T)

LinqExtensions.ToListOfOne<T>(T)

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

WeakReferenceExtensions.WeakReference(object)

SQLExtensions.ToSQLValue(object, bool)

ReflectionExtensions.ClearEventInvocations(object, string)

StringExtensions.ToCSVFormatString(object, Type)