Click here to Skip to main content
13,198,727 members (41,833 online)
Click here to Skip to main content
Add your own
alternative version


153 bookmarked
Posted 25 Oct 2006

Auto-Generating a Comprehensive C# Data Access Layer

, 6 Nov 2006
Rate this:
Please Sign up or sign in to vote.
Simple but powerful GUI O/R Mapper which generates C# 2.0 to handle all of your object relational persistence.



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.

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.

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.

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

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.

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

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

public partial class Book {
    #region ClassKeys
    public string BookISBN;

    #region PersistentAttributes
    public string title;
    public string subtitle;
    public int publisher;

    #region EmbeddedObjects
    public ThesisDemo.Publisher BookPublisher;

    #region PersistentCollections
    public List<ThesisDemo.Author> Authors;
    private List<ThesisDemo.Genre> Genres;

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.

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.

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.

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.


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!


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


About the Author

Web Developer
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMy vote of 4 Pin
Md. Marufuzzaman7-Dec-11 5:16
mvpMd. Marufuzzaman7-Dec-11 5:16 
GeneralOR.NET and a Microsoft Access database [modified] Pin
T-luv21-Sep-07 11:26
memberT-luv21-Sep-07 11:26 
I am working on a GUI application for a legacy database. The database
happens to be Microsoft Access. After a little bit of Internet searching I
found this article about OR.NET. I downloaded OR.NET and tried it out.
While I found a few problems in OR.NET’s application code and with its
generated code, both of which were easily corrected, OR.NET did a pretty
good job. It generated a lot of my data access layer, approximately 20,600
lines of C# code. After all was done I have to give OR.NET and its
designers one of the best compliments I give . . . nice. Smile | :)

As for the article, it was a clear and descriptive summary, which lead me to
try OR.NET. Thank you.

Listed are the problems I ran into and their cooresponding modifications,
which I wanted to share with others who are using OR.NET with a Microsoft
Access database.


Looks like OR.NET is trying to access some of the tables with leading
periods (.) due to the fact that
dataRow["TABLE_SCHEMA"].ToString()<br />
returns an empty string on the Access database that I am using. To
get things working quickly I commented out the following, in two places:

ProjectStructure.GenericOleDbOrNetProvider.GetTables( ) method: line 133 &
line 151
<pre> nextTable.TableName = /*dataRow["TABLE_SCHEMA"].ToString() + "." + */ DataRow["TABLE_NAME"].ToString();</pre>

This will at least get OR.NET to read the Access database’s structure.

Generated Code

The code generated from OR.NET had a few problems. Build your application
with the newly generated code and you too will find the problems.

1. GeneratedClassInfo.cs, Global.Constants class, one of the public fields,
which happened to be a const string, lacked an identifier
name. I chose to add:
<pre> _ConnectionString = …</pre>
making it:
<pre> public const string _ConnectionString = …</pre>

2. There were then several references through out the generated code to this
mysterious Global.Constans public field that I just mentioned,
_ConnectionString in my case, where the field name was
missing. A find and replace solved the problem.

<pre> System.Data.OleDb.OleDbConnection(Global.Constants.);</pre>
And replace with:
<pre> System.Data.OleDb.OleDbConnection( Global.Constants._ConnectionString);</pre>

3. There were also several references the [ClassName]Assembler classes
missing a couple of double-quotes around the OleDbConnection
class’s connectionString parameter. Also, a few delimiters
were missing.

