gistfile1.txt

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Dynamic;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Collections.Specialized;
using System.Data;
using System.Configuration;
using System.Data.Common;

namespace Massive{

    public static class ObjectExtensions{

        /// <summary>
        /// Extension method for adding in a bunch of parameters
        /// </summary>
        public static void AddParams(this DbCommand cmd, object[] args) {
            foreach (var item in args) {
                AddParam(cmd,item);
            }
        }
        /// <summary>
        /// Extension for adding single parameter
        /// </summary>
        public static void AddParam(this DbCommand cmd, object item) {
            var p = cmd.CreateParameter();
            p.ParameterName = string.Format("@{0}", cmd.Parameters.Count);

            //fix for NULLs as parameter values
            if (item == null) {
                p.Value = DBNull.Value;
            } else {
                //fix for Guids
                if (item.GetType() == typeof(Guid)) {
                    p.Value = item.ToString();
                    p.DbType = DbType.String;
                } else {
                    p.Value = item;
                }
            }
            cmd.Parameters.Add(p);
        }

        /// <summary>
        /// Turns an IDataReader to a Dynamic list of things
        /// </summary>
        public static List<dynamic> ToExpandoList(this IDataReader rdr) {
            var result = new List<dynamic>();

            //work with the Expando as a Dictionary
            while (rdr.Read()) {
                dynamic e = new ExpandoObject();
                var d = e as IDictionary<string, object>;
                for (int i = 0; i < rdr.FieldCount; i++) {
                    d.Add(rdr.GetName(i), rdr[i]);
                }
                result.Add(e);
            }

            return result;
        }
        /// <summary>
        /// Turns the object into an ExpandoObject
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static dynamic ToExpando(this object o) {
            var result = new ExpandoObject();

            //work with the Expando as a Dictionary
            var d = result as IDictionary<string, object>;
            //shouldn't have to... but just in case
            if (o.GetType() == typeof(ExpandoObject)) {
                return o;
            }
            
            //special for form submissions
            if (o.GetType() == typeof(NameValueCollection)) {
                var nv = (NameValueCollection)o;
                //there's a better way to do this... I just don't know what it is...
                foreach (var item in nv.Keys) {
                    var key = item.ToString();
                    d.Add(key, nv.Get(key));
                }

            } else {
                //assume it's a regular lovely object
                var props = o.GetType().GetProperties();
                foreach (var item in props) {
                    var val = item.GetValue(o, null);
                    d.Add(item.Name, val);
                }
            }
            return result;
        }

        /// <summary>
        /// Turns the object into a Dictionary
        /// </summary>
        /// <param name="thingy"></param>
        /// <returns></returns>
        public static IDictionary<string, object> ToDictionary(this object thingy) {
            var expando = thingy.ToExpando();
            return (IDictionary<string, object>)expando;
        }   
    }

    /// <summary>
    /// A class that wraps your database table in Dynamic Funtime
    /// </summary>
    public abstract class DynamicModel:DynamicObject {
        DbProviderFactory _factory;
        string _connectionStringName;
        string _connectionString;

        public IList<dynamic> Query(string sql, params object[] args) {
            var result = new List<dynamic>();
            var cmd = CreateCommand(sql, args);
            using (var conn = OpenConnection()) {
                cmd.Connection= conn;
                var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                result = rdr.ToExpandoList();

                //can't help it - like being explicit
                rdr.Dispose();
                cmd.Dispose();
            }
            return result;
        }

        /// <summary>
        /// Creates a DBCommand that you can use for loving your database.
        /// </summary>
        DbCommand CreateCommand(string sql, params object[] args) {
            DbCommand result = null;
            result = _factory.CreateCommand();
            result.CommandText = sql;
            if(args.Length > 0)
                result.AddParams(args);
            return result;
        }
        DbConnection GetConnection() {
            var connection = _factory.CreateConnection();
            connection.ConnectionString = _connectionString;
            return connection;
        }
        DbConnection OpenConnection() {
            var conn = GetConnection();
            conn.Open();
            return conn;
        }
        /// <summary>
        /// Creates a slick, groovy little wrapper for your action
        /// </summary>
        /// <param name="connectionStringName"></param>
        public DynamicModel(string connectionStringName) {
            //can be overridden by property setting
            TableName = this.GetType().Name;
            _connectionStringName = connectionStringName;

            var providerName = "System.Data.SqlClient";
            if (ConfigurationManager.ConnectionStrings[_connectionStringName] != null) {
                providerName = ConfigurationManager.ConnectionStrings[_connectionStringName].ProviderName ?? "System.Data.SqlClient";
            } else {
                throw new InvalidOperationException("Can't find a connection string with the name '" + _connectionStringName + "'");
            }
            _factory = DbProviderFactories.GetFactory(providerName);
            _connectionString = ConfigurationManager.ConnectionStrings[_connectionStringName].ConnectionString;
        }

