IntroductionLast updated: 3/20/2023

1. Write SQL

Consider the following SQL. Nothing out of the ordinary. Insert a customer, and set the @CustomerId parameter to the new identity. So what's missing? Well, quite a bit, but let's keep this example simple, and focus on validating the parameters. Can the parameters be null, what is the max string length, is the email a valid email? SQL+ can make your code better!

    CREATE PROCEDURE [dbo].[CustomerInsert]
    (
        @CustomerId int out,
        @LastName varchar(64),
        @FirstName varchar(64),
        @Email varchar(64)
    )
    AS
    BEGIN
       INSERT INTO [dbo].[Customer]
        (
            [LastName],
            [FirstName],
            [Email]
        )
       VALUES
        (
            @LastName,
            @FirstName,
            @Email
        );
    END;
    SET @CustomerId = SCOPE_IDENTITY();
                            
                        

2. Add Semantic Tags

With SQL+ you can enforce parameter validation by simply adding a few tags. In this example we add required tags, max length tags, and an email tag. The SQL+ Code generation utility will use this information to escalate the validation into the generated services. If you change the underlying SQL, just rerun the builder, and your code is perfectly synchronized. Long story short, you have a single source of truth that you can trust throughout the enterprise.

    CREATE PROCEDURE [dbo].[CustomerInsert]
    (
        @CustomerId int out,

        --+Required
        --+MaxLength=64      
        @LastName varchar(64),

        --+Required
        --+MaxLength=64
        @FirstName varchar(64),

        --+Required
        --+MaxLength=64
        --+Email
        @Email varchar(64)
    )
    AS
    BEGIN
       INSERT INTO [dbo].[Customer]
        (
            [LastName],
            [FirstName],
            [Email]
        )
       VALUES
        (
            @LastName,
            @FirstName,
            @Email
        );
    END;
    SET @CustomerId = SCOPE_IDENTITY();
                            
                        

3. Configure Build Options

With your Semantically Tagged SQL inplace, you can choose build options that match your exact use case. If you want to implement a particular interface, check a box and you are done.

4. Generate Code

Just click build and let the tool do the work.

5. Enjoy

You've now built a service that includes all the validation you need, and you did it by simply adding a few tags to your SQL. Best part of all, your code will run four times faster than Entity Framework and twice as fast as Dapper.

    [TestMethod]
    public void CustomerInsertTest()
    {
        CustomerInsertInput input = new CustomerInsertInput
        {
            Email =  "sample@email.com",
            FirstName = "FirstName",
            LastName = "LastName",
        };

        if (input.IsValid())
        {
            CustomerInsertOutput output = service.CustomerInsert(input);
        }
            else
        {
            // TODO: Handle invalid input object

            foreach(var error in input.ValidationResults)
            {
               //.......
            }
        }
    }
                            
                        

More SQL+ Features

Well, not really. This introduction was designed to familiarize you with Semantic Tags, but it only scratched the surface. How about some additional features?

• Increase productivity without sacrificing performance
• Easy to maintain - if you make a change, just rerun the builder
• Services are easy to test
• Generate static lists of data
• Generate enumerations
• Implement INotifyPropertyChanged
• Implement IChangeTracking
• Implement IRevertibleChangeTracking
• Support Nullable Reference Types
• Use Model Binding with Validation in MVC and Blazor Apps
• Optimized for performance
• Easier to use and Twice as fast as Dapper

We are 100 percent convinced, that SQL+ is the best ORM for C# and SQL, and you will too.