SSWUG TV
With Stephen Wynkoop
Don’t miss IMHO with SQL Server MVP Denny Cherry.
Watch the Show
Code Lookup Options for Enumerations
Yesterday I opened the topic of keeping Enumerations and lookup table values in synch. One technique I used in the past was making the lookup values in the tables the standard. I was using NetTiers for my DAL code generation. NetTiers is a template driven DAL code generator running on CodeSmith. It works similar to Entity Framework; however, the templates are more configurable
I created my own templates allowing me to auto-generate enumerations that may be used in the code. This technique resulted in the synchronization of the Enum to the value in the lookup table. It also exposed compile conflicts when the code used an Enum that was removed from a lookup table for some reason. Any Enum that was referenced from a value in a lookup table, no longer existing in a lookup table, resulted in a compile error of the application…a highly desirable behavior.
Abe shares a similar technique he uses today:
I wish this was more automated (I think MSbuild could do it) but I have a little dialog that takes a table and emits an Enum which I manually copy/paste into my source code. Basically, when I need a new Enum, or value in an Enum, I first add it to my table data, then run Table2Enum.aspx and paste in the new structure.
Here’s the code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Table2Enum.aspx.vb" Inherits="DevTools_Table2Enum" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html amp;quot; >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Table Name:
<asp:TextBox ID="TableName" runat="server"></asp:TextBox>
<br />
<br />
ID Column Name:<asp:TextBox ID="IDColumn" runat="server"></asp:TextBox><br />
Name Column Name:<asp:TextBox ID="NameColumn" runat="server"></asp:TextBox><br />
<br />
<asp:Button ID="Button2" runat="server" Text="Create" />
<br />
<br />
<asp:Panel ID="output" runat="server" Height="50px" Width="863px">
</asp:Panel>
</div>
</form>
</body>
</html>
Partial Class DevTools_Table2Enum
Inherits System.Web.UI.Page
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim DT As Data.DataTable = DBDirect.GetDataTable("select " & Me.IDColumn.Text & ", " & Me.NameColumn.Text & " from " & Me.TableName.Text & " order by " & Me.idcolumn.text)
Dim out As New StringBuilder
out.Append("Public Enum " & Me.TableName.Text & "<br>")
For Each r As Data.DataRow In DT.Rows
out.Append(" " & r(Me.NameColumn.Text) & " = " & r(Me.IDColumn.Text) & "<br>")
Next
out.Append("End Enum <br>")
output.Controls.Add(New LiteralControl(out.ToString))
End Sub
End Class
There have been a lot of other suggestions on this topic that will have to wait until next week. Thanks for the great feedback and ideas.
If you have your own idea on this or any other tipic feel free to send them to btaylor@sswug.org. You may also post questions anytime one our facebook page, Teet us, or even join our group on Linked-in.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Data Quality Starts With Proper Data Types (Part 2)
Chronological data is not the only area where data type troubles abound. Sometimes we can mess up choosing between numeric and character data types.
Featured White Paper(s)
SQL Server 2008 on Stratus ftServer Systems
Written by Stratus Technologies
Microsoft SQL Server 2008 on a Stratus fault… (read more)