Blog Series RSS FeedFollow gutgames on TwitterTwitter Feed



Other Posts in ORM

  1. Creating an ORM in C# - Part 1
  2. Creating an ORM in C# - Part 2
  3. Creating an ORM in C# - Part 3
  4. Creating an ORM in C# - Part 4
  5. Creating an ORM in C# - Part 5
  6. Creating an ORM in C# - Part 6
  7. Creating an ORM in C# - Part 7
  8. Creating an ORM in C# Revisited – Part 1
  9. Creating an ORM in C# Revisited – Part 2

Creating an ORM in C# Revisited – Part 2

6/3/2011

Apparently this didn't actually get uploaded, so here's the second part of this a bit late along with the third. Anyway, last time I talked about coming up with a simple object to object mapper to help speed up and simplify the ORM. In this post we're going to worry about actually connecting to the database. In Dapper and Massive, they simply uses extension methods to DBConnections. I could easily do that but for a while now I've been using a class called SQLHelper to simplify connecting to a database (not Microsoft's SQLHelper, it's my own class). For me, it simplifies things quite a bit (I can set up the connection in one line instead of ten), so I'll be using that instead:

   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.Data;
  25: using System.Data.SqlClient;
  26: using System.Xml;
  27: using System.Data.Common;
  28: #endregion
  29:  
  30: namespace Utilities.SQL
  31: {
  32:     /// <summary>
  33:     /// SQL Helper class
  34:     /// </summary>
  35:     public class SQLHelper : IDisposable
  36:     {
  37:         #region Constructors
  38:  
  39:         /// <summary>
  40:         /// Constructor
  41:         /// </summary>
  42:         /// <param name="Command">Stored procedure/SQL Text to use</param>
  43:         /// <param name="ConnectionUsing">The connection string to user</param>
  44:         /// <param name="CommandType">The command type of the command sent in</param>
  45:         public SQLHelper(string Command, string ConnectionUsing, CommandType CommandType, string DbType = "System.Data.SqlClient")
  46:         {
  47:             Factory = DbProviderFactories.GetFactory(DbType);
  48:             Connection = Factory.CreateConnection();
  49:             Connection.ConnectionString = ConnectionUsing;
  50:             _Command = Command;
  51:             _CommandType = CommandType;
  52:             ExecutableCommand = Factory.CreateCommand();
  53:             ExecutableCommand.CommandText = _Command;
  54:             ExecutableCommand.Connection = Connection;
  55:             ExecutableCommand.CommandType = CommandType;
  56:         }
  57:  
  58:         #endregion
  59:  
  60:         #region Properties
  61:  
  62:         /// <summary>
  63:         /// Db provider factory (creates connections, etc.)
  64:         /// </summary>
  65:         protected virtual DbProviderFactory Factory { get; set; }
  66:  
  67:         /// <summary>
  68:         /// Connection to the database
  69:         /// </summary>
  70:         protected virtual DbConnection Connection { get; set; }
  71:  
  72:         /// <summary>
  73:         /// The executable command
  74:         /// </summary>
  75:         protected DbCommand ExecutableCommand { get; set; }
  76:  
  77:         /// <summary>
  78:         /// The data reader for the query
  79:         /// </summary>
  80:         protected DbDataReader Reader { get; set; }
  81:  
  82:         /// <summary>
  83:         /// The transaction associated with the query
  84:         /// </summary>
  85:         protected DbTransaction Transaction { get; set; }
  86:  
  87:         /// <summary>
  88:         /// Stored procedure's name or SQL Text
  89:         /// </summary>
  90:         public virtual string Command
  91:         {
  92:             get { return _Command; }
  93:             set
  94:             {
  95:                 _Command = value;
  96:                 RecreateConnection();
  97:             }
  98:         }
  99:  
 100:         private string _Command = null;
 101:  
 102:  
 103:         /// <summary>
 104:         /// Command Type
 105:         /// </summary>
 106:         public virtual CommandType CommandType
 107:         {
 108:             get { return _CommandType; }
 109:             set
 110:             {
 111:                 _CommandType = value;
 112:                 RecreateConnection();
 113:             }
 114:         }
 115:         private CommandType _CommandType;
 116:  
 117:         #endregion
 118:  
 119:         #region Functions
 120:  
 121:         #region AddOutputParameter
 122:  
 123:         /// <summary>
 124:         /// Adds an output parameter
 125:         /// </summary>
 126:         /// <param name="ID">Name of the parameter</param>
 127:         /// <param name="Type">SQL type of the parameter</param>
 128:         public virtual void AddOutputParameter(string ID, SqlDbType Type)
 129:         {
 130:             AddOutputParameter(ID, Utilities.DataTypes.DataTypeConversion.SqlDbTypeToDbType(Type));
 131:         }
 132:  
 133:         /// <summary>
 134:         /// Adds an output parameter
 135:         /// </summary>
 136:         /// <param name="ID">Name of the parameter</param>
 137:         /// <param name="Type">SQL type of the parameter</param>
 138:         public virtual void AddOutputParameter(string ID, DbType Type)
 139:         {
 140:             if (ExecutableCommand != null)
 141:             {
 142:                 DbParameter Parameter = null;
 143:                 if (ExecutableCommand.Parameters.Contains(ID))
 144:                     Parameter = ExecutableCommand.Parameters[ID];
 145:                 else
 146:                 {
 147:                     Parameter = ExecutableCommand.CreateParameter();
 148:                     ExecutableCommand.Parameters.Add(Parameter);
 149:                 }
 150:                 Parameter.ParameterName = ID;
 151:                 Parameter.Value = null;
 152:                 Parameter.DbType = Type;
 153:                 Parameter.Direction = ParameterDirection.Output;
 154:             }
 155:         }
 156:  
 157:         /// <summary>
 158:         /// Adds an output parameter
 159:         /// </summary>
 160:         /// <typeparam name="DataType">Data type of the parameter</typeparam>
 161:         /// <param name="ID">ID associated with the output parameter</param>
 162:         public virtual void AddOutputParameter<DataType>(string ID)
 163:         {
 164:             AddOutputParameter(ID, Utilities.DataTypes.DataTypeConversion.NetTypeToDbType(typeof(DataType)));
 165:         }
 166:  
 167:         /// <summary>
 168:         /// Adds an output parameter
 169:         /// </summary>
 170:         /// <param name="ID">Name of the parameter</param>
 171:         /// <param name="Length">Length of the string (either -1 or 5000 should be used to indicate nvarchar(max))</param>
 172:         public virtual void AddOutputParameter(string ID, int Length)
 173:         {
 174:             if (Length == 5000)
 175:                 Length = -1;
 176:             if (ExecutableCommand != null)
 177:             {
 178:                 DbParameter Parameter = null;
 179:                 if (ExecutableCommand.Parameters.Contains(ID))
 180:                     Parameter = ExecutableCommand.Parameters[ID];
 181:                 else
 182:                 {
 183:                     Parameter = ExecutableCommand.CreateParameter();
 184:                     ExecutableCommand.Parameters.Add(Parameter);
 185:                 }
 186:                 Parameter.ParameterName = ID;
 187:                 Parameter.Value = null;
 188:                 Parameter.DbType = Utilities.DataTypes.DataTypeConversion.NetTypeToDbType(typeof(string));
 189:                 Parameter.Direction = ParameterDirection.Output;
 190:                 Parameter.Size = Length;
 191:             }
 192:         }
 193:  
 194:         #endregion
 195:  
 196:         #region AddParameter
 197:  
 198:         /// <summary>
 199:         /// Adds a parameter to the call (for strings only)
 200:         /// </summary>
 201:         /// <param name="ID">Name of the parameter</param>
 202:         /// <param name="Value">Value to add</param>
 203:         /// <param name="Length">Size of the string(either -1 or 5000 should be used to indicate nvarchar(max))</param>
 204:         public virtual void AddParameter(string ID, string Value, int Length)
 205:         {
 206:             if (Length == 5000)
 207:                 Length = -1;
 208:             if (ExecutableCommand != null)
 209:             {
 210:                 DbParameter Parameter = null;
 211:                 if (ExecutableCommand.Parameters.Contains(ID))
 212:                     Parameter = ExecutableCommand.Parameters[ID];
 213:                 else
 214:                 {
 215:                     Parameter = ExecutableCommand.CreateParameter();
 216:                     ExecutableCommand.Parameters.Add(Parameter);
 217:                 }
 218:                 Parameter.ParameterName = ID;
 219:                 Parameter.Value = (string.IsNullOrEmpty(Value)) ? System.DBNull.Value : (object)Value;
 220:                 Parameter.IsNullable = (string.IsNullOrEmpty(Value));
 221:                 Parameter.DbType = Utilities.DataTypes.DataTypeConversion.NetTypeToDbType(typeof(string));
 222:                 Parameter.Direction = ParameterDirection.Input;
 223:                 Parameter.Size = Length;
 224:             }
 225:         }
 226:  
 227:         /// <summary>
 228:         /// Adds a parameter to the call (for all types other than strings)
 229:         /// </summary>
 230:         /// <param name="ID">Name of the parameter</param>
 231:         /// <param name="Value">Value to add</param>
 232:         /// <param name="Type">SQL type of the parameter</param>
 233:         public virtual void AddParameter(string ID, object Value, SqlDbType Type)
 234:         {
 235:             AddParameter(ID, Value, Utilities.DataTypes.DataTypeConversion.SqlDbTypeToDbType(Type));
 236:         }
 237:  
 238:         /// <summary>
 239:         /// Adds a parameter to the call (for all types other than strings)
 240:         /// </summary>
 241:         /// <typeparam name="DataType">Data type of the parameter</typeparam>
 242:         /// <param name="ID">Name of the parameter</param>
 243:         /// <param name="Value">Value to add</param>
 244:         public virtual void AddParameter<DataType>(string ID, DataType Value)
 245:         {
 246:             AddParameter(ID, Value, Utilities.DataTypes.DataTypeConversion.NetTypeToDbType(Value.GetType()));
 247:         }
 248:  
 249:         /// <summary>
 250:         /// Adds a parameter to the call (for all types other than strings)
 251:         /// </summary>
 252:         /// <param name="ID">Name of the parameter</param>
 253:         /// <param name="Value">Value to add</param>
 254:         /// <param name="Type">SQL type of the parameter</param>
 255:         public virtual void AddParameter(string ID, object Value, DbType Type)
 256:         {
 257:             if (ExecutableCommand != null)
 258:             {
 259:                 DbParameter Parameter = null;
 260:                 if (ExecutableCommand.Parameters.Contains(ID))
 261:                     Parameter = ExecutableCommand.Parameters[ID];
 262:                 else
 263:                 {
 264:                     Parameter = ExecutableCommand.CreateParameter();
 265:                     ExecutableCommand.Parameters.Add(Parameter);
 266:                 }
 267:                 Parameter.ParameterName = ID;
 268:                 Parameter.Value = (Value == null) ? System.DBNull.Value : Value;
 269:                 Parameter.IsNullable = (Value == null);
 270:                 Parameter.DbType = Type;
 271:                 Parameter.Direction = ParameterDirection.Input;
 272:             }
 273:         }
 274:  
 275:         #endregion
 276:  
 277:         #region BeginTransaction
 278:  
 279:         /// <summary>
 280:         /// Begins a transaction
 281:         /// </summary>
 282:         public virtual void BeginTransaction()
 283:         {
 284:             Open();
 285:             Transaction = Connection.BeginTransaction();
 286:             Command = _Command;
 287:         }
 288:  
 289:         #endregion
 290:  
 291:         #region ClearParameters
 292:  
 293:         /// <summary>
 294:         /// Clears the parameters
 295:         /// </summary>
 296:         public virtual void ClearParameters()
 297:         {
 298:             if (ExecutableCommand != null)
 299:                 ExecutableCommand.Parameters.Clear();
 300:         }
 301:  
 302:         #endregion
 303:  
 304:         #region Close
 305:  
 306:         /// <summary>
 307:         /// Closes the connection
 308:         /// </summary>
 309:         public virtual void Close()
 310:         {
 311:             if (ExecutableCommand != null
 312:                 && ExecutableCommand.Connection != null
 313:                 && ExecutableCommand.Connection.State != ConnectionState.Closed)
 314:                 ExecutableCommand.Connection.Close();
 315:         }
 316:  
 317:         #endregion
 318:  
 319:         #region Commit
 320:  
 321:         /// <summary>
 322:         /// Commits a transaction
 323:         /// </summary>
 324:         public virtual void Commit()
 325:         {
 326:             if (Transaction != null)
 327:                 Transaction.Commit();
 328:         }
 329:  
 330:         #endregion
 331:  
 332:         #region ExecuteDataSet
 333:  
 334:         /// <summary>
 335:         /// Executes the query and returns a data set
 336:         /// </summary>
 337:         /// <returns>A dataset filled with the results of the query</returns>
 338:         public virtual DataSet ExecuteDataSet()
 339:         {
 340:             Open();
 341:             if (ExecutableCommand != null)
 342:             {
 343:                 DbDataAdapter Adapter = Factory.CreateDataAdapter();
 344:                 Adapter.SelectCommand = ExecutableCommand;
 345:                 DataSet ReturnSet = new DataSet();
 346:                 Adapter.Fill(ReturnSet);
 347:                 return ReturnSet;
 348:             }
 349:             return null;
 350:         }
 351:  
 352:         #endregion
 353:  
 354:         #region ExecuteNonQuery
 355:  
 356:         /// <summary>
 357:         /// Executes the stored procedure as a non query
 358:         /// </summary>
 359:         /// <returns>Number of rows effected</returns>
 360:         public virtual int ExecuteNonQuery()
 361:         {
 362:             Open();
 363:             if (ExecutableCommand != null)
 364:                 return ExecutableCommand.ExecuteNonQuery();
 365:             return 0;
 366:         }
 367:  
 368:         #endregion
 369:  
 370:         #region ExecuteReader
 371:  
 372:         /// <summary>
 373:         /// Executes the stored procedure and returns a reader object
 374:         /// </summary>
 375:         public virtual void ExecuteReader()
 376:         {
 377:             Open();
 378:             if (ExecutableCommand != null)
 379:                 Reader = ExecutableCommand.ExecuteReader();
 380:         }
 381:  
 382:         #endregion
 383:  
 384:         #region ExecuteScalar
 385:  
 386:         /// <summary>
 387:         /// Executes the stored procedure as a scalar query
 388:         /// </summary>
 389:         /// <returns>The object of the first row and first column</returns>
 390:         public virtual object ExecuteScalar()
 391:         {
 392:             Open();
 393:             if (ExecutableCommand != null)
 394:                 return ExecutableCommand.ExecuteScalar();
 395:             return null;
 396:         }
 397:  
 398:         #endregion
 399:  
 400:         #region ExecuteXmlReader
 401:  
 402:         /// <summary>
 403:         /// Executes the query and returns an XmlReader
 404:         /// </summary>
 405:         /// <returns>The XmlReader filled with the data from the query</returns>
 406:         public virtual XmlReader ExecuteXmlReader()
 407:         {
 408:             Open();
 409:             if (ExecutableCommand != null && ExecutableCommand is SqlCommand)
 410:                 return ((SqlCommand)ExecutableCommand).ExecuteXmlReader();
 411:             return null;
 412:         }
 413:  
 414:         #endregion
 415:  
 416:         #region GetParameter
 417:  
 418:         /// <summary>
 419:         /// Returns a parameter's value
 420:         /// </summary>
 421:         /// <param name="ID">Parameter name</param>
 422:         /// <param name="Default">Default value for the parameter</param>
 423:         /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
 424:         public virtual object GetParameter(string ID, object Default)
 425:         {
 426:             if (Reader != null && !Convert.IsDBNull(Reader[ID]))
 427:                 return Reader[ID];
 428:             return Default;
 429:         }
 430:  
 431:         /// <summary>
 432:         /// Returns a parameter's value
 433:         /// </summary>
 434:         /// <typeparam name="DataType">Data type of the object</typeparam>
 435:         /// <param name="ID">Parameter name</param>
 436:         /// <param name="Default">Default value for the parameter</param>
 437:         /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
 438:         public virtual DataType GetParameter<DataType>(string ID, DataType Default)
 439:         {
 440:             if (Reader != null && !Convert.IsDBNull(Reader[ID]))
 441:                 return (DataType)Convert.ChangeType(Reader[ID], typeof(DataType));
 442:             return Default;
 443:         }
 444:  
 445:         /// <summary>
 446:         /// Returns a parameter's value
 447:         /// </summary>
 448:         /// <param name="Position">Position in the row</param>
 449:         /// <param name="Default">Default value for the parameter</param>
 450:         /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
 451:         public virtual object GetParameter(int Position, object Default)
 452:         {
 453:             if (Reader != null && !Convert.IsDBNull(Reader[Position]))
 454:                 return Reader[Position];
 455:             return Default;
 456:         }
 457:  
 458:         /// <summary>
 459:         /// Returns a parameter's value
 460:         /// </summary>
 461:         /// <typeparam name="DataType">Data type of the object</typeparam>
 462:         /// <param name="Position">Position in the row</param>
 463:         /// <param name="Default">Default value for the parameter</param>
 464:         /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
 465:         public virtual DataType GetParameter<DataType>(int Position, DataType Default)
 466:         {
 467:             if (Reader != null && !Convert.IsDBNull(Reader[Position]))
 468:                 return (DataType)Convert.ChangeType(Reader[Position], typeof(DataType));
 469:             return Default;
 470:         }
 471:  
 472:         #endregion
 473:  
 474:         #region GetOutputParameter
 475:  
 476:         /// <summary>
 477:         /// Returns an output parameter's value
 478:         /// </summary>
 479:         /// <param name="ID">Parameter name</param>
 480:         /// <param name="Default">Default value for the parameter</param>
 481:         /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
 482:         public virtual object GetOutputParameter(string ID, object Default)
 483:         {
 484:             if (ExecutableCommand != null && !Convert.IsDBNull(ExecutableCommand.Parameters[ID]))
 485:                 return ExecutableCommand.Parameters[ID].Value;
 486:             return Default;
 487:         }
 488:  
 489:         /// <summary>
 490:         /// Returns an output parameter's value
 491:         /// </summary>
 492:         /// <typeparam name="DataType">Data type of the object</typeparam>
 493:         /// <param name="ID">Parameter name</param>
 494:         /// <param name="Default">Default value for the parameter</param>
 495:         /// <returns>if the parameter exists (and isn't null or empty), it returns the parameter's value. Otherwise the default value is returned.</returns>
 496:         public virtual DataType GetOutputParameter<DataType>(string ID, DataType Default)
 497:         {
 498:             if (ExecutableCommand != null && !Convert.IsDBNull(ExecutableCommand.Parameters[ID]))
 499:                 return (DataType)Convert.ChangeType(ExecutableCommand.Parameters[ID].Value, typeof(DataType));
 500:             return Default;
 501:         }
 502:  
 503:         #endregion
 504:  
 505:         #region NextResult
 506:  
 507:         /// <summary>
 508:         /// Goes to the next result set (used if multiple queries are sent in)
 509:         /// </summary>
 510:         public virtual void NextResult()
 511:         {
 512:             if (Reader != null)
 513:                 Reader.NextResult();
 514:         }
 515:  
 516:         #endregion
 517:  
 518:         #region Open
 519:  
 520:         /// <summary>
 521:         /// Opens the connection
 522:         /// </summary>
 523:         public virtual void Open()
 524:         {
 525:             if (ExecutableCommand != null 
 526:                 && ExecutableCommand.Connection != null 
 527:                 && ExecutableCommand.Connection.State != ConnectionState.Open)
 528:                 ExecutableCommand.Connection.Open();
 529:         }
 530:  
 531:         #endregion
 532:  
 533:         #region Read
 534:  
 535:         /// <summary>
 536:         /// Is there more information?
 537:         /// </summary>
 538:         /// <returns>True if there is more rows, false otherwise</returns>
 539:         public virtual bool Read()
 540:         {
 541:             return (Reader != null) ? Reader.Read() : false;
 542:         }
 543:  
 544:         #endregion
 545:  
 546:         #region RecreateConnection
 547:  
 548:         private void RecreateConnection()
 549:         {
 550:             if (Reader != null)
 551:             {
 552:                 Reader.Close();
 553:                 Reader.Dispose();
 554:                 Reader = null;
 555:             }
 556:             if (ExecutableCommand != null)
 557:             {
 558:                 ExecutableCommand.Dispose();
 559:                 ExecutableCommand = null;
 560:             }
 561:             ExecutableCommand = Factory.CreateCommand();
 562:             ExecutableCommand.CommandText = _Command;
 563:             ExecutableCommand.Connection = Connection;
 564:             ExecutableCommand.CommandType = CommandType;
 565:             if (Transaction != null)
 566:                 ExecutableCommand.Transaction = Transaction;
 567:         }
 568:  
 569:         #endregion
 570:  
 571:         #region Rollback
 572:  
 573:         /// <summary>
 574:         /// Rolls back a transaction
 575:         /// </summary>
 576:         public virtual void Rollback()
 577:         {
 578:             if (Transaction != null)
 579:                 Transaction.Rollback();
 580:         }
 581:  
 582:         #endregion
 583:  
 584:         #endregion
 585:  
 586:         #region IDisposable Members
 587:  
 588:         public virtual void Dispose()
 589:         {
 590:             Close();
 591:             if (Connection != null)
 592:             {
 593:                 Connection.Dispose();
 594:                 Connection = null;
 595:             }
 596:             if (Transaction != null)
 597:             {
 598:                 Transaction.Dispose();
 599:                 Transaction = null;
 600:             }
 601:             if (ExecutableCommand != null)
 602:             {
 603:                 ExecutableCommand.Dispose();
 604:                 ExecutableCommand = null;
 605:             }
 606:             if (Reader != null)
 607:             {
 608:                 Reader.Dispose();
 609:                 Reader = null;
 610:             }
 611:         }
 612:  
 613:         #endregion
 614:     }
 615: }

