ASP.NET Webform Generator

Introduction

Many times I've found myself recreating similar web forms for various projects.  Even copying and pasting my old code to a new project required modifications.  So being the lazy person I am with some free time one evening I decided to code a little tool to help with that.  You supply a connection string and a table name, and it will generate asp controls based on that table and the data types.

It also generates asp labels based on the column name and associates them to the corresponding textboxes, textarea, checkboxes etc...  The other nifty feature I wanted to put in was support for JQuery's datepicker.  If a datetime field type is found in the database, a textbox with corresponding label will be created (just like any other varchar datatype) but this will also generate the necessary javascript required for the datepicker.

Using the code

Pretty easy to impliment.  Make sure you have a reference to the JQuery library. (I used version 1.5 in my project but it should work just fine with newer versions as well)

Place this on your page in the javascript section. (You don't really need the error checking part, but I put it in there anyway.)

function ErrorCheck()
        {
            $("#<%=txtASPCode.ClientID %>").val("");
            $("#<%=txtJquery.ClientID %>").val("");
            TrimAllTextboxes();

            if ($("#<%=txtConnString.ClientID %>").val() == "")
            {
                alert("Connection string can not be blank.");
                return false;
            }
                return true;
        }
        function TrimAllTextboxes()
        {
            $("input[type=text], textarea").each(
            function ()
            {
                $(this).val($.trim($(this).val()));
            });
        }

And copy/paste this code in the form tag:

<asp:Label ID="lblConString" runat="server" Text="Connection String"></asp:Label>:
<asp:TextBox ID="txtConnString" runat="server" style="width:600px"></asp:TextBox><br />        
<asp:Label ID="lblTableName" runat="server" Text="Table Name (Optional)"></asp:Label>:
<asp:TextBox ID="txtTableName" runat="server" style="width:300px"></asp:TextBox><br />
<asp:Label ID="lblDatePickerOptions" runat="server" Text="JQuery Datepicker Options (Optional)"></asp:Label>:
<asp:TextBox ID="txtJQueryDatepickerOptions" runat="server" style="width:300px"></asp:TextBox><br />
<asp:Button ID="btnGenerate" runat="server" Text="Generate!" OnClientClick="ErrorCheck();" CausesValidation="true" /><br /><br/>
<h1>ASP Code:</h1>
<asp:TextBox ID="txtASPCode" runat="server" TextMode="MultiLine" Rows="20" style="width:100%"></asp:TextBox>
<h1>JQuery Code:</h1>
<asp:TextBox ID="txtJquery" runat="server" TextMode="MultiLine" Rows="20" style="width:100%"></asp:TextBox>

And in the code behind, there's a couple imports:

Imports System.Data.SqlClient
Imports System.Data

And the rest of the code is in the btnGenerate.Click

Try
            Dim Script As New StringBuilder
            Script.Append("SELECT table_name, column_name,data_type,character_maximum_length,column_default FROM information_schema.columns where table_name not like '%aspnet_%' and table_name not in( 'sysdiagrams') ")

            'if they specified a table name, filter by that
            If Not String.IsNullOrEmpty(Me.txtTableName.Text) Then
                Script.AppendFormat(" and table_name='{0}' ", Me.txtTableName.Text)
            End If

            Script.Append("ORDER BY table_name, ordinal_position")
            Dim ASPCode As New StringBuilder
            Dim JQueryCode As New StringBuilder
            Using SelectCommand As New SqlCommand
                With SelectCommand
                    .Connection = New SqlConnection(Me.txtConnString.Text)
                    .CommandType = CommandType.Text
                    .CommandText = Script.ToString
                    .Connection.Open()
                    Using dr As SqlDataReader = .ExecuteReader
                        While dr.Read
                            Dim TableName As String = dr("table_name")
                            Dim DataType As String = dr("data_type").tolower
                            Dim DataLength As String = IIf(dr("character_maximum_length") Is DBNull.Value, "", dr("character_maximum_length"))
                            Dim ColumnName As String = dr("column_name")
                            Dim ColumnDefault As String = IIf(dr("column_default") Is DBNull.Value, "", dr("column_default")).ToString.TrimStart("(").TrimEnd(")").TrimStart("'").TrimEnd("'")
                            Dim AssociatedControlPrefix = ""
                            Dim Control As String = ""

                            'only show the table name if it isn't already shown
                            If Not ASPCode.ToString.Contains(String.Format("<h1>{0}</h1>", TableName)) Then
                                ASPCode.AppendFormat("<h1>{0}</h1>{1}", TableName, Environment.NewLine)
                            End If

                            'doing this with if instead of case as there are several date type of data types
                            If DataType = "bit" Then
                                'asp checkbox
                                AssociatedControlPrefix = "chkBox"
                                Control = String.Format("<asp:CheckBox ID=""{0}{1}"" Checked=""{2}"" runat=""server"" />", AssociatedControlPrefix, ColumnName, IIf(ColumnDefault = "((1))", "true", "false")) 'if the default value isn't null then set the checked equal to the default value
                            ElseIf DataType.Contains("date") Then
                                'textbox with jquery to bind as date picker
                                AssociatedControlPrefix = "txt"
                                Control = String.Format("<asp:TextBox ID=""{0}{1}"" runat=""server""></asp:TextBox>", AssociatedControlPrefix, ColumnName)
                                JQueryCode.AppendFormat("$(""#<%={0}{1}.ClientID%>"").datepicker({2});{3}", AssociatedControlPrefix, ColumnName, Me.txtJQueryDatepickerOptions.Text, Environment.NewLine)
                            Else
                                AssociatedControlPrefix = "txt"
                                If String.IsNullOrEmpty(DataLength) OrElse Not IsNumeric(DataLength) Then
                                    'no max length
                                    Control = String.Format("<asp:TextBox ID=""{0}{1}"" runat=""server"">{2}</asp:TextBox>", AssociatedControlPrefix, ColumnName, ColumnDefault)
                                Else
                                    'varchar(max) or blob or ntexts etc
                                    If (DataLength = -1 AndAlso DataType = "varchar") OrElse DataType = "ntext" OrElse DataType = "text" Then
                                        Control = String.Format("<asp:TextBox ID=""{0}{1}"" runat=""server"" TextMode=""MultiLine"">{2}</asp:TextBox>", AssociatedControlPrefix, ColumnName, ColumnDefault)
                                    Else
                                        'textbox with a max length for everything else
                                        Control = String.Format("<asp:TextBox ID=""{0}{1}"" runat=""server"" MaxLength=""{2}"">{3}</asp:TextBox>", AssociatedControlPrefix, ColumnName, DataLength, ColumnDefault)
                                    End If
                                End If
                            End If
                            Dim Label As String = String.Format("<asp:Label ID=""lbl{0}"" runat=""server"" Text=""{1}"" AssociatedControlID=""{2}{3}""></asp:Label>", ColumnName, ColumnName, AssociatedControlPrefix, ColumnName)

                            ASPCode.AppendFormat("{0}:{1}<br/>{2}", Label, Control, Environment.NewLine)                           
                        End While
                    End Using
                    .Connection.Close()
                End With
                Me.txtASPCode.Text = ASPCode.ToString
                If Not String.IsNullOrEmpty(JQueryCode.ToString) Then
                    Me.txtJquery.Text = String.Format("<script language=""javascript"" type=""text/javascript"">{0}$(document).ready(function(){{{0}{1}}});{0}</script>", Environment.NewLine, JQueryCode.ToString)
                End If
            End Using
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

Points of Interest

I jokingly tell my friends that coders are (generally) lazy people, but that they also come up with useful tools to save time in the end.  That's basically where the idea for this little project came from. 

I hope you find it helpful and cuts down some development time for you. :)