Calling a Stored Procedure without Parameters using the StoredProcedureFramework

Calling a Stored Procedure without Parameters using the StoredProcedureFramework

This article is the second in a series of how to use my strongly typed stored procedure framework to call stored procedures from .Net code in a type safe way. The first article  in the series showed how to call a basic stored procedure that had no parameters and did not return any results. This article will show how to call a stored procedure which also does not have parameters, but DOES return some results. So lets get started take a look at how we go about that.

A Stored Procedure without Parameters.

The next stored procedure we will look at today is one which returns a result but does not have any parameters. This would typically be used for your MyTable_GetAll type of stored procedure, so for this example we will use a stored procedure which returns all tenants from the `Tenant` table in the `app` schema:

CREATE PROCEDURE [app].[TenantGetAll]
AS
BEGIN
     -- Insert statements for procedure here
     SELECT      [TenantId]
     ,           [IsActive]
     ,           [TenantName]
     ,           [RecordCreatedDateTime]
     FROM        [app].[Tenant];
END

CREATE TABLE [app].[Tenant] (
     [TenantId]              INT            IDENTITY (1, 1) NOT NULL,
     [IsActive]              BIT            NOT NULL,
     [TenantName]            NVARCHAR (100) NULL,
     [RecordCreatedDateTime] DATETIME       NOT NULL DEFAULT (GETDATE()),
     CONSTRAINT [PK_app.Tenant] PRIMARY KEY CLUSTERED ([TenantId] ASC)
 );

... and seeded the table with the following data:

INSERT INTO [app].[Tenant] ( [IsActive], [TenantName] ) VALUES ( 1, 'Acme Tenant' )
INSERT INTO [app].[Tenant] ( [IsActive], [TenantName] ) VALUES ( 1, 'Universal Tenant') 

As this procedure returns data we need to define a class that will represent a row of data in our result RecordSet. For each field in the RecordSet we need a property in this DTO class to represent it. The property should match the Name and DataType of the field it represents in the RecordSet row returned. Remember that StoredProcedureAttributes can be used to override both the Name and DataType if required, but in this case we will ensure they match the correct DataType and the framework will do the rest. So in the example case of the `TenantGetAll` stored procedure we are looking at a class which contains properties of the names and types we want to return, as below.

/// <summary>
/// Encapsulates tenant data
/// </summary>
internal class TenantDto
{
     public int TenantId { get; set; }
     public bool IsActive { get; set; }
     public string TenantName { get; set; }
     public DateTime RecordCreatedDateTime { get; set; }
}

We will use a DTO as this is likely to be the same object which transports our returned data up through the layers to the client, domain or business logic layer. In our example the DTO is be defined in the Example project which is akin to a DataAccess layer, however in a real world scenario the DTO may be defined in your services layer or elsewhere. Now we have a class which represents our return type we can build a class to represent our stored procedure.

[Schema("app")]
internal class TenantGetAll
    : NoParametersStoredProcedureBase<List<TenantDto>>
{
}

The first thing you may notice is the `Schema` attribute which this class has been decorated with. That informs the framework that the stored procedure exists in the app schema not the dbo schema. By default the the framework anticipates all stored procedures are in the dbo schema, by using the `SchemaAttribuute` to override the default the stored procedures can accessed in any schema. The next point you may observe is the class inherits from `NoParametersStoredProcedureBase<TReturn>`. It could just as easily inherit from `StoredProcedureBase<TReturn, NullStoredProcedureParameters>` but the `NoParametersStoredProcedureBase` base class is a short-cut base class to save defining an extra type parameter. As this stored procedure requires no parameters and we are using the `NoParametersStoredProcedureBase` base class we do not need to provide an explicit constructor with no parameters as the base class will handle this for us. The `TReturn` type parameter is needed and in this case is our list of `TenantDto`.

We can call the stored procedure using the extensions on SqlConnection object like so:    

[TestMethod]
public void TenantGetAll()
{
    // ARRANGE
    var procedure = new TenantGetAll();
    const int expectedTenantCount = 2;

    // ACT
    List<TenantDto> tenants = Connection.ExecuteStoredProcedure(procedure);
    TenantDto tenant1 = tenants.FirstOrDefault();

    // ASSERT
    Assert.AreEqual(expectedTenantCount, tenants.Count);
    Assert.IsNotNull(tenant1);
}

First we create an instance of the stored procedure POCO object, and then we pass that to the `ExecuteStoredProcedure` extension method of the `SqlConnection` object. We are expecting results this time so we can gather them from the results of the `ExecuteStoredProcedure` method call. They will be a List of our `TenantDto` so we can access them like any normal strongly typed list.

Source Code

The source code for the framework and these examples can be found here.

Next time

Next time we will look at calling a Stored Procedure that has parameters but does return any results. Or if you want to know sooner then visit the documentation for the stored procedure framework documentation, here.