Today we are going to look at the first real innovation in data access for quite some time. As someone who has worked with databases for a very long time, I’ve written thousands of stored procedures for CRUD operations. As a SQL developer, I actually enjoy that work, and you probably do too. If data is your thing, stored procedures are your bread and butter.

The pain point for me, and many other developers as well, has always been the ADO part of the equation, you know, that service layer code that feels like you’re writing the same code twice. Now, before you object and tell me how great Entity Framework or Dapper is, let me say that I’ve used both of them, and neither one of them comes close to the ease of use, or runtime performance that SQL+ dotnet provides.

SQL+ dotnet takes stored procedures, and generates your ADO code for you. You write your SQL, run the SQL+ Code Generation Utility, and you are done. That SQL routine, the exact code you have worked so hard on, is now a high performance, object-oriented class library that is ready for production. The easiest way to illustrate this is by example. Given this simple insert procedure:

ALTER 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 add some comments, in SQL+ dot net terminology, semantic tags.

  --+SqlPlusRoutine
      --&SelectType=NonQuery
      --&Author=Your Name Here
      --&Comment=Inerts a new record into the FeedBack table 
  --+SqlPlusRoutine
  ALTER PROCEDURE [dbo].[FeedBackInsert]

and in Visual Studio, run the SQL+ Code Generation Utility (A Visual Studio Plugin), and that’s it, an object-oriented class library has been built.

Note that validation, execution, and transient error handling have been removed for illustration purposes.

So let’s explore the code that SQL+ dot net has built for us. If our routine takes any input parameters, they are built into an input class where the parameters become properties of that class.

/// <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; }
}

And all routines have an output class that have any output parameters, result sets, and return value as follows:

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

In this case we have a non-query routine, so there is no result in the output class. If however the routine returned a result, i.e. SELECT * FROM dbo.Feedback WHERE FeedbackId = @FeedbackId, the output class would have an additional property ResultData like so:

    public class FeedBackByIdOutput
    {
    /// <summary>
    /// FeedBackByIdResult result.
    /// </summary>
    public FeedBackByIdResult ResultData { set; get; }
    }

And there would be an additional result class that represents the columns in that result set like the following:

    /// <summary>
    /// Result object for FeedBackById routine.
    /// </summary>
    public class FeedBackByIdResult
    {
    public int FeedBackId { set; get; }
    public string Name { set; get; }
    public string Subject { set; get; }
    public string Email { set; get; }
    public string Message { set; get; }
    public DateTime CreatedDate { set; get; }
    }

The actual execution of the routine is handled by a service class. You pass in the input, and get an output back.

  /// <summary>
  /// Inserts a new record into the FeedBack table
  /// SQL+ Routine: dbo.FeedBackInsert - Authored by Your Name Here
  /// </summary>
  public FeedBackInsertOutput FeedBackInsert(FeedBackInsertInput input)
  {
  FeedBackInsertOutput output = new FeedBackInsertOutput();
  Execution code removed for illustration
  return output;
  }

And behind the scenes, all that nuisance ADO code that that has plagued us for decades, has all been generated with the simple click of a mouse. No type mismatch errors, no spellings errors, no missing parameter errors, and no time wasted.

  /// <summary>
  /// Builds the command object for FeedBackInsert method.
  /// </summary>
  /// <param name="cnn">The connection that will execute the procedure.</param>
  /// <param name="input">FeedBackInsertInput instance for loading parameter values.</param>
  /// <returns>SqlCommand ready for execution.</returns>
  private SqlCommand GetFeedBackInsertCommand(SqlConnection cnn, FeedBackInsertInput input)
  {
  SqlCommand result = new SqlCommand()
  {
  CommandType = CommandType.StoredProcedure,
  CommandText = "dbo.FeedBackInsert",
  Connection = cnn
  };
  result.Parameters.Add(new SqlParameter()
  {
  ParameterName = "@Name",
  Direction = ParameterDirection.Input,
  SqlDbType = SqlDbType.VarChar,
  Size = 32,
  Value = input.Name
  });
  result.Parameters.Add(new SqlParameter()
  {
  ParameterName = "@Subject",
  Direction = ParameterDirection.Input,
  SqlDbType = SqlDbType.VarChar,
  Size = 32,
  Value = input.Subject
  });
  result.Parameters.Add(new SqlParameter()
  {
  ParameterName = "@Email",
  Direction = ParameterDirection.Input,
  SqlDbType = SqlDbType.VarChar,
  Size = 64,
  Value = input.Email
  });
  result.Parameters.Add(new SqlParameter()
  {
  ParameterName = "@Message",
  Direction = ParameterDirection.Input,
  SqlDbType = SqlDbType.VarChar,
  Size = 1024,
  Value = input.Message
  });
  result.Parameters.Add(new SqlParameter()
  {
  ParameterName = "@FeedBackId",
  Direction = ParameterDirection.Output,
  SqlDbType = SqlDbType.Int,
  Scale = 0,
  Precision = 10,
  Value = DBNull.Value
  });
  result.Parameters.Add(new SqlParameter()
  {
  ParameterName = "@ReturnValue",
  Direction = ParameterDirection.ReturnValue,
  SqlDbType = SqlDbType.Int,
  Scale = 0,
  Precision = 10,
  Value = DBNull.Value
  });
  return result;
  }
  private void SetFeedBackInsertCommandOutputs(SqlCommand cmd, FeedBackInsertOutput output)
  {
  if(cmd.Parameters[4].Value != DBNull.Value)
  {
  output.FeedBackId = (int?)cmd.Parameters[4].Value;
  }
  if(cmd.Parameters[5].Value != DBNull.Value)
  {
  output.ReturnValue = (int?)cmd.Parameters[5].Value;
  }
  }

And since all the code generated is straight up ADO, the code is as performant as it can possibly be. We tested this very simple routine thousands of times against Entity Framework and Dapper. The average results for 1000 iterations are as follows (times in milliseconds):

• Dapper Insert 1000 Records:3364
• EF Insert 1000 Records:12794
• Sql Plus Insert 1000 Records:2316

That’s roughly 450% faster than Entity Framework, and roughly 45% faster than Dapper, impressive to say the least.

As for using the generated class library, developers will quickly adapt and come to appreciate the simplicity and consistency of the simple input/output pattern.

  //Create the input object
  FeedBackInsertInput input = new FeedBackInsertInput
  {
  Email = "SqlDevelopers@everywhere.com",
  Message = "A big thanks to you guys for thinking of us",
  Name = "SQL Developer",
  Subject = "Thank you!"
  };
  //Create the Service
  Service service = new Service(connectionString);
  //Call the service getting the output object back
  FeedBackInsertOutput output = service.FeedBackInsert(input);

Now, if that was all SQL+ did, it would be a very useful tool. But SQL+ does a whole lot more. Through the use of semantic tags, SQL+ allows you to add optional tags to your SQL and generate code that goes way beyond what a database supports natively, and that code is executed in the service hosting your library, thereby, distributing the workload so your database can do what it does best, and your class library can be the gateway to pristine data.

There are tags for parameter validation, display properties, enumerations, and return value enumerations, all designed to make your life easier, and provide the users of your library the best experience possible.

In the next article we are going to have a look at these tags and what they do, as well as see how we can use them to insulate your data, and provide immediate feedback to users of your library. Hope to see you there.

SQL+ was built by a small team of developers who truly care about data. We’re self-funded and we invite you to join our community and put data access back in the hands of SQL professionals.

PS. If you are the impatient type, we invite you to explore the tutorials and getting started series at www.SqlPlus.net.