Here is the SQL data layer implementation which I use in all of my services. This class keeps all database access uniform and through stored procedures. It also requires a structure for stored procedures to follow, in order to support error handling.

  • Stored Procedures
    • Must take an @errormessage NVARCHAR(2048) OUTPUT parameter
    • Must have an INT return value for their error code
  • Result Sets
    • Each result set must have a function which can parse it from a reader row.

raw

/// <summary>
/// The sql manager class.
/// </summary>
public sealed class SqlManager
{
    private readonly string connectionString;

    /// <summary>
    /// Initializes a new instance of the <see cref="SqlManager"/> class.
    /// </summary>
    /// <param name="connectionString"></param>
    public SqlManager(string connectionString)
    {
        this.connectionString = connectionString;
    }

    /// <summary>
    /// Execute a non-query.
    /// </summary>
    /// <param name="storedProcedure">The stored proc.</param>
    /// <param name="parameters">The paramters.</param>
    /// <returns>An async task.</returns>
    public async Task Execute(string storedProcedure, Action<SqlParameterCollection> parameters)
    {
        using (var connection = new SqlConnection(this.connectionString))
        {
            await connection.OpenAsync();
            using (var command = connection.CreateCommand())
            {
                // Initialize parameters
                parameters(command.Parameters);

                // Setup the stored proc.
                SqlManager.InitializeCommandSqlCommand(storedProcedure, command);

                await command.ExecuteNonQueryAsync();

                // Make sure there is no error.
                SqlManager.HandleCustomErrors(command);
            }
        }
    }

    /// <summary>
    /// Execute an SQL reader.
    /// </summary>
    /// <typeparam name="T1">The first type.</typeparam>
    /// <param name="storedProcedure">The stored procedure name.</param>
    /// <param name="parameters">The parameter changer.</param>
    /// <param name="reader1">The first reader.</param>
    /// <returns>The result sets.</returns>
    public Task<IReadOnlyCollection<T1>> Execute<T1>(string storedProcedure, Action<SqlParameterCollection> parameters, Func<SqlDataReader, T1> reader1)
        where T1 : class
    {
        return this.ExecuteInternal(storedProcedure, parameters, reader1).ContinueOnSuccess(result =>
        {
            return result[0].Cast<T1>().AsReadonly();
        });
    }

    /// <summary>
    /// Execute an SQL reader.
    /// </summary>
    /// <typeparam name="T1">The first type.</typeparam>
    /// <typeparam name="T2">The second type.</typeparam>
    /// <param name="storedProcedure">The stored procedure name.</param>
    /// <param name="parameters">The parameter changer.</param>
    /// <param name="reader1">The first reader.</param>
    /// <param name="reader2">The second reader.</param>
    /// <returns>The result sets.</returns>
    public Task<(IReadOnlyCollection<T1>, IReadOnlyCollection<T2>)> Execute<T1, T2>(string storedProcedure, Action<SqlParameterCollection> parameters, Func<SqlDataReader, T1> reader1, Func<SqlDataReader, T2> reader2)
        where T1 : class
        where T2 : class
    {
        return this.ExecuteInternal(storedProcedure, parameters, reader1, reader2).ContinueOnSuccess(result =>
        {
            return (result[0].Cast<T1>().AsReadonly(), result[1].Cast<T2>().AsReadonly());
        });
    }

    /// <summary>
    /// Execute an SQL reader.
    /// </summary>
    /// <typeparam name="T1">The first type.</typeparam>
    /// <typeparam name="T2">The second type.</typeparam>
    /// <typeparam name="T3">The third type.</typeparam>
    /// <param name="storedProcedure">The stored procedure name.</param>
    /// <param name="parameters">The parameter changer.</param>
    /// <param name="reader1">The first reader.</param>
    /// <param name="reader2">The second reader.</param>
    /// <returns>The result sets.</returns>
    public Task<(IReadOnlyCollection<T1>, IReadOnlyCollection<T2>, IReadOnlyCollection<T3>)> Execute<T1, T2, T3>(string storedProcedure, Action<SqlParameterCollection> parameters, Func<SqlDataReader, T1> reader1, Func<SqlDataReader, T2> reader2, Func<SqlDataReader, T3> reader3)
        where T1 : class
        where T2 : class
        where T3 : class
    {
        return this.ExecuteInternal(storedProcedure, parameters, reader1, reader2, reader3).ContinueOnSuccess(result =>
        {
            return (result[0].Cast<T1>().AsReadonly(), result[1].Cast<T2>().AsReadonly(), result[2].Cast<T3>().AsReadonly());
        });
    }

    /// <summary>
    /// Modified an sql command to be a stored proc.
    /// </summary>
    /// <param name="storedProcedure">The stored procedure name.</param>
    /// <param name="command">The command to modify.</param>
    private static void InitializeCommandSqlCommand(string storedProcedure, SqlCommand command)
    {
        // Stored proc
        command.CommandText = storedProcedure;
        command.CommandType = CommandType.StoredProcedure;

        var error = new SqlParameter("errormessage", SqlDbType.NVarChar, 2048){ Direction = ParameterDirection.Output };
        var returnValue = new SqlParameter("ReturnValue", SqlDbType.Int, 4) { Direction = ParameterDirection.ReturnValue };

        command.Parameters.Add(error);
        command.Parameters.Add(returnValue);
    }

    /// <summary>
    /// Handle errors from SQL.
    /// </summary>
    /// <param name="command">The command.</param>
    private static void HandleCustomErrors(SqlCommand command)
    {
        var retvalue = command.Parameters["ReturnValue"]?.Value as int? ?? 0;
        var message = command.Parameters["errormessage"]?.Value as string;

        // These match errors.sql
        switch (retvalue)
        {
            case 0:
                return;
            case 50001:
                throw new InvalidArgumentException(message ?? "Invalid argument exception.");
            case 50002:
                throw new ItemNotFoundException(message ?? "The item was not found.");
            case 50003:
                throw new DuplicateItemViolationException(message ?? "Duplicate item detected.");

            default:
                if (retvalue > 50000)
                {
                    throw new NotImplementedException(message ?? $"Handling for error {retvalue} is missing.");
                }
                else
                {
                    throw new Exception(message ?? "SQL failure. Generic. No error. Everything's fucked. #Yolo.");
                }
        }

    }

    /// <summary>
    /// Execute an SQL data reader.
    /// </summary>
    /// <param name="storedProcedure">The stored procedure name.</param>
    /// <param name="parameters">The parameter setter upper.</param>
    /// <param name="readers">The data reader functions.</param>
    /// <returns>All of the results.</returns>
    private async Task<IReadOnlyCollection<object>[]> ExecuteInternal(string storedProcedure, Action<SqlParameterCollection> parameters, params Func<SqlDataReader, object>[] readers)
    {
        using (var connection = new SqlConnection(this.connectionString))
        {
            await connection.OpenAsync();
            using (var command = connection.CreateCommand())
            {
                // Initialize parameters
                parameters(command.Parameters);

                // Setup the stored proc.
                SqlManager.InitializeCommandSqlCommand(storedProcedure, command);

                var result = new IReadOnlyCollection<object>[readers.Length];

                using (var reader = await command.ExecuteReaderAsync())
                {
                    // Read each result set
                    for (var i = 0; i < readers.Length; i++)
                    {
                        // Make sure there is no error.
                        SqlManager.HandleCustomErrors(command);

                        var list = new List<object>();
                        while (await reader.ReadAsync())
                        {
                            list.Add(readers[i](reader));
                        }

                        result[i] = list;

                        // Something done messed up
                        if(false == await reader.NextResultAsync() && i < readers.Length - 1)
                        {
                            // Try to eat the soft error. This throws an exception on failure.
                            SqlManager.HandleCustomErrors(command);

                            throw new DataException($"Expected {readers.Length} result sets. Got {i + 1}");
                        }
                    }

                    // After execution, one last check to make sure all is well.
                    SqlManager.HandleCustomErrors(command);
                }

                return result;
            }
        }
    }
}