Note that you don't have to use something like this, you can simply add extension methods to DBConnection (like Massive/Dapper). This is just a bit of code to help make things easier for me. The next bit of code that is needed is a way to map the various properties of the business object to the database:

   1: #region Map
   2:  
   3: /// <summary>
   4: /// Maps a property to a database property name (required to actually get data from the database)
   5: /// </summary>
   6: /// <typeparam name="DataType">Data type of the property</typeparam>
   7: /// <param name="Property">Property to add a mapping for</param>
   8: /// <param name="DatabasePropertyName">Property name</param>
   9: public virtual Mapping<ClassType> Map<DataType>(Expression<Func<ClassType, DataType>> Property, string DatabasePropertyName)
  10: {
  11:     Check(Property, "Property");
  12:     Check(DatabasePropertyName, "DatabasePropertyName");
  13:     Check(Mappings, "Mappings");
  14:     Expression Convert = Expression.Convert(Property.Body, typeof(object));
  15:     Expression<Func<ClassType, object>> PropertyExpression = Expression.Lambda<Func<ClassType, object>>(Convert, Property.Parameters);
  16:     Mappings.AddMapping(PropertyExpression,
  17:         new Func<SQLHelper, object>((x) => x.GetParameter(DatabasePropertyName, default(DataType))),
  18:         new Action<SQLHelper, object>((x, y) => x.AddParameter(DatabasePropertyName, y)));
  19:     ParameterNames.Add(DatabasePropertyName);
  20:     return this;
  21: }
  22:  
  23: /// <summary>
  24: /// Maps a property to a database property name (required to actually get data from the database)
  25: /// </summary>
  26: /// <param name="Property">Property to add a mapping for</param>
  27: /// <param name="DatabasePropertyName">Property name</param>
  28: /// <param name="Length">Max length of the string</param>
  29: public virtual Mapping<ClassType> Map(Expression<Func<ClassType, string>> Property, string DatabasePropertyName, int Length)
  30: {
  31:     Check(Property, "Property");
  32:     Check(DatabasePropertyName, "DatabasePropertyName");
  33:     Check(Mappings, "Mappings");
  34:     Expression Convert = Expression.Convert(Property.Body, typeof(object));
  35:     Expression<Func<ClassType, object>> PropertyExpression = Expression.Lambda<Func<ClassType, object>>(Convert, Property.Parameters);
  36:     Mappings.AddMapping(PropertyExpression,
  37:         new Func<SQLHelper, object>((x) => x.GetParameter(DatabasePropertyName, "")),
  38:         new Action<SQLHelper, object>((x, y) => x.AddParameter(DatabasePropertyName, (string)y, Length)));
  39:     ParameterNames.Add(DatabasePropertyName);
  40:     return this;
  41: }
  42:  
  43: #endregion

