Basic Stored Procedures

These scenarios demonstrate how to call basic stored procedures that return a single resultset. This can be interpreted as a value, a row, or a collection of rows.

Future scenarios will cover topics such as:

• Multiple Resultsets
• Output parameter(s)
• Return parameter

Scenario Prototype

public interface IBasicStoredProcScenario<TEmployeeClassification, TEmployeeClassificationWithCount>
   where TEmployeeClassification : class, IEmployeeClassification, new()
   where TEmployeeClassificationWithCount : class, IEmployeeClassificationWithCount
{
    /// <summary>
    /// Execute a stored procedure that returns a collection.
    /// </summary>
    IList<TEmployeeClassificationWithCount> CountEmployeesByClassification();

    /// <summary>
    /// Execute a stored procedure that takes a parameter object and returns a scalar value.
    /// </summary>
    /// <param name="employeeClassification">The employee classification.</param>
    /// <returns>The employee classification key.</returns>
    int CreateEmployeeClassification(TEmployeeClassification employeeClassification);

    /// <summary>
    /// Execute a stored procedure that returns a collection, omitting the optional parameter.
    /// </summary>
    IList<TEmployeeClassification> GetEmployeeClassifications();

    /// <summary>
    /// Execute a stored procedure that returns a collection, providing an optional parameter.
    /// </summary>
    /// <param name="employeesClassificationKey">The employee classification key.</param>
    TEmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey);
}

Database Stored Procedures

CREATE PROCEDURE HR.CountEmployeesByClassification
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(e.EmployeeKey) AS EmployeeCount,
           ec.EmployeeClassificationKey,
           ec.EmployeeClassificationName
    FROM HR.EmployeeClassification ec
        LEFT JOIN HR.Employee e
            ON e.EmployeeClassificationKey = ec.EmployeeClassificationKey
    GROUP BY ec.EmployeeClassificationKey,
             ec.EmployeeClassificationName
    ORDER BY ec.EmployeeClassificationName;

    RETURN 0;
END;

CREATE PROCEDURE HR.CreateEmployeeClassification
    @EmployeeClassificationName VARCHAR(30),
    @IsExempt BIT,
    @IsEmployee BIT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO HR.EmployeeClassification
    (
        EmployeeClassificationName,
        IsExempt,
        IsEmployee
    )
    VALUES
    (@EmployeeClassificationName, @IsExempt, @IsEmployee);

    --Use a temp variable to ensure the correct data type
    DECLARE @EmployeeClassificationKey INT = SCOPE_IDENTITY();
    SELECT @EmployeeClassificationKey AS EmployeeClassificationKey;

    RETURN 0;
END;

CREATE PROCEDURE HR.GetEmployeeClassifications @EmployeeClassificationKey INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ec.EmployeeClassificationKey,
           ec.EmployeeClassificationName,
           ec.IsExempt,
           ec.IsEmployee
    FROM HR.EmployeeClassification ec
    WHERE (@EmployeeClassificationKey IS NULL)
          OR (ec.EmployeeClassificationKey = @EmployeeClassificationKey);

    RETURN 0;
END;

ADO.NET

public class BasicStoredProcScenario : SqlServerScenarioBase,
    IBasicStoredProcScenario<EmployeeClassification, EmployeeClassificationWithCount>
{
    public BasicStoredProcScenario(string connectionString) : base(connectionString)
    { }

    public IList<EmployeeClassificationWithCount> CountEmployeesByClassification()
    {
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand("HR.CountEmployeesByClassification", con)
        { CommandType = CommandType.StoredProcedure })
        using (var reader = cmd.ExecuteReader())
        {
            var results = new List<EmployeeClassificationWithCount>();
            while (reader.Read())
                results.Add(new EmployeeClassificationWithCount(reader));

            return results;
        }
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $"{nameof(employeeClassification)} is null.");

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand("HR.CreateEmployeeClassification", con)
        { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName",
                    employeeClassification.EmployeeClassificationName);
            cmd.Parameters.AddWithValue("@IsEmployee", employeeClassification.IsEmployee);
            cmd.Parameters.AddWithValue("@IsExempt", employeeClassification.IsExempt);

            return (int)cmd.ExecuteScalar();
        }
    }

    public IList<EmployeeClassification> GetEmployeeClassifications()
    {
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand("HR.GetEmployeeClassifications", con)
        { CommandType = CommandType.StoredProcedure })
        using (var reader = cmd.ExecuteReader())
        {
            var results = new List<EmployeeClassification>();
            while (reader.Read())
                results.Add(new EmployeeClassification(reader));

            return results;
        }
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand("HR.GetEmployeeClassifications", con)
        { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
            using (var reader = cmd.ExecuteReader())
            {
                var results = new List<EmployeeClassification>();
                if (reader.Read())
                    return new EmployeeClassification(reader);
                else
                    return null;
            }
        }
    }
}

Chain

public class BasicStoredProcScenario :
    IBasicStoredProcScenario<EmployeeClassification, EmployeeClassificationWithCount>
{
    readonly SqlServerDataSource m_DataSource;

    public BasicStoredProcScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public IList<EmployeeClassificationWithCount> CountEmployeesByClassification()
    {
        return m_DataSource.Procedure("HR.CountEmployeesByClassification")
            .ToCollection<EmployeeClassificationWithCount>().Execute();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $"{nameof(employeeClassification)} is null.");

        //Chain will ask the database which parameters it needs from the object. Other properties on the object will
        //be ignored, so there's no need to manually map it to an anonymous type.

        return m_DataSource.Procedure("HR.CreateEmployeeClassification", employeeClassification).ToInt32().Execute();
    }

    public IList<EmployeeClassification> GetEmployeeClassifications()
    {
        return m_DataSource.Procedure("HR.GetEmployeeClassifications")
            .ToCollection<EmployeeClassification>().Execute();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        return m_DataSource.Procedure("HR.GetEmployeeClassifications", new { employeeClassificationKey })
            .ToObjectOrNull<EmployeeClassification>().Execute();
    }
}

Dapper

public class BasicStoredProcScenario : ScenarioBase,
    IBasicStoredProcScenario<EmployeeClassification, EmployeeClassificationWithCount>
{
    public BasicStoredProcScenario(string connectionString) : base(connectionString)
    { }

    public IList<EmployeeClassificationWithCount> CountEmployeesByClassification()
    {
        using (var con = OpenConnection())
            return con.Query<EmployeeClassificationWithCount>("HR.CountEmployeesByClassification",
                commandType: CommandType.StoredProcedure).ToList();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $"{nameof(employeeClassification)} is null.");

        //Need to copy the parameters so we can exclude the unused EmployeeClassificationKey
        using (var con = OpenConnection())
            return con.ExecuteScalar<int>("HR.CreateEmployeeClassification",
                new
                {
                    employeeClassification.EmployeeClassificationName,
                    employeeClassification.IsEmployee,
                    employeeClassification.IsExempt
                }, commandType: CommandType.StoredProcedure);
    }

    public IList<EmployeeClassification> GetEmployeeClassifications()
    {
        using (var con = OpenConnection())
            return con.Query<EmployeeClassification>("HR.GetEmployeeClassifications",
                commandType: CommandType.StoredProcedure).ToList();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var con = OpenConnection())
            return con.Query<EmployeeClassification>("HR.GetEmployeeClassifications",
                new { employeeClassificationKey }, commandType: CommandType.StoredProcedure).SingleOrDefault();
    }
}

DbConnector

public class BasicStoredProcScenario : ScenarioBase,
    IBasicStoredProcScenario<EmployeeClassification, EmployeeClassificationWithCount>
{
    public BasicStoredProcScenario(string connectionString) : base(connectionString)
    { }

    public IList<EmployeeClassificationWithCount> CountEmployeesByClassification()
    {
        return DbConnector.ReadToList<EmployeeClassificationWithCount>("HR.CountEmployeesByClassification",
            commandType: CommandType.StoredProcedure).Execute();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $"{nameof(employeeClassification)} is null.");

        //Need to project the parameters so we can exclude the unused EmployeeClassificationKey
        return DbConnector.Scalar<int>("HR.CreateEmployeeClassification",
            new
            {
                employeeClassification.EmployeeClassificationName,
                employeeClassification.IsEmployee,
                employeeClassification.IsExempt
            }, commandType: CommandType.StoredProcedure).Execute();
    }

    public IList<EmployeeClassification> GetEmployeeClassifications()
    {
        return DbConnector.ReadToList<EmployeeClassification>("HR.GetEmployeeClassifications",
            commandType: CommandType.StoredProcedure).Execute();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        return DbConnector.ReadSingleOrDefault<EmployeeClassification>("HR.GetEmployeeClassifications",
            new { employeeClassificationKey }, commandType: CommandType.StoredProcedure).Execute();
    }
}

Entity Framework 6

TODO

Entity Framework Core

To use stored procedures that return values, a class is needed to receive the results. This is true even if a scalar value is returned.

