ADO.NET and Stored Procedures Code Generator

• Download source - 14.15 KB

Introduction

The program contains a .aspx and a .aspx.vb file. In VS2008 (whether you create your website with C# or VB), add them to your project as existing files, then do a little modification to the code (see below), and it should work. Once opened, it lists your SQL Server table schema and generates the CRUD codes, including the Stored Procedures and the ADO.NET commands (currently in VB only) that use the Stored Procedures generated.

Background

As the file name indicates, I wrote this program simply to list table schemas. But soon, I found it can help me automate some repetitive works such as writing Stored Procedures and the parameters list for the Command objects. With the help of this program, I now only have to create a table, and (almost) all the rest is generated in a blink.

Using the Code

First of all, you need to have a web site created in Visual Studio 2008 (VS2005 should also do, but I am not quite sure). Second, download the files and add them (remember to rename them to ListTableSchema.aspx and ListTableSchema.aspx.vb) to your project.

There are two language versions, English and Traditional Chinese, included in the download.

Now, check if you have any connection strings defined in your <connectionStrings> section in the web.config. If there isn't any, create one. Suppose you have your connection strings defined like the following:

<connectionStrings>
    <add name="db1" connectionString=... />
    <add name="db2" connectionString=... />
</connectionStrings>

You have two connection strings, "db1" and "db2". Now, search ListTableSchema.aspx with "ddlDb", and then modify the code like this:

<asp:DropDownList ID="ddlDb" runat="server" 
                  AutoPostBack="True" 
                  OnSelectedIndexChanged="ddlDb_SelectedIndexChanged">
    <asp:ListItem Value="db1">Your description for db1</asp:ListItem>
    <asp:ListItem Value="db2">Your description for db2</asp:ListItem>
</asp:DropDownList>

This is done. The program should now be ready to work.

You might want to change the MasterPage file location defined in the ListTableSchema.aspx:

<%@ Page Language="VB" MasterPageFile="~/MasterPages/Management.master" ...

You might also want to replace the spinning wheel image (not included in the download package) with your own, or simply drop it:

<asp:Image ID="Image1" runat="server" ... ImageUrl="~/Images/Wheel.gif" />

You can include the following CSS style used by this program in your theme file:

.colorBlock{
    background-color<span class="code-none">: #FFFFCC<span class="code-none">;
    border-width<span class="code-none">: 2px<span class="code-none">;
    border-style<span class="code-none">: ridge<span class="code-none">;
    padding<span class="code-none">: 5px 5px 5px 5px<span class="code-none">;
<span class="code-none">}
.sqlCommandHeader
<span class="code-none">{
    font-family<span class="code-none">: Verdana, Arial<span class="code-none">;
    font-weight<span class="code-none">: bold<span class="code-none">;
    color<span class="code-none">: #004400<span class="code-none">;
    margin-bottom<span class="code-none">: 1em<span class="code-none">;
<span class="code-none">}</span></span></span></span></

You may also want to customize the generated code to fit your own needs, which I can't help.

Points of interest

Technically, there is nothing clever or tricky in this program, only lots of hard work and time-consuming testing. However, I wish you enjoy this helper.

History

• 2008/9/3 - Published to CodeProject.
• 2008/1/15 - Version 2.0 released.
• 2007/1/5 - Project initiated.