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://web.lemonedge.com/help/sql-wrappers/ for more information
Inherited Members
Namespace: LemonEdge.API.Entities
Assembly: API.dll
Syntax
[EntityDefinition(EntityID.SQLWrapper, "dbo.LT_SQLWrappers", "SQLWrapper", LabelColumn = "Name", IsStandingDataEntity = true, HelpURL = "sql-wrappers")]
[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()