<pre> System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection( Provider=MicrosoftJetOLEDB40;Data Source=C:MyDatabasemdb;User ID=Admin;Password=;" );</pre>
And replace with:
<pre> System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDatabase.mdb;User ID=Admin;Password=;" );</pre>

4. In the [ClassName]Assembler classes, the generated code
specified an incorrect method of the OleDbDataReader class.

<pre> reader.GetSqlSingle</pre>
And replace with:
<pre> reader.GetFloat</pre>

5. In Forms.PAFiller getDataSet method

<pre> row["dtID"] = master.DataTypeLookup[sourceRow["FrameworkDataType"].ToString()];</pre>
And Replace with:
<pre> row["dtID"] = master.DataTypeLookup[sourceRow["DataType"].ToString()];</pre>
<pre> row["dtID"] = master.DataTypeLookup[sourceRow["FrameworkDataType"].ToString()];</pre>
And Replace with:
<pre> row["dtID"] = master.DataTypeLookup[sourceRow["DataType"].ToString()];</pre>

6. In Forms.SetMapTableEODependencies set_button_click method

<pre> tempDD = new ddHolder(row2["Value"].ToString(), row2["ColumnName"].ToString(), tablesCombo.SelectedItem.ToString(), master.lookUpDataType(row2["FrameworkDataType"].ToString()), "");</pre>
And Replace with:
<pre> tempDD = new ddHolder(row2["Value"].ToString(), row2["ColumnName"].ToString(), tablesCombo.SelectedItem.ToString(), master.lookUpDataType(row2["DataType"].ToString()), "");</pre>


...don't spit into the wind... Jim Croce

GeneralIt comes down to the old conundrum... Pin
Jay Shankar2-Apr-07 18:21
memberJay Shankar2-Apr-07 18:21 
GeneralRe: It comes down to the old conundrum... Pin
Nirosh4-Apr-07 18:38
memberNirosh4-Apr-07 18:38 
GeneralOR Mapper Pin
icestatue14-Dec-06 10:21
membericestatue14-Dec-06 10:21 
GeneralRe: OR Mapper Pin
adamAFA4614-Dec-06 12:29
memberadamAFA4614-Dec-06 12:29 
GeneralRe: OR Mapper Pin
icestatue15-Dec-06 1:55
membericestatue15-Dec-06 1:55 
GeneralRe: OR Mapper Pin
adamAFA4615-Dec-06 11:43
memberadamAFA4615-Dec-06 11:43 
GeneralRe: OR Mapper Pin
icestatue18-Dec-06 1:34
membericestatue18-Dec-06 1:34 
GeneralAnother DAL Pin
heinamola15-Nov-06 6:03
memberheinamola15-Nov-06 6:03 
GeneralRe: Another DAL Pin
adamAFA4615-Nov-06 7:42
memberadamAFA4615-Nov-06 7:42 
GeneralRe: Another DAL Pin
heinamola15-Nov-06 9:41
memberheinamola15-Nov-06 9:41 
GeneralRe: Another DAL Pin
Nirosh15-Nov-06 20:46
memberNirosh15-Nov-06 20:46 
GeneralRe: Another DAL Pin
philippe dykmans12-Dec-06 4:44
memberphilippe dykmans12-Dec-06 4:44 
Generaldoesn't work Pin
Chuckxxx8-Nov-06 15:18
memberChuckxxx8-Nov-06 15:18 
GeneralRe: doesn't work Pin
adamAFA468-Nov-06 17:50
memberadamAFA468-Nov-06 17:50 
GeneralRe: doesn't work Pin
Chuck7779-Nov-06 12:15
memberChuck7779-Nov-06 12:15 
GeneralRe: doesn't work Pin
adamAFA4610-Nov-06 18:00
memberadamAFA4610-Nov-06 18:00 
GeneralRe: doesn't work Pin
adamAFA4612-Nov-06 13:37
memberadamAFA4612-Nov-06 13:37 
Questiongetdate() for WhenUpdated(LastUpdated) column Pin
h5208-Nov-06 3:50
memberh5208-Nov-06 3:50 
AnswerRe: getdate() for WhenUpdated(LastUpdated) column Pin
adamAFA468-Nov-06 4:23
memberadamAFA468-Nov-06 4:23 
QuestionLinq? Pin
Chris S Kaiser31-Oct-06 14:13
memberChris S Kaiser31-Oct-06 14:13 
AnswerRe: Linq? Pin
adamAFA4631-Oct-06 15:04
memberadamAFA4631-Oct-06 15:04 
GeneralNice Work... Pin
Chris S Kaiser31-Oct-06 14:12
memberChris S Kaiser31-Oct-06 14:12 
GeneralTo do list Pin
Nirosh29-Oct-06 23:18
memberNirosh29-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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171020.1 | Last Updated 6 Nov 2006
Article Copyright 2006 by adamAFA46
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid