DatabaseConnection.cs

gistfile1.txt

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace Infrastructure.Database
{
    // ================================
    // 1. Enum
    // ================================
    public enum Database
    {
        Main,
        Reporting,
        Finance,
        Legacy
    }

    // ================================
    // 2. DatabaseMap (resolve direto do config)
    // ================================
    public static class DatabaseMap
    {
        public static string Get(Database database)
        {
            string name;

            switch (database)
            {
                case Database.Main:
                    name = "SQL_ERP_PROD";
                    break;

                case Database.Reporting:
                    name = "SQL_REPORTING";
                    break;

                case Database.Finance:
                    name = "SQL_FIN";
                    break;

                case Database.Legacy:
                    name = "SQL_LEGACY_2008";
                    break;

                default:
                    throw new InvalidOperationException(
                        "Database not mapped: " + database);
            }

            var cs = ConfigurationManager.ConnectionStrings[name];
            if (cs == null)
                throw new InvalidOperationException(
                    "Connection string not found: " + name);

            return cs.ConnectionString;
        }
    }

    // ================================
    // 3. DatabaseConnection
    // ================================
    public sealed class DatabaseConnection
    {
        private readonly string _connectionString;

        public DatabaseConnection(Database database)
        {
            _connectionString = DatabaseMap.Get(database);
        }

        private IDbConnection Open()
        {
            var conn = new SqlConnection(_connectionString);
            conn.Open();
            return conn;
        }

        // ------------------------
        // Dapper
        // ------------------------

        public IEnumerable<T> Query<T>(string sql, object param = null)
        {
            using (var conn = Open())
                return conn.Query<T>(sql, param);
        }

        public T QuerySingleOrDefault<T>(string sql, object param = null)
        {
            using (var conn = Open())
                return conn.QuerySingleOrDefault<T>(sql, param);
        }

        public int Execute(string sql, object param = null)
        {
            using (var conn = Open())
                return conn.Execute(sql, param);
        }

        public T Scalar<T>(string sql, object param = null)
        {
            using (var conn = Open())
                return conn.ExecuteScalar<T>(sql, param);
        }

        // ------------------------
        // DataTable
        // ------------------------

        public DataTable QueryDataTable(string sql, object param = null)
        {
            using (var conn = Open())
            using (var reader = conn.ExecuteReader(sql, param))
            {
                var table = new DataTable();
                table.Load(reader);
                return table;
            }
        }
    }
}