Designing an airline passenger reservation system

As with many new technologies, there are a couple of missing features in Linq2SQL and the DBML designer. One feature that would really help during development is the ability to re-sync the DBML with the database so any data model changes can be incorporated in the DBML and the auto-generated entity classes.

At the moment, Microsoft’s tools provides three options for refreshing/resynchronizing the DBML with the database:

1. Remove all, or only the changed entities and re-add them to the DBML designer
2. Same as above, but use SQLMetal to re-generate the entire dbml
3. Manually keep the dbml / Linq2SQL designer in sync with the database

The first two methods work fine if the naming conventions in the database match .net naming conventions, or if one prefer database naming conventions in the code. However, I always use all lowercase underscore separated table and field names in the database but pascal/proper case/.net entity names and member names in .net code. This more or less rules out options one and two when using out-of-the-box VS 2008. Re-generating the Linq2SQL diagram and then renaming entities and members by hand is a lengthy and error prone exercise. On the other hand, manually applying all changes both to the data model diagram and to the dbml is also duplicated work and also risk introducing errors/mismatches.

To remedy this, I will create a couple of Visual Studio add-ins that take over where Microsoft left off.

Add-in #1: Naming convention add-in

First off is an add-in for updating all entity and member names so they match .net naming conventions, e.g. the table airport will get a class name Airport, route_via will become RouteVia, the field airport.airport_code will simply be Airport.Code and so on. The following image shows the airport table before and after being updated by this add-in:

DBML is a fairly straightforward xml document where all tables, fields, entity classes and members are described with their database- and code attributes. Together with the dbml a layout file is also generated; this file is also an xml file and it stores the layout of the Linq2SQL designer diagram; position of tables, association lines etc. All this add-in need to do is go through those two files, update all entity and member names, update associations and then save the files to trigger the code generator to re-generate the entity classes.

The end result is that updating a Linq2SQL designer generated dbml, or sqlmetal generated dbml to use .net naming conventions after creating or refreshing the dbml from the database is as simple as hitting a button.

Add-in #2: Linq2SQL designer refresh add-in

Once the Linq2SQL base DBML has been generated it is important to keep the DBML in sync with any schema changes. If a couple of fields have been added to existing database tables, fields removed, tables removed, primary key changes, or foreign keys added or removed we may still not want to re-generate the entire DBML although this is the only option we’re left with when using the tool supplied by Microsoft.

To overcome this, I created a second add-in that will compare the dbml with the database schema of the database pointed to by the connection string embedded in the dbml and update the dbml accordingly. If any new columns have been added to tables represented in the dbml, columns removed, columns changed nullability or data type, PK members changed, foreign keys has been added/removed, or entire tables has been added or removed the add-in will detect the schema changes and apply them to the dbml.

Support for stored procedures will be added in a future version.

Downloading and installing the add-ins

The add-ins can be downloaded along with a user guide from http://www.huagati.com/dbmltools/

There are three editions; the free edition that support up to 20 tables, the Standard edition that supports up to 80 tables and the Professional edition that supports as many tables as Linq2SQL/DBML.

Using the add-ins

Whenever you have the DBML designer open in Visual Studio, the Tools menu will get two new options from the add-in:

The first one, Standardize DBML Entity and Member names, will update all entity names as described above, and the second one, Update DBML diagram from database, will connect to the database and update the DBML file so it corresponds with the database schema.

Whenever either add-in is used, all changes done by the add-in will be recorded in the output window:

Update: 16 July 2008 - fixed bug related to auto-generated columns and added PK support. The latest version is 1.0.3119.33326 with a timestamp 2008-07-16 18:30

Update: 17 July 2008 - added support for added foreign keys. The latest version is 1.0.3120.33307 with a timestamp 2008-07-17 18:30

Update: 22 July 2008 - added support for dropped foreign keys and fixed a couple of issues with recursive tables (FKs referencing the same table). The current version is 1.0.3125.30798 with a timestamp 2008-07-22 17:06.

Update: 23 July 2008 - added support for new tables, fixed a pile of reported and unreported bugs and re-tested against the reservation db and Northwind. The current version is 1.0.3126.38442 timestamped 23 July 2008 21:21.

Update: 26 July 2008 - numerous fixes and adjustments. Added licensing model; the free edition now supports up to 20 tables per DBML file. For larger DBML files, a license can be acquired from http://www.huagati.com/dbmltools/

Update: 30 July 2008 - added options dialog allowing the user to select what parts to synchronize (new/dropped tables, new/dropped/modified columns, PKs, new/dropped FKs). These options can be persisted along with a table exclusion list for unwanted tables. Also added a user guide detailing how to install and use the add-ins. Updates can be downloaded from http://www.huagati.com/dbmltools/

Update: This article is somewhat outdated. See http://www.huagati.com/dbmltools/ for an up-to-date description of the add-in, its’ features, download link etc.