1: /*
2: Copyright (c) 2011 <a href="http://www.gutgames.com">James Craig</a>
3:
4: Permission is hereby granted, free of charge, to any person obtaining a copy
5: of this software and associated documentation files (the "Software"), to deal
6: in the Software without restriction, including without limitation the rights
7: to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8: copies of the Software, and to permit persons to whom the Software is
9: furnished to do so, subject to the following conditions:
10:
11: The above copyright notice and this permission notice shall be included in
12: all copies or substantial portions of the Software.
13:
14: THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15: IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16: FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17: AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18: LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19: OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
20: THE SOFTWARE.*/
21:
22: #region Usings
23: using System;
24: using System.Collections.Generic;
25: using System.Linq;
26: using System.Text;
27: using Utilities.DataMapper;
28: using System.Linq.Expressions;
29: using Utilities.SQL.MicroORM.Interfaces;
30: using System.Data;
31: #endregion
32:
33: namespace Utilities.SQL.MicroORM
34: {
35: /// <summary>
36: /// Class that acts as a mapping within the micro ORM
37: /// </summary>
38: /// <typeparam name="ClassType">Class type that this will accept</typeparam>
39: public class Mapping<ClassType> : IMapping where ClassType : class,new()
40: {
41: #region Constructors
42:
43: /// <summary>
44: /// Constructor
45: /// </summary>
46: /// <param name="Connection">Connection string</param>
47: /// <param name="TableName">Table name</param>
48: /// <param name="PrimaryKey">Primary key in the table</param>
49: /// <param name="AutoIncrement">Is the primary key set to auto increment?</param>
50: /// <param name="ParameterStarter">What the database expects as the
51: /// parameter starting string ("@" for SQL Server, ":" for Oracle, etc.)</param>
52: /// <param name="DbType">DbType for this connection</param>
53: public Mapping(string Connection, string TableName, string PrimaryKey, bool AutoIncrement = true, string ParameterStarter = "@", string DbType = "System.Data.SqlClient")
54: {
55: Helper = new SQLHelper("", Connection, System.Data.CommandType.Text, DbType);
56: Mappings = new TypeMapping<ClassType, SQLHelper>();
57: ParameterNames = new List<string>();
58: this.TableName = TableName;
59: this.PrimaryKey = PrimaryKey;
60: this.AutoIncrement = AutoIncrement;
61: this.ParameterStarter = ParameterStarter;
62: }
63:
64: /// <summary>
65: /// Constructor (can be used if supplying own SQLHelper)
66: /// </summary>
67: /// <param name="TableName">Table name</param>
68: /// <param name="PrimaryKey">Primary key</param>
69: /// <param name="AutoIncrement">Is the primary key set to auto increment?</param>
70: /// <param name="ParameterStarter">What the database expects as the
71: /// parameter starting string ("@" for SQL Server, ":" for Oracle, etc.)</param>
72: public Mapping(string TableName, string PrimaryKey, bool AutoIncrement = true, string ParameterStarter = "@")
73: {
74: Mappings = new TypeMapping<ClassType, SQLHelper>();
75: ParameterNames = new List<string>();
76: this.TableName = TableName;
77: this.PrimaryKey = PrimaryKey;
78: this.AutoIncrement = AutoIncrement;
79: this.ParameterStarter = ParameterStarter;
80: }
81:
82: #endregion
83:
84: #region Properties
85:
86: /// <summary>
87: /// SQL Helper
88: /// </summary>
89: public virtual SQLHelper Helper { get; set; }
90:
91: /// <summary>
92: /// Mapper used to map properties to SQLHelper
93: /// </summary>
94: public virtual TypeMapping<ClassType, SQLHelper> Mappings { get; set; }
95:
96: /// <summary>
97: /// Table name
98: /// </summary>
99: protected virtual string TableName { get; set; }
100:
101: /// <summary>
102: /// Primar key
103: /// </summary>
104: protected virtual string PrimaryKey { get; set; }
105:
106: /// <summary>
107: /// Auto increment?
108: /// </summary>
109: protected virtual bool AutoIncrement { get; set; }
110:
111: /// <summary>
112: /// Parameter starter
113: /// </summary>
114: protected virtual string ParameterStarter { get; set; }
115:
116: /// <summary>
117: /// Parameter names
118: /// </summary>
119: public virtual List<string> ParameterNames { get; set; }
120:
121: #endregion
122:
123: #region Public Functions
124:
125: #region All
126:
127: /// <summary>
128: /// Gets a list of all objects that meet the specified criteria
129: /// </summary>
130: /// <param name="Command">Command to use (can be an SQL string or stored procedure)</param>
131: /// <param name="CommandType">Command type</param>
132: /// <param name="Parameters">Parameters to search by</param>
133: /// <returns>A list of all objects that meet the specified criteria</returns>
134: public virtual IEnumerable<ClassType> All(string Command, CommandType CommandType, params IParameter[] Parameters)
135: {
136: Check(Command, "Command");
137: Check(Helper, "Helper");
138: Check(Mappings, "Mappings");
139: List<ClassType> Return = new List<ClassType>();
140: SetupCommand(Command, CommandType, Parameters);
141: Helper.ExecuteReader();
142: while (Helper.Read())
143: {
144: ClassType Temp = new ClassType();
145: Mappings.Copy(Helper, Temp);
146: Return.Add(Temp);
147: }
148: return Return;
149: }
150:
151: /// <summary>
152: /// Gets a list of all objects that meet the specified criteria
153: /// </summary>
154: /// <param name="Columns">Columns to return</param>
155: /// <param name="Limit">Limit on the number of items to return</param>
156: /// <param name="OrderBy">Order by clause</param>
157: /// <param name="Parameters">Parameters to search by</param>
158: /// <returns>A list of all objects that meet the specified criteria</returns>
159: public virtual IEnumerable<ClassType> All(string Columns = "*", int Limit = 0, string OrderBy = "", params IParameter[] Parameters)
160: {
161: Check(Columns, "Columns");
162: return All(SetupSelectCommand(Columns, Limit, OrderBy, Parameters), CommandType.Text, Parameters);
163: }
164:
165: #endregion
166:
167: #region Any
168:
169: /// <summary>
170: /// Gets a single object that fits the criteria
171: /// </summary>
172: /// <param name="Columns">Columns to select</param>
173: /// <param name="Parameters">Parameters to search by</param>
174: /// <returns>An object fitting the criteria specified or null if none are found</returns>
175: public virtual ClassType Any(string Columns = "*", ClassType ObjectToReturn = null, params IParameter[] Parameters)
176: {
177: Check(Columns, "Columns");
178: return Any(SetupSelectCommand(Columns, 1, "", Parameters), CommandType.Text, ObjectToReturn, Parameters);
179: }
180:
181: /// <summary>
182: /// Gets a single object that fits the criteria
183: /// </summary>
184: /// <param name="Command">Command to use (can be an SQL string or stored procedure name)</param>
185: /// <param name="CommandType">Command type</param>
186: /// <param name="ObjectToReturn">Object to return (in case the object needs to be created outside this)</param>
187: /// <param name="Parameters">Parameters used to search by</param>
188: /// <returns>An object fitting the criteria specified or null if none are found</returns>
189: public virtual ClassType Any(string Command, CommandType CommandType, ClassType ObjectToReturn = null, params IParameter[] Parameters)
190: {
191: Check(Mappings, "Mappings");
192: Check(Command, "Command");
193: Check(Helper, "Helper");
194: ClassType Return = (ObjectToReturn == null) ? new ClassType() : ObjectToReturn;
195: SetupCommand(Command, CommandType, Parameters);
196: Helper.ExecuteReader();
197: if (Helper.Read())
198: Mappings.Copy(Helper, Return);
199: return Return;
200: }
201:
202: #endregion
203:
204: #region Close
205:
206: /// <summary>
207: /// Closes the connection to the database
208: /// </summary>
209: public virtual void Close()
210: {
211: Check(Helper, "Helper");
212: Helper.Close();
213: }
214:
215: #endregion
216:
217: #region Delete
218:
219: /// <summary>
220: /// Deletes an object from the database
221: /// </summary>
222: /// <param name="Command">Command to use</param>
223: /// <param name="CommandType">Command type</param>
224: /// <param name="Object">Object to delete</param>
225: public virtual void Delete(string Command, CommandType CommandType, ClassType Object)
226: {
227: Check(Object, "Object");
228: Check(Command, "Command");
229: Check(Helper, "Helper");
230: Check(Mappings, "Mappings");
231: SetupCommand(Command, CommandType, null);
232: Mappings.Copy(Object, Helper);
233: Helper.ExecuteNonQuery();
234: }
235:
236: /// <summary>
237: /// Deletes an object from the database
238: /// </summary>
239: /// <param name="Object">Object to delete</param>
240: public virtual void Delete(ClassType Object)
241: {
242: Delete(SetupDeleteCommand(), CommandType.Text, Object);
243: }
244:
245: #endregion
246:
247: #region Insert
248:
249: /// <summary>
250: /// Inserts an object based on the command specified
251: /// </summary>
252: /// <typeparam name="DataType">Data type expected to be returned from the query (to get the ID, etc.)</typeparam>
253: /// <param name="Command">Command to run</param>
254: /// <param name="CommandType">Command type</param>
255: /// <param name="Object">Object to insert</param>
256: /// <returns>The returned object from the query (usually the newly created row's ID)</returns>
257: public virtual DataType Insert<DataType>(string Command, CommandType CommandType, ClassType Object)
258: {
259: Check(Object, "Object");
260: Check(Command, "Command");
261: Check(Helper, "Helper");
262: Check(Mappings, "Mappings");
263: SetupCommand(Command, CommandType, null);
264: Mappings.Copy(Object, Helper);
265: return (DataType)Convert.ChangeType(Helper.ExecuteScalar(), typeof(DataType));
266: }
267:
268: /// <summary>
269: /// Inserts an object into the database
270: /// </summary>
271: /// <typeparam name="DataType">Data type expected (should be the same type as the primary key)</typeparam>
272: /// <param name="Object">Object to insert</param>
273: /// <returns>The returned object from the query (the newly created row's ID)</returns>
274: public virtual DataType Insert<DataType>(ClassType Object)
275: {
276: return Insert<DataType>(SetupInsertCommand(), CommandType.Text, Object);
277: }
278:
279: #endregion
280:
281: #region Map
282:
283: /// <summary>
284: /// Maps a property to a database property name (required to actually get data from the database)
285: /// </summary>
286: /// <typeparam name="DataType">Data type of the property</typeparam>
287: /// <param name="Property">Property to add a mapping for</param>
288: /// <param name="DatabasePropertyName">Property name</param>
289: public virtual Mapping<ClassType> Map<DataType>(Expression<Func<ClassType, DataType>> Property, string DatabasePropertyName)
290: {
291: Check(Property, "Property");
292: Check(DatabasePropertyName, "DatabasePropertyName");
293: Check(Mappings, "Mappings");
294: Expression Convert = Expression.Convert(Property.Body, typeof(object));
295: Expression<Func<ClassType, object>> PropertyExpression = Expression.Lambda<Func<ClassType, object>>(Convert, Property.Parameters);
296: Mappings.AddMapping(PropertyExpression,
297: new Func<SQLHelper, object>((x) => x.GetParameter(DatabasePropertyName, default(DataType))),
298: new Action<SQLHelper, object>((x, y) => x.AddParameter(DatabasePropertyName, y)));
299: ParameterNames.Add(DatabasePropertyName);
300: return this;
301: }
302:
303: /// <summary>
304: /// Maps a property to a database property name (required to actually get data from the database)
305: /// </summary>
306: /// <param name="Property">Property to add a mapping for</param>
307: /// <param name="DatabasePropertyName">Property name</param>
308: /// <param name="Length">Max length of the string</param>
309: public virtual Mapping<ClassType> Map(Expression<Func<ClassType, string>> Property, string DatabasePropertyName, int Length)
310: {
311: Check(Property, "Property");
312: Check(DatabasePropertyName, "DatabasePropertyName");
313: Check(Mappings, "Mappings");
314: Expression Convert = Expression.Convert(Property.Body, typeof(object));
315: Expression<Func<ClassType, object>> PropertyExpression = Expression.Lambda<Func<ClassType, object>>(Convert, Property.Parameters);
316: Mappings.AddMapping(PropertyExpression,
317: new Func<SQLHelper, object>((x) => x.GetParameter(DatabasePropertyName, "")),
318: new Action<SQLHelper, object>((x, y) => x.AddParameter(DatabasePropertyName, (string)y, Length)));
319: ParameterNames.Add(DatabasePropertyName);
320: return this;
321: }
322:
323: #endregion
324:
325: #region Open
326:
327: /// <summary>
328: /// Opens the connection to the database
329: /// </summary>
330: public virtual void Open()
331: {
332: Check(Helper, "Helper");
333: Helper.Open();
334: }
335:
336: #endregion
337:
338: #region PageCount
339:
340: /// <summary>
341: /// Gets the number of pages based on the specified
342: /// </summary>
343: /// <param name="PageSize">Page size</param>
344: /// <param name="Parameters">Parameters to search by</param>
345: /// <returns>The number of pages that the table contains for the specified page size</returns>
346: public virtual int PageCount(int PageSize = 25, params IParameter[] Parameters)
347: {
348: Check(Helper, "Helper");
349: SetupCommand(SetupPageCountCommand(PageSize, Parameters), CommandType.Text, Parameters);
350: Helper.ExecuteReader();
351: if (Helper.Read())
352: {
353: int Total = Helper.GetParameter("Total", 0);
354: return Total % PageSize == 0 ? Total / PageSize : (Total / PageSize) + 1;
355: }
356: return 0;
357: }
358:
359: #endregion
360:
361: #region Paged
362:
363: /// <summary>
364: /// Gets a paged list of objects fitting the specified criteria
365: /// </summary>
366: /// <param name="Columns">Columns to return</param>
367: /// <param name="OrderBy">Order by clause</param>
368: /// <param name="PageSize">Page size</param>
369: /// <param name="CurrentPage">The current page (starting at 0)</param>
370: /// <param name="Parameters">Parameters to search by</param>
371: /// <returns>A list of objects that fit the specified criteria</returns>
372: public virtual IEnumerable<ClassType> Paged(string Columns = "*", string OrderBy = "", int PageSize = 25, int CurrentPage = 0, params IParameter[] Parameters)
373: {
374: Check(Columns, "Columns");
375: return All(SetupPagedCommand(Columns, OrderBy, PageSize, CurrentPage, Parameters), CommandType.Text, Parameters);
376: }
377:
378: #endregion
379:
380: #region Update
381:
382: /// <summary>
383: /// Updates an object in the database
384: /// </summary>
385: /// <param name="Command">Command to use</param>
386: /// <param name="CommandType">Command type</param>
387: /// <param name="Object">Object to update</param>
388: public virtual void Update(string Command, CommandType CommandType, ClassType Object)
389: {
390: Check(Helper, "Helper");
391: Check(Mappings, "Mappings");
392: Check(Command, "Command");
393: SetupCommand(Command, CommandType, null);
394: Mappings.Copy(Object, Helper);
395: Helper.ExecuteNonQuery();
396: }
397:
398: /// <summary>
399: /// Updates an object in the database
400: /// </summary>
401: /// <param name="Object">Object to update</param>
402: public virtual void Update(ClassType Object)
403: {
404: Update(SetupUpdateCommand(), CommandType.Text, Object);
405: }
406:
407: #endregion
408:
409: #endregion
410:
411: #region Protected Functions
412:
413: #region Check
414:
415: /// <summary>
416: /// Checks if an object is null, throwing an exception if it is
417: /// </summary>
418: /// <param name="Object">Object to check</param>
419: /// <param name="Name">Parameter name</param>
420: protected virtual void Check(object Object, string Name)
421: {
422: if (Object == null)
423: throw new ArgumentNullException(Name);
424: }
425:
426: /// <summary>
427: /// Checks if a string is null/empty, throwing an exception if it is
428: /// </summary>
429: /// <param name="String">String to check</param>
430: /// <param name="Name">Parameter name</param>
431: protected virtual void Check(string String, string Name)
432: {
433: if (string.IsNullOrEmpty(String))
434: throw new ArgumentNullException(Name);
435: }
436:
437: #endregion
438:
439: #region SetupCommand
440:
441: /// <summary>
442: /// Sets up a command
443: /// </summary>
444: /// <param name="Command">Command to add to the SQL Helper</param>
445: /// <param name="CommandType">Command type</param>
446: /// <param name="Parameters">Parameter list</param>
447: protected virtual void SetupCommand(string Command, CommandType CommandType, IParameter[] Parameters)
448: {
449: Check(Helper, "Helper");
450: Check(Command, "Command");
451: Helper.Command = Command;
452: Helper.CommandType = CommandType;
453: if (Parameters != null)
454: {
455: foreach (IParameter Parameter in Parameters)
456: {
457: Parameter.AddParameter(Helper);
458: }
459: }
460: }
461:
462: #endregion
463:
464: #region SetupDeleteCommand
465:
466: /// <summary>
467: /// Sets up the delete command
468: /// </summary>
469: /// <returns>The command string</returns>
470: protected virtual string SetupDeleteCommand()
471: {
472: return string.Format("DELETE FROM {0} WHERE {1}", TableName, PrimaryKey + "=" + ParameterStarter + PrimaryKey);
473: }
474:
475: #endregion
476:
477: #region SetupInsertCommand
478:
479: /// <summary>
480: /// Sets up the insert command
481: /// </summary>
482: /// <returns>The command string</returns>
483: protected virtual string SetupInsertCommand()
484: {
485: string ParameterList = "";
486: string ValueList = "";
487: string Splitter = "";
488: foreach (string Name in ParameterNames)
489: {
490: if (!AutoIncrement || Name != PrimaryKey)
491: {
492: ParameterList += Splitter + Name;
493: ValueList += Splitter + ParameterStarter + Name;
494: Splitter = ",";
495: }
496: }
497: return string.Format("INSERT INTO {0}({1}) VALUES({2}) SELECT scope_identity() as [ID]", TableName, ParameterList, ValueList);
498: }
499:
500: #endregion
501:
502: #region SetupPageCountCommand
503:
504: /// <summary>
505: /// Sets up the page count command
506: /// </summary>
507: /// <param name="PageSize">Page size</param>
508: /// <param name="Parameters">Parameter list</param>
509: /// <returns>The string command</returns>
510: protected virtual string SetupPageCountCommand(int PageSize, IParameter[] Parameters)
511: {
512: string WhereCommand = "";
513: if (Parameters != null && Parameters.Length > 0)
514: {
515: WhereCommand += " WHERE ";
516: string Splitter = "";
517: foreach (IParameter Parameter in Parameters)
518: {
519: WhereCommand += Splitter + Parameter;
520: Splitter = " AND ";
521: }
522: }
523: return string.Format("SELECT COUNT({0}) as Total FROM {1} {2}", PrimaryKey, TableName, WhereCommand);
524: }
525:
526: #endregion
527:
528: #region SetupPagedCommand
529:
530: /// <summary>
531: /// Sets up the paged select command
532: /// </summary>
533: /// <param name="Columns">Columns to return</param>
534: /// <param name="OrderBy">Order by clause</param>
535: /// <param name="PageSize">Page size</param>
536: /// <param name="CurrentPage">Current page</param>
537: /// <param name="Parameters">Parameter list</param>
538: /// <returns>The command string</returns>
539: protected virtual string SetupPagedCommand(string Columns, string OrderBy, int PageSize, int CurrentPage, IParameter[] Parameters)
540: {
541: if (string.IsNullOrEmpty(OrderBy))
542: OrderBy = PrimaryKey;
543:
544: string WhereCommand = "";
545: if (Parameters != null && Parameters.Length > 0)
546: {
547: WhereCommand += " WHERE ";
548: string Splitter = "";
549: foreach (IParameter Parameter in Parameters)
550: {
551: WhereCommand += Splitter + Parameter;
552: Splitter = " AND ";
553: }
554: }
555: string Command = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS Row, {0} FROM {2} {3}) AS Paged ", Columns, OrderBy, TableName, WhereCommand);
556: int PageStart = CurrentPage * PageSize;
557: Command += string.Format(" WHERE Row>{0} AND Row<={1}", PageStart, PageStart + PageSize);
558: return Command;
559: }
560:
561: #endregion
562:
563: #region SetupSelectCommand
564:
565: /// <summary>
566: /// Sets up the select command
567: /// </summary>
568: /// <param name="Columns">Columns to return</param>
569: /// <param name="Limit">limit on the number of items to return</param>
570: /// <param name="OrderBy">Order by clause</param>
571: /// <param name="Parameters">Parameter list</param>
572: /// <returns>The string command</returns>
573: protected virtual string SetupSelectCommand(string Columns, int Limit, string OrderBy, IParameter[] Parameters)
574: {
575: string Command = (Limit > 0 ? "SELECT TOP " + Limit : "SELECT") + " {0} FROM {1}";
576: if (Parameters != null && Parameters.Length > 0)
577: {
578: Command += " WHERE ";
579: string Splitter = "";
580: foreach (IParameter Parameter in Parameters)
581: {
582: Command += Splitter + Parameter;
583: Splitter = " AND ";
584: }
585: }
586: if (!string.IsNullOrEmpty(OrderBy))
587: Command += OrderBy.Trim().ToLower().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase) ? " " + OrderBy : " ORDER BY " + OrderBy;
588: return string.Format(Command, Columns, TableName);
589: }
590:
591: #endregion
592:
593: #region SetupUpdateCommand
594:
595: /// <summary>
596: /// Sets up the update command
597: /// </summary>
598: /// <returns>The command string</returns>
599: protected virtual string SetupUpdateCommand()
600: {
601: string ParameterList = "";
602: string WhereCommand = "";
603: string Splitter = "";
604: foreach (string Name in ParameterNames)
605: {
606: if (Name != PrimaryKey)
607: {
608: ParameterList += Splitter + Name + "=" + ParameterStarter + Name;
609: Splitter = ",";
610: }
611: else
612: WhereCommand = Name + "=" + ParameterStarter + Name;
613: }
614: return string.Format("UPDATE {0} SET {1} WHERE {2}", TableName, ParameterList, WhereCommand);
615: }
616:
617: #endregion
618:
619: #endregion
620:
621: #region IDisposable
622:
623: /// <summary>
624: /// Dispose
625: /// </summary>
626: public void Dispose()
627: {
628: if (Helper != null)
629: {
630: Helper = null;
631: }
632: }
633:
634: #endregion
635: }
636: }