This code comes from a class called Mapping, which will hold our data and make our calls to the database. These functions simply map our properties to database properties. As you can see, it takes in an expression and the name of the parameter (database side). It in turn uses that expression and our object to object mapper from the previous post to create a mapping between the SQLHelper (the GetParameter/AddParameter calls). This isn't perfect as it doesn't deal with IEnumerables that well but we're only going to deal with simple items for now (int, string, etc.). Also it returns this so we can do a nice, simple fluent interface. So now that we've seen the small bit of code that handles the mapping of the data, lets look at everything else:

   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: }

Lets start with the top, the constructors take in (at minimum) the table name and primary key for the class. Doing this makes things a lot simpler later on (specifically inserts, updates, etc.). But basically we're doing basic data setup. The next bit is the properties that are needed, we'll skip that... The next, actually interesting bit of code is All. The All function makes a query against the database and gives us all entries that it finds. For the most part it's pretty straightforward, it takes in the Command (could be sql text or a stored procedure), the command type, and allows you to add a number of parameters if they are needed. The parameter class looks like the following:

   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.SQL.MicroORM.Interfaces;
  28: #endregion
  29:  
  30: namespace Utilities.SQL.MicroORM
  31: {
  32:     /// <summary>
  33:     /// Parameter class
  34:     /// </summary>
  35:     /// <typeparam name="DataType">Type of the parameter</typeparam>
  36:     public class Parameter<DataType>:IParameter
  37:     {
  38:         #region Constructor
  39:  
  40:         /// <summary>
  41:         /// Constructor
  42:         /// </summary>
  43:         /// <param name="Value">Value of the parameter</param>
  44:         /// <param name="ID">Name of the parameter</param>
  45:         /// <param name="ParameterStarter">What the database expects as the
  46:         /// parameter starting string ("@" for SQL Server, ":" for Oracle, etc.)</param>
  47:         public Parameter(DataType Value, string ID, string ParameterStarter = "@")
  48:         {
  49:             this.Value = Value;
  50:             this.ID = ID;
  51:             this.ParameterStarter = ParameterStarter;
  52:         }
  53:  
  54:         #endregion
  55:  
  56:         #region Properties
  57:  
  58:         /// <summary>
  59:         /// Value of the parameter
  60:         /// </summary>
  61:         public DataType Value { get; set; }
  62:  
  63:         /// <summary>
  64:         /// Name of the parameter
  65:         /// </summary>
  66:         public string ID { get; set; }
  67:  
  68:         /// <summary>
  69:         /// Starting string of the parameter
  70:         /// </summary>
  71:         public string ParameterStarter { get; set; }
  72:  
  73:         #endregion
  74:  
  75:         #region Functions
  76:  
  77:         public void AddParameter(SQLHelper Helper) { Helper.AddParameter(ID, Value); }
  78:  
  79:         public override string ToString() { return ID + "=" + ParameterStarter + ID; }
  80:  
  81:         #endregion
  82:     }
  83: }

