The schema generator and ViewRecord class were written by Justin Dunlap, and the article and unit tests by Marc Clifton.

Introduction

In Part I, I described the ViewRecord class, a base class for the code generated classes that implements a row cursor to be used by the application in conjunction with the object-mapping (OM) class. This article describes what the code generator does and the required object graph to represent the schema.

The motivation for this code, which Justin Dunlap wrote, is so that I can write client and server-side business rules that look like:

employee.LastName="Clifton";

rather than:

dataViewEmp[0]["LastName"]="Clifton";

However, since my client/server architecture handles the table relationships for me, I wanted something that wasn't fully object relational mapping (ORM), but rather just object-mapping, where a class is generated that maps to a virtualized view of the database schema. The virtualized view is an abstracted collection of tables and fields, including view-only, or calculated, fields. The tradeoff, of course, is that the business rule still has to handle relationships in a more raw, manual way than would be necessary in a true ORM environment. For example, if I have two tables, employee and company, I still have to write:

employee.CompanyID=company.ID;

to establish the relationship, rather than something more "natural", such as:

company.Employees.Add(employee);

The former case is simple enough to work with, and dealing with foreign keys in a more natural way never made it into the requirements.

Requirements

What did make it into the requirements were the following:

Utilize nullable types for value types for fields that are nullable.

Automatically convert between DBNull.Value and "null" when moving data between the OM instance and the underlying row.

The nullable type should not be exposed to the application--meaning, the internal fields can be nullable, but the property exposing those fields is never a nullable type. Instead, the generated code implements explicit methods for setting a field value to null and querying whether a field value is null. This was somewhat of an arbitrary requirement.

Perform simple validation--whether the field allows null and min/max value range checking. This may be expanded in the future.

Support common SQL data types and map them appropriately to C# types (not all SQL types are supported):

varchar <=> string

integer <=> int

real <=> double

money <=> decimal

bit <=> bool

datetime <=> datetime

uniqueidentifier <=> guid

Rather than using one of the free or commercial template-based generators, I wanted to utilize CodeDom to simplify the code generation process by not relying on a tool that possibly did both more and at the same time less than what I wanted. I also wanted the code generator embedded in the schema designer that I use. By using CodeDom, I can emit the generated code in any supported .NET language. Woohoo. I admit that I'm biased against template based code generators and didn't even bother to look to see if there was something that could handle the different requirements.

Implement property change events.

The View Schema Object Graph

The object graph of the view schema that gets passed to the code generator is a collection of classes that must implement some minimal interface properties and methods. While I typically instantiate the object graph from XML, you may choose to do something different. The only requirement is that the object graph must consist of classes implementing these interfaces. Some of the setters aren't necessary--they are included here so that the object graph can be easily deserialized.

As the code illustrates, the minimal view schema must maintain a collection of view fields and a collection of view tables. Ideally, your view fields would reference their containing view table, but this isn't actually necessary in the minimal implementation. The only reason we maintain this collection is to determine if the view table is read-only, which is a completely independent information from the schema.

The ReadOnly Property

