1. Object Relational Mapping(ORM)

2. Persistence • Almost all applications require some form of persistence. • Almost all persistence is done using relational databases. • Usually the business classes are the ones that are persisted.

3. Actual Example lsSqlStatement = "INSERT INTO tblClaimsHistory " + "(fldBatchLet, fldBatchNum, fldBatchFy,fldBatchReqSeq, fldPatientSSN,fldProvTaxId,fldProvSuff,fldProvType,"+ "fldDiag1,fldDiag2,fldDiag3,fldDiag4, " + "fldPatientLastName, fldPatientFirstName, fldProviderName ," + "fldTotalCharged,fldTotalToPay,fldTotalDue,fldTotalCalcAmount,fldTotalPaid, "+ "fldRecDate, fldClaimBeginDate, fldClaimEndDate,fldCurrDocNum, fldMailCode, MessageIdentifier ) " + "VALUES ('"+lsBatchNum.Substring(0,1)+"',"+lsBatchNum.Substring(1)+","+lsBatchFy+",'"+ lsBatchReqSeq + "', '" +lsSSN+"','"+lsFedTaxID+"','"+lsProvTaxSfx+"','"+lsProviderType +"','"+ fldDiag1+"','"+fldDiag2+"','"+fldDiag3+"','"+fldDiag4 +fldPatientLastName+"','"+fldPatientFirstName+"','" +lsProviderName+"',"+ fldTotalCharged+","+fldTotalToPay+","+fldTotalDue+","+fldTotalCalcAmount+","+fldTotalAmtPaid +",'"+ClaimRecievedDate + "','" + fldClaimBeginDate + "','" + fldClaimEndDate + "','"+fldCurrDocNum+"','"+fldMailCode+"','"+MessageIdentifier+"') " +"SELECT @@IDENTITY AS 'Identity'";

4. And as preparation we need: lsBeginDate = lsBeginDate.Replace("-", "/"); lsEndDate = lsEndDate.Replace("-", "/"); OR Replace("'","''");

5. Usually we have • A business Object • & A database Table

6. Traditional way to handle persistence • Using a N-Tier design • UI Tier • Business Tier • Data Tier • Database

7. This approach requires • Long and tedious work to: • Build SQL statements for Insert, update, Delete, select • Send the Objects’ properties to the data layer as parameters • Account for different types of databases/data fields (ex: ‘ ‘ for strings and dates, format dates, handling Null values,.. • Handle IDs, Keys,..

8. This work is usually.. • Time Consuming • Boring • Error Prone • And most importantly: • Doesn’t require much thinking • Shifts focus from main target ( handle business case)

9. ORM • ORM is about Mapping an Object to one or more database tables. • It eliminates the need to create a data layer tier ( data layer is implicit) • “In a nutshell, object/relational mapping is the automated (and transparent) persistence of objects in an application to the tables in a relational database, using metadata that describes the mapping between the objects and the database.” • In short: ORM saves you from writing boring and error prone code thus saving time and getting better quality.

10. Main Advantages of ORM • Better productivity ( no more tedious coding) • Better performance • DB independence • Less error prone

11. Many ORM tools, Many Approaches • There are different Tools for implementing ORM using different approach • Using Attributes in the classes (GENTLE.NET) • Using XML mapping Files (NHIBERNATE) • Using Visual Mapping, • … etc.

12. Introduction to GENTLE.NET • Open source, free tool. • Supports many DB types. • Uses attributes .. No complex configuration files • Queries generated at runtime using features in the .NET framework (class attributes) • Handles “impedance mismatch” (Null values, auto generated keys,..) • Handles transactions • Supports caching • Supports validations on the object level

13. Example Class public class User { private int userId; private string userName; public User( int userId, string userName ) { this.userId = userId; this.userName = userName; } public int Id { get{ return userId; } set{ userId = value; } } public string Name { get{ return userName; } set{ userName = value; } } } • To be saved in

14. All we need to do is • Add reference to 3 DLLs • Add 3 “using..” lines • Add appropriate attributes to object propertied

15. Class Becomes using Gentle.Framework; [TableName("Users")] public class User : Persistent { private int userId; private string userName; // this is used by clients to construct new users public User( string userName ) : this( 0, userName ) {} // this is used by Gentle to reconstruct objects read from the database public User( int userId, string userName ) { this.userId = userId; this.userName = userName; } // this is used by client to fetch users from the database static public User Retrieve( int userId ) { Key key = new Key( typeof(User), true, "Id", userId ); return Broker.RetrieveInstance( typeof(User), key ) as User; } [TableColumn("UserId"), PrimaryKey(AutoGenerated=true)] public int Id { get{ return userId; } set{ userId = value; } } [TableColumn(NotNull=true)] public string Name { get{ return userName; } set{ userName = value; } } }

16. That was all the “SQL” we need to write. • Also we don’t need to “create” an object from a row in DB. It is done implicitly • To save an object all we need to do is: User ford = new User( "Ford Prefect" ); ford.Persist(); • To Load an object all we need to do is: User prefect = User.Retrieve( ford.Id ); Using Id ( or any other Key) BINGO !!!

17. Demo

18. Additives Using SQL builder static public IList ListByNameStartsWith( string partialName ) { SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(User) ); // note: the partialName parameter must also contain the %'s for the LIKE query! sb.AddConstraint( Operator.Like, "Name", partialName ); // passing true indicates that we'd like a list of elements, i.e. that no primary key // constraints from the type being retrieved should be added to the statement SqlStatement stmt = sb.GetStatement( true ); // execute the statement/query and create a collection of User instances from the result set return ObjectFactory.GetCollection( typeof(User), stmt.Execute() ); }

19. Additives • Retrieving Lists of objects • IList is directly bindable with data grids. GentleList list = new GentleList( typeof(User), parentInstance );

20. Additives • Generating business class using MyGeneration • Demo

21. References • http://www.mertner.com/projects/gentle • http://www.mygenerationsoftware.com

22. Thank You! Emad Magdy