Automatically generate classes and enums from sql datatables

Source code: RepositoryBuilder_src.zip.

Enable intellisense for sql repositories of messages, defaults etc in asp.net using BuildProvider

Introduction

With many applications, the SQL database doesn't only hold the business data. There is a good chance that the sys.messages table holds custom messages for the application and that another table may be used for application wide defaults. These repositories for messages, defaults and so on help the developer to maintain the vital attribute of consistency. When it comes to developing the asp.net application we need to make regular reference to these tables to determine the message id of a particular message from the database, or the exact name or value of a default. My experience has been that this can lead to short cuts and inconsistency. What was needed was an hassle free way of generating a class and enums for my messages.

What I required was all my messages for the application to be held in sys.messages and all my application defaults to be held in my table PortDefaults. In SQL they are available to other developers who want to run adhoc queries directly on the database, and to stored procedures and user defined functions which is the only access the asp.net application has to the database. When writing vb.net code for the application I required intellisense to offer me a list of the available messages and defaults for me to chose from so I did not need to constantly be refering back to my sql tables. Most of all I wanted all this to be fuss and maintenance free. If another developer had added a new set of messages, I wanted them to be available to me without any need to change other tables or code. If I added a new default to the database, I wanted it to show on my intellisense prompting.

The fabulous BuildProvider class in conjunction with the CodeDom allowed these goals to be acheived easily with considerable help from two excellent articles: Jaw-dropping experience with custom build providers by Friz Onion and Dino Esposito's Cutting Edge article.

Background

The application I was dealing with has hundreds of messages associated with it, and I realized that having the entire list appear in the intellisense drop down each time was going to be too much, so I decided that tables that I would use would have three columns:

1. An ID column. An integer unique to the message, this would be the integer value of my enum field value.
2. A text column. The message text which would be the enum field name. Of course I would have to get rid of any punctuation for the name to be valid.
3. A group column. A group name for the message to belong to which would be the enum's name. Fortunately, like many, I had ready made groups for my messages in sys.messages in as much as different number ranges represented different type of message eg. 50001-59999 for information, 70000-79999 for error etc.

sys.messages doesn't have a "group" column so I created a view to supply one.

SELECT message_id,
CASE
WHEN message_id < 60000 THEN 'Information'
WHEN message_id < 70000 THEN 'Warning'
WHEN message_id < 80000 THEN 'Error'
END AS [group],
text FROM sys.messages
WHERE (message_id > 50000)

Using the code

There are three distinct elements to our task. The first is to establish where our data tables are and which columns we are interested in. The second is generating the code based on the contents of our data tables. The third is to get visual studio to create the code automatically when we are developing code. The code we want to create is going to be something similar to:

Namespace repository

    Class SqlMessage

        Enum Information
            The_task_has_completed_successfully = 50001
            Your_password_was_changed = 50002
        End Enum

        Enum Warning
            Stock_of_this_item_is_now_low = 60001
            This_supplier_will_not_deliver_at_weekends = 60002
            Your_password_will_expire_in_PARM_days
        End Enum

        Enum [Error]
            No_items_were_found = 70001
            This_account_has_not_been_authorised = 70002
            Your_password_has_expired = 70003
        End Enum

    End Class

End Namespace

We start by creating an XML file to hold information about our sql connection, data tables and columns and a few details about what we want to create. We will give the file an extension of .repos. Any unused extension will do, but the extension will be important later. The name of the file is not important. Our XML file will look similar to this

	
	tableName="PortMessagesView"
	numberColumnName="message_id"
	groupColumnName="group"
	textColumnName="text"
	className="SqlMessage">

	
	tableName="PortDefaultsView"
	numberColumnName="uid"
	groupColumnName="group"
	textColumnName="name"
	className="PortDefaults">

The has the namespace attribute which specifies the namespace that our created code will be in. I have shown two s here to demonstrate that multiple repository entries can be made in the same file. The attributes for the are:

• connectionString - to get connected to the database.
• tableName - to specify which table (or in our case, view) the data we want is in.
• numberColumnName - to specify the column in the table containing the unique integer identifier.
• groupColumnName - to specify the column in the table containing the group that the message belongs to.
• textColumnName - to specify the column containing the text of the message.
• className - to specify the name of our class in our created code.