A read-only view table will only generate getter properties in the generated code. There must be a 1::1 relationship between the MockTable and the MockViewTable, and the names must be identical, given how the schema code generator looks up the MockViewTable instance. (This is my fault, not Justin's, as he was coding to an already implemented object model).

A MockViewField instance encapsulates the reference to the actual table field, and also provides a mechanism to alias the table field's name via the Name property. The ability to alias the table field name is important when dealing with multiple tables that have common field names.

The virtual view includes the ability to create calculated view fields. These are view fields that the application might want for internal use but are not represented in the table schema. These fields are expected, however, to have corresponding columns in the DataView instance that is associated with the record. However, in my client/server application, they are not persisted to the database.

The MockTable isn't actually even necessary, were it not for the two lines in the code generator that acquire the MockViewTable instance using the MockTable name, in order to determine whether the MockViewTable is read only or not. Something to be refactored at some point, I suppose.

An Example

Let's look at a simple example in which an Employee table and view is created. The Employee table consists of the following fields:

Possible Enhancements

As I was writing this, it occurred to me that it would be possible to deal with the foreign key ID problem in a more natural way if the code generator assigns the ID, given an assignment like this:

employee.Company=company;

As mentioned earlier, validation might be extended to include things like regex.

Default values might be interesting to add.

Support for additional SQL types (should be easy enough for value types, by extending the enumeration and adding the correct conversion to the C# type in the Helpers class). Dealing with types like image, xml, and blob's might be more complicated.

For now though, I'm quite happy with how the code generator works, and prefer to actually accrue some use-case time before deciding whether and how it needs to be improved.

Conclusion

As I've mentioned before (probably to the point of annoyance), I don't totally buy into the idea of ORM because I feel that the server should generate the SQL on the fly based on the current schema. If I cut out the ORM, I can modify the schema and reload it without shutting down the server to recompile the code. Nor do I see the place for ORM in a general purpose server architecture. I do, however, see where object mapping is very useful for client and server-side business rules and custom client applications that interface with the server through some client API. The result of the work done by Justin in creating the schema code generator has been invaluable in making it easier to write these business rules and custom client applications.

References

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.

All my life I have been passionate about architecture / software design, as this is the cornerstone to a maintainable and extensible application. As such, I have enjoyed exploring some crazy ideas and discovering that they are not so crazy after all. I also love writing about my ideas and seeing the community response. As a consultant, I've enjoyed working in a wide range of industries such as aerospace, boatyard management, remote sensing, emergency services / data management, and casino operations. I've done a variety of pro-bono work non-profit organizations related to nature conservancy, drug recovery and women's health.

My main goal as a developer is to improve the way software is designed, and how it interacts with the user. I like designing software best, but I also like coding and documentation. I especially like to work with user interfaces and graphics.

I have extensive knowledge of the .NET Framework, and like to delve into its internals. I specialize in working with VG.net and MyXaml. I also like to work with ASP.NET, AJAX, and DHTML.

People are just notoriously impossible. --DavidCrowThere's NO excuse for not commenting your code. -- John Simmons / outlaw programmerPeople who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

Hey guys. I see a lot of code here , a lot of mapping but I´m thinking "what about objects?" "Why don´t we use all the power of Object Oriented Paradigm?"

I think all this implementation only generate some kind (with pros and cons) of typed data set, but that in no way you are modeling your business model with objects. Your object must be completely clear of db logic and how they can be obtained from and save to your data source.

There are a lot of architecture's patterns that make that possible very easily. Why just don´t use or implement something like that instead of tiring your model completely to your DB structure.

I think all this implementation only generate some kind (with pros and cons) of typed data set

That's exactly right.

Fernando Arámburu wrote:

but that in no way you are modeling your business model with objects.

The business model uses the classes generated by this tool.

Fernando Arámburu wrote:

Your object must be completely clear of db logic and how they can be obtained from and save to your data source.

With this architecture, they are. First of all, the base class is abstract so it's not tied to working just with a DataView. Second, these objects only manage the records themselves and do not interface directly to the DB. So you're free to choose your data provider as you wish.

Fernando Arámburu wrote:

There are a lot of architecture's patterns that make that possible very easily.

I've never found one that works the way I needed. Perhaps I didn't look very hard. Like you said, this looks a lot like typed datasets. The reason I don't want to use Microsoft's typed datasets is because 1) I'm not using datasets in my client application, but rather DataView's that are abstracted views of the schema, 2) I don't want to be tied to Microsoft's implementation, and 3) I want something that is extensible rather than being locked in to someone else's idea of how a typed dataset should be generated.

At some point there has to be an interface between the business object and the data layer. While I completely agree that the business object should have no knowledge of the data layer, I decided to bend the rules here. Ideally, you would probably generate two classes, one to be used for the business logic and one by the DAL, and the BL classes would have events that you could hook with the DAL. For my requirements, I didn't need this level of complexity. The internals of working with the DataView are hidden from the business logic even though they are in the same class.

