Introducing SQL+ .NET

Every once in a while, something comes along that is just, well, better. SQL+ .NET is one of those things that once you start using, you’ll wonder how you ever got along without it.

So what is SQL+ .NET? It is actually two components that work together.

1. A set of semantic tags that you add to your SQL routines in the form of comments.
   
2. A Visual Studio plugin that reads your tagged procedures and generates an object-oriented class library that is ready for production.

The simplest way to understand why it is fast becoming the go-to product of choice for SQL developers is to actually see what it does.

Given the following stored procedure,

CREATE PROCEDURE [dbo].[FeedBackInsert]
(
    @Name varchar(32),
    @Subject varchar(32),
    @Email varchar(64),
    @Message varchar(1024),
    @FeedBackId int out
)
AS
BEGIN
    SET NOCOUNT ON;
INSERT INTO dbo.FeedBack
(
    [Name],
    [Subject],
    [Email],
    [Message],
    [CreatedDate]
)
VALUES
(
    @Name,
    @Subject,
    @Email,
    @Message,
    GETDATE()
)
SET @FeedbackId = SCOPE_IDENTITY();
         RETURN 1;
END;

All we need to do is to generate our ADO.NET code. So, simply add the following tag to the routine definition.

--+SqlPlusRoutine
    --&SelectType=NonQuery
    --&Comment=Inserts a new record into the feedback table
    --&Author=Alan Hyneman
--+SqlPlusRoutine
CREATE PROCEDURE [dbo].[FeedBackInsert]

And, that’s it. You can now go to your Visual Studio project, right-click the project, and choose the SQL Plus Build menu option.

 

 

SQL+ .NET does the rest for you. The generated code contains an input object which has all our input parameters as well as methods for validation.

/// <summary>
/// Input object for FeedBackInsert method.
/// </summary>
public class FeedBackInsertInput
{
    public string Name { set; get; }
    public string Subject { set; get; }
    public string Email { set; get; }
    public string Message { set; get; }

    /// <summary>
    /// Use this method to validate the instance.
    /// If the method returns false, the ValidationResults list will be populated.
    /// </summary>
    public bool IsValid()
    {
        ValidationResults = new List<ValidationResult>();
        return Validator.TryValidateObject
                 (this, new ValidationContext(this), ValidationResults, true);
    }

    /// <summary>
    /// ValidationResults populated from the IsValid() call.
    /// </summary>
    public List<ValidationResult> ValidationResults{ set; get; }
}

An output object that contains all the output parameters, return value, and any result sets that would be returned as a result of executing our stored procedure,

/// <summary>
/// Output object for FeedBackInsert method.
/// </summary>
public class FeedBackInsertOutput
{
    public int? FeedBackId { set; get; }
    public int? ReturnValue { set; get; }

    /// <summary>
    /// This is the collection of TransientErrors that occured during the execution of the call.
    /// </summary>
    public List<SqlException> TransientErrors { set; get; }
}

And, a service method that takes the input object and returns the output object.

/// <summary>
/// Inserts a new record into the feedback table
/// SQL+ Routine: dbo.FeedBackInsert - Authored by Alan Hyneman
/// </summary>
public FeedBackInsertOutput FeedBackInsert(FeedBackInsertInput input)
{
    //additional code - removed for simplification of article
}

And, to call our procedure using our newly generated code is as simple as this.

//create the input object
FeedBackInsertInput input = new FeedBackInsertInput()
{
    Email = "[email protected]",
    Name = "The Ninja Coder",
    Subject = "Is SQL+ dot net for real",
    Message = "Yes, very much so and it is the tool that SQL developers have been waiting for.",
};

//create the service
Service service = new Service("your connection string");

//call the service and get the result
FeedBackInsertOutput output = service.FeedBackInsert(input);

Now, if that was all SQL+ .NET did, it would be a very useful tool, however, it does a whole lot more. SQL+ .NET gives you the ability to add validation tags to your parameters, and that information is then escalated into the generated code. For instance, if we want to make it so that all our parameters are required, we simply add the required tag like so.

CREATE PROCEDURE [dbo].[FeedBackInsert]
(
--+Required
    @Name varchar(32),

--+Required
    @Subject varchar(32),

--+Required
    @Email varchar(64),

--+Required
    @Message varchar(1024),

    @FeedBackId int out
)

And now, our input object can enforce that validation through the use of data annotations, and the IsValid() method would then test your properties to enforce the validation, all by adding a few tags to your stored procedure.

/// <summary>
/// Input object for FeedBackInsert method.
/// </summary>
public class FeedBackInsertInput
{
    [Required(AllowEmptyStrings = false)]
    public string Name { set; get; }

    [Required(AllowEmptyStrings = false)]
    public string Subject { set; get; }

    [Required(AllowEmptyStrings = false)]
    public string Email { set; get; }

    [Required(AllowEmptyStrings = false)]
    public string Message { set; get; }
    /// <summary>
    /// Use this method to validate the instance.
    /// If the method returns false, the ValidationResults list will be populated.
    /// </summary>
    public bool IsValid()
    {
        ValidationResults = new List<ValidationResult>();
        return Validator.TryValidateObject
        (this, new ValidationContext(this), ValidationResults, true);
    }

    /// <summary>
    /// ValidationResults populated from the IsValid() call.
    /// </summary>
    public List<ValidationResult> ValidationResults{ set; get; }
}

There are validation tags for required, string length, ranges of values, formatting options like email, phone, and credit cards. There are tools to build enumerations from tables, and associate those enumerations with parameters. You can enumerate return values for procedures that may have different logical outcomes and you can even use these tags, in tandem with resource files, to create a class library that supports multiple languages.

Alan Hyneman has been working with computers and developing software for nearly 40 years and has just taken over as the CTO for Comfort Depot.