Introduction

With all the time developing applications using stored procedures, generating classes that used this stored procs, I got sick of having to go through the same process all the time; name of stored proc, parameters, parameter type, parameter direction,... oh God, really, it was sickening. So once, I decided I wouldn't do this anymore. That's why I did this little application and decided to share it with you.

At the time I developed it, May 2004, I didn't know about Microsoft Application Blocks, so I had to hard code it, let's say so. If I had known before about MS App Blocks, I would have done it with it, but it was done already, and it works sort of fast: some 40,000 lines of code in five seconds. Some friend said to me I could have used the CodeDom namespace to do so, but honestly I only used SqlClient class and one miserable StringBuilder, so it was pure appending strings.

This is a picture of the running application:

This is a little snippet of the code I use to get the parameters for each of the stored procs:

As you can see, it was just some commands I run against the SQL Server. I didn't know about these commands but with the Help, I found them and it really worked.

Summary

Honestly, there are better ways to do this, but this was the first way I found. I didn't know about Application Blocks at the time and after I finished the application, I really wouldn't change the code because it really works and, for the use it has, it is pretty fine. It does go several times to the SQL Server for finding the stored procs, then parameters, etc., but I consider it runs real fast.

I expect that you can find some use out of it. Hope you enjoy it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

I like to see things that make my job easier as a programmer...I LOVE to reduce keystrokes

I would like to make an observation, perhaps you can correct me if I'm wrong:

The methods produced all make ExecuteNonQuery() calls, which of course, do not return rows. Obviously, not every SP returns rows so filling out a datatable or dataset is not practical. I think the next step would be to somehow program the tool so that it can predict what the SP is returning, if anything.

For instance, if it detects that the SP has output parameters, the method could return a SqlParameterCollection containing the output values, or maybe a simple object array. If, nothing else it should return the SP's return value.

Detecting whether or not it returns any rows is probably the most challenging, though. I know I have written many SP's that use select statements, but don't return anything. So simply checking for a "Select" clause doesn't mean any rows are returned.

Anyway, your article has got me to thinking, which , to me, are the best kind of articles!;)

there is a nice and free utility for this. Have a look at mygeneration (http://www.mygenerationsoftware.com). Mygeneration is a template based (VB.Net / C# / MS VBScript or JScript) code generation tool with schema / meta data support for most of the common databases (MS SQL / MS Access / MySQL / Oracle / ......). Thera are already templates for building classes from tables or stored procedures or even stored procedures (CRUD) from exisitng tables.

If you do not feel comfortable with the supplied templates, you even may write your own templates but still having the full advantage of using the schema information provided by mygeneration.

Advantages:
1. You do not have to care how to fetch schema information for different databases
2. You write only one template, but get code for different databases at the same time
3. The underlying code has not to be hardcoded, therefore it is more easy to edit and reuseable.
4. ....
5. ....

I saw the mygeneration app. This is really far beyond my app, which is a very simple application. For the future, if possible, I would add more attributes which could make it much better and useful. Thanks a lot for the comment and letting me know that this other app existed. Thanks.

This is a very useful tool. There are only 2 improvements/additions I can think of.
1. Add an option for saving the output to a file. Much like this article http://www.codeproject.com/cs/database/genProcMethods.asp. Even though the other utility writes to an output file, I like your utility better because of the option to set the connection string, namespace and class with a gui instead of having to either change the source or create your own front end (which I did prior to seeing your article).
2. The ability to create classes for the tables as well as stored procedures.

sides_dale, I have the option for saving to an output file with Save button, I think. about the ability for creating classes for tables I honestly dont understand. If you were more explicit I would greatly appreciate it. Lately I have been thinking about changing the return type for the methods and not only working with stored procedures but with Views and Functions. Thanks for your comment.