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.
="1.0" ="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!
