Data Helpers

Database.cs

//
//  Copyright 2013, Desert Software Solutions Inc.
//    Database.cs: https://gist.github.com/rostreim/9453953
//
//    Licensed under the Apache License, Version 2.0 (the "License");
//    you may not use this file except in compliance with the License.
//    You may obtain a copy of the License at
//
//        http://www.apache.org/licenses/LICENSE-2.0
//
//    Unless required by applicable law or agreed to in writing, software
//    distributed under the License is distributed on an "AS IS" BASIS,
//    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
//    See the License for the specific language governing permissions and
//    limitations under the License.
//

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;

/// <summary>
/// Generic methods for accessing a database using dapper.net
/// </summary>
public class Database
{
    private ConnectionFactory connectionFactory;

    /// <summary>
    /// Initializes a new instance of the <see cref="Database"/> class.
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    internal Database(string connectionString) {
        this.connectionFactory = new ConnectionFactory(connectionString);
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="Database"/> class.
    /// </summary>
    /// <param name="connectionFactory">The <see cref="ConnectionFactory"/> instance.</param>
    internal Database(ConnectionFactory connectionFactory) {
        this.connectionFactory = connectionFactory;
    }

    /// <summary>
    /// Creates the connection.
    /// </summary>
    /// <returns></returns>
    internal SqlConnection CreateConnection() {
        return this.connectionFactory.CreateConnection();
    }

    /// <summary>
    /// Executes the specified SQL.
    /// </summary>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <returns></returns>
    internal int Execute(string sql, dynamic parameters = null, CommandType? commandType = null) {
        using (SqlConnection connection = CreateConnection()) {
            connection.Open();

            try {
                return Execute(connection, sql, parameters, commandType);
            } finally {
                connection.Close();
            }
        }
    }

    /// <summary>
    /// Executes the specified SQL on the specified connection.
    /// </summary>
    /// <param name="connection">The connection.</param>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <returns></returns>
    internal int Execute(SqlConnection connection, string sql, dynamic parameters = null, CommandType? commandType = null) {
        return connection.Execute(sql, new DynamicParameters(parameters), commandType: commandType);
    }

    /// <summary>
    /// Returns the first result for the specified SQL.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <returns></returns>
    internal T First<T>(string sql, dynamic parameters = null, CommandType? commandType = null) {
        using (SqlConnection connection = CreateConnection()) {
            connection.Open();

            try {
                return First<T>(connection, sql, parameters, commandType);
            } finally {
                connection.Close();
            }
        }
    }

    /// <summary>
    /// Returns the first result for the specified SQL.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="connection">The connection.</param>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <returns></returns>
    internal T First<T>(SqlConnection connection, string sql, dynamic parameters = null, CommandType? commandType = null) {
        return connection.Query<T>(sql, new DynamicParameters(parameters), commandType: commandType).FirstOrDefault();
    }

    /// <summary>
    /// Returns the result list of the specified SQL.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <returns></returns>
    internal List<T> ListOf<T>(string sql, dynamic parameters = null, CommandType? commandType = null) {
        using (SqlConnection connection = CreateConnection()) {
            connection.Open();

            try {
                return ListOf<T>(connection, sql, parameters, commandType);
            } finally {
                connection.Close();
            }
        }
    }

    /// <summary>
    /// Returns the result list of the specified SQL.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="connection">The connection.</param>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <returns></returns>
    internal List<T> ListOf<T>(SqlConnection connection, string sql, dynamic parameters = null, CommandType? commandType = null) {
        return connection.Query<T>(sql, new DynamicParameters(parameters), commandType: commandType).ToList();
    }

    /// <summary>
    /// Connection Factory class that provides a simple connection abstraction
    /// Inherit this class to provide a connection factory that dispenses connections in a manner that suits 
    /// your security and environment requirements
    /// </summary>
    internal class ConnectionFactory
    {
        protected string connectionString = "";

        /// <summary>
        /// Initializes a new instance of the <see cref="ConnectionFactory"/> class.
        /// </summary>
        protected ConnectionFactory() { }

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

        /// <summary>
        /// Creates a connection.
        /// </summary>
        /// <returns></returns>
        public virtual SqlConnection CreateConnection() {
            return new SqlConnection(this.connectionString);
        }
    }
}

Transaction.cs

//
//  Copyright 2013, Desert Software Solutions Inc.
//   Transaction.cs: https://gist.github.com/rostreim/9453953
//
//    Licensed under the Apache License, Version 2.0 (the "License");
//    you may not use this file except in compliance with the License.
//    You may obtain a copy of the License at
//
//        http://www.apache.org/licenses/LICENSE-2.0
//
//    Unless required by applicable law or agreed to in writing, software
//    distributed under the License is distributed on an "AS IS" BASIS,
//    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
//    See the License for the specific language governing permissions and
//    limitations under the License.
//

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

public abstract class Transaction
{
    private SqlConnection connection;
    private SqlTransaction transaction;

    /// <summary>
    /// Executes the specified SQL on the specified connection.
    /// </summary>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <returns></returns>
    internal int Execute(string sql, dynamic parameters = null, CommandType? commandType = null) {
        return this.connection.Execute(sql, new DynamicParameters(parameters), transaction: this.transaction, commandType: commandType);
    }

    /// <summary>
    /// Returns the first result for the specified SQL.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameters">The parameters.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <returns></returns>
    internal T First<T>(string sql, dynamic parameters = null, CommandType? commandType = null) {
        return this.connection.Query<T>(sql, new DynamicParameters(parameters), transaction: this.transaction, commandType: commandType).FirstOrDefault();
    }

    /// <summary>
    /// Begins the transaction.
    /// </summary>
    /// <param name="db">The database this transaction will be executed on.</param>
    protected void Begin(Database db) {
        this.connection = db.CreateConnection();

        this.connection.Open();
        this.transaction = this.connection.BeginTransaction();
    }

    public abstract void Begin();

    /// <summary>
    /// Commits the transaction.
    /// </summary>
    public void Commit() {
        try {
            if (this.transaction != null)
                this.transaction.Commit();
        } finally {
            this.connection.Close();
        }
    }

    /// <summary>
    /// Rolls back the transaction.
    /// </summary>
    public void Rollback() {
        try {
            if (this.transaction != null)
                this.transaction.Rollback();
        } finally {
            this.connection.Close();
        }
    }
}