Interface ISQLWrapper

The system entity for a sql wrapper, which wraps custom (or system generated) sql and defines its schema both of parameters and results for integration and use with our query tools

See https://help.lemonedge.com/help/enterprise-data-tools/reporting/sql-wrappers/intro.html for more information

Inherited Members

ISetCopier.GetCopyInfo(IEntityUpdater)

IHasVersion.IsSystem

IHasVersion.Version

IUserSpecific.UserID

IHasFolderStructure.FolderID

IBaseEntity.GetLabel(string)

IBaseEntity.SetLabel(string, string)

IBaseEntity.SetSilentLabel(string, string)

IBaseEntity.AccountID

IBaseEntity.ID

IBaseEntity.LastUpdated

IBaseEntity.ModifiedByUserID

IBaseEntity.CanvasID

IBaseEntity.AlgorithmStepID

IBaseEntity.SafeID

IEquatable<IBaseEntity>.Equals(IBaseEntity)

INotifyPropertyChanged.PropertyChanged

INotifyPropertyChanging.PropertyChanging

ICloneableAsync.Clone(object)

ICloneableAsync.CopyFromSource(object)

ICanTrackProperties.HasTrackedPropertyChanged(string)

ICanTrackProperties.OriginalTrackedPropertyValue(string)

ICanTrackProperties.OriginalTrackedPropertyValue<T>(string)

ICanTrackProperties.ClearTrackedOriginalValues()

ICanTrackProperties.GetAllOriginalTrackedPropertyValues()

Namespace: LemonEdge.API.Entities.Reporting

Assembly: LemonEdge.API.dll

Syntax

[EntityDefinition(EntityID.SQLWrapper, "dbo.LT_SQLWrappers", "SQLWrapper", LabelColumn = "Name", IsStandingDataEntity = true, HelpURL = "help/enterprise-data-tools/reporting/sql-wrappers/intro.html")]
[DefaultEntityIcon(ImageType.SQLFile)]
[EntityIndex(Name = "Name", ColumnNames = new string[] { "UserID", "OnlySystemQueryable", "Name", "OwnerEntityID" }, IndexOrder = new IndexOrder[] { IndexOrder.Ascending, IndexOrder.Ascending, IndexOrder.Ascending, IndexOrder.Ascending }, IncludeColumns = new string[] { "SafeID", "IsSystem", "Version", "Description", "Type", "OwnerEntityType", "SQL", "SQLCanvas", "SQLAsOf", "SQLMain", "SQLName", "HelpURL", "AlwaysUseCoreSQL", "AlwaysRecompileFunctionSQL", "AllowAsSubQuery", "AlgorithmStepID", "GridNewItemType", "FolderID", "GridPagingtype", "DefaultPagingRowCount" }, IsClustered = false, IsUnique = false)]
public interface ISQLWrapper : IBaseEntityWithPermissions, ISetCopier, IHasVersion, IUserSpecific, IHasFolderStructure, IBaseEntity, IEquatable<IBaseEntity>, INotifyPropertyChanged, INotifyPropertyChanging, ICloneableAsync, ICanTrackProperties

Properties

AllowAsSubQuery

Indicates this sql wrapper can be used as a sub query in other queries (such as via the dataset query tool)

Declaration

[EntityDescription("Indicates this sql wrapper can be used as a sub query in other queries (such as via the dataset query tool).")]
[EntityProperty(SQLType.Bit, false, "0")]
bool AllowAsSubQuery { get; set; }

Property Value

Type	Description
bool

AlwaysCreateInDatabase

Indicates this sqlwrapper should always have its sql created in the database - including during saves/upgrades. This moves the initial performance hit of running a query for the first time to the save/upgrade process.

Declaration

[EntityDescription("Indicates this sqlwrapper should always have its sql created in the database - including during saves/upgrades. This moves the initial performance hit of running a query for the first time to the save/upgrade process.")]
[EntityProperty(SQLType.Bit, false, "0")]
bool AlwaysCreateInDatabase { get; set; }

Property Value

Type	Description
bool

AlwaysRecompileFunctionSQL

Ensure the SQL of this function is recompiled on each call for higher performance.

Declaration

[EntityDescription("Ensure the SQL of this function is recompiled on each call for higher performance.")]
[EntityProperty(SQLType.Bit, false, "0")]
bool AlwaysRecompileFunctionSQL { get; set; }

Property Value

Type	Description
bool

AlwaysUseCoreSQL

Indicates calling this sql wrapper should always use the core sql and never use any of the _Main, _AsOf, _Canvas performance alternatvies.

Declaration