The parameter class is really just a glorified key/value pair. Anyway, the All function just creates a list, calls our command, and lets the object to object mapper fill our object for us. We have another option (the function that asks for Columns, etc.), that will allow us to simply call All() if we only want to worry about such things like an order by clause. In this call though the class is generating the select statement for us (Pretty standard: SELECT Columns FROM TableName WHERE [List of parameters are true] ORDER BY OrderByClause).

You'll find that most of the functions are similar to All. For instance, the Any function is pretty much a copy but it only returns the first item found by the query. Delete, Update, and Insert are only slightly different. Instead of copying data from the SQLHelper to the object, it simply goes the other way around. They all have the option to generate the code for you. Since we've set up our mappings beforehand, we know the names of the properties and we know the primary key from the constructor so it's not too difficult to generate the needed queries.

The only function that might give you pause are the Pages and PageCount functions. Paged is simply a paged query (automatically generated). It works similarly to All but takes in page size and the current page that you want (0 based as I'm not a big fan of starting at 1 for such things). The code that it generates is also rather straightforward (but probably not the best), if you've ever done a paged query before. That being said, it's currently pretty specific to SQL Server but I'll probably change that to make it a bit more open in the future. Anyway, I also have a separate query called PageCount to allow you to figure out the total number of pages based on the page size that you specify. I could have that as an out parameter of the Paged function, but I figure you might want that information separately. But that's it really. There are also Open/Close functions to actually open the connection to the database, but those are pretty simple.

So at this point we have our simple mapping of a single class. What we need now is a class to hold all of our mappings that we can use to make this simpler:

   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:     /// Manager class that can be used to manage
  37:     /// </summary>
  38:     public class MicroORM : SQLHelper
  39:     {
  40:         #region Constructor
  41:  
  42:         /// <summary>
  43:         /// Constructor
  44:         /// </summary>
  45:         public MicroORM(string Connection)
  46:             : base("", Connection, CommandType.Text)
  47:         {
  48:         }
  49:  
  50:         #endregion
  51:  
  52:         #region Properties
  53:  
  54:         /// <summary>
  55:         /// Mappings
  56:         /// </summary>
  57:         protected static Dictionary<Type, IMapping> Mappings = new Dictionary<Type, IMapping>();
  58:  
  59:         #endregion
  60:  
  61:         #region Functions
  62:  
  63:         /// <summary>
  64:         /// Creates a mapping
  65:         /// </summary>
  66:         /// <typeparam name="ClassType">Class type to map</typeparam>
  67:         /// <param name="TableName">Table name</param>
  68:         /// <param name="PrimaryKey">Primary key</param>
  69:         /// <param name="AutoIncrement">Auto incrementing primar key</param>
  70:         /// <param name="ParameterStarter">Parameter starter</param>
  71:         /// <returns>The created mapping (or an already created one if it exists</returns>
  72:         public static Mapping<ClassType> Map<ClassType>(string TableName, string PrimaryKey, bool AutoIncrement = true, string ParameterStarter = "@") where ClassType : class,new()
  73:         {
  74:             if (Mappings.ContainsKey(typeof(ClassType)))
  75:                 return (Mapping<ClassType>)Mappings[typeof(ClassType)];
  76:             Mapping<ClassType> Mapping = new Mapping<ClassType>(TableName, PrimaryKey, AutoIncrement, ParameterStarter);
  77:             Mappings.Add(typeof(ClassType), Mapping);
  78:             return Mapping;
  79:         }
  80:  
  81:         /// <summary>
  82:         /// Returns a specific mapping
  83:         /// </summary>
  84:         /// <typeparam name="ClassType">Class type to get</typeparam>
  85:         /// <returns>The mapping specified</returns>
  86:         public Mapping<ClassType> Map<ClassType>() where ClassType : class,new()
  87:         {
  88:             if (!Mappings.ContainsKey(typeof(ClassType)))
  89:                 throw new ArgumentOutOfRangeException(typeof(ClassType).Name + " not found");
  90:             Mapping<ClassType> ReturnValue = (Mapping<ClassType>)Mappings[typeof(ClassType)];
  91:             ReturnValue.Helper = this;
  92:             return ReturnValue;
  93:         }
  94:  
  95:         #endregion
  96:  
  97:         #region IDisposable Members
  98:  
  99:         public override void Dispose()
 100:         {
 101:             base.Dispose();
 102:             foreach (Type Key in Mappings.Keys)
 103:             {
 104:                 Mappings[Key].Dispose();
 105:             }
 106:             Mappings.Clear();
 107:         }
 108:  
 109:         #endregion
 110:     }
 111: }

