SQL+

SQL+
Website
https://www.sqlplus.net/

SQL+ is an extension to the (Structured Query Language) where semantic tags add functionality to SQL that does not exist in the native language. It also includes a code generation utility that parses the SQL+ code to generate an object-oriented class library.

History

SQL+ was conceived and built by Alan Hyneman, a computer scientist from the United States. Alan, frustrated with existing ORM deficiencies and tendencies toward bad patterns and practices, decided to build an ORM that featured a SQL first approach and promoted good patterns and practice.

The introduction of “Semantic Tags” to the SQL language (where SQL+ gets its name) is a game changer for SQL developers and embraces the DRY (don’t repeat yourself principal) as well as the SSOT (Single Source of Truth) practice. This means that data services built with SQL+ are not only the best performing services available, they are also the gatekeepers of pristine data.

The original version SQL+ 1 was built in 2013, and many years and many iterations later, SQL+ Version 4 was released on March 1st of 2023.

Supported SQL Forms

SQL+ works with scalar functions, table functions, stored procedures and Ad-Hoc queries. Regardless of the approach, the generated code subscribes to the same patterns and practices. Additionally, the developer experience (consumers of your services) benefit from that consistent pattern, making your services easy to develop, and easy to use.

Stored procedures

Simply add comments to the stored procedure or function body and run the code generator when utilising stored procedures or functions. In order to make certain procedures available for inclusion in the build, the builder will search the database for them. Once found, it will offer them in the UI. Choose any method you wish to include in the build, then give it a namespace.

Ad-Hoc queries

By writing SQL statements and placing them in folders within your project, the code generator can inspect those folders and present those queries in the UI. Simply select the queries you want to include in the build and run the builder. When using concrete queries, the folder structure dictates the namespace structure.

SQL+ Semantic Tags

Semantic tags are used to define your service, and optionally provide validation to parameters, indicate display properties, enumerate return values, specify parameter modes, and identify multi-result-set queries all within your SQL. This makes that SQL a single source of truth and defines a contract that protects your data and keeps it in a pristine state.

Routine Tag

The routine tag is the only tag that is required and is placed at the beginning of the procedure or query.

--+SqlPlusRoutine

--&SelectType=NonQuery | SingleRow | MultiRow | JSON | XML | MultiSet

--&Comment=Explain service

--&Author=Identify Service Creator

--&CommandTimeout=seconds*

--&Obsolete=Error | Warning,Error Message*

--+SqlPlusRoutine

This tag provides the flexibility to tailor your service to the exact results you wish to achieve. You specify a select type that matches the type of query to be executed, as well as provide a comment and author name. You may also include the optional values for Command Timeout and Obsolete tags.

Parameter Validation Tags

Parameter validation tags are used to enforce validation of input parameters. Place one or more tags above the parameter declaration to achieve the desired results. The following parameter validation tags are available:

--+CreditCard

--+Email

--+MaxLength=MaximumLength

--+MinLength=MinimumLength

--+Phone

--+Range=MinimumValue,MaximumValue

--+RegExPattern=RegularExpression

--+Required

--+StringLength=MinimumLength,MaximumLength

--+Url

Parameter Validation Tags - Custom Errors

The error messages generated by invalid data are provided through data annotations in C#, and the default values are acceptable most of the time. However, if custom messages are preferred, the following addition tags may be used to customize error messages.

--&ErrorMessage=ErrorMessage

--&ErrorResource=ResourceType,ResourceName

Parameter Display Tags

Display tags are utilized when input objects are bound to UI elements. In addition, when error messages are created, the display value is used in place of the member name to make a more human readable error message.

--+Display=Name, Description

Return Tags (Routines)

The return tag is used to enumerate return values. Enumerating return values allows clear information about the outcome of a given service call to be passed back to developers using your service. Simply add the tag to each unique return value, and an enumeration will be generated and associated with the value of the return.

--+Return=EnumeratedValue,Description

Return Tags (Queries)

The return tag is used to enumerate return values. Enumerating return values allows clear information about the outcome of a given service call to be passed back to developers using your service. Simply add the tag above each @ReturnValue SET statement, and an enumeration will be generated and associated with the value of the @ReturnValue.

--+Return=EnumeratedValue,Description

Multiple Result Sets and Query Tags

When procedures or statements return multiple result sets (mars queries), each query is wrapped with query tags to indicate a name for the result set property as well as the select type that applies to that specific query.

--+QueryStart=Name,SelectType

--+QueryEnd

Parameter Tags Miscellaneous

--+Enum=EnumerationName

This tag is utilized to associate enumerations (generated or not) to the given parameter. When using with generated enumerations, only the name of the enumeration as defined in the builder is required. In other cases, you must use the fully qualified name of the enumeration. For instance System.DayOfWeek will work as expected, DayOfWeek will not.

--+Comment=your comment here

The comment tag is utilized to provide clear information to the users of your service where those comments are available via intellisense.

--+Explicit=value

This tag provides a means to include custom annotations to your code where the value is copied verbatim and applied to the property.

--+Default=value

Provides a default value for the given parameter. If the data type of the parameter is a string, the value must be enclosed in double quotes

Parameter SectionTags (Queries Only)

When generating services with ad-hoc queries, use this tag to surround any variables that should be included as parameters to your query. You place the opening tag above the DECLARE statement, and the closing tag after the last variable you want to include. Any values assigned to the variables within the parameters tags will be ignored by the builder. This provides a convenient way to test the procedures in isolation.

--+Parameters

DECLARE @YourParameter int;

--+Parameters

Parameter Mode Tags – (Database Routines)

By default, parameters in T-SQL are inputs. When out is specified for the parameter in the SQL it becomes an in/out parameter in the parameters collection. This is due to the nature of ADO, but isn’t necesarilly intuitive. SQL+ treats parameter direction context as follows:

SQL parameters that are input (not marked as out) are present on the input objects only. (Write)

SQL parameters that are output (marked as out) are present on the output object only. (Read)

SQL parameters that are output (marked as out) and include the InOut tag are present on the input and output objects. (Read Write)

--+InOut

Parameter Mode Tags – (Queries)

By default, parameters in Queries are inputs and the out keyword is not available. To compenstate for this the following rules apply:

Variables with no mode tag applied are available on inputs objects. (Write)

Variables with the output tag applied are are available on output objects. (Read)

Variables with the input tag applied are available on input and output objects. (Read Write)

--+InOut

--+Output

Building Your Project

To run the builder, right-click on the project and choose the menu option SQL+ Builder.

Configure Database Connection

The database connection points to the source database that contains your SQL Routines, or the database that you queries will execute against.

Configure Database Routines

You assign the routines to namespaces. This can be done at the schema level or routine level.

Configure Query Routines

If you have any ad-hoc queries in your project, they will be displayed here. Unlike routines, when using ad-hoc queries, it is the folder structure that dictates namespace assignments, where each subdirectory of the Queries folder becomes a namespace, and the name of the file is utilized for the name of the generated service.

Configure Static Lists

Class definitions, as well as populated lists of static data will be created for each query defined in the builder. Simply write your query and paste it into the builder.

Configure Enum Queries

Enumerations will be created for all Enumerations Queries defined in the builder. Replace the <column> values with the appropriate columns names to create the enumeration. When enumerations are created in this manner, they can be assigned to input parameters using the enum tag and specifying the name of the of the enum query form the Enum Queries collection.

--+Enum=MyEnumQuery

@MyParameter int

Configure Build Options

Choose any of the build options you want to utilize for your generated services. Each is outline below

• SQLClient - choose the SQL Client that you want to utilize. Options are System.Data.SqlClient, or Microsoft.Data.SqlClient - note that this is related to the nuget package you need to add to your service project.

• INotifyPropertyChanged - select this if you want your input objects to implement the INotifyPropertyChanged interface.

• IChangeTracking - select this if you want your input objects to implement the IChangeTracking interface.

• IRevertibleChangeTracking - select this if you want your input objects to implement the IRevertibleChangeTracking interface.

• Use Nullable Reference Types - select this if you want your input objects to utilize Nullable Reference Types interface.

Stay tuned... More build options are on the way!

Run The Builder

Once you are satisfied with all your build objects, and have made your build option choices, you can simply run the builder. In the Build Project area, click the Build Project button.

And you now have a robust, object-oriented class library that will hold up in the most demanding environments, and serve as the gatekeeper of pristine data across your enterprise.

Exploring the Code

A common Pattern

The input object encapsulates the parameters passed to the service and the output object encapsulates any output parameters and/or result sets.

We choose this object-oriented design to facilitate calling services directly, as well as supporting data binding in various UI clients. In addition, the input objects with escalated validation tags provide the Is Valid() method which means invalid data is prevented from ever making it to the database.

That object is validated and passed into the service call, which returns an output object containing out parameters, result set data, and (optionally enumerated) return value. When a procedure does not require input parameters the service is called without an input object.

Transactions

Execute multiple calls in a single transaction using the generated services.

Note that transactions cannot be used in tandem with retry options.

Transient Errors and Retry Options

If you're new to transient errors, in short, a transient error is an error that has an underlying cause that is self-resolving. For instance, a network-related error has nothing to do with your SQL, so retry logic can compensate for that situation. On the other hand, an index violation will continue to fail no matter how many times it’s tried.

Transient errors and retries are managed by SQL+.NET, by providing retry options in the constructor of the service. You create a custom retry object by deriving from the abstract class retry options, and provide the list of error numbers you deem transient, express the intervals for retries, and supply an optional logging component.

In practice, you will normally create several variations of retry options for specific purposes. UI might be very short and fewer retries, back end processes would be longer intervals and more retries.