[EntityDescription("Indicates calling this sql wrapper should always use the core sql and never use any of the _Main, _AsOf, _Canvas performance alternatvies.")]
[EntityProperty(SQLType.Bit, false, "0")]
bool AlwaysUseCoreSQL { get; set; }

Property Value

Type	Description
bool

DefaultPagingRowCount

The default number of rows to return in the grid display when paging results

Declaration

[EntityProperty(SQLType.Int, false, "500")]
[EntityDescription("The default number of rows to return in the grid display when paging results.")]
[HardCodedDefaultValueOnNew("500")]
int DefaultPagingRowCount { get; set; }

Property Value

Type	Description
int

Description

A user friendly description of this SQL Wrapper

Declaration

[EntityProperty(SQLType.NVarChar, 2000, true)]
[EntityDescription("A user friendly description of this sql wrapper.")]
string Description { get; set; }

Property Value

Type	Description
string

GridNewItemType

Indicates a new button will be shown on the grid that defaults to creating a new entity of the specified type

Declaration

[EntityProperty(SQLType.UniqueIdentifier, true, IsEntityTypeLink = true)]
[EntityDescription("Indicates a new button will be shown on the grid that defaults to creating a new entity of the specified type")]
Guid? GridNewItemType { get; set; }

Property Value

Type	Description
Guid?

GridPagingtype

By default all queries first run a count to return the total number of rows then download just the first page of results. This flag prevents the execution of the count improving performance at the cost of knowing how many pages of data there is.

Declaration

[EntityProperty(SQLType.SmallInt, false, "0")]
[EntityDescription("By default all queries first run a count to return the total number of rows then download just the first page of results. This flag prevents the execution of the count improving performance at the cost of knowing how many pages of data there is.")]
SQLWrapperLoadType GridPagingtype { get; set; }

Property Value

Type	Description
SQLWrapperLoadType

HelpURL

A unique link to an html help page describing the purpose of this SQL Wrapper

Declaration

[EntityProperty(SQLType.NVarChar, 500, true)]
string HelpURL { get; set; }

Property Value

Type	Description
string

Name

[Key] The unique user friendly name of this SQL Wrapper

Declaration

[EntityProperty(SQLType.NVarChar, 500, false)]
[EntityKeyProperty]
[EntityDescription("The unique name of this sql stored procedure or sql function.")]
[Unique]
[Required]
[DBTrackChanges]
string Name { get; set; }

Property Value

Type	Description
string

OnlySystemQueryable

Indicates this sql wrapper is a normal one and can be executed by the user if false. Dataset Shortcuts are examples of sqlwrappers that are not directly queryable and have this as true

Declaration

[EntityProperty(SQLType.Bit, false, "0")]
[EntityDescription("Indicates this sql wrapper is a normal one and can be executed by the user if false. Dataset Shortcuts are examples of sqlwrappers that are not directly queryable and have this as true")]
bool OnlySystemQueryable { get; set; }

Property Value

Type	Description
bool

OwnerEntityID

A link to the entity that owns this SQL Wrapper and is responsible for dynamically generating its SQL such as the IDataset entity type.

Declaration

[EntityProperty(SQLType.UniqueIdentifier, true)]
[EntityLinkToAnyParentRelationship("OwnerEntityType", SingleJoinType.ZeroToOne, "SQL Wrapper Owner", true, new Type[] { typeof(ISQLFunction), typeof(IVennSetQueryableItem), typeof(ISQLWrapper), typeof(ISQLTypeWrapper), typeof(IDatasetQueryableItem) }, DeleteWithRelationship = true, PreventAutoConstraint = true, LinkToItemInSet = true, PartOfParentSet = false, OnlyWithLabels = true)]
Guid? OwnerEntityID { get; set; }

Property Value

Type	Description
Guid?

OwnerEntityType

An entity type that owns this SQL Wrapper and is responsible for dynamically generating its SQL such as the IDataset entity type.

Declaration

[EntityProperty(SQLType.UniqueIdentifier, true, IsEntityTypeLink = true)]
Guid? OwnerEntityType { get; set; }

Property Value

Type	Description
Guid?

SQL

The sql to be wrapped. This should start with an [alter function dbo.] or [alter procedure dbo.].

Declaration

