Contents
OR.NET is an open-source O/R Mapper / Code Generator for SQL Server, and the .NET 2.0 framework. I recently posted an article on OR.NET, and after making major updates to the project, I felt that this more comprehensive article would be appropriate.
In a nutshell, OR.NET works by soliciting all necessary O/R information through a simple GUI, and then generates a comprehensive data-access layer entirely in C# 2.0. It supports nullable types, generic and non-generic collections, both foreign key and mapping table relationships, SQL Server Identity Columns, transaction management, and much more.
The download link above will allow you to freely download the complete source code, executable, and comprehensive users manual. I am not posting the code here directly since it would be inconvenient to maintain this project in multiple locations. Also, the OR.NET home page can be found here: O/R Mapper
Just in case anyone reads only a small amount of this article and then downloads the application, I want to state that after you generate your DAL code, you must add a reference to the System.Transactions library in whatever .NET project you bring the generated code into. This is, obviously, necessary for the distributed transaction capability that OR.NET provides.
Currently there is a raging debate in the O/R Mapping community about whether code generation, or an internal DAL is preferable. I seek to neither settle this debate, nor even seriously engage in it here. I will simply say that I created OR.NET as a code generator because I personally prefer to have absolute control over my project, in that I can make a direct, targeted modification at the SQL level should a specific need arise. The alternative of reading through an applications documentation to learn how to accomplish the same through an internal DAL seems less desirable to me.
The remainder of this article will provide a good overview on using OR.NETs GUI, and generated code respectively. My goal for this article is to provide a solid description of how to use the program, and then subsequently how to use the generated code. It does not go into painstaking detail about the underlying source code, the inner details of the DAL, or some of the more advanced features of the GUI, as I felt placing this much information here would make the article unreadable, and unattractive. Those interested in this information can freely download the source code and and user's manual.
This section will contain a basic description of the GUI's functionality.
The File -> New Project menu item will, obviously, start a new project. The user will have to enter connection information for a Microsoft SQL Server DB (more databases can also be added). This information is recorded through a simple form (not shown). Once at least one database has been entered, you may begin entering information for your business objects, and how they relate to the databases.
The screenshot below shows the form used to enter a new class. As you can see, the top section takes all of the standard class information class name, and namespace. The middle section is where you specify the database, and table that the class is based on.
Identity keys are attributes created off of a database column that, when combined, uniquely identify an object. These are specified in the bottom section of the new class form shown above. Most objects will have only one identity key, based off the tables primary key, but OR.NET easily supports tables with a composite key.
The checkbox labeled DBMS Manages Key allows the user to specify identity keys which are based on an SQL Server Identity Column (unique columns maintained by the DBMS). In such a case, the generated code would not prompt for a value for this field when creating new objects, but would instead leave it to the DBMS to calculate the value.
Persistent Attributes represent those class properties which are based on a single database column. The form shown below is used to specify all persistent attributes for a single class. As you can see, the form defaults to creating properties for all columns in the table, along with default names, and data types - all of which can be changed.
The final column in the persistent attribute form (above) specifies whether the corresponding database field is nullable. If this option is set, then the underlying attribute will be declared as a .NET 2.0 nullable type. Beyond that, every time the generated DAL needs to do something with this property, it will first check for a null value. Because of all this overhead, it is recommended that you keep the default non-nullable for nullable columns which you know will always contain an actual value.
The figure below shows the form that is used to collect information on a single composite object. The form used for collections of objects is identical, except that the blank area contains information on the type of collection that will be returned (all generic, and non-generic collections are supported).
OR.NET lets you create composite relationships between persistence classes in one of two ways: through a foreign key from one table to the other, or through a third, mapping table. The drop down labeled connect to XXX table specifies which of these options is being used.
If directly is chosen, then the following form will come up, asking you to specify the foreign key relationship between the two classes.
If through a mapping table is chosen, the following will come up, asking you to specify the relationship between the two classes, and the mapping table.
Once all of the information is entered, your main project page should look something like the image at the top of the page
To generate your DAL code, select generate code from the Project menu, tell OR.NET where to put the code, and youre done. You can now begin using your persistence objects without having to bother with any SQL.
Assume that I have created the following Book class. Each object referenced is for another persistent class, which, for brevitys sake, I have omitted. Additionally, I have chopped the code up a bit, and removed all of the get / set sections of the properties (there was nothing special about them).
public partial class Book {
#region ClassKeys
public string BookISBN;
#endregion
#region PersistentAttributes
public string title;
public string subtitle;
public int publisher;
#endregion
#region EmbeddedObjects
public ThesisDemo.Publisher BookPublisher;
#endregion
#region PersistentCollections
public List<ThesisDemo.Author> Authors;
private List<ThesisDemo.Genre> Genres;
#endregion
}
For each persistent class that is generated, a corresponding assembler class will be created. For the book class discussed above, it would be called BookAssembler. This assembler acts as the liaison between the database and the persistent class. It handles all of your reads, updates, inserts, and deletes.
The user's manual goes into extensive detail about the inner workings of the assembler class, but briefly, it is an extension on the domain object assembler class from Clifton Nock's Data Access Patterns, Addison-Wesley 2003.
In order to read a book from the DBMS, you first must instantiate a BookAssembler, and then call the ReadSingle method, passing all identity keys as parameters, which, in this case, is only the ISBN. As the user's manual discusses, the ReadSingle (as well as the ReadCollection) method also has an overload which takes a criteria object. Criteria objects allow the user to specify, in an object oriented manner, certain conditions which must be met when reading an object, or collection of objects from the DBMS.
BookAssembler ba = new BookAssembler();
Book b = ba.ReadSingle("0-7645-7135-4");
From here, the object can be modified like any other object. Perhaps, we would like to add an author to the Authors collection and set its title.
b.title = "Professional .NET Framework 2.0"
b.Authors.Add(new Author("Joe Duffy"));
In order to save the changes we made to an object, or insert a new one, you would call the MakePersistent method in the BookAssembler. The decision on whether to update the object, or insert it is made based on the value of the IsNew property which the DAL inserts into the class. This property resides in a separate file (made possible by the partial keyword) and is fully managed by the DAL.
The first parameter of MakePersistent is the object to be persisted. The second is a custom CascadeType enumeration, specifying how to persist composite objects. If CascadeType.None is specified, then composite objects will not be persisted. If CascadeType.MappingTablesOnly is specified, then only the mapping tables used to persist many-many collections will be updated (but not the objects in the collections). Finally, if CascadeType.Complete is specified, then everything will be persisted. For those classes which do not contain any composite objects, this parameter will be left off of the MakePersistent methods which are generated.
ba.MakePersistent(b, CascadeType.Complete);
MakePersistent also defines other overloads, which allow you to specify the type of transaction management used. These overloads are not discussed here, but like every other feature not discussed here, are covered in the users manual.
In order to delete an object from the database, the Assembler specifies a Delete method, which acts as expected, and contains all of the same overloads as MakePersistent, with one exception. Here, the cascade parameter is boolean, and specifies whether or not to delete the underlying composite objects. Regardless of what value is specified, any mapping tables used for composite objects will be updated to reflect the removal of the parent object.
OR.NET is a powerful O/R Mapper and Code Generator for the .NET 2.0 Framework. The user's manual will provide an excruciatingly detailed account of all aspects of the generated code, of which only a fraction was discussed here.
I'm always looking for feedback, so if you have any, please feel free.
Thanks for reading!