You must specify all attributes for each repository. We now know enough to move on to the second task: generating the code. If you are not familiar with the CodeDom, this is not the article to learn much from, but hopefully will be enough to inspire you to investigate further. We will navigate through our XML file creating a CodeCompileUnit and adding our namespace on the way

        'get the xml input file

        Try
            Dim filename As String = MyBase.VirtualPath
            Dim xmlStream As Stream = VirtualPathProvider.OpenFile(MyBase.VirtualPath)
            xmlFile.Load(xmlStream)
        Catch ex As XPath.XPathException
            System.Console.WriteLine("XML Exception:" & ex.Message)
        Catch ex As Exception
            System.Console.WriteLine("Exception:" & ex.Message)
        End Try

        'and create our navigator
        navigator = xmlFile.CreateNavigator

        'now on to the business of creating the code


        'somewhere to put our code
        Dim createdCode As New CodeCompileUnit

        'create the namespace
        Dim createdNamespace As New CodeNamespace

        'and find its name and name it
        Dim ns As String = ""
        iterator = navigator.Select("/repositorys")
        iterator.MoveNext()
        ns = iterator.Current.GetAttribute("namespace", "")
        If ns = "" Then
            ns = "DefaultRepository"
            System.Console.WriteLine("No namespace found - using default")
        End If
        createdNamespace.Name = ns
        createdCode.Namespaces.Add(createdNamespace)

        'add commentary
        Dim comment As New CodeCommentStatement("This code has been generated by the message repository tool")
        createdNamespace.Comments.Add(comment)

        'now we iterate through the individual repository(s) pulling of the attributes we need to access the data

        'so that we can enumerate the datarows
        iterator = navigator.Select("/repositorys/repository")

        Do While iterator.MoveNext
            Dim cs As String = iterator.Current.GetAttribute("connectionString", "")
            If cs = "" Then
                System.Console.WriteLine("connectionString not specified for repository " & iterator.Current.Name)
                Exit Sub
            End If

            '... and so on for our other attributes (tn(tablename), nc(numberColumn), gc(groupColumn) tc(textColumn) and cn(className) ...

we now know what all our attributes are so we can go on to fill the namespace with a class using CodeTypeDeclaration. Then fill the class with one or more enums (depending on how many groups there are) using CodeTypeDeclaration with isEnum set true. Each enum is filled with declarations using CodeMemberField to create the field and CodePrimitiveExpression to set its value. The field name must comprise only alphas and underscores so a quick function filterName will clean the text up for use.

    Private Function filterName(ByVal source As String) As String
        Dim filtered As String = ""
        For Each letter As Char In source.ToCharArray
            If Not Char.IsLetter(letter) Then
                If letter = "%"c Then
                    filtered &= "PARM"
                Else
                    letter = "_"c
                    filtered &= letter
                End If
            Else
                filtered &= letter
            End If
        Next
        Return filtered
    End Function

The filtered function returns PARM in place of the percent sign, just to highlight that a parameter is expected for the message. Not perfect as it does not deal with escaped % signs, but adequate for our purposes.

        'create our  top level class with the classname
        Dim messageClass As CodeTypeDeclaration = New CodeTypeDeclaration(cn)
        messageClass.Name = cn
        createdNamespace.Types.Add(messageClass) 'class is the default type


        'now access the data


        'get the data we need             
        Dim allDa As SqlDataAdapter = New SqlDataAdapter("select * from " & tn, cs)
        Dim allDs As DataSet = New DataSet
        allDa.Fill(allDs)

        'and and a list of the distinct groups in the table which will become enums
        Dim groupsDa As SqlDataAdapter = New SqlDataAdapter("select distinct [" & GC & "] from " & tn, cs)
        Dim groupsDs As DataSet = New DataSet
        groupsDa.Fill(groupsDs)

        For Each group As DataRow In groupsDs.Tables(0).Rows 'zero is the only table

            Dim currentGroup As String = group.Item(0) ' there is only column zero

            'now create an enum for this group
            Dim createEnum As CodeTypeDeclaration = New CodeTypeDeclaration(currentGroup)
            createEnum.IsEnum = True 'need to specify enum for this type

            'and add it to our message class
            messageClass.Members.Add(createEnum)

            'now fill it with declarations
            For Each datarow As DataRow In allDs.Tables(0).Select("[" & GC & "]='" & currentGroup & "'")
                'our field name is derived from the text, replacing punctuation with underscores using filterName function
                Dim fieldName As String = filterName(datarow.Item(tc).ToString)

                'and our value is the value form the numbercolumn
                Dim fieldValue As Integer = CInt(datarow.Item(nc))

                'create the field
                Dim field As CodeMemberField = New CodeMemberField
                field.Name = fieldName
                field.InitExpression = New CodePrimitiveExpression(fieldValue)

                'add to the current group enumeration
                createEnum.Members.Add(field)
            Next
        Next

