Class SQLWrapper
Assembly: LemonEdge.API.Entities.Auto.dll
Syntax
[DataContract]
public class SQLWrapper : BaseEntityWithPermissions, ISQLWrapper, IBaseEntityWithPermissions, ISetCopier, IHasVersion, IUserSpecific, IHasFolderStructure, IBaseEntity, IEquatable<IBaseEntity>, INotifyPropertyChanged, INotifyPropertyChanging, ICloneableAsync, ICanTrackProperties, ICloneableAsync<ISQLWrapper>, IUserSpecificPublicToggle, ISetItemLoaderOverride, ISetCopierCloneAlterer
Constructors
SQLWrapper()
Declaration
Fields
SQL_DUMMY_FUNCTION
Declaration
public const string SQL_DUMMY_FUNCTION = "\r\nALTER Function dbo.{0}\r\n(\r\n\t@accountID BigInt, \r\n\t@canvasID UniqueIdentifier = null, \r\n\t@teamID UniqueIdentifier, \r\n\t@userID UniqueIdentifier, \r\n\t@lastUpdated DateTimeOffset = null\r\n)\r\nRETURNS TABLE \r\nAS\r\nRETURN \r\n(\r\n\t-- Add the SELECT statement with parameter references here\r\n -- Example Users\r\n\tSelect Name, EmailLogin, ID \r\n From fn_Users(@accountID, @canvasID, @teamID, @userID, @lastUpdated)\r\n)\r\n"
Field Value
SQL_DUMMY_SP
Declaration
public const string SQL_DUMMY_SP = "\r\nALTER Procedure dbo.{0}\r\n\t@accountID BigInt, \r\n\t@canvasID UniqueIdentifier = null, \r\n\t@teamID UniqueIdentifier, \r\n\t@userID UniqueIdentifier, \r\n\t@lastUpdated DateTimeOffset = null\r\nAS\r\nBEGIN\r\n\t-- SET NOCOUNT ON added to prevent extra result sets from\r\n\t-- interfering with SELECT statements.\r\n\tSET NOCOUNT ON;\r\n\r\n\t-- Insert statements for procedure here\r\n -- Example Users\r\n\tSelect Name, EmailLogin, ID \r\n From fn_Users(@accountID, @canvasID, @teamID, @userID, @lastUpdated)\r\nEND\r\n"
Field Value
Properties
AllowAsSubQuery
Indicates this sql wrapper can be used as a sub query in other queries (such as via the dataset query tool)
Declaration
public bool AllowAsSubQuery { get; set; }
Property Value
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
public bool AlwaysCreateInDatabase { get; set; }
Property Value
AlwaysRecompileFunctionSQL
Ensure the SQL of this function is recompiled on each call for higher performance.
Declaration
public bool AlwaysRecompileFunctionSQL { get; set; }
Property Value
AlwaysUseCoreSQL
Indicates calling this sql wrapper should always use the core sql and never use any of the _Main, _AsOf, _Canvas
performance alternatvies.
Declaration
public bool AlwaysUseCoreSQL { get; set; }
Property Value
CopierCloneType
Declaration
public SetCopierCloneType CopierCloneType { get; set; }
Property Value
DefaultPagingRowCount
The default number of rows to return in the grid display when paging results
Declaration
public int DefaultPagingRowCount { get; set; }
Property Value
Description
A user friendly description of this SQL Wrapper
Declaration
[StringLength(2000)]
public string Description { get; set; }
Property Value
DisplayItemOnCopy
Indicates if the items should appear in the UI when copied from a grid. Typically child items shouldn't be added as
items in a grid or opened on copy, just the root item itself
Declaration
public bool DisplayItemOnCopy { get; }
Property Value
FolderID
The link to the folder this item is contained in
Declaration
public Guid? FolderID { get; set; }
Property Value
FolderID_Label
Declaration
public string FolderID_Label { get; set; }
Property Value
FolderID_Label_Silent
Declaration
[NotMapped]
public string FolderID_Label_Silent { get; set; }
Property Value
ForceNewItemOnCopy
Indicates if the specified item should always be created as a new item. For instance when copying sub transactions
are always created as new items
Declaration
public bool ForceNewItemOnCopy { get; }
Property Value
GridNewItemType
Indicates a new button will be shown on the grid that defaults to creating a new entity of the specified type
Declaration
public Guid? GridNewItemType { get; set; }
Property Value
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
[EnumDataType(typeof(SQLWrapperLoadType))]
public SQLWrapperLoadType GridPagingtype { get; set; }
Property Value
Declaration
[NotMapped]
public string GridPagingtype_Tooltip { get; set; }
Property Value
HelpURL
A unique link to an html help page describing the purpose of this SQL Wrapper
Declaration
[StringLength(500)]
public string HelpURL { get; set; }
Property Value
IsPublic
Indicates if this entity is visible to all users (public), or just the current user (private).
Directly maps to UserID property
Declaration
[NotMapped]
public bool IsPublic { get; set; }
Property Value
IsSystem
Indicates this item is part of the core system. Any changes will be overwritten if the version number is lower than
the provided version number and the item is also marked as a system item.
Declaration
[Required]
public bool IsSystem { get; set; }
Property Value
Name
[Key] The unique user friendly name of this SQL Wrapper
Declaration
[Unique]
[Required]
[StringLength(500)]
public string Name { get; set; }
Property Value
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
public bool OnlySystemQueryable { get; set; }
Property Value
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
public Guid? OwnerEntityID { get; set; }
Property Value
OwnerEntityID_Label
Declaration
public string OwnerEntityID_Label { get; set; }
Property Value
OwnerEntityID_Label_Silent
Declaration
[NotMapped]
public string OwnerEntityID_Label_Silent { get; set; }
Property Value
OwnerEntityType
An entity type that owns this SQL Wrapper and is responsible for dynamically generating its SQL such as the
IDataset entity type.
Declaration
public Guid? OwnerEntityType { get; set; }
Property Value
SQL
The sql to be wrapped. This should start with an [alter function dbo.] or [alter procedure dbo.].
Declaration
[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.")]
public string SQL { get; set; }
Property Value
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
[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.")]
public string SQLAsOf { get; set; }
Property Value
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
[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.")]
public string SQLCanvas { get; set; }
Property Value
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
[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.")]
public string SQLMain { get; set; }
Property Value
SQLName
The actual name of this stored procedure or function in the sql database
Declaration
[Unique]
[Required]
[StringLength(500)]
public string SQLName { get; set; }
Property Value
Type
The type of sql this SQL Wrapper is wrapping: Function or Stored Procedure
Declaration
[EnumDataType(typeof(SQLWrapperType))]
public SQLWrapperType Type { get; set; }
Property Value
Declaration
[NotMapped]
public string Type_Tooltip { get; set; }
Property Value
UserID
Links to IUser. Indicates if this entity record is private to just the specified user, or if it is
public to everyone (holding null)
Declaration
public Guid? UserID { get; set; }
Property Value
UserID_Label
Declaration
public string UserID_Label { get; set; }
Property Value
UserID_Label_Silent
Declaration
[NotMapped]
public string UserID_Label_Silent { get; set; }
Property Value
Version
Indicates the version number of this item. Changes will only be overwritten if the provided new item has a higer
version number than this one.
Declaration
[Required]
[DatabasePropertyValidation("MyItem.IsSystem", "var allUserTeams = await Cache.GetAllItems<LemonEdge.API.Entities.Administration.IUserTeam>(); (await Cache.GetAllItems<LemonEdge.API.Entities.Administration.ITeam>()).Any(x => allUserTeams.Where(ut => ut.UserID == User.ID).Select(ut => ut.TeamID).Contains(x.ID) && x.CanEditSystemItems)", "User is not authorized to modify system items.")]
public short Version { get; set; }
Property Value
Methods
AlterCopiedItem(IEntityUpdater, UserInfo, IReadOnlyCache, IBaseEntity)
A custom ability to alter this item when it is was copied by the user in the UI, not when it is being imported
Declaration
public Task AlterCopiedItem(IEntityUpdater host, UserInfo user, IReadOnlyCache cache, IBaseEntity sourceItem)
Parameters
Returns
| Type |
Description |
| Task |
A task inidicating the finishing of this method
|
CopyFromEntity(IBaseEntity)
Declaration
protected override void CopyFromEntity(IBaseEntity src)
Parameters
Overrides
CopyFromSource(ISQLWrapper)
Updates all properties in this item to have the same properties as the source object.
Declaration
public void CopyFromSource(ISQLWrapper source)
Parameters
| Type |
Name |
Description |
| ISQLWrapper |
source |
The source object to copy all values from.
|
Determines whether the source item should be copied based on the provided relationship
Declaration
public bool CopyRelatedItem(IBaseEntity sourceItem, EntityRelationship rel)
Parameters
| Type |
Name |
Description |
| IBaseEntity |
sourceItem |
The original source item this item was copied from
|
| EntityRelationship |
rel |
The particular relationship from the source item
|
Returns
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
public bool GetAutoUpdated()
Returns
GetPropertyValue(ISetItemInfo, string, object)
Declaration
public object GetPropertyValue(ISetItemInfo info, string propName, object currentValue)
Parameters
Returns
ResetDefaultSQL()
Declaration
public void ResetDefaultSQL()
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
public void SetAutoUpdated()
ToString()
Declaration
public override string ToString()
Returns
Overrides
Implements
Extension Methods