The datalayer is used as follows:

raw

internal sealed class RatingsDataLayer : IRatingDataLayer
{
    /// <summary>
    /// The sql manager.
    /// </summary>
    private readonly SqlManager sqlManager;

    public RatingsDataLayer(string connectionString)
    {
        this.sqlManager = new SqlManager(connectionString);
    }

    /// <summary>
    /// Rate a user.
    /// </summary>
    /// <param name="rating">The rating.</param>
    /// <returns>An async task.</returns>
    public Task RateUser(UserRatingData rating)
    {
        return this.sqlManager.Execute("ronny.rateuser", parameters =>
        {
            parameters.AddWithValue("source", rating.SourceUser);
            parameters.AddWithValue("target", rating.TargetUser);
            parameters.AddWithValue("value", rating.Value);
        });
    }

    /// <summary>
    /// Get ratings for a user.
    /// </summary>
    /// <param name="top">The top.</param>
    /// <param name="skip">The skip.</param>
    /// <param name="targetUser">The target user.</param>
    /// <param name="sourceUser">The source user.</param>
    /// <param name="minValue">The min value.</param>
    /// <param name="maxValue">The max value.</param>
    /// <param name="startRange">The start date range.</param>
    /// <param name="endRange">The end date range.</param>
    /// <returns>The ratings.</returns>
    public Task<IReadOnlyCollection<UserRatingData>> GetRatings(int? top, int? skip, Guid? targetUser, Guid? sourceUser, int? minValue, int? maxValue, DateTime? startRange, DateTime? endRange)
    {
        return this.sqlManager.Execute("ronny.getratings", parameters =>
        {
            parameters.AddWithValue("top", top);
            parameters.AddWithValue("skip", skip);
            parameters.AddWithValue("source", sourceUser);
            parameters.AddWithValue("target", targetUser);
            parameters.AddWithValue("startdate", startRange);
            parameters.AddWithValue("enddate", endRange);
            parameters.AddWithValue("minvalue", minValue);
            parameters.AddWithValue("maxvalue", maxValue);
        },
        UserRatingData.FromReader);
    }
}

raw

/// <summary>
/// From an sql data reader.
/// </summary>
/// <param name="reader">The reader.</param>
/// <returns>The user rating..</returns>
internal static UserRatingData FromReader(SqlDataReader reader)
{
    return new UserRatingData(
        (Guid)reader["targetid"],
        (Guid)reader["sourceid"],
        (DateTime)reader["whenoccured"],
        (byte)reader["rating"]);
}

raw

CREATE PROCEDURE ronny.getratings
    @errormessage   NVARCHAR(2048)          OUTPUT
   ,@top            INT                     = 100
   ,@skip           INT                     = 0
   ,@source         UNIQUEIDENTIFIER        = NULL
   ,@target         UNIQUEIDENTIFIER        = NULL
   ,@startdate      DATETIME2               = NULL
   ,@enddate        DATETIME2               = NULL
   ,@minvalue       TINYINT                 = NULL
   ,@maxvalue       TINYINT                 = NULL
AS
    DECLARE @error            INT       = 0

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    SELECT 
        r.targetid
       ,r.sourceid
       ,r.whenoccured
       ,r.rating
    FROM ronny.ratings r
    WHERE
        (@source IS NULL OR r.sourceid = @source)
    AND (@target IS NULL OR r.targetid = @target)
    AND (@startdate IS NULL OR r.whenoccured >= @startdate)
    AND (@enddate IS NULL OR r.whenoccured <= @enddate)
    AND (@minvalue IS NULL OR r.rating >= @minvalue)
    AND (@maxvalue IS NULL OR r.rating <= @maxvalue)
    ORDER BY r.whenoccured
    OFFSET (@skip) ROWS
    FETCH NEXT (@top) ROWS ONLY

RETURN 0

There you have it!