jeffreyabecker

/*
** 02/06/2009
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
**
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace JB.DbExample
{
    public class DbParameter
    {
        public string Name { get; set; }
        public object Value { get; set; }
    }

    public interface IDbManager : IDisposable
    {
        /// <summary>
        /// Executes a procedure returning the results as a DataSet
        /// </summary>
        /// <param name="procName">The name of the stored procedure</param>
        /// <param name="args">the argument name/value pairs for the procedure</param>
        /// <returns>the resuts as a data set</returns>
        DataSet Exec(string procName, params DbParameter[] args);

        /// <summary>
        /// Executes some sql returning the results as a DataSet
        /// </summary>
        /// <param name="sql">the sql to be executed</param>
        /// <param name="args">the argument name/value pairs for the sql</param>
        /// <returns>the resuts as a data set</returns>
        DataSet ExecDynamicSql(string sql, params DbParameter[] args);

        /// <summary>
        /// Executes a stored procedure and returns a single result
        /// </summary>
        /// <typeparam name="T">The type of the result</typeparam>
        /// <param name="procName">the name of hte procedure</param>
        /// <param name="args">the argument name/value pairs for the procedure</param>
        /// <returns></returns>
        T Scalar<T>(string procName, params DbParameter[] args);

        /// <summary>
        /// Executes some sql returning a single result
        /// </summary>
        /// <typeparam name="T">The type of the result</typeparam>
        /// <param name="sql">the sql to be executed</param>
        /// <param name="args">the argument name/value pairs for the sql</param>
        /// <returns></returns>
        T ScalarDynamicSql<T>(string sql, params DbParameter[] args);

        /// <summary>
        /// Begins a transaction.
        /// </summary>
        void Begin();

        /// <summary>
        /// Commits a transaction.
        /// </summary>
        void Commit();
        /// <summary>
        /// Rolls a transaction back.
        /// </summary>
        void Rollback();
    }
    /// <summary>
    /// Provides an "anonymous object as dictionary" interface to IDbManager
    /// </summary>
    public static class DbManagerExtensions
    {
        // This class contains Extension Methods which are a new feature in C# 3f

        //this method uses reflection to extract an object into name-value pairsf
        private static DbParameter[] GetArgsFromObject(object obj)
        {
            List<DbParameter> parameters = new List<DbParameter>();
            foreach (var propertyInfo in obj.GetType().GetProperties())
            {
                parameters.Add(new DbParameter() { Name = propertyInfo.Name, Value = propertyInfo.GetValue(obj, null) });
            }
            return parameters.ToArray();
        }

        /// <summary>
        /// Executes a procedure returning the results as a DataSet
        /// </summary>
        /// <param name="db">the DbManager</param>
        /// <param name="procName">The name of the stored procedure</param>
        /// <param name="argsDict">the argument name/value pairs for the procedure</param>
        /// <returns>the resuts as a data set</returns>
        public static DataSet Exec(this IDbManager db, string procName, object argsDict)
        {
            return db.Exec(procName, GetArgsFromObject(argsDict));
        }

        /// <summary>
        /// Executes some sql returning the results as a DataSet
        /// </summary>
        /// <param name="db">the DbManager</param>
        /// <param name="sql">the sql to be executed</param>
        /// <param name="argsDict">the argument name/value pairs for the sql</param>
        /// <returns>the resuts as a data set</returns>
        public static DataSet ExecDynamicSql(this IDbManager db, string sql, object argsDict)
        {
            return db.ExecDynamicSql(sql, GetArgsFromObject(argsDict));
        }
        /// <summary>
        /// Executes a stored procedure and returns a single result
        /// </summary>
        /// <param name="db">the DbManager</param>
        /// <typeparam name="T">The type of the result</typeparam>
        /// <param name="procName">the name of hte procedure</param>
        /// <param name="argsDict">the argument name/value pairs for the procedure</param>
        /// <returns></returns>
        public static T Scalar<T>(this IDbManager db, string procName, object argsDict)
        {
            return db.Scalar<T>(procName, GetArgsFromObject(argsDict));
        }
        /// <summary>
        /// Executes some sql returning a single result
        /// </summary>
        /// <param name="db">the DbManager</param>
        /// <typeparam name="T">The type of the result</typeparam>
        /// <param name="sql">the sql to be executed</param>
        /// <param name="argsDict">the argument name/value pairs for the sql</param>
        /// <returns></returns>
        public static T ScalarDynamicSql<T>(this IDbManager db, string sql, object argsDict)
        {
            return db.ScalarDynamicSql<T>(sql, GetArgsFromObject(argsDict));
        }
    }

    public class DbManagerFactory
    {
        public static IDbManager GetDbManager(string connectionStringName)
        {
            return new SqlDbManager(System.Configuration.ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString);
        }

        // this is the actual implementation of IDbManager.  It should never be instantiated outside the factory class
        private class SqlDbManager : IDbManager
        {
            string _connectionString;
            SqlConnection _conn;
            SqlTransaction _trans;

            public SqlDbManager(string connectionString)
            {
                _connectionString = connectionString;
            }

            private SqlConnection GetConnection()
            {
                if (_conn == null)
                {
                    _conn = new SqlConnection(_connectionString);
                    _conn.Open();
                }
                return _conn;
            }

            #region IDbManager Members

            public DataSet Exec(string procName, params DbParameter[] args)
            {
                SqlCommand cmd = new SqlCommand(procName, GetConnection());
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (DbParameter p in args)
                {
                    cmd.Parameters.AddWithValue(p.Name, p.Value);
                }
                DataSet ds = new DataSet();
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(ds);
                return ds;
            }

            public DataSet ExecDynamicSql(string sql, params DbParameter[] args)
            {
                SqlCommand cmd = new SqlCommand(sql, GetConnection());
                cmd.CommandType = CommandType.Text;
                foreach (DbParameter p in args)
                {
                    cmd.Parameters.AddWithValue(p.Name, p.Value);
                }
                DataSet ds = new DataSet();
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(ds);
                return ds;
            }

            public T Scalar<T>(string procName, params DbParameter[] args)
            {
                DataSet ds = Exec(procName, args);
                return ds.Tables[0].Rows[0].Field<T>(0);
            }

            public T ScalarDynamicSql<T>(string sql, params DbParameter[] args)
            {
                DataSet ds = ExecDynamicSql(sql, args);
                return ds.Tables[0].Rows[0].Field<T>(0);
            }


            public void Begin()
            {
                if (_trans != null)
                {
                    throw new DataException("Attempted to begin a transaction with one already pending.");
                }
                SqlConnection conn = GetConnection();
                _trans = conn.BeginTransaction();
            }

            public void Commit()
            {
                if (_trans == null)
                    throw new DataException("Attempted to commit without a pending transaction.");
                _trans.Commit();
                _trans.Dispose();
                _trans = null;
            }

            public void Rollback()
            {
                if (_trans == null)
                    throw new DataException("Attempted to roll back without a pending transaction.");
                _trans.Rollback();
                _trans.Dispose();
                _trans = null;
            }
            #endregion

            #region IDisposable Members

            public void Dispose()
            {
                if (_trans != null)
                {
                    _trans.Rollback();
                    _trans.Dispose();
                    throw new DataException("Disposed a connection with a pending transaction.  Make sure to explicitly commit or rollback your transactions");
                }
                if (_conn != null)
                {
                    _conn.Dispose();
                }

            }

            #endregion

        }
    }
}