When supplied with a connection string and
table name, this tool will read the field data types and create asp controls
for you to copy and paste in your own project.
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.)
Hide Copy Code
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:
Hide Copy Code
<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:
protected void
btnGenerate_Click(object sender, EventArgs e)
{
try
{
StringBuilder Script = 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 (!string.IsNullOrEmpty(this.txtTableName.Text))
{
Script.AppendFormat("
and table_name='{0}' ", this.txtTableName.Text);
}
Script.Append("ORDER BY
table_name, ordinal_position");
StringBuilder ASPCode = new StringBuilder();
StringBuilder JQueryCode = new StringBuilder();
using (SqlCommand SelectCommand = new SqlCommand())
{
var _with1
= SelectCommand;
_with1.Connection = new SqlConnection(this.txtConnString.Text);
_with1.CommandType = CommandType.Text;
_with1.CommandText = Script.ToString();
_with1.Connection.Open();
using (SqlDataReader dr = _with1.ExecuteReader())
{
while
(dr.Read())
{
string TableName = dr["table_name"].ToString();
string DataType = dr["data_type"].ToString().ToLower();
string
DataLength = (object.ReferenceEquals(dr["character_maximum_length"], DBNull.Value) ? "" : dr["character_maximum_length"].ToString());
string ColumnName = dr["column_name"].ToString();
string ColumnDefault = (object.ReferenceEquals(dr["column_default"], DBNull.Value) ? "" : dr["column_default"].ToString().Trim());
dynamic AssociatedControlPrefix = "";
string Control = "";
//only show the table name if
it isn't already shown
if (!ASPCode.ToString().Contains(string.Format("<h1>{0}</h1>", TableName)))
{
ASPCode.AppendFormat("<h1>{0}</h1>{1}", TableName, Environment.NewLine);
}
//doing this with if instead
of case as there are several date type of data types
if (DataType == "bit")
{
//asp checkbox
AssociatedControlPrefix = "chkBox";
Control = string.Format("<asp:CheckBox
ID=\"{0}{1}\" Checked=\"{2}\" runat=\"server\"
/>", AssociatedControlPrefix,
ColumnName, (ColumnDefault == "((1))" ? "true" : "false"));
//if the default value isn't
null then set the checked equal to the default value
}
else if (DataType.Contains("date"))
{
//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, this.txtJQueryDatepickerOptions.Text,
Environment.NewLine);
}
else
{
AssociatedControlPrefix = "txt";
if (string.IsNullOrEmpty(DataLength) ||
!Information.IsNumeric(DataLength))
{
//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" && DataType == "varchar") || DataType == "ntext" || DataType == "text")
{
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);
}
}
}
string Label = 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);
}
}
_with1.Connection.Close();
this.txtASPCode.Text
= ASPCode.ToString();
if (!string.IsNullOrEmpty(JQueryCode.ToString()))
{
this.txtJquery.Text
= string.Format("<script
language=\"javascript\"
type=\"text/javascript\">{0}$(document).ready(function(){{{0}{1}}});{0}</script>", Environment.NewLine, JQueryCode.ToString());
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
No comments:
Post a Comment