context: 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 QuienPaga.Api.Manager.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(!string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName))
                  _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) {
  
gt:                 if (HasPrimaryKey(item)) {