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 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 = ""
If Not ASPCode.ToString.Contains(String.Format("<h1>{0}</h1>", TableName)) Then
ASPCode.AppendFormat("<h1>{0}</h1>{1}", TableName, Environment.NewLine)
End If
If DataType = "bit" Then
AssociatedControlPrefix = "chkBox"
Control = String.Format("<asp:CheckBox ID=""{0}{1}"" Checked=""{2}"" runat=""server"" />", AssociatedControlPrefix, ColumnName, IIf(ColumnDefault = "((1))", "true", "false"))
ElseIf DataType.Contains("date") Then
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
Control = String.Format("<asp:TextBox ID=""{0}{1}"" runat=""server"">{2}</asp:TextBox>", AssociatedControlPrefix, ColumnName, ColumnDefault)
Else
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
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. :)