public class EmployeeClassificationWithCount : IEmployeeClassificationWithCount
{
    public int EmployeeClassificationKey { get; set; }
    public string? EmployeeClassificationName { get; set; }
    public int EmployeeCount { get; set; }
}

public class EmployeeClassificationKeyHolder
{
    public int EmployeeClassificationKey { get; set; }
}

The receiver class should be registered in the DbContext as shown below:

partial class OrmCookbookContext : DbContext
{
    //Using "= null!;" to remove the compiler warning.
    //Assume that the DbContext constructor will populate these properties
    public virtual DbSet<EmployeeClassificationKeyHolder> EmployeeClassificationKeyHolder { get; set; } = null!;

    public virtual DbSet<EmployeeClassificationWithCount> EmployeeClassificationWithCount { get; set; } = null!;

    static void RegisterEntitiesForStoredProcedures(ModelBuilder modelBuilder)
    {
        //Output types for stored procedures. Not generated by Scaffold-DbContext

        modelBuilder.Entity<EmployeeClassificationWithCount>(entity =>
        {
            entity.HasNoKey();

            entity.Property(e => e.EmployeeClassificationName).IsUnicode(false);
        });

        modelBuilder.Entity<EmployeeClassificationKeyHolder>(entity =>
        {
            entity.HasNoKey();
        });
    }
}

public class BasicStoredProcScenario :
    IBasicStoredProcScenario<EmployeeClassification, EmployeeClassificationWithCount>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public BasicStoredProcScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public IList<EmployeeClassificationWithCount> CountEmployeesByClassification()
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassificationWithCount
                .FromSqlRaw("EXEC HR.CountEmployeesByClassification;").ToList();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $"{nameof(employeeClassification)} is null.");

        //Notes:
        //EF Core cannot return scalar values from stored procedures. A holder class is needed to receive the
        //results.
        //Named parameters are not supported, so parameter order is important.
        using (var context = CreateDbContext())
        {
            var temp = context.EmployeeClassificationKeyHolder
                  .FromSqlRaw("EXEC HR.CreateEmployeeClassification {0}, {1}, {2};",
                      employeeClassification.EmployeeClassificationName,
                      employeeClassification.IsExempt,
                      employeeClassification.IsEmployee
                  ).ToList();

            //Single isn't allowed for stored procedures. Thus ToList must be called first.
            return temp.Single().EmployeeClassificationKey;
        }
    }

    public IList<EmployeeClassification> GetEmployeeClassifications()
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassifications.FromSqlRaw("EXEC HR.GetEmployeeClassifications;").ToList();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            var temp = context.EmployeeClassifications.FromSqlRaw("EXEC HR.GetEmployeeClassifications {0};",
                employeeClassificationKey).ToList();
            //Note that SingleOrDefault isn't allowed for stored procedures. Thus ToList must be called first.
            return temp.SingleOrDefault();
        }
    }
}

LINQ to DB

To use stored procedures that return values, a class is needed to receive the results. This is true even if a scalar value is returned.

public class BasicStoredProcScenario : IBasicStoredProcScenario<EmployeeClassification, EmployeeClassificationWithCount>
{
    public IList<EmployeeClassificationWithCount> CountEmployeesByClassification()
    {
        using (var db = new OrmCookbook())
            return db.QueryProc<EmployeeClassificationWithCount>("HR.CountEmployeesByClassification").ToList();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification), $"{nameof(employeeClassification)} is null.");

        //Notes:
        //LINQ to DB cannot return scalar values from stored procedures. A holder class is needed to receive the results.
        using (var db = new OrmCookbook())
            return db.QueryProc<EmployeeClassificationKeyHolder>("HR.CreateEmployeeClassification",
                  new DataParameter("@EmployeeClassificationName", employeeClassification.EmployeeClassificationName),
                  new DataParameter("@IsExempt", employeeClassification.IsExempt),
                  new DataParameter("@IsEmployee", employeeClassification.IsEmployee)
                ).Single().EmployeeClassificationKey;
    }

    public IList<EmployeeClassification> GetEmployeeClassifications()
    {
        using (var db = new OrmCookbook())
            return db.QueryProc<EmployeeClassification>("HR.GetEmployeeClassifications").ToList();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
        {
            return db.QueryProc<EmployeeClassification>("HR.GetEmployeeClassifications",
                new DataParameter("@EmployeeClassificationKey", employeeClassificationKey)
                ).SingleOrDefault();
        }
    }
}

LLBLGen Pro

To use stored procedures that return a resultset, a class is needed to receive the results.