We now have everything in our CodeCompileUnit. Of course we have not done anything with it yet. Our next task is to get the code in our CodeCompileUnit to be made available to our application. For this we use the BuildProvider facilities available to asp.net. If you have never come across the BuildProvider before, then be warned - THIS REALLY IS AS EASY AS IT LOOKS!

Firstly we need to tell asp.net about our provider which we do in web.config. I have created a folder in my App_Code folder called CustomBuilders which is where I will put the builder. We specify this in . The namespace and class of my BuildProvider will be CustomBuilders.ReposBuilder. We specify this in the type attribute of the in . Earlier, you will remember, we created our input XML file with a file extension of .repos. This is specifed in the extension attribute of the . The entry in the web.config file will be similar to the example below:

	
	
		
			
		
		
			
			
		
	
	...

The extension attribute of .repos (or whatever extension you chose for your XML input file earlier on) is the wonderful thing about the build provider. Now, everytime you place a file with the .repos (or whatever) extension into the App_Code folder, the build provider will be triggered to generate the code you have specified. You won't see the code (just as you don't see so much of the code in asp.net 2.0) but its there, and as if by magic your newly generated namespace and classes will be there for you use.

So (at last!) it is time to bring things together by creating our custom builder namespace (CustomBuilders) containing our build provider (ReposBuilder) . We inherit the BuildProvider class and provide just one override for the GenerateCode method which will contain our code-generating code and a couple of lines to write out the code.

Imports Microsoft.VisualBasic
Imports System
Imports System.IO
Imports System.Text
Imports System.Web
Imports System.Web.UI
Imports System.Web.Hosting
Imports System.Web.Compilation
Imports System.CodeDom
Imports System.Xml
Imports System.Data
Imports System.Data.SqlClient


Namespace CustomBuilders

     _
    Public Class ReposBuilder
        Inherits BuildProvider
        Private xmlFile As New XmlDocument
        Private navigator As XPath.XPathNavigator
        Private iterator As XPath.XPathNodeIterator


        Public Overrides Sub GenerateCode(ByVal assemblyBuilder As System.Web.Compilation.AssemblyBuilder)
            MyBase.GenerateCode(assemblyBuilder)

            '...
            'in here, our code for reading our attributes and creating 
            'our CodeComplieUnit
            '...

            If Not (createdCode Is Nothing) Then
                assemblyBuilder.AddCodeCompileUnit(Me, createdCode)
            End If
        End Sub
    End Class
End Namespace

This vb file needs to be placed in the App_Code/CustomBuilders folder that we created earlier. No need to compile - nothing else required beyond this code, the web.config entries and our XML input file with the .repos extension in the App_Code folder.

So what do we get?

When you add you .repos file to the App_Code folder asp.net will see to the code creation for you. If you go to the vb code for a page and add an import, you will see (in our case) {}repository come up on the list. Having imported it you can use a simple statement like:

dim t as integer = message.error.No_items_were_found

When you enter the dot after message, the intellisense will offer you Error|Information|Warning, and as you enter the dot after error, the intellisense dropdown offers you all you error messages. The variable t will be assigned the message number from you message table. The designer is even kind enough to put them all in alphabetical order for you!

Points of Interest

If, like me, you tend to shy away from some of the less obvious features of asp.net, because you don't have time to acquire the skills or feel that the return on the effort would not be worthwhile, think again when it comes to the BuildProvider. It really is so straightforward to use and even a simple application like this could reap gains in a very short time, not to mention improvements in consistency and reductions in maintenance. By stewartamackenzie. Trovato qui.