Skip to content
Newer
Older
100644 636 lines (618 sloc) 25.4 KB
5c9c372 @DoggettCK Added DbConnectionStringBuilder constructor to Massive.Sqlite.cs.
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.SQLite;
12
13 namespace Massive.SQLite
14 {
15 public static class ObjectExtensions
16 {
17 /// <summary>
18 /// Extension method for adding in a bunch of parameters
19 /// </summary>
20 public static void AddParams(this DbCommand cmd, params object[] args)
21 {
22 foreach (var item in args)
23 {
24 AddParam(cmd, item);
25 }
26 }
27 /// <summary>
28 /// Extension for adding single parameter
29 /// </summary>
30 public static void AddParam(this DbCommand cmd, object item)
31 {
32 var p = cmd.CreateParameter();
33 p.ParameterName = string.Format("@{0}", cmd.Parameters.Count);
34 if (item == null)
35 {
36 p.Value = DBNull.Value;
37 }
38 else
39 {
40 if (item.GetType() == typeof(Guid))
41 {
42 p.Value = item.ToString();
43 p.DbType = DbType.String;
44 p.Size = 4000;
45 }
46 else if (item.GetType() == typeof(ExpandoObject))
47 {
48 var d = (IDictionary<string, object>)item;
49 p.Value = d.Values.FirstOrDefault();
50 }
51 else
52 {
53 p.Value = item;
54 }
55 if (item.GetType() == typeof(string))
56 p.Size = ((string)item).Length > 4000 ? -1 : 4000;
57 }
58 cmd.Parameters.Add(p);
59 }
60 /// <summary>
61 /// Turns an IDataReader to a Dynamic list of things
62 /// </summary>
63 public static List<dynamic> ToExpandoList(this IDataReader rdr)
64 {
65 var result = new List<dynamic>();
66 while (rdr.Read())
67 {
68 result.Add(rdr.RecordToExpando());
69 }
70 return result;
71 }
72 public static dynamic RecordToExpando(this IDataReader rdr)
73 {
74 dynamic e = new ExpandoObject();
75 var d = e as IDictionary<string, object>;
76 for (int i = 0; i < rdr.FieldCount; i++)
77 d.Add(rdr.GetName(i), DBNull.Value.Equals(rdr[i]) ? null : rdr[i]);
78 return e;
79 }
80 /// <summary>
81 /// Turns the object into an ExpandoObject
82 /// </summary>
83 public static dynamic ToExpando(this object o)
84 {
85 var result = new ExpandoObject();
86 var d = result as IDictionary<string, object>; //work with the Expando as a Dictionary
87 if (o.GetType() == typeof(ExpandoObject)) return o; //shouldn't have to... but just in case
88 if (o.GetType() == typeof(NameValueCollection) || o.GetType().IsSubclassOf(typeof(NameValueCollection)))
89 {
90 var nv = (NameValueCollection)o;
91 nv.Cast<string>().Select(key => new KeyValuePair<string, object>(key, nv[key])).ToList().ForEach(i => d.Add(i));
92 }
93 else
94 {
95 var props = o.GetType().GetProperties();
96 foreach (var item in props)
97 {
98 d.Add(item.Name, item.GetValue(o, null));
99 }
100 }
101 return result;
102 }
103 /// <summary>
104 /// Turns the object into a Dictionary
105 /// </summary>
106 public static IDictionary<string, object> ToDictionary(this object thingy)
107 {
108 return (IDictionary<string, object>)thingy.ToExpando();
109 }
110 }
111 /// <summary>
112 /// A class that wraps your database table in Dynamic Funtime
113 /// </summary>
114 public class DynamicModel : DynamicObject
115 {
116 DbProviderFactory _factory;
117 string ConnectionString;
118 public static DynamicModel Open(string connectionStringName)
119 {
120 dynamic dm = new DynamicModel(connectionStringName);
121 return dm;
122 }
123 public DynamicModel(string connectionStringName, string tableName = "", string primaryKeyField = "")
124 {
125 TableName = tableName == "" ? this.GetType().Name : tableName;
126 PrimaryKeyField = string.IsNullOrEmpty(primaryKeyField) ? "ID" : primaryKeyField;
127 var _providerName = "System.Data.SQLite";
128 _factory = DbProviderFactories.GetFactory(_providerName);
129 ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
130 _providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;
131 }
132 public DynamicModel(DbConnectionStringBuilder connectionStringBuilder, string tableName = "", string primaryKeyField = "")
133 {
134 TableName = tableName == "" ? this.GetType().Name : tableName;
135 PrimaryKeyField = string.IsNullOrEmpty(primaryKeyField) ? "ID" : primaryKeyField;
136
137 _factory = DbProviderFactories.GetFactory("System.Data.SQLite");
138
139 ConnectionString = connectionStringBuilder.ConnectionString;
140 }
141
142 /// <summary>
143 /// Creates a new Expando from a Form POST - white listed against the columns in the DB
144 /// </summary>
145 public dynamic CreateFrom(NameValueCollection coll)
146 {
147 dynamic result = new ExpandoObject();
148 var dc = (IDictionary<string, object>)result;
149 var schema = Schema;
150 //loop the collection, setting only what's in the Schema
151 foreach (var item in coll.Keys)
152 {
153 var exists = schema.Any(x => x.COLUMN_NAME.ToLower() == item.ToString().ToLower());
154 if (exists)
155 {
156 var key = item.ToString();
157 var val = coll[key];
158 if (!String.IsNullOrEmpty(val))
159 {
160 //what to do here? If it's empty... set it to NULL?
161 //if it's a string value - let it go through if it's NULLABLE?
162 //Empty? WTF?
163 dc.Add(key, val);
164 }
165 }
166 }
167 return result;
168 }
169 /// <summary>
170 /// Gets a default value for the column
171 /// </summary>
172 public dynamic DefaultValue(dynamic column)
173 {
174 dynamic result = null;
175 string def = column.COLUMN_DEFAULT;
176 if (String.IsNullOrEmpty(def))
177 {
178 result = null;
179 }
180 else if (def.ToUpper() == "CURRENT_TIME")
181 {
182 result = DateTime.UtcNow.ToString("HH:mm:ss");
40368ad @nemesv Fixed SQlite Schema discovery.
nemesv authored
183 }
77f9e7f @nemesv CURRENT_* keywords are case-independent.
nemesv authored
184 else if (def.ToUpper() == "CURRENT_DATE")
40368ad @nemesv Fixed SQlite Schema discovery.
nemesv authored
185 {
186 result = DateTime.UtcNow.ToString("yyyy-MM-dd");
187 }
77f9e7f @nemesv CURRENT_* keywords are case-independent.
nemesv authored
188 else if (def.ToUpper() == "CURRENT_TIMESTAMP")
40368ad @nemesv Fixed SQlite Schema discovery.
nemesv authored
189 {
190 result = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss");
5c9c372 @DoggettCK Added DbConnectionStringBuilder constructor to Massive.Sqlite.cs.
authored
191 }
192 return result;
193 }
194 /// <summary>
195 /// Creates an empty Expando set with defaults from the DB
196 /// </summary>
197 public dynamic Prototype
198 {
199 get
200 {
201 dynamic result = new ExpandoObject();
202 var schema = Schema;
203 foreach (dynamic column in schema)
204 {
205 var dc = (IDictionary<string, object>)result;
206 dc.Add(column.COLUMN_NAME, DefaultValue(column));
207 }
208 result._Table = this;
209 return result;
210 }
211 }
212 /// <summary>
213 /// List out all the schema bits for use with ... whatever
214 /// </summary>
215 IEnumerable<dynamic> _schema;
216 public IEnumerable<dynamic> Schema
217 {
218 get
40368ad @nemesv Fixed SQlite Schema discovery.
nemesv authored
219 {
220 if (_schema == null)
221 {
222 var rows = new List<dynamic>();
223 foreach (var row in Query("PRAGMA table_info('" + TableName + "')"))
224 {
225 rows.Add(new
226 {
227 COLUMN_NAME = (row as IDictionary<string, object>)["name"].ToString(),
228 DATA_TYPE = (row as IDictionary<string, object>)["type"].ToString(),
229 IS_NULLABLE = (row as IDictionary<string, object>)["notnull"].ToString() == "0" ? "NO" : "YES",
230 COLUMN_DEFAULT = (row as IDictionary<string, object>)["dflt_value"] ?? "",
231 });
232 }
233 _schema = rows;
234 }
5c9c372 @DoggettCK Added DbConnectionStringBuilder constructor to Massive.Sqlite.cs.
authored
235 return _schema;
236 }
237 }
238
239 /// <summary>
240 /// Enumerates the reader yielding the result - thanks to Jeroen Haegebaert
241 /// </summary>
242 public virtual IEnumerable<dynamic> Query(string sql, params object[] args)
243 {
244 using (var conn = OpenConnection())
245 {
246 var rdr = CreateCommand(sql, conn, args).ExecuteReader();
247 while (rdr.Read())
248 {
249 yield return rdr.RecordToExpando(); ;
250 }
251 }
252 }
253
254
255 public virtual IEnumerable<dynamic> Query(string sql, DbConnection connection, params object[] args)
256 {
257 using (var rdr = CreateCommand(sql, connection, args).ExecuteReader())
258 {
259 while (rdr.Read())
260 {
261 yield return rdr.RecordToExpando(); ;
262 }
263 }
264 }
265 /// <summary>
266 /// Returns a single result
267 /// </summary>
268 public virtual object Scalar(string sql, params object[] args)
269 {
270 object result = null;
271 using (var conn = OpenConnection())
272 {
273 result = CreateCommand(sql, conn, args).ExecuteScalar();
274 }
275 return result;
276 }
277 /// <summary>
278 /// Creates a DBCommand that you can use for loving your database.
279 /// </summary>
280 DbCommand CreateCommand(string sql, DbConnection conn, params object[] args)
281 {
282 var result = _factory.CreateCommand();
283 result.Connection = conn;
284 result.CommandText = sql;
285 if (args.Length > 0)
286 result.AddParams(args);
287 return result;
288 }
289 /// <summary>
290 /// Returns and OpenConnection
291 /// </summary>
292 public virtual DbConnection OpenConnection()
293 {
294 var result = _factory.CreateConnection();
295 result.ConnectionString = ConnectionString;
296 result.Open();
297 return result;
298 }
299 /// <summary>
300 /// Builds a set of Insert and Update commands based on the passed-on objects.
301 /// These objects can be POCOs, Anonymous, NameValueCollections, or Expandos. Objects
302 /// With a PK property (whatever PrimaryKeyField is set to) will be created at UPDATEs
303 /// </summary>
304 public virtual List<DbCommand> BuildCommands(params object[] things)
305 {
306 var commands = new List<DbCommand>();
307 foreach (var item in things)
308 {
309 if (HasPrimaryKey(item))
310 {
311 commands.Add(CreateUpdateCommand(item, GetPrimaryKey(item)));
312 }
313 else
314 {
315 commands.Add(CreateInsertCommand(item));
316 }
317 }
318 return commands;
319 }
320 /// <summary>
321 /// Executes a set of objects as Insert or Update commands based on their property settings, within a transaction.
322 /// These objects can be POCOs, Anonymous, NameValueCollections, or Expandos. Objects
323 /// With a PK property (whatever PrimaryKeyField is set to) will be created at UPDATEs
324 /// </summary>
325 public virtual int Save(params object[] things)
326 {
327 var commands = BuildCommands(things);
328 return Execute(commands);
329 }
330
331 public virtual int Execute(DbCommand command)
332 {
333 return Execute(new DbCommand[] { command });
334 }
335
336 public virtual int Execute(string sql, params object[] args)
337 {
338 return Execute(CreateCommand(sql, null, args));
339 }
340 /// <summary>
341 /// Executes a series of DBCommands in a transaction
342 /// </summary>
343 public virtual int Execute(IEnumerable<DbCommand> commands)
344 {
345 var result = 0;
346 using (var conn = OpenConnection())
347 {
348 using (var tx = conn.BeginTransaction())
349 {
350 foreach (var cmd in commands)
351 {
352 cmd.Connection = conn;
353 cmd.Transaction = tx;
354 result += cmd.ExecuteNonQuery();
355 }
356 tx.Commit();
357 }
358 }
359 return result;
360 }
361 public virtual string PrimaryKeyField { get; set; }
362 /// <summary>
363 /// Conventionally introspects the object passed in for a field that
364 /// looks like a PK. If you've named your PrimaryKeyField, this becomes easy
365 /// </summary>
366 public virtual bool HasPrimaryKey(object o)
367 {
368 return o.ToDictionary().ContainsKey(PrimaryKeyField);
369 }
370 /// <summary>
371 /// If the object passed in has a property with the same name as your PrimaryKeyField
372 /// it is returned here.
373 /// </summary>
374 public virtual object GetPrimaryKey(object o)
375 {
376 object result = null;
377 o.ToDictionary().TryGetValue(PrimaryKeyField, out result);
378 return result;
379 }
380 public virtual string TableName { get; set; }
381 /// <summary>
382 /// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
383 /// </summary>
384 public virtual DbCommand CreateInsertCommand(object o)
385 {
386 DbCommand result = null;
387 var expando = o.ToExpando();
388 var settings = (IDictionary<string, object>)expando;
389 var sbKeys = new StringBuilder();
390 var sbVals = new StringBuilder();
391 var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2})";
392 result = CreateCommand(stub, null);
393 int counter = 0;
394 foreach (var item in settings)
395 {
396 sbKeys.AppendFormat("{0},", item.Key);
397 sbVals.AppendFormat("@{0},", counter.ToString());
398 result.AddParam(item.Value);
399 counter++;
400 }
401 if (counter > 0)
402 {
403 var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
404 var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
405 var sql = string.Format(stub, TableName, keys, vals);
406 result.CommandText = sql;
407 }
408 else throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
409 return result;
410 }
411 /// <summary>
412 /// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
413 /// </summary>
414 public virtual DbCommand CreateUpdateCommand(object o, object key)
415 {
416 var expando = o.ToExpando();
417 var settings = (IDictionary<string, object>)expando;
418 var sbKeys = new StringBuilder();
419 var stub = "UPDATE {0} SET {1} WHERE {2} = @{3}";
420 var args = new List<object>();
421 var result = CreateCommand(stub, null);
422 int counter = 0;
423 foreach (var item in settings)
424 {
425 var val = item.Value;
426 if (!item.Key.Equals(PrimaryKeyField, StringComparison.CurrentCultureIgnoreCase) && item.Value != null)
427 {
428 result.AddParam(val);
429 sbKeys.AppendFormat("{0} = @{1}, \r\n", item.Key, counter.ToString());
430 counter++;
431 }
432 }
433 if (counter > 0)
434 {
435 //add the key
436 result.AddParam(key);
437 //strip the last commas
438 var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 4);
439 result.CommandText = string.Format(stub, TableName, keys, PrimaryKeyField, counter);
440 }
441 else throw new InvalidOperationException("No parsable object was sent in - could not divine any name/value pairs");
442 return result;
443 }
444 /// <summary>
445 /// Removes one or more records from the DB according to the passed-in WHERE
446 /// </summary>
447 public virtual DbCommand CreateDeleteCommand(string where = "", object key = null, params object[] args)
448 {
449 var sql = string.Format("DELETE FROM {0} ", TableName);
450 if (key != null)
451 {
452 sql += string.Format("WHERE {0}=@0", PrimaryKeyField);
453 args = new object[] { key };
454 }
455 else if (!string.IsNullOrEmpty(where))
456 {
457 sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "WHERE " + where;
458 }
459 return CreateCommand(sql, null, args);
460 }
461 /// <summary>
462 /// Adds a record to the database. You can pass in an Anonymous object, an ExpandoObject,
463 /// A regular old POCO, or a NameValueColletion from a Request.Form or Request.QueryString
464 /// </summary>
465 public virtual object Insert(object o)
466 {
467 dynamic result = 0;
468 using (var conn = OpenConnection())
469 {
470 var cmd = CreateInsertCommand(o);
471 cmd.Connection = conn;
472 cmd.ExecuteNonQuery();
473 cmd.CommandText = "select last_insert_rowid()";
474 result = cmd.ExecuteScalar();
475 }
476 return result;
477 }
478 /// <summary>
479 /// Updates a record in the database. You can pass in an Anonymous object, an ExpandoObject,
480 /// A regular old POCO, or a NameValueCollection from a Request.Form or Request.QueryString
481 /// </summary>
482 public virtual int Update(object o, object key)
483 {
484 return Execute(CreateUpdateCommand(o, key));
485 }
486 /// <summary>
487 /// Removes one or more records from the DB according to the passed-in WHERE
488 /// </summary>
489 public int Delete(object key = null, string where = "", params object[] args)
490 {
491 return Execute(CreateDeleteCommand(where: where, key: key, args: args));
492 }
493 /// <summary>
494 /// Returns all records complying with the passed-in WHERE clause and arguments,
495 /// ordered as specified, limited (TOP) by limit.
496 /// </summary>
497 public virtual IEnumerable<dynamic> All(string where = "", string orderBy = "", int limit = 0, string columns = "*", params object[] args)
498 {
499 string sql = BuildSelect(where, orderBy, limit);
500 return Query(string.Format(sql, columns, TableName), args);
501 }
502 private static string BuildSelect(string where, string orderBy, int limit)
503 {
504 string sql = limit > 0 ? "SELECT TOP " + limit + " {0} FROM {1} " : "SELECT {0} FROM {1} ";
505 if (!string.IsNullOrEmpty(where))
506 sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "WHERE " + where;
507 if (!String.IsNullOrEmpty(orderBy))
508 sql += orderBy.Trim().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase) ? orderBy : " ORDER BY " + orderBy;
509 return sql;
510 }
511
512 /// <summary>
513 /// Returns a dynamic PagedResult. Result properties are Items, TotalPages, and TotalRecords.
514 /// </summary>
515 public virtual dynamic Paged(string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
516 {
517 dynamic result = new ExpandoObject();
518 var countSQL = string.Format("SELECT COUNT({0}) FROM {1}", PrimaryKeyField, TableName);
519 if (String.IsNullOrEmpty(orderBy))
520 orderBy = PrimaryKeyField;
521
522 if (!string.IsNullOrEmpty(where))
523 {
524 if (!where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
525 {
526 where = "WHERE " + where;
527 }
528 }
529 var sql = string.Format("select {0} FROM {3} {4} ORDER BY {2} ", columns, pageSize, orderBy, TableName, where);
530 var pageStart = (currentPage - 1) * pageSize;
531 sql += string.Format(" LIMIT {0},{1}",pageStart, pageSize);
532 countSQL += where;
533 result.TotalRecords = Scalar(countSQL, args);
534 result.TotalPages = result.TotalRecords / pageSize;
535 if (result.TotalRecords % pageSize > 0)
536 result.TotalPages += 1;
537 result.Items = Query(string.Format(sql, columns, TableName), args);
538 return result;
539 }
540 /// <summary>
541 /// Returns a single row from the database
542 /// </summary>
543 public virtual dynamic Single(string where, params object[] args)
544 {
545 var sql = string.Format("SELECT * FROM {0} WHERE {1}", TableName, where);
546 return Query(sql, args).FirstOrDefault();
547 }
548 /// <summary>
549 /// Returns a single row from the database
550 /// </summary>
551 public virtual dynamic Single(object key, string columns = "*")
552 {
553 var sql = string.Format("SELECT {0} FROM {1} WHERE {2} = @0", columns, TableName, PrimaryKeyField);
554 return Query(sql, key).FirstOrDefault();
555 }
556 /// <summary>
557 /// A helpful query tool
558 /// </summary>
559 public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
560 {
561 //parse the method
562 var constraints = new List<string>();
563 var counter = 0;
564 var info = binder.CallInfo;
565 // accepting named args only... SKEET!
566 if (info.ArgumentNames.Count != args.Length)
567 {
568 throw new InvalidOperationException("Please use named arguments for this type of query - the column name, orderby, columns, etc");
569 }
570
571
572 //first should be "FindBy, Last, Single, First"
573 var op = binder.Name;
574 var columns = " * ";
575 string orderBy = string.Format(" ORDER BY {0}", PrimaryKeyField);
576 string where = "";
577 var whereArgs = new List<object>();
578
579 //loop the named args - see if we have order, columns and constraints
580 if (info.ArgumentNames.Count > 0)
581 {
582
583 for (int i = 0; i < args.Length; i++)
584 {
585 var name = info.ArgumentNames[i].ToLower();
586 switch (name)
587 {
588 case "orderby":
589 orderBy = " ORDER BY " + args[i];
590 break;
591 case "columns":
592 columns = args[i].ToString();
593 break;
594 default:
595 constraints.Add(string.Format(" {0} = @{1}", name, counter));
596 whereArgs.Add(args[i]);
597 counter++;
598 break;
599 }
600 }
601 }
602 //Build the WHERE bits
603 if (constraints.Count > 0)
604 {
605 where = " WHERE " + string.Join(" AND ", constraints.ToArray());
606 }
607 //build the SQL
608 string sql = "SELECT TOP 1 " + columns + " FROM " + TableName + where;
609 var justOne = op.StartsWith("First") || op.StartsWith("Last") || op.StartsWith("Get");
610
611 //Be sure to sort by DESC on the PK (PK Sort is the default)
612 if (op.StartsWith("Last"))
613 {
614 orderBy = orderBy + " DESC ";
615 }
616 else
617 {
618 //default to multiple
619 sql = "SELECT " + columns + " FROM " + TableName + where;
620 }
621
622 if (justOne)
623 {
624 //return a single record
625 result = Query(sql + orderBy, whereArgs.ToArray()).FirstOrDefault();
626 }
627 else
628 {
629 //return lots
630 result = Query(sql + orderBy, whereArgs.ToArray());
631 }
632
633 return true;
634 }
635 }
ef6b783 @majimenezp Adding massive class for sqlite
majimenezp authored
636 }
Something went wrong with that request. Please try again.