I wrote this helper to make calling SQL stored procedures easier and less error prone.

The idea here is to encapsulate the parameters of the stored procedure in an interface method that gets implemented for each stored procedure. The values of the input parameters are encapsulated in an object.

It makes use of Microsoft’s (now defunct) Enterprise Library DAAB (https://learn.microsoft.com/en-us/archive/msdn-magazine/2005/august/the-enterprise-library-data-access-application-block-part-2).

using System;
using System.Configuration;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Emmerel.Exceptions;

namespace Emmerel.Data.ADO.Net
{
    public sealed class StoredProcedure : IDisposable
    {
        private bool _disposed;

        public StoredProcedure(string spName)
        {
            if (string.IsNullOrEmpty(spName))
                throw new ArgumentException("nanme");

            Name = spName;
            Database = CreateDatabase(ConfigurationManager.AppSettings["DbName"]);
        }

        public StoredProcedure(string name, Database database)
        {
            if (string.IsNullOrEmpty(name))
                throw new ArgumentException("nanme");
            if (database == null)
                throw new ArgumentException("database");

            Name = name;
            Database = database;
        }

        public StoredProcedure(string name, string databaseName)
        {
            if (string.IsNullOrEmpty(name))
                throw new ArgumentException("name");
            if (string.IsNullOrEmpty(databaseName))
                throw new ArgumentException("databaseName");

            Database = CreateDatabase(databaseName);
            Name = name;
        }

        public DbCommand Command { get; private set; }
        public Database Database { get; }

        public string Name { get; }

        public void Dispose()
        {
            Dispose(true);
        }

        public static Database CreateDatabase(string databaseName)
        {
            if (string.IsNullOrEmpty(databaseName))
                throw new ArgumentException("databaseName");
            return new DatabaseProviderFactory().Create(databaseName);
        }

        // Creates the DbCommand object and discovers the parameters.
        public void DeriveCommand()
        {
            Command = Database.GetStoredProcCommand(Name);
            Database.DiscoverParameters(Command);
        }

        private void Dispose(bool disposing)
        {
            if (_disposed)
                return;
            if (disposing)
                Command?.Dispose();
            _disposed = true;
        }

        // Executes the supplied Action delegate.
        public void Execute(Action action)
        {
            Execute(null, action);
        }

        // Sets the Command object parameter values, executes the supplied 
        // Action delegate.
        public void Execute(IPrepareSpParameterValues instance, Action action)
        {
            Prepare(instance);
            action();
            ThrowException();
        }

        // Executes the supplied Func<T> delegate and returns T.
        public T Execute<T>(Func<T> funcT)
        {
            return Execute(null, funcT);
        }

        // Sets the Command object's parameter values, executes the supplied 
        // Func<T> delegate and returns T.
        public T Execute<T>(IPrepareSpParameterValues instance, Func<T> funcT)
        {
            Prepare(instance);
            var t = funcT();
            ThrowException();
            return t;
        }

        // Prepare for execution
        private void Prepare(IPrepareSpParameterValues values)
        {
            if (Command == null)
                DeriveCommand();

            SetParameterValues(values);
        }

        // Invoke the method to set the parameter values
        public void SetParameterValues(IPrepareSpParameterValues values)
        {
            values?.Prepare(Command);
        }

        private void ThrowException()
        {
            if (!Command.Parameters.Contains("@errorSev")) return;

            switch (Command.Parameters["@errorSev"].Value.ToString().ToUpper())
            {
                case "W":
                    throw new WarningException(Command.Parameters["@errorCode"].Value.ToString(),
                        Command.Parameters["@errorDesc"].Value.ToString());

                case "S":
                    throw new SevereException(Command.Parameters["@errorCode"].Value.ToString(),
                        Command.Parameters["@errorDesc"].Value.ToString());

                case "C":
                    throw new CriticalException(Command.Parameters["@errorCode"].Value.ToString(),
                        Command.Parameters["@errorDesc"].Value.ToString());
            }
        }
    }
}

Here’s the interface definition:

    public interface IPrepareSpParameterValues
    {
        void Prepare(DbCommand dboCommand);
    }

Here’s the definition of a class named “LoginParameters” that implements the interface “IPrepareSpParameterValues”. It encapsulates the parameters, “@email” and “@password”, of the stored procedure named “spLogin”:

    public sealed class LogInParameters : IPrepareSpParameterValues
    {
        private readonly string _email;
        private readonly string _password;

        public LogInParameters(string email, string password)
        {
            _email = email;
            _password = password;
        }

        public void Prepare(DbCommand dboCommand)
        {
            dboCommand.Parameters["@email"].Value = _email;
            dboCommand.Parameters["@password"].Value = _password;
        }
    }

Here’s the implementation of the Login method. It demonstrates how to use the StoredProcedure class to call the stored procedure named “spLogin” and how to pass the required parameters using the “LogInParameters” class:

        public Customer LogIn(string email, string password)
        {
            using (var sp = new StoredProcedure("[Customer].[spLogin]"))
            {
                using (var ds = sp.Execute(new LogInParameters(email, password),
                    () => sp.Database.ExecuteDataSet(sp.Command)))
                {
                    var c = ToCustomer(ds.Tables[0].Rows[0]);
                    return c;
                }
            }