Click here to Skip to main content
15,868,088 members
Articles / Database Development / SQL Server
Article

Build SQL Server Stored Procedures From Information_Schema Tables

Rate me:
Please Sign up or sign in to vote.
4.67/5 (10 votes)
5 Apr 2006CPOL4 min read 69.7K   756   51   24
Save hours of time by creating stored procedures and code from database metadata.

BuildCode application window

Introduction

Like so many of you, I depend on CodeProject, and the amazing community of brilliant and generous people who fill its pages, to get me out of tight spots on a weekly (if not daily) basis. And now it is time for me to give something back.

Background

How much time gets wasted hand-coding stored procedures, classes, and other code related to database tables? One of the most tedious and time-consuming tasks in any project can be the creation of standard Insert, Update, Delete, and Select stored procedures.

The application I am offering here allows the user to select a database, select a table from that database, and automatically generate all of these standard stored procedures in seconds, generally without fear of a syntax error.

There is nothing new here. There are certainly applications you can buy, that will do something similar in a far more elegant way. But, hey... this is free!

Using the code

The first step for you will be to modify the config file. The appSettings section has key/value pairs where the key is the name of a SQL Server database you have access to, while the value contains a connection string to that database. That's the hard part. Pay no attention to the "Northwind" setting for the time being.

XML
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="LoanAbstract" 
          value="server='SVR-A-XYZ';user id='ReaderWriter';
                 password='jeremiahwasa..."></add>
        <add key="Northwind" 
          value="ZeqIxX2fZc9cKSwH8osU1LxOdbsnvvJzeVQfXqnpoq0=">
        </add>
    </appSettings>
</configuration>

Once you've done this, open the application. The left-most drop down list should now contain the database name(s) from the appSettings section. Select one, then click on the "Show Tables" button. This will populate the drop down on the right with all the tables in that database (assuming the connection string is correct). Select a table.

Now, choose from one of the radio buttons (the SQL Insert, for example), and hit the "Build Code" button. What you should now see in the text box below is a syntactically-correct Insert stored procedure for the selected table. And the same should be true for all the other radio buttons. Each of them represents a separate class within the application.

Hit the "Copy to Clipboard" button, then paste the result into the Query Analyzer, make any necessary changes, and you are done. Or, select a folder by hitting the "Select Output Folder" button at the bottom left, and write the code out to a file. (The files that are written have a name beginning with the table name, followed by the code type that is in the text box, followed by a timestamp. So, if the table is "Address", and the code type is "SQL Insert", the file name will look something like this: "Address_SqlInsert_20060404_161216.txt".)

At some point, you will want to go into the various classes, making changes, or creating new classes to build code according to your needs. One of the first things you will probably want to do is replace my name and user ID throughout with yours. And if my decidedly idiosyncratic style of writing stored procedures (yes, this is the way I hand-code them!) annoys you, go in and change them.

Each class has three methods to build the code. The first, BuildCodeStartOfLoop, builds the table-level part of the code. Similarly, to supply any code at the end of the process is the BuildCodeEndOfLoop method. In between, building code for each column in the table, is the BuildCodeEachColumn method.

I hope you find this application useful, saving you time for more interesting pursuits.

Acknowledgments

If you are like me, you won't want your connection strings hanging out in plain code in the config file. I always encrypt the connection string (see the Northwind example in the code above; you were wondering about that, weren't you?), and decrypt it when it is pulled into the application. See the commented out code in the GetConnectionString method in frmBuildCode.cs. The encryption class code is included, and was developed initially by Frank Fang, and shared with the rest of us in an article in CodeProject[^]. The included Encrypter class contains all of the improvements recommended by other readers of that article. I have used it successfully for quite a while without any hint of error.

Likewise, the DataProvider class included in this application was strongly influenced by an interesting article[^] (actually, a trio of articles) by David Veeneman. If a method requires SqlParameters, I build them in the calling method (see the SQL Parms code type), load them into an ArrayList, and pass them to the appropriate DataProvider method. Data that is pulled back in a DataSet object using a FillDataSet method then gets reloaded into an instance of a class based on the table (see the C# class code type). The Table, TableDAO, and DataProvider classes demonstrate some of the concepts in the Veeneman articles.

Conclusion

I truly hope this application takes some of the drudgery from your life. And to all those who contribute in any way to CodeProject, my most sincere thanks!

License

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


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

Comments and Discussions

 
GeneralGreat !! Pin
XKaleshX26-Jul-08 8:17
XKaleshX26-Jul-08 8:17 

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.