        string _primaryKeyField;
        /// <summary>
        /// Conventionally returns a PK field. The default is "ID" if you don't set one
        /// </summary>
        public string PrimaryKeyField {
            get {
                //a bit of convention here
                if (!string.IsNullOrEmpty(_primaryKeyField))
                    return _primaryKeyField;
                
                //oh well - did our best
                return "ID";
            }
            set {
                _primaryKeyField = value;
            }
        }

        /// <summary>
        /// Conventionally introspects the object passed in for a field that 
        /// looks like a PK. If you've named your PrimaryKeyField, this becomes easy
        /// </summary>
        public bool HasPrimaryKey(object o) {
            var result = o.ToDictionary().ContainsKey(PrimaryKeyField);
            return result;
        }
        
        /// <summary>
        /// If the object passed in has a property with the same name as your PrimaryKeyField
        /// it is returned here.
        /// </summary>
        public object GetPrimaryKey(object o) {
            var d = o.ToDictionary();
            object result = null;
            d.TryGetValue(PrimaryKeyField, out result);
            return result;
        }
        
        /// <summary>
        /// The name of the Database table we're working with. This defaults to 
        /// the class name - set this value if it's different
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// Adds a record to the database. You can pass in an Anonymous object, an ExpandoObject,
        /// A regular old POCO, or a NameValueColletion from a Request.Form or Request.QueryString
        /// </summary>
        public dynamic Insert(object o) {
            dynamic result = 0;
            
            if (BeforeInsert(o)) {
                var cmd = CreateInsertCommand(o);
                using (var conn = OpenConnection()) {
                    cmd.Connection = conn;
                    result = cmd.ExecuteScalar();
                    cmd.Dispose();
                    AfterInsert(o);
                }
            }
            return result;
        }

        /// <summary>
        /// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
        /// </summary>
        public DbCommand CreateInsertCommand(object o) {
            DbCommand result = null;

            //turn this into an expando - we'll need that for the validators
            var expando = o.ToExpando();
            var settings = (IDictionary<string, object>)expando;

            var sbKeys = new StringBuilder();
            var sbVals = new StringBuilder();
            var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2}); \r\nSELECT SCOPE_IDENTITY()";
            result = CreateCommand(stub);

