Click here to Skip to main content
15,867,686 members
Articles / Desktop Programming / Windows Forms
Article

Auto-Generating a Comprehensive C# Data Access Layer

Rate me:
Please Sign up or sign in to vote.
4.75/5 (33 votes)
6 Nov 20069 min read 176.4K   154   56
Simple but powerful GUI O/R Mapper which generates C# 2.0 to handle all of your object relational persistence.

Image 1

Contents

Introduction

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

Preliminaries

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.

Background

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.

Using the GUI

This section will contain a basic description of the GUI's functionality.

Beginning a New Project.

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.

Entering a New Class

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.

Image 2

Identity Keys

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.

SQL Server Identity Columns

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

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.

Image 3

Nullable Persistent Attributes

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.

Composite Relationships

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).

Image 4

Composite Object Dependencies

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.

Foreign Key Dependencies

If directly is chosen, then the following form will come up, asking you to specify the foreign key relationship between the two classes.

Image 5

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.

Image 6

Once all of the information is entered, your main project page should look something like the image at the top of the page

Generating Code

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.

Using the Generated Code

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).

C#
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
}

Introducing the Assembler Class

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.

Reading an Object from the DBMS

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.

C#
BookAssembler ba = new BookAssembler(); 
Book b = ba.ReadSingle("0-7645-7135-4");

Modifying an Object

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.

C#
b.title = "Professional .NET Framework 2.0" 
b.Authors.Add(new Author("Joe Duffy"));

Persisting an Object

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.

C#
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.

Deleting an Object

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.

Summary

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!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 4 Pin
Md. Marufuzzaman7-Dec-11 5:16
professionalMd. Marufuzzaman7-Dec-11 5:16 
GeneralOR.NET and a Microsoft Access database [modified] Pin
C-codist21-Sep-07 11:26
C-codist21-Sep-07 11:26 
GeneralIt comes down to the old conundrum... Pin
Jay Shanker2-Apr-07 18:21
Jay Shanker2-Apr-07 18:21 
GeneralRe: It comes down to the old conundrum... Pin
Nirosh4-Apr-07 18:38
professionalNirosh4-Apr-07 18:38 
GeneralOR Mapper Pin
icestatue14-Dec-06 10:21
icestatue14-Dec-06 10:21 
GeneralRe: OR Mapper Pin
adamAFA4614-Dec-06 12:29
adamAFA4614-Dec-06 12:29 
GeneralRe: OR Mapper Pin
icestatue15-Dec-06 1:55
icestatue15-Dec-06 1:55 
GeneralRe: OR Mapper Pin
adamAFA4615-Dec-06 11:43
adamAFA4615-Dec-06 11:43 
GeneralRe: OR Mapper Pin
icestatue18-Dec-06 1:34
icestatue18-Dec-06 1:34 
GeneralAnother DAL Pin
heinamola15-Nov-06 6:03
heinamola15-Nov-06 6:03 
GeneralRe: Another DAL Pin
adamAFA4615-Nov-06 7:42
adamAFA4615-Nov-06 7:42 
GeneralRe: Another DAL Pin
heinamola15-Nov-06 9:41
heinamola15-Nov-06 9:41 
GeneralRe: Another DAL Pin
Nirosh15-Nov-06 20:46
professionalNirosh15-Nov-06 20:46 
GeneralRe: Another DAL Pin
philippe dykmans12-Dec-06 4:44
philippe dykmans12-Dec-06 4:44 
Generaldoesn't work Pin
Chuckxxx8-Nov-06 15:18
Chuckxxx8-Nov-06 15:18 
GeneralRe: doesn't work Pin
adamAFA468-Nov-06 17:50
adamAFA468-Nov-06 17:50 
GeneralRe: doesn't work Pin
Chuck7779-Nov-06 12:15
Chuck7779-Nov-06 12:15 
GeneralRe: doesn't work Pin
adamAFA4610-Nov-06 18:00
adamAFA4610-Nov-06 18:00 
GeneralRe: doesn't work Pin
adamAFA4612-Nov-06 13:37
adamAFA4612-Nov-06 13:37 
Questiongetdate() for WhenUpdated(LastUpdated) column Pin
h5208-Nov-06 3:50
h5208-Nov-06 3:50 
I want to use the sql server's getdate() function to update WhenUpdated(LastUpdated)column when inserting or updateing, instead of setting a real value at client side.And I also want to check the value in every WHERE cause to solve collision problems. Could you help to inculde these features in OR.Net?
AnswerRe: getdate() for WhenUpdated(LastUpdated) column Pin
adamAFA468-Nov-06 4:23
adamAFA468-Nov-06 4:23 
QuestionLinq? Pin
Chris S Kaiser31-Oct-06 14:13
Chris S Kaiser31-Oct-06 14:13 
AnswerRe: Linq? Pin
adamAFA4631-Oct-06 15:04
adamAFA4631-Oct-06 15:04 
GeneralNice Work... Pin
Chris S Kaiser31-Oct-06 14:12
Chris S Kaiser31-Oct-06 14:12 
GeneralTo do list Pin
Nirosh29-Oct-06 23:18
professionalNirosh29-Oct-06 23:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.