Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

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