It suits my needs perfectly. If it doesn't suit your needs, that's fine, I'm not writing these articles to please everyone. I'm writing them to show people what I'm doing. However, yes, your comments are actually quite good and helpful.

People are just notoriously impossible. --DavidCrowThere's NO excuse for not commenting your code. -- John Simmons / outlaw programmerPeople who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

I was working on a something simular and after reading some of the other critics, I have to say that I understand the point of creating an O-R-M or other variant that fits your needs. Others won't appreiciate designing code generator that fits your own needs, but I do. I will have to add though. Look into a class called DbProviderFactory, it maybe useful.

For years, I made things simpler by outlawing NULLs in my databases. In the rare case that I actually needed to note that a value had not yet been entered, I used a special value or even stored a "ColumnXIsValid" flag in a separate column. However, there are times the ability to have a real database NULL is handy. NULLs make working with foreign keys easier at the SQL level. Two things happened that have caused me to allow NULLs to creep into my databases: C# support for nullable types and broad support for complex, nested relational queries in SQL databases. With nullable types built into the language, it's pretty easy to have a code generator support nullable types. If you happen to mix nullable and non-nullable types in the wrong way, the compiler catches the problem at compile time. You do have to come up with a way to handle NULLs in the UI.

Some points to consider about Dates: What range of dates are supported by the database? How does the database deal with invalid or out of range dates? What is the time zone associated with the dates in your database? If your application will ever be multi-user, you may want to consider storing dates in GMT (for consistency) in the database. However, you'll need to convert to local time in either your generated record object code and/or in your UI code. "local time", of course, depends on the location of the end user, not necessarily the location of the running application or database server.

Your bias against template based code generators seems at odds with the stated goals of MyXAML: "With MyXaml, you can instantiate presentation layer components such as forms and controls through markup rather than code." Like XML, templates are a layer of abstration over code. In time, you'll see how boring and repetitive code like this is:

You'll want to automate it. You could automate it by looking at the MyXAML XML schema. However, you can also generate a lot of useful code straight off a database schema. Coding database schemas in raw SQL is not too bad - IMO, it's at lot better than coding in raw XML or fiddling with properties for a huge database in a GUI. Generating code from the database schema also makes reverse engineering straightforward. Imagine the process of porting an existing SQL Server VisualBasic application to MyXAML.

Applications are more fun to develop if your tools are designed to accommodate schema changes. For example, instead of generating EmployeeRecord directly, consider generating a BaseEmployeeRecord class (and a sample EmployeeRecord class that derives from BaseEmployeeRecord). This way, the user can (re)generate BaseEmployeeRecord any time the schema (or template) changes without fear of overwrites or having to merge changes. Now, EmployeeRecord becomes the focus for business logic develpment (security, validation, etc.). Also, you won't need classes like MockCalcViewField - developers can add properties directly to EmployeeRecord. FWIW, I tried using partial classes instead of generating a base class and it didn't work as well.

This is probably the major use of nullable columns in my database designs. If something is designated as nullable, I take a hard look at why I'm doing that and if there is a default value that can be used instead.

P.J. Tezza wrote:

C# support for nullable types

I actually have never come across a need for nullable types in C#, I guess maybe because a flag works well too. But I also was never enamoured with nullable types because they don't intrinsically map to DBNull.Value at all.

P.J. Tezza wrote:

and broad support for complex, nested relational queries in SQL databases

Hmm. Not sure how C# really supports that.

P.J. Tezza wrote:

What range of dates are supported by the database?

It's interesting you mention that. Dates are such a PITA in DB's. It seems every DB has a different concept of min/max dates. SQL Server 2000 has a min date of something in the 1700's, IIRC. How in the world are you supposed to use SQL Server 2000's date field for tracking, say, historical events? In one application, I was so disguisted with the date/time quirks that I used an encoded integer instead.

P.J. Tezza wrote:

What is the time zone associated with the dates in your database?