            int counter = 0;
            foreach (var item in settings) {
                sbKeys.AppendFormat("{0},", item.Key);
                sbVals.AppendFormat("@{0},", counter.ToString());
                result.AddParam(item.Value);
                counter++;

            }
            if (counter > 0) {
                //strip off trailing commas
                var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
                var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
                var sql = string.Format(stub, TableName, keys, vals);
                result.CommandText = sql;
            } else {
                throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
            }  
            return result;
        }

        /// <summary>
        /// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
        /// </summary>
        public DbCommand CreateUpdateCommand(object o, object key) {
            var expando = o.ToExpando();

            var settings = (IDictionary<string, object>)expando;
            var sbKeys = new StringBuilder();
            var stub = "UPDATE {0} SET {1} WHERE {2} = @{3}";
            var args = new List<object>();
            var result = CreateCommand(stub);
            int counter = 0;
            foreach (var item in settings) {
                var val = item.Value;
                if (!item.Key.Equals(PrimaryKeyField, StringComparison.CurrentCultureIgnoreCase) && item.Value != null) {
                    result.AddParam(val);
                    sbKeys.AppendFormat("{0} = @{1}, \r\n", item.Key, counter.ToString());
                    counter++;
                }
            }
            if (counter > 0) {
                //add the key
                result.AddParam(key);
                //strip the last commas
                var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 4);
                var sql = string.Format(stub, TableName, keys, PrimaryKeyField, counter);

                result.CommandText = sql;
            } else {
                //throw
                throw new InvalidOperationException("No parsable object was sent in - could not divine any name/value pairs");
            }
            return result;
        }
        /// <summary>
        /// Updates a record in the database. You can pass in an Anonymous object, an ExpandoObject,
        /// A regular old POCO, or a NameValueColletion from a Request.Form or Request.QueryString
        /// </summary>
        public int Update(object o, object key) {
            //turn this into an expando - we'll need that for the validators
            int result = 0;
            if (BeforeUpdate(o)) {
                using (var conn = OpenConnection()) {
                    var cmd = CreateUpdateCommand(o, key);
                    result = cmd.ExecuteNonQuery();
                    AfterUpdate(o);
                }

            }
            return result;

        }
        /// <summary>
        /// Updates a bunch of records in the database within a transaction. You can pass Anonymous objects, ExpandoObjects,
        /// Regular old POCOs - these all have to have a PK set
        /// </summary>
        public int InsertMany(IEnumerable<object> things) {
            int result = 0;

            using (var conn = OpenConnection()) {
                var tx = conn.BeginTransaction();
                foreach (var item in things) {
                    if (BeforeInsert(item)) {
                        var cmd = CreateInsertCommand(item);
                        cmd.Connection = conn;
                        cmd.Transaction = tx;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        AfterInsert(item);
                    }
                    result++;
                }
                tx.Commit();
                conn.Dispose();
                tx.Dispose();
            }

            return result;

        }
        /// <summary>
        /// Updates a bunch of records in the database within a transaction. You can pass Anonymous objects, ExpandoObjects,
        /// Regular old POCOs - these all have to have a PK set
        /// </summary>
        public int UpdateMany(IEnumerable<object> things) {
            //turn this into an expando - we'll need that for the validators
            int result = 0;
            
            using (var conn = OpenConnection()) {
                var tx = conn.BeginTransaction();
                foreach (var item in things) {
                    var pk = GetPrimaryKey(item);
                    if (pk == null)
                        throw new InvalidOperationException("Please be sure to set a value for the primary key");
                    if (BeforeUpdate(item)) {
                        var cmd = CreateUpdateCommand(item, pk);
                        cmd.Connection = conn;
                        cmd.Transaction = tx;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        AfterUpdate(item);
                        result++;
                    }

                }
                tx.Commit();
                conn.Dispose();
                tx.Dispose();
            }

            return result;

        }
        /// <summary>
        /// If you're feeling lazy, or are just unsure about whether to use Update or Insert you can use
        /// this method. It will look for a PrimaryKeyField with a set value to determine if this should
        /// be an Insert or Save. You can pass in an Anonymous object, an ExpandoObject,
        /// A regular old POCO, or a NameValueColletion from a Request.Form or Request.QueryString
        /// </summary>
        public dynamic Save(object o) {
            dynamic result = 0;
            if (BeforeSave(o)) {
                var expando = o.ToExpando();
                //decide insert or update
                if (HasPrimaryKey(expando)) {
                    result = Update(expando, GetPrimaryKey(o));

                } else {
                    result = Insert(expando);
                }
                AfterSave(o);
            }
            return result;
        }    

        /// <summary>
        /// Removes a record from the database
        /// </summary>
        public int Delete(object key) {
            //execute
            var sql = string.Format("DELETE FROM {0} WHERE {1} = @0", TableName, PrimaryKeyField);
            var result = 0;
            var cmd = CreateCommand(sql, key);
            using (var conn = OpenConnection()) {
                cmd.Connection = conn;
                result = cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            return result;
        }

        /// <summary>
        /// Removes one or more records from the DB according to the passed-in WHERE
        /// </summary>
        public dynamic Delete(string where, params object[] args) {
            //execute
            var sql = string.Format("DELETE FROM {0} ", TableName);
            if (!where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase)) {
                sql += "WHERE ";
            }
            sql += where;
            var result = 0;
            var cmd = CreateCommand(sql, args);
            using (var conn = OpenConnection()) {
                cmd.Connection = conn;
                result = cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            return result;
        }

        /// <summary>
        /// Returns all records complying with the passed-in WHERE clause and arguments, 
        /// ordered as specified, limited (TOP) by limit.
        /// </summary>
        public IEnumerable<dynamic> All(string where="", string orderBy="", int limit=0, params object[] args) {
            string sql = "SELECT * FROM {0} ";
            if (limit > 0) {
                sql = "SELECT TOP "+limit+" * FROM {0} ";
            }

            if (!string.IsNullOrEmpty(where)) {
                if (where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase)) {
                    sql += where;
                } else {
                    sql += "WHERE " + where;
                }
            }

            if (!String.IsNullOrEmpty(orderBy)) {
                if (!orderBy.Trim().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase)) {
                    sql += " ORDER BY ";
                }
                sql += orderBy;
            }
            return Query(string.Format(sql, TableName), args);           
        }

        /// <summary>
        /// Returns a single row from the database
        /// </summary>
        /// <returns>ExpandoObject</returns>
        public dynamic Single(object key) {
            var sql = string.Format("SELECT * FROM {0} WHERE {1} = @0", TableName, PrimaryKeyField);
            var single = Query(sql,key).FirstOrDefault();
            return single;            
        }
        #region hooks
        //hooks for save routines
        public virtual bool BeforeInsert(object o) {
            return true;
        }
        public virtual bool BeforeUpdate(object o) {
            return true;
        }
        public virtual bool BeforeSave(object o) {
            return true;
        }
        public virtual bool BeforeDelete(object key) {
            return true;
        }

        public virtual void AfterInsert(object o) { }
        public virtual void AfterUpdate(object o) { }
        public virtual void AfterSave(object o) { }
        public virtual void AfterDelete(object key) { }
        #endregion
    }
}