[EntityProperty(SQLType.NVarChar, false)]
[EntityDescription("The sql to be wrapped. This should start with an 'alter function dbo.' or 'alter procedure dbo.'.")]
[Required]
[PropertyValidation("!string.IsNullOrEmpty(MyItem.SQL)", "MyItem.SQL.Trim().Replace(\"[\", \"\").Replace(\"]\", \"\").StartsWith((MyItem.Type == LemonEdge.API.Entities.Reporting.SQLWrapperType.Function ? \"alter function dbo.\" : \"alter procedure dbo.\") + MyItem.SQLName, StringComparison.InvariantCultureIgnoreCase)", "Must start with 'alter function dbo.' or 'alter procedure dbo.'")]
[PropertyValidation("!string.IsNullOrEmpty(MyItem.SQL)", "!MyItem.SQL.ContainsWholeWord(\"go\", StringComparison.InvariantCultureIgnoreCase) && !MyItem.SQL.ContainsWholeWord(\"drop\", StringComparison.InvariantCultureIgnoreCase, new string[] { \"drop table #\" }) && !MyItem.SQL.ContainsWholeWord(\"create\", StringComparison.InvariantCultureIgnoreCase, new string[] { \"create table #\", \"create index\" }) && (!MyItem.SQL.ContainsWholeWord(\"exec\", StringComparison.InvariantCultureIgnoreCase) || MyItem.Type == LemonEdge.API.Entities.Reporting.SQLWrapperType.StoredProcedure)", "Contains invalid sql.")]
string SQL { get; set; }

Property Value

Type	Description
string

SQLAsOf

The sql to be wrapped that doesn't reference a canvas, this is optional for performance it is dynsmically created from main SQL anyway. This should start with an [alter function dbo.] or [alter procedure dbo.] and the name should end with _AsOf.

Declaration

[EntityProperty(SQLType.NVarChar, true)]
[EntityDescription("The sql to be wrapped that doesn't reference a canvas, this is optional for performance it is dynamically created from main SQL anyway. This should start with an 'alter function' or 'alter procedure' and the name should end with _AsOf.")]
[PropertyValidation("!string.IsNullOrEmpty(MyItem.SQLAsOf)", "MyItem.SQLAsOf.Trim().Replace(\"[\", \"\").Replace(\"]\", \"\").StartsWith((MyItem.Type == LemonEdge.API.Entities.Reporting.SQLWrapperType.Function ? \"alter function dbo.\" : \"alter procedure dbo.\") + MyItem.SQLName + \"_AsOf\", StringComparison.InvariantCultureIgnoreCase)", "Must start with 'alter function dbo.' or 'alter procedure dbo.'")]
[PropertyValidation("!string.IsNullOrEmpty(MyItem.SQLAsOf)", "!MyItem.SQLAsOf.ContainsWholeWord(\"go\", StringComparison.InvariantCultureIgnoreCase) && !MyItem.SQLAsOf.ContainsWholeWord(\"drop\", StringComparison.InvariantCultureIgnoreCase, new string[] { \"drop table #\" }) && !MyItem.SQLAsOf.ContainsWholeWord(\"create\", StringComparison.InvariantCultureIgnoreCase, new string[] { \"create table #\", \"create index\" }) && (!MyItem.SQL.ContainsWholeWord(\"exec\", StringComparison.InvariantCultureIgnoreCase) || MyItem.Type == LemonEdge.API.Entities.Reporting.SQLWrapperType.StoredProcedure)", "Contains invalid sql.")]
string SQLAsOf { get; set; }

Property Value

Type	Description
string

SQLCanvas

The sql to be wrapped that doesn't reference an as of date, this is optional for performance it is dynsmically created from main SQL anyway. This should start with an [alter function dbo.] or [alter procedure dbo.] and the name should end with _Canvas.

Declaration

[EntityProperty(SQLType.NVarChar, true)]
[EntityDescription("The sql to be wrapped that doesn't reference an as of date, this is optional for performance it is dynsmically created from main SQL anyway. This should start with an 'alter function' or 'alter procedure' and the name should end with _Canvas.")]
[PropertyValidation("!string.IsNullOrEmpty(MyItem.SQLCanvas)", "MyItem.SQLCanvas.Trim().Replace(\"[\", \"\").Replace(\"]\", \"\").StartsWith((MyItem.Type == LemonEdge.API.Entities.Reporting.SQLWrapperType.Function ? \"alter function dbo.\" : \"alter procedure dbo.\") + MyItem.SQLName + \"_Canvas\", StringComparison.InvariantCultureIgnoreCase)", "Must start with 'alter function dbo.' or 'alter procedure dbo.'")]
[PropertyValidation("!string.IsNullOrEmpty(MyItem.SQLCanvas)", "!MyItem.SQLCanvas.ContainsWholeWord(\"go\", StringComparison.InvariantCultureIgnoreCase) && !MyItem.SQLCanvas.ContainsWholeWord(\"drop\", StringComparison.InvariantCultureIgnoreCase, new string[] { \"drop table #\" }) && !MyItem.SQLCanvas.ContainsWholeWord(\"create\", StringComparison.InvariantCultureIgnoreCase, new string[] { \"create table #\", \"create index\" }) && (!MyItem.SQL.ContainsWholeWord(\"exec\", StringComparison.InvariantCultureIgnoreCase) || MyItem.Type == LemonEdge.API.Entities.Reporting.SQLWrapperType.StoredProcedure)", "Contains invalid sql.")]
string SQLCanvas { get; set; }