Definitely a good point.

P.J. Tezza wrote:

Your bias against template based code generators seems at odds with the stated goals of MyXAML

I don't view MyXaml as being a code generator or a template. My bias regarding templates is that they often utilize an obscure syntax that's cumbersome and difficult to learn, and I quickly run up against the limitations of the template engine. MyXaml is based on XML, which is pretty easy to learn, and whatever namespace, classes, collections and properties you're instantiating/setting, so there should already be familiarity on that front as well.

P.J. Tezza wrote:

In time, you'll see how boring and repetitive code like this is:

Of course. But the point of that code was for unit testing and demonstrating the minimal concrete classes to implement the interfaces.

P.J. Tezza wrote:

You'll want to automate it. You could automate it by looking at the MyXAML XML schema.

Absolutely, and it is. I have a schema designer that is a nice UI for creating the schema, serialized to XML, and includes the code generator.

P.J. Tezza wrote:

However, you can also generate a lot of useful code straight off a database schema.

That's limiting. In my client/server architecture, I needed code to be generated from the virtual views. These are often groups of tables with specific fields selected from each table and often with calculated fields thrown into the mix. Using the database schema directly would be pretty limiting, unless you were to include SQL views. But I don't implement my views in SQL--they views reside in the application server not the db server. The reason being, that I can trigger business rules on the separate table transactions.

P.J. Tezza wrote:

Coding database schemas in raw SQL is not too bad - IMO, it's at lot better than coding in raw XML or fiddling with properties for a huge database in a GUI.

Oh, I wouldn't agree regarding editing a DB with a GUI. Though there are cases when edits do need to be made in SQL so that constraints can be temporarily disabled, etc.

People are just notoriously impossible. --DavidCrowThere's NO excuse for not commenting your code. -- John Simmons / outlaw programmerPeople who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

I actually have never come across a need for nullable types in C#, I guess maybe because a flag works well too. But I also was never enamoured with nullable types because they don't intrinsically map to DBNull.Value at all.

I'm not sure what you mean here. I haven't had any problems using nullable types with parameterized database queries, for example.

Marc Clifton

Hmm. Not sure how C# really supports that.

It's the SQL databases I'm using that now all have sophisticated query support. I'll try to clarify with an example: In the past, when I needed to do a complex outer join, instead of doing the outer join with explicit SQL commands, instead, I would use simple result sets in my C++ or C# code and do the joins in the C++ or C# code. I did this because support for outer joins was inconsistent (and in some cases incomplete) between the various databases. Because I was doing my own joins in code, there was no need for a true database NULL - I could just handle special values the way I wanted to in my code. However, now that I'm using SQL commands to do outer joins (and other complex operations in SQL), I find that using a true database NULL has its advantages.

Marc Clifton

It's interesting you mention that. Dates are such a PITA in DB's. It seems every DB has a different concept of min/max dates. SQL Server 2000 has a min date of something in the 1700's, IIRC. How in the world are you supposed to use SQL Server 2000's date field for tracking, say, historical events? In one application, I was so disguisted with the date/time quirks that I used an encoded integer instead.

Yes, however, you can get a lot done with "least common denominator" dates. I can copy and paste a big section of one of my requirements documents, but the end result is that we use UTC Jan 1, 1970 00:00 as the "zero date" unless we need something special (like your need to record dates for historical events).

Marc Clifton

My bias regarding templates is that they often utilize an obscure syntax that's cumbersome and difficult to learn, and I quickly run up against the limitations of the template engine.

Maybe I'll break down and post the source code to my template based generator. As with some other code generation tools, I stuck with .asp style syntax, so it's not difficult to learn if you know .asp. I wrote the code, so the language is unlimited. Writing the templates is a mind bender, but it's the level of abstraction, not the syntax that is complex.

Marc Clifton