This class really only has a couple of functions, but inherits from the SQLHelper class from earlier. It basically just adds a static Map function (allowing us to store our mapping data in the MicroORM object). It also adds a nonstatic Map function on the object that we can use to actually call our mapping. For instance we can do the following:

   1: class Program
   2: {
   3:     static void Main(string[] args)
   4:     {
   5:         MicroORM.Map<User>("User_", "ID_")
   6:             .Map(x => x.ID, "ID_")
   7:             .Map(x => x.DateCreated, "DateCreated_")
   8:             .Map(x => x.DateModified, "DateModified_")
   9:             .Map(x => x.Active, "Active_")
  10:             .Map(x => x.UserName, "UserName_", 128);
  11:  
  12:         using (MicroORM ORM = new MicroORM("Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=SSPI;"))
  13:         {
  14:             IEnumerable<User> Users = ORM.Map<User>().All();
  15:             foreach (User User in Users)
  16:             {
  17:                 Console.WriteLine(User.UserName);
  18:             }
  19:         }
  20:  
  21:         Console.ReadKey();
  22:     }
  23: }
  24:  
  25: public class User
  26: {
  27:     public virtual int ID { get; set; }
  28:     public virtual DateTime DateModified { get; set; }
  29:     public virtual DateTime DateCreated { get; set; }
  30:     public virtual bool Active { get; set; }
  31:     public virtual string UserName { get; set; }
  32: }

That code sets up the user object in a couple of lines of code, then it creates a MicroORM object an feeds it the connection string. From there it gets the User mapping and calls All (giving us all of the users). It then just spits the user names onto the screen. It's basic, simple, rather quick and all that you need for a micro ORM. In the future I will integrate this into my ORM project. Basically I'll show how to take a micro ORM and add on the features that you might want (database generation, lazy loading, etc.) while still keeping things simple.

Comments

 





 


Recent Posts

Craig’s Utility Library 3.2 Released
Version 3.2 of Craig's Utility Library has been released.
Craig’s Framework
This post talks about Craig's Framework, a set of core classes that will be used in most of my MVC based projects going forward and how I plan to open source it.
Personal Data Management
Start of a project that I will be working on that will attempt to put the vast majority of my personal online life into one location.
Craig’s Utility Library 3.1 Released
New version of Craig's Utility Library has been released.
Small Changes, Big Speed Ups
This post talks about a basic change that I did to the bitmap extensions that gave me a large increase in speed.

Recent Comments

Adjusting Contrast of an Image in C#
Roey - It is better to use ColorMatri...
Enabling JSONP in WCF
BLop - Just to inform :"Cross doma...
Viewstate Compression When Using AJAX
Tahri Akram - I'm using compressing/decompre...
Creating a Zip File in C#
Nicholas Carey - Why not use DotNetZip from htt...
Major Rework Happening
mark foley - Hi!Your site is amazing. ...
DISCLAIMER

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2010