Massive DB

/*
 Example:
 var tztable = new DynamicModel("DBZOrders_PROD",
        tableName :"aht_TimeZone",
        primaryKeyField :"ID");

    var records = tztable
        .All()
        .OrderBy(t=>t.SortOrder);

    Console.WriteLine("Count -> {0:N0}",records.Count());
    Console.WriteLine();
    foreach(var item in records) {
        string desc = item.Description;

            if(desc.Length > 20)
            desc = desc.Substring(0,20);
        Console.WriteLine("{0,-20}{1,5:N0}",desc,item.GMTDiff);
    }
 */

/*
 * See docs at the end of file
 */
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Dynamic;
using System.Linq;
using System.Text;

namespace Massive {
    public static class ObjectExtensions {
        /// <summary>
        /// Extension method for adding in a bunch of parameters
        /// </summary>
        public static void AddParams(this DbCommand cmd, params 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);
            if (item == null) {
                p.Value = DBNull.Value;
            } else {
                if (item.GetType() == typeof(Guid)) {
                    p.Value = item.ToString();
                    p.DbType = DbType.String;
                    p.Size = 4000;
                } else if (item.GetType() == typeof(ExpandoObject)) {
                    var d = (IDictionary<string, object>)item;
                    p.Value = d.Values.FirstOrDefault();
                } else {
                    p.Value = item;
                }
                if (item.GetType() == typeof(string))
                    p.Size = ((string)item).Length > 4000 ? -1 : 4000;
            }
            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>();
            while (rdr.Read()) {
                result.Add(rdr.RecordToExpando());
            }
            return result;
        }
        public static dynamic RecordToExpando(this IDataReader rdr) {
            dynamic e = new ExpandoObject();
            var d = e as IDictionary<string, object>;
            for (int i = 0; i < rdr.FieldCount; i++)
                d.Add(rdr.GetName(i), DBNull.Value.Equals(rdr[i]) ? null : rdr[i]);
            return e;
        }
        /// <summary>
        /// Turns the object into an ExpandoObject
        /// </summary>
        public static dynamic ToExpando(this object o) {
            var result = new ExpandoObject();
            var d = result as IDictionary<string, object>; //work with the Expando as a Dictionary
            if (o.GetType() == typeof(ExpandoObject)) return o; //shouldn't have to... but just in case
            if (o.GetType() == typeof(NameValueCollection) || o.GetType().IsSubclassOf(typeof(NameValueCollection))) {
                var nv = (NameValueCollection)o;
                nv.Cast<string>().Select(key => new KeyValuePair<string, object>(key, nv[key])).ToList().ForEach(i => d.Add(i));
            } else {
                var props = o.GetType().GetProperties();
                foreach (var item in props) {
                    d.Add(item.Name, item.GetValue(o, null));
                }
            }
            return result;
        }

        /// <summary>
        /// Turns the object into a Dictionary
        /// </summary>
        public static IDictionary<string, object> ToDictionary(this object thingy) {
            return (IDictionary<string, object>)thingy.ToExpando();
        }
    }

    /// <summary>
    /// Convenience class for opening/executing data
    /// </summary>
    public static class DB {
        public static DynamicModel Current {
            get {
                if (ConfigurationManager.ConnectionStrings.Count > 1) {
                    return new DynamicModel(ConfigurationManager.ConnectionStrings[1].Name);
                }
                throw new InvalidOperationException("Need a connection string name - can't determine what it is");
            }
        }
    }

    /// <summary>
    /// A class that wraps your database table in Dynamic Funtime
    /// </summary>
    public class DynamicModel : DynamicObject {
        DbProviderFactory _factory;
        string ConnectionString;
        public static DynamicModel Open(string connectionStringName) {
            dynamic dm = new DynamicModel(connectionStringName);
            return dm;
        }
        public DynamicModel(string connectionStringName, string tableName = "",
            string primaryKeyField = "", string descriptorField = "") {
            TableName = tableName == "" ? this.GetType().Name : tableName;
            PrimaryKeyField = string.IsNullOrEmpty(primaryKeyField) ? "ID" : primaryKeyField;
            DescriptorField = descriptorField;
            var _providerName = "System.Data.SqlClient";

            if(ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName != null)
                _providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;

            _factory = DbProviderFactories.GetFactory(_providerName);
            ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        }

        /// <summary>
        /// Creates a new Expando from a Form POST - white listed against the columns in the DB
        /// </summary>
        public dynamic CreateFrom(NameValueCollection coll) {
            dynamic result = new ExpandoObject();
            var dc = (IDictionary<string, object>)result;
            var schema = Schema;
            //loop the collection, setting only what's in the Schema
            foreach (var item in coll.Keys) {
                var exists = schema.Any(x => x.COLUMN_NAME.ToLower() == item.ToString().ToLower());
                if (exists) {
                    var key = item.ToString();
                    var val = coll[key];
                    dc.Add(key, val);
                }
            }
            return result;
        }
        /// <summary>
        /// Gets a default value for the column
        /// </summary>
        public dynamic DefaultValue(dynamic column) {
            dynamic result = null;
            string def = column.COLUMN_DEFAULT;
            if (String.IsNullOrEmpty(def)) {
                result = null;
            } else if (def == "getdate()" || def == "(getdate())") {
                result = DateTime.Now.ToShortDateString();
            } else if (def == "newid()") {
                result = Guid.NewGuid().ToString();
            } else {
                result = def.Replace("(", "").Replace(")", "");
            }
            return result;
        }
        /// <summary>
        /// Creates an empty Expando set with defaults from the DB
        /// </summary>
        public dynamic Prototype {
            get {
                dynamic result = new ExpandoObject();
                var schema = Schema;
                foreach (dynamic column in schema) {
                    var dc = (IDictionary<string, object>)result;
                    dc.Add(column.COLUMN_NAME, DefaultValue(column));
                }
                result._Table = this;
                return result;
            }
        }
        public string DescriptorField { get; protected set; }
        /// <summary>
        /// List out all the schema bits for use with ... whatever
        /// </summary>
        IEnumerable<dynamic> _schema;
        public IEnumerable<dynamic> Schema {
            get {
                if (_schema == null)
                    _schema = Query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0", TableName);
                return _schema;
            }
        }

        /// <summary>
        /// Enumerates the reader yielding the result - thanks to Jeroen Haegebaert
        /// </summary>
        public virtual IEnumerable<dynamic> Query(string sql, params object[] args) {
            using (var conn = OpenConnection()) {
                var rdr = CreateCommand(sql, conn, args).ExecuteReader();
                while (rdr.Read()) {
                    yield return rdr.RecordToExpando(); ;
                }
            }
        }
        public virtual IEnumerable<dynamic> Query(string sql, DbConnection connection, params object[] args) {
            using (var rdr = CreateCommand(sql, connection, args).ExecuteReader()) {
                while (rdr.Read()) {
                    yield return rdr.RecordToExpando(); ;
                }
            }
        }
        /// <summary>
        /// Returns a single result
        /// </summary>
        public virtual object Scalar(string sql, params object[] args) {
            object result = null;
            using (var conn = OpenConnection()) {
                result = CreateCommand(sql, conn, args).ExecuteScalar();
            }
            return result;
        }
        /// <summary>
        /// Creates a DBCommand that you can use for loving your database.
        /// </summary>
        DbCommand CreateCommand(string sql, DbConnection conn, params object[] args) {
            var result = _factory.CreateCommand();
            result.Connection = conn;
            result.CommandText = sql;
            if (args.Length > 0)
                result.AddParams(args);
            return result;
        }
        /// <summary>
        /// Returns and OpenConnection
        /// </summary>
        public virtual DbConnection OpenConnection() {
            var result = _factory.CreateConnection();
            result.ConnectionString = ConnectionString;
            result.Open();
            return result;
        }
        /// <summary>
        /// Builds a set of Insert and Update commands based on the passed-on objects.
        /// These objects can be POCOs, Anonymous, NameValueCollections, or Expandos. Objects
        /// With a PK property (whatever PrimaryKeyField is set to) will be created at UPDATEs
        /// </summary>
        public virtual List<DbCommand> BuildCommands(params object[] things) {
            var commands = new List<DbCommand>();
            foreach (var item in things) {
                if (HasPrimaryKey(item)) {
                    commands.Add(CreateUpdateCommand(item, GetPrimaryKey(item)));
                } else {
                    commands.Add(CreateInsertCommand(item));
                }
            }
            return commands;
        }


        public virtual int Execute(DbCommand command) {
            return Execute(new DbCommand[] { command });
        }

        public virtual int Execute(string sql, params object[] args) {
            return Execute(CreateCommand(sql, null, args));
        }
        /// <summary>
        /// Executes a series of DBCommands in a transaction
        /// </summary>
        public virtual int Execute(IEnumerable<DbCommand> commands) {
            var result = 0;
            using (var conn = OpenConnection()) {
                using (var tx = conn.BeginTransaction()) {
                    foreach (var cmd in commands) {
                        cmd.Connection = conn;
                        cmd.Transaction = tx;
                        result += cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
            }
            return result;
        }
        public virtual string PrimaryKeyField { get; set; }
        /// <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 virtual bool HasPrimaryKey(object o) {
            return o.ToDictionary().ContainsKey(PrimaryKeyField);
        }
        /// <summary>
        /// If the object passed in has a property with the same name as your PrimaryKeyField
        /// it is returned here.
        /// </summary>
        public virtual object GetPrimaryKey(object o) {
            object result = null;
            o.ToDictionary().TryGetValue(PrimaryKeyField, out result);
            return result;
        }
        public virtual string TableName { get; set; }
        /// <summary>
        /// Returns all records complying with the passed-in WHERE clause and arguments,
        /// ordered as specified, limited (TOP) by limit.
        /// </summary>
        public virtual IEnumerable<dynamic> All(string where = "", string orderBy = "", int limit = 0, string columns = "*", params object[] args) {
            string sql = BuildSelect(where, orderBy, limit);
            return Query(string.Format(sql, columns, TableName), args);
        }
        private static string BuildSelect(string where, string orderBy, int limit) {
            string sql = limit > 0 ? "SELECT TOP " + limit + " {0} FROM {1} " : "SELECT {0} FROM {1} ";
            if (!string.IsNullOrEmpty(where))
                sql += where.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase) ? where : " WHERE " + where;
            if (!String.IsNullOrEmpty(orderBy))
                sql += orderBy.Trim().StartsWith("order by", StringComparison.OrdinalIgnoreCase) ? orderBy : " ORDER BY " + orderBy;
            return sql;
        }

        /// <summary>
        /// Returns a dynamic PagedResult. Result properties are Items, TotalPages, and TotalRecords.
        /// </summary>
        public virtual dynamic Paged(string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
        {
            return BuildPagedResult(where: where, orderBy: orderBy, columns: columns, pageSize: pageSize, currentPage: currentPage, args: args);
        }

        public virtual dynamic Paged(string sql, string primaryKey, string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
        {
            return BuildPagedResult(sql, primaryKey, where, orderBy, columns, pageSize, currentPage, args);
        }

        private dynamic BuildPagedResult(string sql = "", string primaryKeyField = "", string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
        {
            dynamic result = new ExpandoObject();
            var countSQL = "";
            if (!string.IsNullOrEmpty(sql))
                countSQL = string.Format("SELECT COUNT({0}) FROM ({1}) AS PagedTable", primaryKeyField, sql);
            else
                countSQL = string.Format("SELECT COUNT({0}) FROM {1}", PrimaryKeyField, TableName);

            if (String.IsNullOrEmpty(orderBy))
            {
                orderBy = string.IsNullOrEmpty(primaryKeyField) ? PrimaryKeyField : primaryKeyField;
            }

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

            var query = "";
            if (!string.IsNullOrEmpty(sql))
                query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {2}) AS Row, {0} FROM ({3}) AS PagedTable {4}) AS Paged ", columns, pageSize, orderBy, sql, where);
            else
                query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {2}) AS Row, {0} FROM {3} {4}) AS Paged ", columns, pageSize, orderBy, TableName, where);

            var pageStart = (currentPage - 1) * pageSize;
            query += string.Format(" WHERE Row > {0} AND Row <={1}", pageStart, (pageStart + pageSize));
            countSQL += where;
            result.TotalRecords = Scalar(countSQL, args);
            result.TotalPages = result.TotalRecords / pageSize;
            if (result.TotalRecords % pageSize > 0)
                result.TotalPages += 1;
            result.Items = Query(string.Format(query, columns, TableName), args);
            return result;
        }
        /// <summary>
        /// Returns a single row from the database
        /// </summary>
        public virtual dynamic Single(string where, params object[] args) {
            var sql = string.Format("SELECT * FROM {0} WHERE {1}", TableName, where);
            return Query(sql, args).FirstOrDefault();
        }
        /// <summary>
        /// Returns a single row from the database
        /// </summary>
        public virtual dynamic Single(object key, string columns = "*") {
            var sql = string.Format("SELECT {0} FROM {1} WHERE {2} = @0", columns, TableName, PrimaryKeyField);
            return Query(sql, key).FirstOrDefault();
        }
        /// <summary>
        /// This will return a string/object dictionary for dropdowns etc
        /// </summary>
        public virtual IDictionary<string, object> KeyValues(string orderBy = "") {
            if (String.IsNullOrEmpty(DescriptorField))
                throw new InvalidOperationException("There's no DescriptorField set - do this in your constructor to describe the text value you want to see");
            var sql = string.Format("SELECT {0},{1} FROM {2} ", PrimaryKeyField, DescriptorField, TableName);
            if (!String.IsNullOrEmpty(orderBy))
                sql += "ORDER BY " + orderBy;

            var results = Query(sql).ToList().Cast<IDictionary<string, object>>();
            return results.ToDictionary(key => key[PrimaryKeyField].ToString(), value => value[DescriptorField]);
        }

        /// <summary>
        /// This will return an Expando as a Dictionary
        /// </summary>
        public virtual IDictionary<string, object> ItemAsDictionary(ExpandoObject item) {
            return (IDictionary<string, object>)item;
        }
        //Checks to see if a key is present based on the passed-in value
        public virtual bool ItemContainsKey(string key, ExpandoObject item) {
            var dc = ItemAsDictionary(item);
            return dc.ContainsKey(key);
        }
        /// <summary>
        /// Executes a set of objects as Insert or Update commands based on their property settings, within a transaction.
        /// These objects can be POCOs, Anonymous, NameValueCollections, or Expandos. Objects
        /// With a PK property (whatever PrimaryKeyField is set to) will be created at UPDATEs
        /// </summary>
        public virtual int Save(params object[] things) {
            foreach (var item in things) {
                if (!IsValid(item)) {
                    throw new InvalidOperationException("Can't save this item: " + String.Join("; ", Errors.ToArray()));
                }
            }
            var commands = BuildCommands(things);
            return Execute(commands);
        }
        public virtual DbCommand CreateInsertCommand(dynamic expando) {
            DbCommand result = null;
            var settings = (IDictionary<string, object>)expando;
            var sbKeys = new StringBuilder();
            var sbVals = new StringBuilder();
            var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2})";
            result = CreateCommand(stub, null);
            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) {
                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 virtual DbCommand CreateUpdateCommand(dynamic expando, object key) {
            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, null);
            int counter = 0;
            foreach (var item in settings) {
                var val = item.Value;
                if (!item.Key.Equals(PrimaryKeyField, StringComparison.OrdinalIgnoreCase) && 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);
                result.CommandText = string.Format(stub, TableName, keys, PrimaryKeyField, counter);
            } else throw new InvalidOperationException("No parsable object was sent in - could not divine any name/value pairs");
            return result;
        }
        /// <summary>
        /// Removes one or more records from the DB according to the passed-in WHERE
        /// </summary>
        public virtual DbCommand CreateDeleteCommand(string where = "", object key = null, params object[] args) {
            var sql = string.Format("DELETE FROM {0} ", TableName);
            if (key != null) {
                sql += string.Format("WHERE {0}=@0", PrimaryKeyField);
                args = new object[] { key };
            } else if (!string.IsNullOrEmpty(where)) {
                sql += where.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase) ? where : "WHERE " + where;
            }
            return CreateCommand(sql, null, args);
        }

        public bool IsValid(dynamic item) {
            Errors.Clear();
            Validate(item);
            return Errors.Count == 0;
        }

        //Temporary holder for error messages
        public IList<string> Errors = new List<string>();
        /// <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 virtual dynamic Insert(object o) {
            var ex = o.ToExpando();
            if (!IsValid(ex)) {
                throw new InvalidOperationException("Can't insert: " + String.Join("; ", Errors.ToArray()));
            }
            if (BeforeSave(ex)) {
                using (dynamic conn = OpenConnection()) {
                    var cmd = CreateInsertCommand(ex);
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT @@IDENTITY as newID";
                    ex.ID = cmd.ExecuteScalar();
                    Inserted(ex);
                }
                return ex;
            } else {
                return null;
            }
        }
        /// <summary>
        /// Updates a record in the database. You can pass in an Anonymous object, an ExpandoObject,
        /// A regular old POCO, or a NameValueCollection from a Request.Form or Request.QueryString
        /// </summary>
        public virtual int Update(object o, object key) {
            var ex = o.ToExpando();
            if (!IsValid(ex)) {
                throw new InvalidOperationException("Can't Update: " + String.Join("; ", Errors.ToArray()));
            }
            var result = 0;
            if (BeforeSave(ex)) {
                result = Execute(CreateUpdateCommand(ex, key));
                Updated(ex);
            }
            return result;
        }
        /// <summary>
        /// Removes one or more records from the DB according to the passed-in WHERE
        /// </summary>
        public int Delete(object key = null, string where = "", params object[] args) {
            var deleted = this.Single(key);
            var result = 0;
            if (BeforeDelete(deleted)) {
                result = Execute(CreateDeleteCommand(where: where, key: key, args: args));
                Deleted(deleted);
            }
            return result;
        }

        public void DefaultTo(string key, object value, dynamic item) {
            if (!ItemContainsKey(key, item)) {
                var dc = (IDictionary<string, object>)item;
                dc[key] = value;
            }
        }

        //Hooks
        public virtual void Validate(dynamic item) { }
        public virtual void Inserted(dynamic item) { }
        public virtual void Updated(dynamic item) { }
        public virtual void Deleted(dynamic item) { }
        public virtual bool BeforeDelete(dynamic item) { return true; }
        public virtual bool BeforeSave(dynamic item) { return true; }

        //validation methods
        public virtual void ValidatesPresenceOf(object value, string message = "Required") {
            if (value == null)
                Errors.Add(message);
            if (String.IsNullOrEmpty(value.ToString()))
                Errors.Add(message);
        }
        //fun methods
        public virtual void ValidatesNumericalityOf(object value, string message = "Should be a number") {
            var type = value.GetType().Name;
            var numerics = new string[] { "Int32", "Int16", "Int64", "Decimal", "Double", "Single", "Float" };
            if (!numerics.Contains(type)) {
                Errors.Add(message);
            }
        }
        public virtual void ValidateIsCurrency(object value, string message = "Should be money") {
            if (value == null)
                Errors.Add(message);
            decimal val = decimal.MinValue;
            decimal.TryParse(value.ToString(), out val);
            if (val == decimal.MinValue)
                Errors.Add(message);


        }
        public int Count() {
            return Count(TableName);
        }
        public int Count(string tableName, string where="") {
            return (int)Scalar("SELECT COUNT(*) FROM " + tableName+" "+where);
        }

        /// <summary>
        /// A helpful query tool
        /// </summary>
        public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result) {
            //parse the method
            var constraints = new List<string>();
            var counter = 0;
            var info = binder.CallInfo;
            // accepting named args only... SKEET!
            if (info.ArgumentNames.Count != args.Length) {
                throw new InvalidOperationException("Please use named arguments for this type of query - the column name, orderby, columns, etc");
            }
            //first should be "FindBy, Last, Single, First"
            var op = binder.Name;
            var columns = " * ";
            string orderBy = string.Format(" ORDER BY {0}", PrimaryKeyField);
            string sql = "";
            string where = "";
            var whereArgs = new List<object>();

            //loop the named args - see if we have order, columns and constraints
            if (info.ArgumentNames.Count > 0) {

                for (int i = 0; i < args.Length; i++) {
                    var name = info.ArgumentNames[i].ToLower();
                    switch (name) {
                        case "orderby":
                            orderBy = " ORDER BY " + args[i];
                            break;
                        case "columns":
                            columns = args[i].ToString();
                            break;
                        default:
                            constraints.Add(string.Format(" {0} = @{1}", name, counter));
                            whereArgs.Add(args[i]);
                            counter++;
                            break;
                    }
                }
            }

            //Build the WHERE bits
            if (constraints.Count > 0) {
                where = " WHERE " + string.Join(" AND ", constraints.ToArray());
            }
            //probably a bit much here but... yeah this whole thing needs to be refactored...
            if (op.ToLower() == "count") {
                result = Scalar("SELECT COUNT(*) FROM " + TableName + where, whereArgs.ToArray());
            } else if (op.ToLower() == "sum") {
                result = Scalar("SELECT SUM(" + columns + ") FROM " + TableName + where, whereArgs.ToArray());
            } else if (op.ToLower() == "max") {
                result = Scalar("SELECT MAX(" + columns + ") FROM " + TableName + where, whereArgs.ToArray());
            } else if (op.ToLower() == "min") {
                result = Scalar("SELECT MIN(" + columns + ") FROM " + TableName + where, whereArgs.ToArray());
            } else if (op.ToLower() == "avg") {
                result = Scalar("SELECT AVG(" + columns + ") FROM " + TableName + where, whereArgs.ToArray());
            } else {

                //build the SQL
                sql = "SELECT TOP 1 " + columns + " FROM " + TableName + where;
                var justOne = op.StartsWith("First") || op.StartsWith("Last") || op.StartsWith("Get") || op.StartsWith("Single");

                //Be sure to sort by DESC on the PK (PK Sort is the default)
                if (op.StartsWith("Last")) {
                    orderBy = orderBy + " DESC ";
                } else {
                    //default to multiple
                    sql = "SELECT " + columns + " FROM " + TableName + where;
                }

                if (justOne) {
                    //return a single record
                    result = Query(sql + orderBy, whereArgs.ToArray()).FirstOrDefault();
                } else {
                    //return lots
                    result = Query(sql + orderBy, whereArgs.ToArray());
                }
            }
            return true;
        }
    }
}

/*
 Massive is a Single File Database Lover. It's Better Than Chocolate. No Really.
===============================================================================


I'm sharing this with the world because we need another way to access data - don't you think? Truthfully - I wanted to see if I could flex the C# 4 stuff and
run up data access with a single file. I used to have this down to 350 lines, but you also needed to reference WebMatrix.Data. Now you don't - this is ready to roll
and weighs in at a lovely 524 lines of code. Most of which is comments.

How To Install It?
------------------
Drop the code file into your app and change it as you wish.

How Do You Use It?
------------------
Massive is a "wrapper" for your DB tables and uses System.Dynamic extensively. If you try to use this with C# 3.5 or below, it will explode and you will be sad. Me too honestly - I like how this doesn't require any DLLs other than what's in the GAC. Yippee.

 * Get a Database. Northwind will work nicely. Add a connection to your database in your web.config (or app.config). Don't forget the providerName! If you don't know what that is - just add providerName = 'System.Data.SqlClient' right after the whole connectionString stuff.
 * Create a class that wraps a table. You can call it whatever you like, but if you want to be cool just name it the same as your table.
 * Query away and have fun

Code Please
-----------
Let's say we have a table named "Products". You create a class like this:

```csharp
public class Products:DynamicModel {
    //you don't have to specify the connection - Massive will use the first one it finds in your config
    public Products():base("northwind", "products","productid") {}
}
```

You could also just instantiate it inline, as needed:

```csharp
var tbl = new DynamicModel("northwind", tableName:"Products", primaryKeyField:"ProductID");
```

Or ignore the object hierarchy altogether:

```csharp
Massive.DB.Current.Query(...);
```

Now you can query thus:

```csharp
var table = new Products();
//grab all the products
var products = table.All();
//just grab from category 4. This uses named parameters
var productsFour = table.All(columns: "ProductName as Name", where: "WHERE categoryID=@0",args: 4);
```

That works, but Massive is "dynamic" - which means that it can figure a lot of things out on the fly. That query above can be rewritten like this:

```csharp
dynamic table = new Products(); //"dynamic" is important here - don't use "var"!
var productsFour = table.Find(CategoryID:4,columns:"ProductName");
```

The "Find" method doesn't exist, but since Massive is dynamic it will try to infer what you mean by using DynamicObject's TryInvokeMember. See the source for more details. There's more on the dynamic query stuff down below.

You can also run ad-hoc queries as needed:

```csharp
var result = tbl.Query("SELECT * FROM Categories");
```

This will pull categories and enumerate the results - streaming them as opposed to bulk-fetching them (thanks to Jeroen Haegebaert for the code). If you need to run a Fetch - you can:

```csharp
var result = tbl.Fetch("SELECT * FROM Categories");
```

If you want to have a paged result set - you can:

```csharp
var result = tbl.Paged(where: "UnitPrice > 20", currentPage:2, pageSize: 20);
```

In this example, ALL of the arguments are optional and default to reasonable values. CurrentPage defaults to 1, pageSize defaults to 20, where defaults to nothing.

What you get back is `IEnumerable<ExpandoObject>` - meaning that it's malleable and exciting. It will take the shape of whatever you return in your query, and it will have properties and so on. You can assign events to it, you can create delegates on the fly. You can give it chocolate, and it will kiss you.

That's pretty much it. One thing I really like is the groovy DSL that Massive uses - it looks just like SQL. If you want, you can use this DSL to query the database:

```csharp
var table = new Products();
var productsThatILike = table.Query("SELECT ProductName, CategoryName FROM Products INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID WHERE CategoryID = @0",5);
//get down!
```

Some of you might look at that and think it looks suspiciously like inline SQL. It *does* look sort of like it doesn't it! But I think it reads a bit better than Linq to SQL - it's a bit closer to the mark if you will.

Inserts and Updates
-------------------
Massive is built on top of dynamics - so if you send an object to a table, it will get parsed into a query. If that object has a property on it that matches the primary key, Massive will think you want to update something. Unless you tell it specifically to update it.

You can send just about anything into the MassiveTransmoQueryfier and it will magically get turned into SQL:

```csharp
var table = new Products();
var poopy = new {ProductName = "Chicken Fingers"};
//update Product with ProductID = 12 to have a ProductName of "Chicken Fingers"
table.Update(poopy, 12);
```

This also works if you have a form on your web page with the name "ProductName" - then you submit it:

```csharp
var table = new Products();
//update Product with ProductID = 12 to have a ProductName of whatever was submitted via the form
table.Update(poopy, Request.Form);
```

Insert works the same way:

```csharp
//pretend we have a class like Products but it's called Categories
var table = new Categories();
//do it up - the new ID will be returned from the query
var newID = table.Insert(new {CategoryName = "Buck Fify Stuff", Description = "Things I like"});
```

Yippee Skippy! Now we get to the fun part - and one of the reasons I had to spend 150 more lines of code on something you probably won't care about. What happens when we send a whole bunch of goodies to the database at once!

```csharp
var table = new Products();
//OH NO YOU DIDN't just pass in an integer inline without a parameter!
//I think I might have... yes
var drinks = table.All("WHERE CategoryID = 8");
//what we get back here is an IEnumerable < ExpandoObject > - we can go to town
foreach(var item in drinks.ToArray()){
    //turn them into Haack Snacks
    item.CategoryID = 12;
}
//Let's update these in bulk, in a transaction shall we?
table.Save(drinks.ToArray());
```

Named Argument Query Syntax
-------------------
I recently added the ability to run more friendly queries using Named Arguments and C#4's Method-on-the-fly syntax. Originally this was trying to be like ActiveRecord, but I figured "C# is NOT Ruby, and Named Arguments can be a lot more clear". In addition, Mark Rendle's Simple.Data is already doing this so ... why duplicate things?

If your needs are more complicated - I would suggest just passing in your own SQL with Query().

```csharp
//important - must be dynamic
dynamic table = new Products();

var drinks = table.FindBy(CategoryID:8);
//what we get back here is an IEnumerable < ExpandoObject > - we can go to town
foreach(var item in drinks){
    Console.WriteLine(item.ProductName);
}
//returns the first item in the DB for category 8
var first = table.First(CategoryID:8);

//you dig it - the last as sorted by PK
var last = table.Last(CategoryID:8);

//you can order by whatever you like
var firstButReallyLast = table.First(CategoryID:8,OrderBy:"PK DESC");

//only want one column?
var price = table.First(CategoryID:8,Columns:"UnitPrice").UnitPrice;

//Multiple Criteria?
var items = table.Find(CategoryID:5, UnitPrice:100, OrderBy:"UnitPrice DESC");
```

Aggregates with Named Arguments
-------------------------------
You can do the same thing as above for aggregates:

```csharp
var sum = table.Sum(columns:Price, CategoryID:5);
var avg = table.Avg(columns:Price, CategoryID:3);
var min = table.Min(columns:ID);
var max = table.Max(columns:CreatedOn);
var count = table.Count();
```

Metadata
--------
If you find that you need to know information about your table - to generate some lovely things like ... whatever - just ask for the Schema property. This will query INFORMATION_SCHEMA for you, and you can take a look at DATA_TYPE, DEFAULT_VALUE, etc for whatever system you're running on.

In addition, if you want to generate an empty instance of a column - you can now ask for a "Prototype()" - which will return all the columns in your table with the defaults set for you (getdate(), raw values, newid(), etc).

Factory Constructor
-------------------
One thing that can be useful is to use Massive to just run a quick query. You can do that now by using "Open()" which is a static builder on DynamicModel:

```csharp
var db = Massive.DynamicModel.Open("myConnectionStringName");
```

You can execute whatever you like at that point.

Validations
-----------
One thing that's always needed when working with data is the ability to stop execution if something isn't right. Massive now has Validations, which are built with the Rails approach in mind:

```csharp
public class Productions:DynamicModel {
    public Productions():base("MyConnectionString","Productions","ID") {}
    public override void Validate(dynamic item) {
        ValidatesPresenceOf("Title");
        ValidatesNumericalityOf(item.Price);
        ValidateIsCurrency(item.Price);
        if (item.Price <= 0)
            Errors.Add("Price can't be negative");
    }
}
```

The idea here is that `Validate()` is called prior to Insert/Update. If it fails, an Error collection is populated and an InvalidOperationException is thrown. That simple. With each of the validations above, a message can be passed in.

CallBacks
---------
Need something to happen after Update/Insert/Delete? Need to halt before save? Massive has callbacks to let you do just that:

```csharp
public class Customers:DynamicModel {
    public Customers():base("MyConnectionString","Customers","ID") {}

    //Add the person to Highrise CRM when they're added to the system...
    public override void Inserted(dynamic item) {
        //send them to Highrise
        var svc = new HighRiseApi();
        svc.AddPerson(...);
    }
}
```

The callbacks you can use are:

 * Inserted
 * Updated
 * Deleted
 * BeforeDelete
 * BeforeSave



 */