public class BasicStoredProcScenario : IBasicStoredProcScenario<GetEmployeeClassificationsResultRow, CountEmployeesByClassificationResultRow>
{
    public IList<CountEmployeesByClassificationResultRow> CountEmployeesByClassification()
    {
        // Use the generated calls to the procedure and pass in a generated projection to a poco to project the resultset
        // to a set of poco instances. The Fetch... method creates a new adapter instance. We could also pass one in.
        return RetrievalProcedures.FetchCountEmployeesByClassificationResultTypedView(
                                        new QueryFactory().GetCountEmployeesByClassificationResultTypedViewProjection());
    }


    public int CreateEmployeeClassification(GetEmployeeClassificationsResultRow employeeClassification)
    {
        if(employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification), $"{nameof(employeeClassification)} is null.");

        // The stored procedure in question returns the created PK value as a resultset and not as an output parameter. 
        // We therefore have to call the procedure as if it's returning a resultset. 
        var result = RetrievalProcedures.FetchCreateEmployeeClassificationResultTypedView(
                                                  new QueryFactory().GetCreateEmployeeClassificationResultTypedViewProjection(),
                                                  employeeClassification.EmployeeClassificationName, employeeClassification.IsExempt,
                                                  employeeClassification.IsEmployee)
                                        .FirstOrDefault();
        return result?.EmployeeClassificationKey ?? 0;
    }


    public IList<GetEmployeeClassificationsResultRow> GetEmployeeClassifications()
    {
        return RetrievalProcedures.FetchGetEmployeeClassificationsResultTypedView(
                                                new QueryFactory().GetGetEmployeeClassificationsResultTypedViewProjection(), 
                                                null);
    }


    public GetEmployeeClassificationsResultRow? GetEmployeeClassifications(int employeeClassificationKey)
    {
        return RetrievalProcedures.FetchGetEmployeeClassificationsResultTypedView(
                                                new QueryFactory().GetGetEmployeeClassificationsResultTypedViewProjection(), 
                                                employeeClassificationKey).FirstOrDefault();
    }
}

NHibernate

TODO

RepoDb

public class BasicStoredProcScenario : DbRepository<SqlConnection>,
    IBasicStoredProcScenario<EmployeeClassification, EmployeeClassificationWithCount>
{
    public BasicStoredProcScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public IList<EmployeeClassificationWithCount> CountEmployeesByClassification()
    {
        return ExecuteQuery<EmployeeClassificationWithCount>("[HR].[CountEmployeesByClassification]",
            commandType: CommandType.StoredProcedure).AsList();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $"{nameof(employeeClassification)} is null.");

        return ExecuteScalar<int>("[HR].[CreateEmployeeClassification]", new
        {
            employeeClassification.EmployeeClassificationName,
            employeeClassification.IsExempt,
            employeeClassification.IsEmployee
        }, commandType: CommandType.StoredProcedure);
    }

    public IList<EmployeeClassification> GetEmployeeClassifications()
    {
        return ExecuteQuery<EmployeeClassification>("[HR].[GetEmployeeClassifications]",
            commandType: CommandType.StoredProcedure).AsList();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        return ExecuteQuery<EmployeeClassification>("[HR].[GetEmployeeClassifications]",
            new { EmployeeClassificationKey = employeeClassificationKey },
            commandType: CommandType.StoredProcedure).FirstOrDefault();
    }
}

ServiceStack

public class BasicStoredProcScenario : IBasicStoredProcScenario<EmployeeClassification, EmployeeClassificationWithCount>
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public BasicStoredProcScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public IList<EmployeeClassificationWithCount> CountEmployeesByClassification()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using (var cmd = db.SqlProc("HR.CountEmployeesByClassification"))
            {
                return cmd.ConvertToList<EmployeeClassificationWithCount>();
            }
        }
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification), $"{nameof(employeeClassification)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using (var cmd = db.SqlProc("HR.CreateEmployeeClassification", new { employeeClassification.EmployeeClassificationName, employeeClassification.IsEmployee, employeeClassification.IsExempt }))
            {
                return (int) cmd.Scalar();
            }
        }
    }

    public IList<EmployeeClassification> GetEmployeeClassifications()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using (var cmd = db.SqlProc("HR.GetEmployeeClassifications"))
            {
                return cmd.ConvertToList<EmployeeClassification>();
            }
        }
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using (var cmd = db.SqlProc("HR.GetEmployeeClassifications", new { EmployeeClassificationKey = employeeClassificationKey }))
            {
                return cmd.ConvertTo<EmployeeClassification>();
            }
        }
    }
}