Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET
Article

ADO.NET and Stored Procedures Code Generator

Rate me:
Please Sign up or sign in to vote.
4.00/5 (8 votes)
4 Sep 2008CPOL2 min read 45.7K   866   54   3
A VB code generator that reads a SQL Server table and generates ADO.NET code and also Stored Procedures.

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.

Code 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:

XML
<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:

HTML
<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:

ASP.NET
<%@ 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.NET
<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:

CSS
.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></span></span></span></span></span></span></span></span></span></span></span></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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Taiwan Taiwan
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralGood work Pin
Donsw10-Dec-08 4:38
Donsw10-Dec-08 4:38 
GeneralGood Job Pin
2182684-Sep-08 1:36
2182684-Sep-08 1:36 
GeneralVery Nice Pin
Abhijit Jana3-Sep-08 18:58
professionalAbhijit Jana3-Sep-08 18:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.