Calling a Basic Stored Procedure using StoredProcedureFramework

Introduction

Now that my strongly typed stored procedure framework has reached a stable version that is being considered as a Release Candidate, I can now spend some time publishing how to call stored procedures using the framework. There is a full suite of examples both in the project and in the project Wiki.

So, let's start by looking at how to call the most basic type of stored procedure that you can imagine.

The Most Basic Type of Stored Procedure

The most basic type of stored procedure is one that has no parameters and returns no result. For example, a stored procedure that just performs an action like resetting a field value, but does not take any parameters and does not return any results, it uses maybe a configuration table or function in the database. For example the procedure below which resets the LastUpdatedDateTime field on the Account table.

CREATE PROCEDURE [dbo].[AccountLastUpdatedDateTimeReset]
 AS
 BEGIN
     UPDATE
         [app].[Account]
     SET
         [LastUpdatedDateTime] = GETDATE();
 END

So to call this stored procedure using the framework, we need a class to represent this stored procedure, AccountLastUpdatedDateTimeReset. So the framework knows how to use this class it must inherit from the StoredProcedureBase abstract class. This is the base class which the framework expects all stored procedure POCO classes to inherit from. The StoredProcedureBase base class expects two type parameters to be defined for it).

public abstract class StoredProcedureBase<TReturn, TParameters> {...}

If we wish to inherit from this class, which we must for the framework to function correctly, then we must provide a class for each type parameter. The TReturn type parameter defines the type of the which the stored procedure is to return and the TParameters type parameter defines a class for the stored procedure parameters. As our procedure neither returns any values or takes any parameters, we need to explicitly state this. The framework already provides us with concrete classes that can be used when there is no return type and or no parameter type. These both exist in the Dibware.StoredProcedureFramework namespace and are the NullStoredProcedureResult and NullStoredProcedureParameters classes:

NullStoredProcedureResult

This class is used when the procedure will not return any kind of result.

/// <summary>
/// An object that represents the absence of an
/// expected result from a stored procedure
/// </summary>
public class NullStoredProcedureResult
{
}

NullStoredProcedureParameters

This class is used when the stored procedure does not require any parameters.

/// <summary>
 /// An object that represents the absence of parameters
 /// for a stored procedure
 /// </summary>
 public class NullStoredProcedureParameters
 {
 }

So we could define the class that represents this stored procedure as follows...

internal class AccountLastUpdatedDateTimeReset
    : StoredProcedureBase<NullStoredProcedureResult, NullStoredProcedureParameters>
{
    public AccountLastUpdatedDateTimeReset()
        : base(new NullStoredProcedureParameters())
    {
    }
}

...but this is a bit cumbersome for such a basic stored procedure. Having to define the "Null" return type and "Null" parameters is a bit clumsy, so the framework provides another abstract base class NoParametersNoReturnTypeStoredProcedureBase which our stored procedure class can inherit from which does this for us and makes our code a little more succinct. Now we can define the class like below:

internal class AccountLastUpdatedDateTimeReset
     : NoParametersNoReturnTypeStoredProcedureBase
 {
 }

We do not need to provide a constructor as the NoParametersNoReturnTypeStoredProcedureBase already handles this for us in its default constructor. We can call the procedure using the code given in the test below. Please note the SqlConnectionExampleTestBase base class just sets up the SqlConnection for the test and handles opening and closing of the SqlConnection for us.

[TestClass]
public class StoredProcedureWithoutParametersOrReturnType
    : SqlConnectionExampleTestBase
{
    [TestMethod]
    public void AccountLastUpdatedDateTimeReset()
    {
        // ARRANGE
        var procedure = new AccountLastUpdatedDateTimeReset();

        // ACT
        Connection.ExecuteStoredProcedure(procedure);

        // ASSERT
        // Nothing to assert
    }
}

So to call the procedure, we first create a new instance of the stored procedure POCO object, and then we pass that to the ExecuteStoredProcedure extension method of the SqlConnection object. No results are expected so none are gathered. It's as simple as that. Next time we will look at calling a Stored Procedure without Parameters but does return results. Or if you want to know sooner, then visit the documentation for the stored procedure framework documentation, here.