IntroductionLast updated: 7/23/2024

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
        );

        SET @CustomerId = SCOPE_IDENTITY();

    END;
                            
                        

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
        );

        SET @CustomerId = SCOPE_IDENTITY();

    END;

                            
                        

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)
            {
               //.......
            }
        }
    }
                            
                        

Top 10 SQL+ Features

• SQL-Based Ecosystem: You have the full power of the database available to you. Even the most complex SQL is easily translated to an object-oriented library.
• Productivity: Write SQL, add Semantic Tags, generate code, and you're done. You will never write wrapper code for your SQL again.
• Semantic Tags: This is a game-changer for SQL developers. It provides field-level validation of parameters, enumerated return values, comments that are available through intellisense, and allows fine-tuning of your generated services.
• Table Value Parameters: Fully supported, including generation of types and mapping to parameters.
• Multiple Result Set Queries (Mars): Each recordset is mapped to a class and encapsulated in a result data object.
• Transient Error Management: Allows configurable retry options and logging.
• Best Practices: Subscribes to DRY (Don’t Repeat Yourself) and SOLID principles.
• Usability: The object-oriented nature of the generated services facilitates model binding and event distribution.
• Performance: SQL+ services eliminate transpiling at runtime and execute up to four times faster than Entity Framework and nearly twice as fast as Dapper.
• Support: When you are successful, we are successful, and we are here to help you succeed. All upgraded subscriptions have access to unlimited support.

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