Property Value

Type	Description
string

SQLMain

The sql to be wrapped that doesn't reference an as of date or canvas, this is optional for performance it is dynsmically created from main SQL anyway. This should start with an [alter function dbo.] or [alter procedure dbo.] and the name should end with _Main.

Declaration

[EntityProperty(SQLType.NVarChar, true)]
[EntityDescription("The sql to be wrapped that doesn't reference an as of date or canvas, this is optional for performance it is dynsmically created from main SQL anyway. This should start with an 'alter function' or 'alter procedure' and the name should end with _Main.")]
[PropertyValidation("!string.IsNullOrEmpty(MyItem.SQLMain)", "MyItem.SQLMain.Trim().Replace(\"[\", \"\").Replace(\"]\", \"\").StartsWith((MyItem.Type == LemonEdge.API.Entities.Reporting.SQLWrapperType.Function ? \"alter function dbo.\" : \"alter procedure dbo.\") + MyItem.SQLName + \"_Main\", StringComparison.InvariantCultureIgnoreCase)", "Must start with 'alter function dbo.' or 'alter procedure dbo.'")]
[PropertyValidation("!string.IsNullOrEmpty(MyItem.SQLMain)", "!MyItem.SQLMain.ContainsWholeWord(\"go\", StringComparison.InvariantCultureIgnoreCase) && !MyItem.SQLMain.ContainsWholeWord(\"drop\", StringComparison.InvariantCultureIgnoreCase, new string[] { \"drop table #\" }) && !MyItem.SQLMain.ContainsWholeWord(\"create\", StringComparison.InvariantCultureIgnoreCase, new string[] { \"create table #\", \"create index\" }) && (!MyItem.SQL.ContainsWholeWord(\"exec\", StringComparison.InvariantCultureIgnoreCase) || MyItem.Type == LemonEdge.API.Entities.Reporting.SQLWrapperType.StoredProcedure)", "Contains invalid sql.")]
string SQLMain { get; set; }

Property Value

Type	Description
string

SQLName

The actual name of this stored procedure or function in the sql database

Declaration

[EntityProperty(SQLType.NVarChar, 500, false)]
[EntityDescription("The unique name of the function, or stored procedure, to call.")]
[Unique]
[Required]
[DBTrackChanges]
string SQLName { get; set; }

Property Value

Type	Description
string

Type

The type of sql this SQL Wrapper is wrapping: Function or Stored Procedure

Declaration

[EntityProperty(SQLType.SmallInt, false, "0")]
[EntityDescription("Indicates the type of sql being wrapped.")]
SQLWrapperType Type { get; set; }

Property Value

Type	Description
SQLWrapperType

Methods

GetAutoUpdated()

Indicates if the changes to the sql for this sql wrapper were made automatically (such as by an IDataset) or by the user manually

Declaration

bool GetAutoUpdated()

Returns

Type	Description
bool

SetAutoUpdated()

Sets internally that this sql wrapper had its sql modified internally by the LemonEdge platform.

Used as sometimes sql is not allowed to be modified against a sql wrapper

Declaration

void SetAutoUpdated()

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)

Interface ISQLWrapper

Inherited Members

Namespace: LemonEdge.API.Entities.Reporting

Assembly: LemonEdge.API.dll

Syntax

Properties

AllowAsSubQuery

Declaration

Property Value

AlwaysCreateInDatabase

Declaration

Property Value

AlwaysRecompileFunctionSQL

Declaration

Property Value

AlwaysUseCoreSQL

Declaration

Property Value

DefaultPagingRowCount

Declaration

Property Value

Description

Declaration

Property Value

GridNewItemType

Declaration

Property Value

GridPagingtype

Declaration

Property Value

HelpURL

Declaration

Property Value

Name

Declaration

Property Value

OnlySystemQueryable

Declaration

Property Value

OwnerEntityID

Declaration

Property Value

OwnerEntityType

Declaration

Property Value

SQL

Declaration

Property Value

SQLAsOf

Declaration

Property Value

SQLCanvas

Declaration

Property Value

SQLMain

Declaration

Property Value

SQLName

Declaration

Property Value

Type

Declaration

Property Value

Methods

GetAutoUpdated()

Declaration

Returns

SetAutoUpdated()

Declaration

Extension Methods

See Also