That's limiting. In my client/server architecture, I needed code to be generated from the virtual views. These are often groups of tables with specific fields selected from each table and often with calculated fields thrown into the mix. Using the database schema directly would be pretty limiting, unless you were to include SQL views. But I don't implement my views in SQL--they views reside in the application server not the db server. The reason being, that I can trigger business rules on the separate table transactions.

Sure, I'm not suggesting that support for views (created at the DB or business logic layer) is not the ideal case. I'm suggesting that you can get a lot accomplished quickly by generating off the DB schema. BTW, if you check out my post in the earlier article, you'll see a couple of ways I create "custom views" at the business logic layer. These views are read only, but as long as I include the ID numbers I need, it's just a few lines of code to load, update and store any object. IWFMYMMV.

Marc Clifton

Oh, I wouldn't agree regarding editing a DB with a GUI. Though there are cases when edits do need to be made in SQL so that constraints can be temporarily disabled, etc.

You've raised your own counter example: Suppose you are using SQL Server and have a VARCHAR column, Username, that is used in many primary / foreign key relationships. At some point, you find you need to to increase the width of that column. SQL Server requires you to drop all the relationships in proper order before you increase the the width of the column. Then you have to add the relationships back in proper order. It would not be impossible to create a GUI editor that can do all this, but it's a lot of work to get right. I really do prefer to edit SQL by hand. I am a programmer, after all. Creating perfectly formed text files is natural to me. But, I have some tools that support me. Maybe one day, I'll write up a CP article that explains a way to work efficiently (and in a database independent manner) with SQL schemas.

Since you like the suggestion of generating a base class, I'll explain my system in a little more detail. I generate code into several different folders. First, I wouldn't call the object EmployeeRecord, I'd just call it Employee. I would generate a class called BaseEmployee into a folder named GeneratedCode. All code in this folder is included in the library. Next, I would generate an example class called Employee (that derives from BaseEmployee) into a folder named GeneratedExamples\Objects (in my projects, I also generate example Forms, Searches, etc.). The code in this folder is not included in the library. I just use the code as a starting point for the library's Employee object. It's a little less tedious than creating Employee.cpp by hand with the usual "using" declarations, constructors and so forth.

I haven't had any problems using nullable types with parameterized database queries, for example.

Hmmm. That's interesting. The last time I tried that, I had problems converting between DBNull.Value and a nullable type. Perhaps it's not an issue with queries, but isn't it still an issue when moving a field value that's DBNull into a nullable value type?

P.J. Tezza wrote:

I'm suggesting that you can get a lot accomplished quickly by generating off the DB schema.

That's certainly true. Which is why my schema editor includes an "import" option.

P.J. Tezza wrote:

I'll explain my system in a little more detail.

You've obviously put a lot of thought into your system. You should write about it!

People are just notoriously impossible. --DavidCrowThere's NO excuse for not commenting your code. -- John Simmons / outlaw programmerPeople who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

Hmmm. That's interesting. The last time I tried that, I had problems converting between DBNull.Value and a nullable type. Perhaps it's not an issue with queries, but isn't it still an issue when moving a field value that's DBNull into a nullable value type?

Yes, you can use nullable types directly as query parameters. You do have to use a little glue code to read from the DB into a nullable type. I do this in the mapping code created by my code generator. Here is an example my code generator created:

That's certainly true. Which is why my schema editor includes an "import" option.

An import option in your GUI tool... It's OK, I get it. On average I just prefer code and automation to GUI. Call me crazy. I've tried both ways - though not with your GUI tool, I'll admit - and generally, I prefer code.

Marc Clifton wrote:

You've obviously put a lot of thought into your system. You should write about it!

Thanks - I've put in more than just some thought - I have a lot of real world experience on some substantial projects. There is a lot more than just the the bits I can put in to replies here - for example, I have an undo and audit trail facility tied into my data access code - but my time is limited. Maybe one day.

Hardly. I find editing UI's in XML much easier than using a form designer. Until that is, VS2005's form designer--the automatic alignment feature is very slick and is exactly what was missing from all prior incarnations of any form designer I've ever seen.

P.J. Tezza wrote:

