Angel \”Java\” Lopez on Blog

April 21, 2008

AjGenesis: Generating the model from the database

Filed under: .NET, AjGenesis, Code Generation — ajlopez @ 9:39 am

My code generation project AjGenesis uses one or many user-defined models to generate text artifacts, using programmable tasks and templates. When I started the project, I read the Kathleen Dollard‘s book Code Generation in Microsoft .NET

One of her recommendations is take metatadata from some resource, like a database. In my opinion, not all needed metadata resides in a database schema, so, from the very beginning of the project, I follow the way of a free user-defined model (I quickly go away from the Dollard’s ideas of using XSLT transformations: I think XSLT is the devil’s work… don’t try it at your home..;-). There are many interesting points her ideas, I should write a post about her book.

But metadata from database is not discarded. It can be used, applying some work. Remember: code generation is a pragmatic solution, don’t ignore useful metadata source.

So, some months ago, I wrote a simple example inside the ones that come with AjGenesis. You can try the example, downloading the last published version of the project:

AjGenesis Versión 0.5

The example is located at examples\LoadDB directory.

It works using the Northwind database that comes as example for MS SQL Server. In the directory example, from the command line, execute

make

This command executes an AjGenesis task:

..\..\bin\AjGenesis.Console Tasks\DbProcess.ajg
pause

It connects with database:

PrintLine "Connection" AssemblyManager.LoadWithPartialName("System.Data") con = new System.Data.SqlClient.SqlConnection() PrintLine "ConnectionString" con.ConnectionString = "server=(local);uid=sa;database=Northwind" PrintLine "Command" cmd = new System.Data.SqlClient.SqlCommand() cmd.Connection = con cmd.CommandText = "select * from Information_Schema.Tables" con.Open()

This code uses the information inside Information_Schema.Tables. Take notice of the use of the new AssemblyManager, an utility in AjGenesis that loads assemblies.

Then, the code obtains the table names:

dr = cmd.ExecuteReader() TableNames = new System.Collections.ArrayList() while dr.Read() PrintLine "Table " & dr.Item("Table_Name") & ": " & dr.Item("Table_Type") TableName = dr.Item("Table_Name") if dr.Item("Table_Type")="BASE TABLE" then TableNames.Add(TableName) end if end while dr.Close()

Once this info was obtained, it gets the columns of the tables, making an in-memory model in a Tables collection:

Tables = new System.Collections.ArrayList() For each TableName in TableNames PrintLine "Loading Table " & TableName Table = new AjGenesis.Models.DynamicModel.DynamicObject() Table.Name = TableName Table.Columns = new System.Collections.ArrayList() cmd.CommandText = "SELECT * from Information_Schema.Columns where Table_name='" & TableName & "'" dr = cmd.ExecuteReader() while dr.Read() ColumnName = dr.Item("Column_Name") PrintLine "Processing Column " & ColumnName & ": " & dr.Item("Data_Type") Column = new AjGenesis.Models.DynamicModel.DynamicObject() Column.Name = ColumnName Column.DataType = dr.Item("Data_Type") if dr.Item("Character_Maximum_Length") then Column.Length = dr.Item("Character_Maximum_Length") Column.IsNumeric = false else if dr.Item("Numeric_Precision") then Column.Length = dr.Item("Numeric_Precision") Column.Scale = dr.Item("Numeric_Scale") Column.IsNumeric = true end if end if Column.SqlName = Column.Name Column.Name = Column.Name.Replace(" ","") Table.Columns.Add(Column) end while dr.Close() Table.SqlName = Table.Name Table.Name = Table.Name.Replace(" ","") Tables.Add(Table) end for

Finally, I use the old trick of visiting the model, applying a template to it to generate other model. This is an interesting idea that deserves more attention: multi-step model generation and transformation. I could inject some expert system to complete the model, and infers any missing part. The model can be a in-memory one, or it can saved in an XML file, to be adapted and improved:

FileManager.CreateDirectory("Model") for each Table in Tables TransformerManager.Transform("Templates\Table.tpl", "Model\${Table.Name}.xml", Environment) end for

A generated model as example:

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?> <Table> <Name>Region</Name> <SqlName>Region</SqlName> <Columns> <Column> <Name>RegionID</Name> <SqlName>RegionID</SqlName> <DataType>int</DataType> <Length></Length> <Scale></Scale> </Column> <Column> <Name>RegionDescription</Name> <SqlName>RegionDescription</SqlName> <DataType>nchar</DataType> <Length>50</Length> <Scale></Scale> </Column> </Columns> </Table>

Now, using AssemblyManager, we can load any .dll assembly, and use it to generate any code. Darío Quintana works with this feature to generate code from compiled code (post in Spanish Generando con AjGenesis desde los assemblies). You can download his utility from

http://code.google.com/p/darioquintana/downloads/detail?name=AjGenesis.FromAssembly.7z

One idea: we can use the Meta library from MyGenerationSoftware to obtain metadata from any supported database. Notably, now this utility has published source code.

Angel “Java” Lopez
http://www.ajlopez.com/en

6 Comments »

  1. [...] AjGenesis: Generating the model from the database [...]

    Pingback by AjGenesis: Generating the model from assemblies « Angel “Java” Lopez on Blog — April 23, 2008 @ 10:33 am

  2. [...] AjGenesis- Generating the model from the database [...]

    Pingback by Code Generation for Mere Mortals Framework « Angel “Java” Lopez on Blog — June 21, 2009 @ 2:37 pm

  3. [...] AjGenesis- Generating the model from the database AjGenesis- Modelo desde la Base de Datos [...]

    Pingback by Generación de código con AjGenesis para Mere Mortals Framework - Angel "Java" Lopez — June 22, 2009 @ 10:39 am

  4. [...] as the initial input. Even a model could be the output of this process. Month ago, I wrote about generating the model from the database. In my opinion, the database is not the more expressive model to use as starting point, but it is [...]

    Pingback by Generating a model from the Database using AjGenesis « Angel “Java” Lopez on Blog — November 28, 2009 @ 10:49 pm

  5. [...] I wrote about some examples at: Generating a Model From the Database Using AjGenesis and AjGenesis: Generating the Model from the Database [...]

    Pingback by Models for Code Generation in AjGenesis « Angel “Java” Lopez on Blog — February 27, 2010 @ 12:45 pm

  6. [...] AjGenesis – Generating the model from the database Generating a Model from the Database using AjGenesis Models for Code Generation in AjGenesis [...]

    Pingback by Code generation with AjGenesis in a Real Project « Angel “Java” Lopez on Blog — May 27, 2010 @ 12:50 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 66 other followers

%d bloggers like this: