Does anyone have experience with generating database schema from c# (.net 3.5) domain model? Which of the tools produces the cleanest script?
Telerik OpenAccess ORM would have been ok but it doesn't produce clean column names from c# classes if the property fields use .net 3.5 implicit private fields.
I just need an initial script gen which I will then enhance and maintain manually.
P
I just generated one through reflection. Surprisingly took less effort that I imagined, once I got down to thinking about it.
How I did it: (aka: algorithm)
Load assembly.
For each Class (Type) in assembly => create table
Generate primary key as ${className}ID
for each get/set property in class create => add field (map property type to db type)
Get Foreign key custom attributes on class => add key if not exist. set it as fk_index (Custom attribute signature is like [ForeignKey("TableA,TableB")]
Get Other constraints (eg: check) custom attributes => generate as appropriate
If you don't have access to the source code, read these mappings from xml. *****Edit: If xml is always a last option solution for you (like for me), you can create extension methods on your classes that return IEnumerable<Attribute>. In this case, your Attribute surface has to include 'string TargetProperty {get; set;}' property for storing the field name it refers in case of check and unique constraints.*****
That's it!!
Tips:
ASSEMBLY LOADING: Oh! By the way, one common gotcha will be to ensure that all the assembly dependencies are provided where the loading program will find them. Remember, Visual Studio cannot help you resolve dependencies in this dynamic load scenario. Think like you are running csc.exe on the command line. If you get assembly load issues, check to ensure that every single internal dependency of the domain model assembly is available where the loading program can find it.
TYPE MAPPING: Nullable types are set to NULL otherwise NOT NULL type. String types are set to nvarchar(max). Map money to decimal or double as you wish, map bool to bit or equivalent, map DateTime to datetime, Map byte[] to binary, map long to number(x,0) and map int to int.
APP ARCHITECTURE: It was easy for me to add a console app to my solution for the immediate need, but then, you may want to provide input args to ensure that this can easily port over to other projects. To smoothen this, you will need for the code gen program to have a dll part, which will declare your custom attributes otherwise you may want to also pass in type specs or set up config sections for the custom attributes The former appears to be easier to use and configure and again the codegen program knows exactly what it's getting back.
Hope this helps.
P
P,
I have used Reflection, Introspection (FxCop SDK) and CodeModel (Visual Studio Automation) to solve a similar problem. I went from one API to the next in this order and like CodeModel best. The problem with Reflection is that it locks assemblies in memory as long as the AppDomain is loaded. This is a problem in T4, although you may not have it with your standalone executable. With T4 you couldn't recompile the original assembly after generating code from its metadata. Introspection worked well, didn't lock the assemblies, but it's still not documented, plus it requires you to compile the assembly before you can use its metadata. CodeModel is both documented and allows you to access metadata directly from C# source code.
Hope this helps, Oleg
