Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 464 lines (451 sloc) 21.926 kb
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
1 using System;
2 using System.Collections.Generic;
3 using System.Collections.Specialized;
4 using System.Configuration;
5 using System.Data;
6 using System.Data.Common;
7 using System.Dynamic;
8 using System.Linq;
9 using System.Text;
10 using System.Threading.Tasks;
11 using System.Data.SqlClient;
12
13 namespace Massive {
14 public static class ObjectExtensions {
15 /// <summary>
16 /// Extension method for adding in a bunch of parameters
17 /// </summary>
18 public static void AddParams(this DbCommand cmd, params object[] args) {
19 foreach (var item in args) {
20 AddParam(cmd, item);
21 }
22 }
23 /// <summary>
24 /// Extension for adding single parameter
25 /// </summary>
26 public static void AddParam(this DbCommand cmd, object item) {
27 var p = cmd.CreateParameter();
28 p.ParameterName = string.Format("@{0}", cmd.Parameters.Count);
29 if (item == null) {
30 p.Value = DBNull.Value;
31 } else {
32 if (item.GetType() == typeof(Guid)) {
33 p.Value = item.ToString();
34 p.DbType = DbType.String;
35 p.Size = 4000;
36 } else if (item.GetType() == typeof(ExpandoObject)) {
37 var d = (IDictionary<string, object>)item;
38 p.Value = d.Values.FirstOrDefault();
39 } else {
40 p.Value = item;
41 }
42 if (item.GetType() == typeof(string))
43 p.Size = ((string)item).Length > 4000 ? -1 : 4000;
44 }
45 cmd.Parameters.Add(p);
46 }
47 /// <summary>
48 /// Turns an IDataReader to a Dynamic list of things
49 /// </summary>
50 public static List<dynamic> ToExpandoList(this IDataReader rdr) {
51 var result = new List<dynamic>();
52 while (rdr.Read()) {
53 result.Add(rdr.RecordToExpando());
54 }
55 return result;
56 }
57 public static dynamic RecordToExpando(this IDataReader rdr) {
58 dynamic e = new ExpandoObject();
59 var d = e as IDictionary<string, object>;
007d200 When returning data from a Query, convert DBNull values to null so can u...
David Clarke authored
60 for (int i = 0; i < rdr.FieldCount; i++)
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
61 d.Add(rdr.GetName(i), DBNull.Value.Equals(rdr[i]) ? null : rdr[i]);
62 return e;
63 }
64 /// <summary>
65 /// Turns the object into an ExpandoObject
66 /// </summary>
67 public static dynamic ToExpando(this object o) {
68 var result = new ExpandoObject();
69 var d = result as IDictionary<string, object>; //work with the Expando as a Dictionary
70 if (o.GetType() == typeof(ExpandoObject)) return o; //shouldn't have to... but just in case
71 if (o.GetType() == typeof(NameValueCollection) || o.GetType().IsSubclassOf(typeof(NameValueCollection))) {
72 var nv = (NameValueCollection)o;
73 nv.Cast<string>().Select(key => new KeyValuePair<string, object>(key, nv[key])).ToList().ForEach(i => d.Add(i));
74 } else {
75 var props = o.GetType().GetProperties();
76 foreach (var item in props) {
77 d.Add(item.Name, item.GetValue(o, null));
78 }
79 }
80 return result;
81 }
82 /// <summary>
83 /// Turns the object into a Dictionary
84 /// </summary>
85 public static IDictionary<string, object> ToDictionary(this object thingy) {
86 return (IDictionary<string, object>)thingy.ToExpando();
87 }
88 }
89 /// <summary>
90 /// A class that wraps your database table in Dynamic Funtime
91 /// </summary>
47c98a8 @subsonic Cleaned up a bit of stuff
subsonic authored
92 public class DynamicModel : DynamicObject {
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
93 DbProviderFactory _factory;
94 string _connectionString;
95
47c98a8 @subsonic Cleaned up a bit of stuff
subsonic authored
96 public DynamicModel(string connectionStringName,string tableName = "", string primaryKeyField = "") {
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
97 TableName = tableName == "" ? this.GetType().Name : tableName;
98 PrimaryKeyField = string.IsNullOrEmpty(primaryKeyField) ? "ID" : primaryKeyField;
99 var _providerName = "System.Data.SqlClient";
100 _factory = DbProviderFactories.GetFactory(_providerName);
101 _connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
102 }
103 /// <summary>
bb4497e @subsonic Added a Schema property that returns, dynamically, all the information_s...
subsonic authored
104 /// List out all the schema bits for use with ... whatever
105 /// </summary>
106 public IEnumerable<dynamic> Schema {
107 get {
108 return Query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0", TableName);
109 }
110 }
111
112 /// <summary>
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
113 /// Enumerates the reader yielding the result - thanks to Jeroen Haegebaert
114 /// </summary>
115 public virtual IEnumerable<dynamic> Query(string sql, params object[] args) {
116 using (var conn = OpenConnection()) {
117 var rdr = CreateCommand(sql, conn, args).ExecuteReader();
118 while (rdr.Read()) {
119 yield return rdr.RecordToExpando(); ;
120 }
121 }
122 }
123 /// <summary>
6424009 updates to async API
unknown authored
124 /// Executes the reader using SQL async API - thanks to Damian Edwards
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
125 /// </summary>
126 public void QueryAsync(string sql, Action<List<dynamic>> callback, params object[] args) {
6424009 updates to async API
unknown authored
127 using (var conn = new SqlConnection(_connectionString)) {
128 var cmd = new SqlCommand(sql, new SqlConnection(_connectionString));
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
129 cmd.AddParams(args);
47c98a8 @subsonic Cleaned up a bit of stuff
subsonic authored
130 cmd.Connection.Open();
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
131 var task = Task.Factory.FromAsync<IDataReader>(cmd.BeginExecuteReader, cmd.EndExecuteReader, null);
132 task.ContinueWith(x => callback.Invoke(x.Result.ToExpandoList()));
133 }
134 }
bb4497e @subsonic Added a Schema property that returns, dynamically, all the information_s...
subsonic authored
135
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
136 public virtual IEnumerable<dynamic> Query(string sql, DbConnection connection, params object[] args) {
137 using (var rdr = CreateCommand(sql, connection, args).ExecuteReader()) {
138 while (rdr.Read()) {
139 yield return rdr.RecordToExpando(); ;
140 }
141 }
142 }
143 /// <summary>
144 /// Returns a single result
145 /// </summary>
146 public virtual object Scalar(string sql, params object[] args) {
147 object result = null;
148 using (var conn = OpenConnection()) {
149 result = CreateCommand(sql, conn, args).ExecuteScalar();
150 }
151 return result;
152 }
153 /// <summary>
154 /// Creates a DBCommand that you can use for loving your database.
155 /// </summary>
156 DbCommand CreateCommand(string sql, DbConnection conn, params object[] args) {
157 var result = _factory.CreateCommand();
158 result.Connection = conn;
159 result.CommandText = sql;
160 if (args.Length > 0)
161 result.AddParams(args);
162 return result;
163 }
164 /// <summary>
165 /// Returns and OpenConnection
166 /// </summary>
167 public virtual DbConnection OpenConnection() {
168 var result = _factory.CreateConnection();
169 result.ConnectionString = _connectionString;
170 result.Open();
171 return result;
172 }
173 /// <summary>
174 /// Builds a set of Insert and Update commands based on the passed-on objects.
175 /// These objects can be POCOs, Anonymous, NameValueCollections, or Expandos. Objects
176 /// With a PK property (whatever PrimaryKeyField is set to) will be created at UPDATEs
177 /// </summary>
178 public virtual List<DbCommand> BuildCommands(params object[] things) {
179 var commands = new List<DbCommand>();
180 foreach (var item in things) {
181 if (HasPrimaryKey(item)) {
182 commands.Add(CreateUpdateCommand(item, GetPrimaryKey(item)));
183 } else {
184 commands.Add(CreateInsertCommand(item));
185 }
186 }
187 return commands;
188 }
189 /// <summary>
190 /// Executes a set of objects as Insert or Update commands based on their property settings, within a transaction.
191 /// These objects can be POCOs, Anonymous, NameValueCollections, or Expandos. Objects
192 /// With a PK property (whatever PrimaryKeyField is set to) will be created at UPDATEs
193 /// </summary>
194 public virtual int Save(params object[] things) {
195 var commands = BuildCommands(things);
196 return Execute(commands);
197 }
bb4497e @subsonic Added a Schema property that returns, dynamically, all the information_s...
subsonic authored
198
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
199 public virtual int Execute(DbCommand command) {
200 return Execute(new DbCommand[] { command });
201 }
202 /// <summary>
203 /// Executes a series of DBCommands in a transaction
204 /// </summary>
205 public virtual int Execute(IEnumerable<DbCommand> commands) {
206 var result = 0;
207 using (var conn = OpenConnection()) {
208 using (var tx = conn.BeginTransaction()) {
209 foreach (var cmd in commands) {
210 cmd.Connection = conn;
211 cmd.Transaction = tx;
212 result += cmd.ExecuteNonQuery();
213 }
214 tx.Commit();
215 }
216 }
217 return result;
218 }
219 public virtual string PrimaryKeyField { get; set; }
220 /// <summary>
221 /// Conventionally introspects the object passed in for a field that
222 /// looks like a PK. If you've named your PrimaryKeyField, this becomes easy
223 /// </summary>
224 public virtual bool HasPrimaryKey(object o) {
225 return o.ToDictionary().ContainsKey(PrimaryKeyField);
226 }
227 /// <summary>
228 /// If the object passed in has a property with the same name as your PrimaryKeyField
229 /// it is returned here.
230 /// </summary>
231 public virtual object GetPrimaryKey(object o) {
232 object result = null;
233 o.ToDictionary().TryGetValue(PrimaryKeyField, out result);
234 return result;
235 }
236 public virtual string TableName { get; set; }
237 /// <summary>
238 /// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
239 /// </summary>
240 public virtual DbCommand CreateInsertCommand(object o) {
241 DbCommand result = null;
242 var expando = o.ToExpando();
243 var settings = (IDictionary<string, object>)expando;
244 var sbKeys = new StringBuilder();
245 var sbVals = new StringBuilder();
246 var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2})";
247 result = CreateCommand(stub, null);
248 int counter = 0;
249 foreach (var item in settings) {
250 sbKeys.AppendFormat("{0},", item.Key);
251 sbVals.AppendFormat("@{0},", counter.ToString());
252 result.AddParam(item.Value);
253 counter++;
254 }
255 if (counter > 0) {
256 var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
257 var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
258 var sql = string.Format(stub, TableName, keys, vals);
259 result.CommandText = sql;
260 } else throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
261 return result;
262 }
263 /// <summary>
264 /// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
265 /// </summary>
266 public virtual DbCommand CreateUpdateCommand(object o, object key) {
267 var expando = o.ToExpando();
268 var settings = (IDictionary<string, object>)expando;
269 var sbKeys = new StringBuilder();
270 var stub = "UPDATE {0} SET {1} WHERE {2} = @{3}";
271 var args = new List<object>();
272 var result = CreateCommand(stub, null);
273 int counter = 0;
274 foreach (var item in settings) {
275 var val = item.Value;
276 if (!item.Key.Equals(PrimaryKeyField, StringComparison.CurrentCultureIgnoreCase) && item.Value != null) {
277 result.AddParam(val);
278 sbKeys.AppendFormat("{0} = @{1}, \r\n", item.Key, counter.ToString());
279 counter++;
280 }
281 }
282 if (counter > 0) {
283 //add the key
284 result.AddParam(key);
285 //strip the last commas
286 var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 4);
287 result.CommandText = string.Format(stub, TableName, keys, PrimaryKeyField, counter);
288 } else throw new InvalidOperationException("No parsable object was sent in - could not divine any name/value pairs");
289 return result;
290 }
291 /// <summary>
292 /// Removes one or more records from the DB according to the passed-in WHERE
293 /// </summary>
294 public virtual DbCommand CreateDeleteCommand(string where = "", object key = null, params object[] args) {
295 var sql = string.Format("DELETE FROM {0} ", TableName);
296 if (key != null) {
297 sql += string.Format("WHERE {0}=@0", PrimaryKeyField);
298 args = new object[] { key };
299 } else if (!string.IsNullOrEmpty(where)) {
300 sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "WHERE " + where;
301 }
302 return CreateCommand(sql, null, args);
303 }
304 /// <summary>
305 /// Adds a record to the database. You can pass in an Anonymous object, an ExpandoObject,
306 /// A regular old POCO, or a NameValueColletion from a Request.Form or Request.QueryString
307 /// </summary>
308 public virtual object Insert(object o) {
309 dynamic result = 0;
310 using (var conn = OpenConnection()) {
311 var cmd = CreateInsertCommand(o);
312 cmd.Connection = conn;
313 cmd.ExecuteNonQuery();
314 cmd.CommandText = "SELECT @@IDENTITY as newID";
315 result = cmd.ExecuteScalar();
316 }
317 return result;
318 }
319 /// <summary>
320 /// Updates a record in the database. You can pass in an Anonymous object, an ExpandoObject,
321 /// A regular old POCO, or a NameValueCollection from a Request.Form or Request.QueryString
322 /// </summary>
323 public virtual int Update(object o, object key) {
324 return Execute(CreateUpdateCommand(o, key));
325 }
326 /// <summary>
327 /// Removes one or more records from the DB according to the passed-in WHERE
328 /// </summary>
329 public int Delete(object key = null, string where = "", params object[] args) {
330 return Execute(CreateDeleteCommand(where: where, key: key, args: args));
331 }
332 /// <summary>
333 /// Returns all records complying with the passed-in WHERE clause and arguments,
334 /// ordered as specified, limited (TOP) by limit.
335 /// </summary>
336 public virtual IEnumerable<dynamic> All(string where = "", string orderBy = "", int limit = 0, string columns = "*", params object[] args) {
337 string sql = BuildSelect(where, orderBy, limit);
338 return Query(string.Format(sql, columns, TableName), args);
339 }
340 private static string BuildSelect(string where, string orderBy, int limit) {
341 string sql = limit > 0 ? "SELECT TOP " + limit + " {0} FROM {1} " : "SELECT {0} FROM {1} ";
342 if (!string.IsNullOrEmpty(where))
343 sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "WHERE " + where;
344 if (!String.IsNullOrEmpty(orderBy))
345 sql += orderBy.Trim().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase) ? orderBy : " ORDER BY " + orderBy;
346 return sql;
347 }
348 /// <summary>
349 /// Returns all records complying with the passed-in WHERE clause and arguments,
350 /// ordered as specified, limited (TOP) by limit.
351 /// </summary>
47c98a8 @subsonic Cleaned up a bit of stuff
subsonic authored
352 public virtual void AllAsync(Action<List<dynamic>> callback, string where = "", string orderBy = "", int limit = 0, string columns = "*", params object[] args) {
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
353 string sql = BuildSelect(where, orderBy, limit);
47c98a8 @subsonic Cleaned up a bit of stuff
subsonic authored
354 QueryAsync(string.Format(sql, columns, TableName), callback, args);
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
355 }
356 /// <summary>
357 /// Returns a dynamic PagedResult. Result properties are Items, TotalPages, and TotalRecords.
358 /// </summary>
359 public virtual dynamic Paged(string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args) {
360 dynamic result = new ExpandoObject();
361 var countSQL = string.Format("SELECT COUNT({0}) FROM {1}", PrimaryKeyField, TableName);
362 if (String.IsNullOrEmpty(orderBy))
363 orderBy = PrimaryKeyField;
364
365 if (!string.IsNullOrEmpty(where)) {
366 if (!where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase)) {
367 where = "WHERE " + where;
368 }
369 }
370 var sql = 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);
371 var pageStart = (currentPage - 1) * pageSize;
372 sql += string.Format(" WHERE Row > {0} AND Row <={1}", pageStart, (pageStart + pageSize));
373 countSQL += where;
374 result.TotalRecords = Scalar(countSQL, args);
375 result.TotalPages = result.TotalRecords / pageSize;
376 if (result.TotalRecords % pageSize > 0)
377 result.TotalPages += 1;
378 result.Items = Query(string.Format(sql, columns, TableName), args);
379 return result;
380 }
381 /// <summary>
382 /// Returns a single row from the database
383 /// </summary>
bb4497e @subsonic Added a Schema property that returns, dynamically, all the information_s...
subsonic authored
384 public virtual dynamic Single(string where, params object[] args) {
385 var sql = string.Format("SELECT * FROM {0} WHERE {1}", TableName, where);
386 return Query(sql, args).First();
387 }
388 /// <summary>
389 /// Returns a single row from the database
390 /// </summary>
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
391 public virtual dynamic Single(object key, string columns = "*") {
392 var sql = string.Format("SELECT {0} FROM {1} WHERE {2} = @0", columns, TableName, PrimaryKeyField);
bb4497e @subsonic Added a Schema property that returns, dynamically, all the information_s...
subsonic authored
393 return Query(sql, key).FirstOrDefault();
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
394 }
395 /// <summary>
4ae06a0 @subsonic Changed my mind - ActiveRecord is based on some smooth Ruby. Lets use C ...
subsonic authored
396 /// A helpful query tool
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
397 /// </summary>
398 public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result) {
399 //parse the method
4ae06a0 @subsonic Changed my mind - ActiveRecord is based on some smooth Ruby. Lets use C ...
subsonic authored
400 var constraints = new List<string>();
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
401 var counter = 0;
4ae06a0 @subsonic Changed my mind - ActiveRecord is based on some smooth Ruby. Lets use C ...
subsonic authored
402 var info = binder.CallInfo;
403 // accepting named args only... SKEET!
404 if(info.ArgumentNames.Count != args.Length){
405 throw new InvalidOperationException("Please use named arguments for this type of query - the column name, orderby, columns, etc");
406 }
407
408
409 //first should be "FindBy, Last, Single, First"
410 var op = binder.Name;
411 var columns = " * ";
412 string orderBy = string.Format(" ORDER BY {0}", PrimaryKeyField);
413 string where = "";
414 var whereArgs = new List<object>();
415
416 //loop the named args - see if we have order, columns and constraints
417 if (info.ArgumentNames.Count > 0) {
418
419 for (int i = 0; i < args.Length; i++) {
420 var name = info.ArgumentNames[i].ToLower();
421 switch (name) {
422 case "orderby":
423 orderBy = " ORDER BY " + args[i];
424 break;
425 case "columns":
426 columns = args[i].ToString();
427 break;
428 default:
429 constraints.Add(string.Format(" {0} = @{1}",name,counter));
430 whereArgs.Add(args[i]);
431 counter++;
432 break;
433 }
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
434 }
435 }
4ae06a0 @subsonic Changed my mind - ActiveRecord is based on some smooth Ruby. Lets use C ...
subsonic authored
436 //Build the WHERE bits
437 if (constraints.Count > 0) {
438 where = " WHERE " + string.Join(" AND ", constraints.ToArray());
439 }
440 //build the SQL
441 string sql = "SELECT TOP 1 "+columns+" FROM " + TableName + where;
e32c8b2 @subsonic small bug with first
subsonic authored
442 var justOne = op.StartsWith("First") || op.StartsWith("Last") || op.StartsWith("Get");
4ae06a0 @subsonic Changed my mind - ActiveRecord is based on some smooth Ruby. Lets use C ...
subsonic authored
443
444 //Be sure to sort by DESC on the PK (PK Sort is the default)
445 if (op.StartsWith("Last")) {
446 orderBy = orderBy + " DESC ";
447 } else {
448 //default to multiple
449 sql = "SELECT "+columns+" FROM " + TableName + where;
450 justOne = false;
451 }
452
453 if (justOne) {
454 //return a single record
455 result = Query(sql + orderBy, whereArgs.ToArray()).FirstOrDefault();
456 } else {
457 //return lots
458 result = Query(sql + orderBy, whereArgs.ToArray());
459 }
460
bdfd2a2 @subsonic Added async methods (thanks to Damien Edwards) and Rails ActiveRecord st...
subsonic authored
461 return true;
462 }
463 }
b03bf61 @davecowart light refactoring; 500 lines
davecowart authored
464 }
Something went wrong with that request. Please try again.