As a programmer I love my enums. I find code written using enums to be more readable than without them. However, there has been and there still is a disconnect between lookup tables in a database and enums in code. One has to restore to some creative trickery to get them working in harmony. Recently while working on a project which involves using LINQ To SQL, I came up with a process which gives me the best of both worlds i.e. using lookup tables in database and enums in code. I am documenting my solution here. Hopefully it will help you.
The problem
In my database I have two tables. To make things simple I will call them Customer and CustomerStatus. Customer table stores information about a customer and CustomerStatus table is a lookup table which will store values such as "Active" and "Inactive".
For CustomerStatus I would like to use an enum in code. This can be done using the designer as explained here. But I would like to use SqlMetal to automate code generation. SqlMetal does not have any options which can be used to solve my problem.
My Solution
My solution is to generate dbml file using SqlMetal, then run a custom process which modifies dbml to make it enum ready and finally generate code using SqlMetal looking over dbml file.
Step 1
Step 1 is to generate a dbml file from database using SQLMetal with a statement like this:
SqlMetal /server:. /database:CustomerDb /dbml:CustomerDb.dbml /namespace:CustomerApp
In the dbml file I look at the column element generated for CustomerStatusID in Customer Table node. I find that it’s Type is set to System.Int32
Step 2
Step 2 involves writing a process which could just be a simple console application. This process looks for usage of lookup table’s Id colum in main table and replaces the value of Type attribute with corresponding enum. I am following a naming convention so that my enums are all suffixed with Enum and in database the foreign key field on my main table is named <lookup table name> + Id.
Step 3
Generate code with SqlMetal. Use the option which works with a dbml file. Here is a sample statement:
SqlMetal /code:Customer.cs /map:CustomerDb.map CustomerDb.dbml
Generated code now uses the enum for CustomerStatusId property rather than System.Int32
Step 4
A batch file can be created to tie this all up. Batch file will do the following things:
- Run SqlMetal to generate dbml file
- Run the process which modifies dbml file as mentioned in step 2
- Generate code using SqlMetal from dbml file
Doing this allows me to work with enums in my code and at the same use lookup tables in database. As an addition a simple process can also be written which can populate lookup tables with the values from enums.
4 Responses to Enum Support With LINQ To SQL And SqlMetal
Leave a Reply
Connect with me
-
I would probably make it part of the build process using msbuild rather than a batch file. It looks like Step 2 could easily be made into a custom task.
Chris,
The reason I am not making it a part of build process is because as per my requirement I do not re-generate my code very often. But I take your point and I agree.
I may be misunderstanding, but does this actually generate the enum for you, or does it just generate the code correctly with references to a predefined enum?
It seems to me there should be a way to automatically generate the enum definition itself from the table.
Hi Keith,
The process I described in this post relies on predefined enums.