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

IBaseEntity.AccountID

IBaseEntity.ID

IBaseEntity.LastUpdated

IBaseEntity.ModifiedByUserID

IBaseEntity.CanvasID

IBaseEntity.GetLabel(String)

IBaseEntity.SetLabel(String, String)

IBaseEntity.SetSilentLabel(String, String)

System.IEquatable<LemonEdge.API.Core.IBaseEntity>.Equals(LemonEdge.API.Core.IBaseEntity)

System.ComponentModel.INotifyPropertyChanged.PropertyChanged

System.ComponentModel.INotifyPropertyChanging.PropertyChanging

ICloneableAsync.Clone(Object)

ICloneableAsync.CopyFromSource(Object)

ICanTrackProperties.HasTrackedPropertyChanged(String)

ICanTrackProperties.OriginalTrackedPropertyValue(String)

ICanTrackProperties.OriginalTrackedPropertyValue<T>(String)

ICanTrackProperties.ClearTrackedOriginalValues()

ICanTrackProperties.GetAllOriginalTrackedPropertyValues()

ISetCopier.GetCopyInfo(IEntityUpdater)

IHasVersion.IsSystem

IHasVersion.Version

IUserSpecific.UserID

Namespace: LemonEdge.API.Entities

Assembly: 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)]
[EntityLinkToAnyParentRelationshipModifier(EntityID.Transaction, "ParentItemID", "ID", ExcludeFromRelationship = true)]
[EntityLinkToAnyParentRelationshipModifier(EntityID.DataSourceIDs, "EntityID", "ID", ExcludeFromRelationship = true)]
public interface ISQLWrapper : IBaseEntityWithPermissions, IBaseEntity, IEquatable<IBaseEntity>, INotifyPropertyChanged, INotifyPropertyChanging, ICloneableAsync, ICanTrackProperties, ISetCopier, IHasVersion, IUserSpecific

Properties

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
System.Boolean

Description

A user friendly description of this SQL Wrapper

Declaration

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

Property Value

Type	Description
System.String

HelpURL

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

Declaration

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

Property Value

Type	Description
System.String

Name

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

Declaration

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

Property Value

Type	Description
System.String

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", true, new Type[]{typeof(ISQLWrapper), typeof(ISQLTypeWrapper)}, DeleteWithRelationship = true, PreventAutoConstraint = true, LinkToItemInSet = true, PartOfParentSet = false)]
Guid? OwnerEntityID { get; set; }

Property Value

Type	Description
System.Nullable<System.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
System.Nullable<System.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.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 #\" }) && !MyItem.SQL.ContainsWholeWord(\"exec\", StringComparison.InvariantCultureIgnoreCase)", "Contains invalid sql.")]
string SQL { get; set; }

Property Value

Type	Description
System.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 dynsmically 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.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 #\" }) && !MyItem.SQLAsOf.ContainsWholeWord(\"exec\", StringComparison.InvariantCultureIgnoreCase)", "Contains invalid sql.")]
string SQLAsOf { get; set; }

Property Value

Type	Description
System.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.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 #\" }) && !MyItem.SQLCanvas.ContainsWholeWord(\"exec\", StringComparison.InvariantCultureIgnoreCase)", "Contains invalid sql.")]
string SQLCanvas { get; set; }

Property Value

Type	Description
System.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.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 #\" }) && !MyItem.SQLMain.ContainsWholeWord(\"exec\", StringComparison.InvariantCultureIgnoreCase)", "Contains invalid sql.")]
string SQLMain { get; set; }

Property Value

Type	Description
System.String

SQLName

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

Declaration

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

Property Value

Type	Description
System.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
System.Boolean

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

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

ReflectionExtensions.ClearEventInvocations(Object, String)

StringExtensions.ToCSVFormatString(Object, Type)

SQLExtensions.ToSQLValue(Object, Boolean)

See Also

IDataset

IPivot

IChart