I have an undo and audit trail facility tied into my data access code

Those are features a client of mine put into their client/server generic app. It's really nice when you can automate those tasks.

People are just notoriously impossible. --DavidCrowThere's NO excuse for not commenting your code. -- John Simmons / outlaw programmerPeople who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

Hardly. I find editing UI's in XML much easier than using a form designer. Until that is, VS2005's form designer--the automatic alignment feature is very slick and is exactly what was missing from all prior incarnations of any form designer I've ever seen.

Oh, that's hard core. XML is really painful for hand coding.

Yeah, I agree the latest alignment options in the winforms designer are the ones you need. Of course, these are the same auto resizing options we've been using for years in Win32 land with libraries like http://www.codeproject.com/dialog/dlgresizearticle.asp[^]. What we always did was lay the form out at a good size (using the GUI designer, not hand coding the dialog resources, though), then go into the window/dialog initialization code and add the resizing options by hand. It worked great for some applications like Search Engine Commando[^].

More what I was think about though, than form editors, was a common scenario for me like adding a new column to a table. What I do now is open the DB schema file (in my case, it's basically SQL commands to create a new database). Then, I run a batch file to rebuild the test database and regenerate my object mapping code and I'm ready to go. See Ma, no GUI? I admit, it wouldn't be such a big difference to open a GUI to add the column or even edit an XML schema file by hand (holding my nose), then have a batch file that runs off the XML schema rather than the DB schema.

Marc Clifton wrote:

Those are features a client of mine put into their client/server generic app. It's really nice when you can automate those tasks.

I find adding Undo support so much nicer than explaining to customers: Now, don't delete this unless you really, really mean it because once you delete it you really, really can't get it back...

The code created by my code generator logs all inserts, updates, and deletes to a single table called the Audit Trail. Enough information is logged (serialized into a single field) to undo the insert, update or delete. Once you have an audit trail table, you might as well make it easy to log other events (logins, errors, etc.). For master/child relationships, the code generator creates working code, but I like to go in and add code by hand to make sure the child records are handled appropriately. For example, if an end user deletes a company record from the system, all child records (associated users, machines, etc.) should get deleted as well. When the company is undeleted, the child records have to be undeleted as well. It's pretty natural to add in this code when you code in the business logic to make sure the user logged in has authority to delete the company. I ride on C# serialization support, so it doesn't take a lot of code.

May be I am missing something here but aren't you re-creating the functionality of the typed datasets here?

Similar but:

1) The generated class includes the concept of a row cursor and navigation methods
2) For my purposes, I needed something that worked with the schema definition that I'm using in a much larger application, and the schema is expressed in XML rather than XSD.
3) The code is extensible so that other kinds of data sources can be used other than what the DataSet provides.
4) Nullable types was a feature cut from the typed DataSet. Though there are workarounds[^] to this problem.

In the final analysis, being somewhat of a maverick, I wanted something that was a bit more flexible in terms of the schema source and the architecture. Typed DataSets simply don't float my boat.

People are just notoriously impossible. --DavidCrowThere's NO excuse for not commenting your code. -- John Simmons / outlaw programmerPeople who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith

Excellent article, as usual. I have a question, though--Since this code already maps an object to an in-memory view of a datatable, how difficult would it be to make this an ORM by mapping the relational database to the in-memory datatables?

how difficult would it be to make this an ORM by mapping the relational database to the in-memory datatables?

It would definitely be possible. The only caveat is that the entire schema would need to be represented so that all foreign key references, as objects, could resolved.

It'd be easy to replace a single primary key (like a Guid) with a class instance that the underlying PK references. It'd be more complicated deaing with multiple PK's, but that's a scenario I try to avoid.

What I'm not sure about is whether the referenced class should contain collections of the instances referencing it. This would complicate the management of the referencing class, as it would need to be automatically added and removed from the referenced collection.

People are just notoriously impossible. --DavidCrowThere's NO excuse for not commenting your code. -- John Simmons / outlaw programmerPeople who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith