2 using System.Collections.Generic;
3 using System.Collections.Specialized;
4 using System.Configuration;
6 using System.Data.Common;
12 public static class ObjectExtensions {
14 /// Extension method for adding in a bunch of parameters
16 public static void AddParams(this DbCommand cmd, object[] args) {
17 foreach (var item in args) {
22 /// Extension for adding single parameter
24 public static void AddParam(this DbCommand cmd, object item) {
25 var p = cmd.CreateParameter();
26 p.ParameterName = string.Format("@{0}", cmd.Parameters.Count);
27 //fix for NULLs as parameter values
29 p.Value = DBNull.Value;
32 if (item.GetType() == typeof(Guid)) {
33 p.Value = item.ToString();
34 p.DbType = DbType.String;
39 cmd.Parameters.Add(p);
42 /// Turns an IDataReader to a Dynamic list of things
44 public static List<dynamic> ToExpandoList(this IDataReader rdr) {
45 var result = new List<dynamic>();
46 //work with the Expando as a Dictionary
48 dynamic e = new ExpandoObject();
49 var d = e as IDictionary<string, object>;
50 for (int i = 0; i < rdr.FieldCount; i++)
51 d.Add(rdr.GetName(i), rdr[i]);
57 /// Turns the object into an ExpandoObject
59 /// <param name="o"></param>
60 /// <returns></returns>
61 public static dynamic ToExpando(this object o) {
62 var result = new ExpandoObject();
63 var d = result as IDictionary<string, object>; //work with the Expando as a Dictionary
64 if (o.GetType() == typeof(ExpandoObject)) return o; //shouldn't have to... but just in case
65 //special for form submissions
66 if (o.GetType() == typeof(NameValueCollection)) {
67 var nv = (NameValueCollection)o;
68 nv.Cast<string>().Select(key => new KeyValuePair<string, object>(key, nv[key])).ToList().ForEach(i => d.Add(i));
70 //assume it's a regular lovely object
71 var props = o.GetType().GetProperties();
72 foreach (var item in props) {
73 d.Add(item.Name, item.GetValue(o, null));
79 /// Turns the object into a Dictionary
81 /// <param name="thingy"></param>
82 /// <returns></returns>
83 public static IDictionary<string, object> ToDictionary(this object thingy) {
84 return (IDictionary<string, object>)thingy.ToExpando();
88 /// A class that wraps your database table in Dynamic Funtime
90 public abstract class DynamicModel : DynamicObject {
91 DbProviderFactory _factory;
92 string _connectionStringName;
93 string _connectionString;
95 public IList<dynamic> Query(string sql, params object[] args) {
96 var result = new List<dynamic>();
97 using (var conn = OpenConnection()) {
98 using (var cmd = CreateCommand(sql, args)) {
99 cmd.Connection = conn;
100 using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
101 result = rdr.ToExpandoList();
108 /// Creates a DBCommand that you can use for loving your database.
110 DbCommand CreateCommand(string sql, params object[] args) {
111 DbCommand result = null;
112 result = _factory.CreateCommand();
113 result.CommandText = sql;
115 result.AddParams(args);
118 DbConnection GetConnection() {
119 var connection = _factory.CreateConnection();
120 connection.ConnectionString = _connectionString;
123 DbConnection OpenConnection() {
124 var conn = GetConnection();
129 /// Creates a slick, groovy little wrapper for your action
131 /// <param name="connectionStringName"></param>
132 public DynamicModel(string connectionStringName) {
133 //can be overridden by property setting
134 TableName = this.GetType().Name;
135 _connectionStringName = connectionStringName;
137 var providerName = "System.Data.SqlClient";
138 if (ConfigurationManager.ConnectionStrings[_connectionStringName] != null) {
139 providerName = ConfigurationManager.ConnectionStrings[_connectionStringName].ProviderName ?? "System.Data.SqlClient";
141 throw new InvalidOperationException("Can't find a connection string with the name '" + _connectionStringName + "'");
143 _factory = DbProviderFactories.GetFactory(providerName);
144 _connectionString = ConfigurationManager.ConnectionStrings[_connectionStringName].ConnectionString;
146 string _primaryKeyField;
148 /// Conventionally returns a PK field. The default is "ID" if you don't set one
150 public string PrimaryKeyField {
151 get { return string.IsNullOrEmpty(_primaryKeyField) ? /*a bit of convention here*/ "ID" : /*oh well - did our best*/ _primaryKeyField; }
152 set { _primaryKeyField = value; }
155 /// Conventionally introspects the object passed in for a field that
156 /// looks like a PK. If you've named your PrimaryKeyField, this becomes easy
158 public bool HasPrimaryKey(object o) {
159 var result = o.ToDictionary().ContainsKey(PrimaryKeyField);
163 /// If the object passed in has a property with the same name as your PrimaryKeyField
164 /// it is returned here.
166 public object GetPrimaryKey(object o) {
167 var d = o.ToDictionary();
168 object result = null;
169 d.TryGetValue(PrimaryKeyField, out result);
173 /// The name of the Database table we're working with. This defaults to
174 /// the class name - set this value if it's different
176 public string TableName { get; set; }
178 /// Adds a record to the database. You can pass in an Anonymous object, an ExpandoObject,
179 /// A regular old POCO, or a NameValueColletion from a Request.Form or Request.QueryString
181 public dynamic Insert(object o) {
183 if (BeforeInsert(o)) {
184 using (var conn = OpenConnection()) {
185 using (var cmd = CreateInsertCommand(o)) {
186 cmd.Connection = conn;
187 result = cmd.ExecuteScalar();
196 /// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
198 public DbCommand CreateInsertCommand(object o) {
199 DbCommand result = null;
200 //turn this into an expando - we'll need that for the validators
201 var expando = o.ToExpando();
202 var settings = (IDictionary<string, object>)expando;
203 var sbKeys = new StringBuilder();
204 var sbVals = new StringBuilder();
205 var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2}); \r\nSELECT SCOPE_IDENTITY()";
206 result = CreateCommand(stub);
209 foreach (var item in settings) {
210 sbKeys.AppendFormat("{0},", item.Key);
211 sbVals.AppendFormat("@{0},", counter.ToString());
212 result.AddParam(item.Value);
216 //strip off trailing commas
217 var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
218 var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
219 var sql = string.Format(stub, TableName, keys, vals);
220 result.CommandText = sql;
221 } else throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
226 /// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
228 public DbCommand CreateUpdateCommand(object o, object key) {
229 var expando = o.ToExpando();
230 var settings = (IDictionary<string, object>)expando;
231 var sbKeys = new StringBuilder();
232 var stub = "UPDATE {0} SET {1} WHERE {2} = @{3}";
233 var args = new List<object>();
234 var result = CreateCommand(stub);
236 foreach (var item in settings) {
237 var val = item.Value;
238 if (!item.Key.Equals(PrimaryKeyField, StringComparison.CurrentCultureIgnoreCase) && item.Value != null) {
239 result.AddParam(val);
240 sbKeys.AppendFormat("{0} = @{1}, \r\n", item.Key, counter.ToString());
246 result.AddParam(key);
247 //strip the last commas
248 var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 4);
249 result.CommandText = string.Format(stub, TableName, keys, PrimaryKeyField, counter);
250 } else throw new InvalidOperationException("No parsable object was sent in - could not divine any name/value pairs");
254 /// Updates a record in the database. You can pass in an Anonymous object, an ExpandoObject,
255 /// A regular old POCO, or a NameValueCollection from a Request.Form or Request.QueryString
257 public int Update(object o, object key) {
258 //turn this into an expando - we'll need that for the validators
260 if (BeforeUpdate(o)) {
261 using (var conn = OpenConnection()) {
262 using (var cmd = CreateUpdateCommand(o, key)) {
263 result = cmd.ExecuteNonQuery();
271 /// Updates a bunch of records in the database within a transaction. You can pass Anonymous objects, ExpandoObjects,
272 /// Regular old POCOs - these all have to have a PK set
274 public int InsertMany(IEnumerable<object> things) {
276 using (var conn = OpenConnection()) {
277 using (var tx = conn.BeginTransaction()) {
278 foreach (var item in things) {
279 if (BeforeInsert(item)) {
280 using (var cmd = CreateInsertCommand(item)) {
281 cmd.Connection = conn;
282 cmd.Transaction = tx;
283 cmd.ExecuteNonQuery();
295 /// Updates a bunch of records in the database within a transaction. You can pass Anonymous objects, ExpandoObjects,
296 /// Regular old POCOs - these all have to have a PK set
298 public int UpdateMany(IEnumerable<object> things) {
300 using (var conn = OpenConnection()) {
301 using (var tx = conn.BeginTransaction()) {
302 foreach (var item in things) {
303 var pk = GetPrimaryKey(item);
305 throw new InvalidOperationException("Please be sure to set a value for the primary key");
306 if (BeforeUpdate(item)) {
307 using (var cmd = CreateUpdateCommand(item, pk)) {
308 cmd.Connection = conn;
309 cmd.Transaction = tx;
310 cmd.ExecuteNonQuery();
322 /// If you're feeling lazy, or are just unsure about whether to use Update or Insert you can use
323 /// this method. It will look for a PrimaryKeyField with a set value to determine if this should
324 /// be an Insert or Save. You can pass in an Anonymous object, an ExpandoObject,
325 /// A regular old POCO, or a NameValueColletion from a Request.Form or Request.QueryString
327 public dynamic Save(object o) {
330 var expando = o.ToExpando();
331 //decide insert or update
332 result = HasPrimaryKey(expando) ? Update(expando, GetPrimaryKey(o)) : Insert(expando);
338 /// Removes a record from the database
340 public int Delete(object key) {
342 var sql = string.Format("DELETE FROM {0} WHERE {1} = @0", TableName, PrimaryKeyField);
344 using (var conn = OpenConnection()) {
345 using (var cmd = CreateCommand(sql, key)) {
346 cmd.Connection = conn;
347 result = cmd.ExecuteNonQuery();
353 /// Removes one or more records from the DB according to the passed-in WHERE
355 public dynamic Delete(string where, params object[] args) {
357 var sql = string.Format("DELETE FROM {0} ", TableName);
358 sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "WHERE " + where;
360 using (var conn = OpenConnection()) {
361 using (var cmd = CreateCommand(sql, args)) {
362 cmd.Connection = conn;
363 result = cmd.ExecuteNonQuery();
369 /// Returns all records complying with the passed-in WHERE clause and arguments,
370 /// ordered as specified, limited (TOP) by limit.
372 public IEnumerable<dynamic> All(string where = "", string orderBy = "", int limit = 0, params object[] args) {
373 string sql = limit > 0 ? "SELECT TOP " + limit + " * FROM {0} " : "SELECT * FROM {0} ";
374 if (!string.IsNullOrEmpty(where))
375 sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "WHERE " + where;
376 if (!String.IsNullOrEmpty(orderBy))
377 sql += orderBy.Trim().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase) ? orderBy : " ORDER BY " + orderBy;
378 return Query(string.Format(sql, TableName), args);
381 /// Returns a single row from the database
383 /// <returns>ExpandoObject</returns>
384 public dynamic Single(object key) {
385 var sql = string.Format("SELECT * FROM {0} WHERE {1} = @0", TableName, PrimaryKeyField);
386 return Query(sql, key).FirstOrDefault();
389 //hooks for save routines
390 public virtual bool BeforeInsert(object o) { return true; }
391 public virtual bool BeforeUpdate(object o) { return true; }
392 public virtual bool BeforeSave(object o) { return true; }
393 public virtual bool BeforeDelete(object key) { return true; }
394 public virtual void AfterInsert(object o) { }
395 public virtual void AfterUpdate(object o) { }
396 public virtual void AfterSave(object o) { }
397 public virtual void AfterDelete(object key) { }