Looking for SQL2K Stored Procedure utility

1 May 2007 1:30 PM

Brian Simmons

Hi All,

Many moons ago, I remember the ASP.NET Pro magazine publishing an article
about a Winform utility that generated stored procedure .net calling code.

i.e. if you have stored proc had 5 params it would generate some text you
could copy & paste into your code, like this (just a sample, may be typos):
                SqlCommand myComm = new SqlCommand("sp_test", myConn);
                myComm.CommandType = CommandType.StoredProcedure;

                SqlParameter emailParam = new SqlParameter("@Email",
SqlDbType.VarChar, 255);
                SqlParameter passwordParam = new SqlParameter("@Password",
SqlDbType.VarChar, 255);
                SqlParameter userIDParam = new SqlParameter("@UserID",
SqlDbType.Int);
                userIDParam.Direction = ParameterDirection.Output;
                SqlParameter isAdminParam = new SqlParameter("@IsAdmin",
SqlDbType.Bit);
                isAdminParam.Direction = ParameterDirection.Output;
                SqlParameter returnValue = new SqlParameter("@RETURN_VALUE",
SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;

                myComm.Parameters.Add(emailParam);
                myComm.Parameters.Add(passwordParam);
                myComm.Parameters.Add(userIDParam);
                myComm.Parameters.Add(isAdminParam);
                myComm.Parameters.Add(returnValue);

Basically, it generated a pretty good skeleton of code that you could add
upon. You just picked the stored proc from a list of available ones, and it
clicked generate.

Anybody know of such a beast?

Thanks,
Brian

1 May 2007 3:52 PM

Jim Hughes

I would suggest checking out CodeSmith.

http://www.codesmithtools.com/

Or at least a google search for "Code Generator".

Personally I use LLBLGen www.llblgen.com for my DAL and Codesmith for other
code generation tasks.

Show quote

"Brian Simmons" <centraso@newsgroup.nospam> wrote in message
news:O1HrFU$iHHA.3472@TK2MSFTNGP04.phx.gbl...
> Hi All,
>
> Many moons ago, I remember the ASP.NET Pro magazine publishing an article
> about a Winform utility that generated stored procedure .net calling code.
>
> i.e. if you have stored proc had 5 params it would generate some text you
> could copy & paste into your code, like this (just a sample, may be
> typos):
>                SqlCommand myComm = new SqlCommand("sp_test", myConn);
>                myComm.CommandType = CommandType.StoredProcedure;
>
>                SqlParameter emailParam = new SqlParameter("@Email",
> SqlDbType.VarChar, 255);
>                SqlParameter passwordParam = new SqlParameter("@Password",
> SqlDbType.VarChar, 255);
>                SqlParameter userIDParam = new SqlParameter("@UserID",
> SqlDbType.Int);
>                userIDParam.Direction = ParameterDirection.Output;
>                SqlParameter isAdminParam = new SqlParameter("@IsAdmin",
> SqlDbType.Bit);
>                isAdminParam.Direction = ParameterDirection.Output;
>                SqlParameter returnValue = new
> SqlParameter("@RETURN_VALUE", SqlDbType.Int);
>                returnValue.Direction = ParameterDirection.ReturnValue;
>
>                myComm.Parameters.Add(emailParam);
>                myComm.Parameters.Add(passwordParam);
>                myComm.Parameters.Add(userIDParam);
>                myComm.Parameters.Add(isAdminParam);
>                myComm.Parameters.Add(returnValue);
>
> Basically, it generated a pretty good skeleton of code that you could add
> upon. You just picked the stored proc from a list of available ones, and
> it clicked generate.
>
> Anybody know of such a beast?
>
> Thanks,
> Brian
>

1 May 2007 4:12 PM

Rad [Visual C# MVP]

On Tue, 1 May 2007 09:30:21 -0400, Brian Simmons wrote:

Show quote

> Hi All,
>
> Many moons ago, I remember the ASP.NET Pro magazine publishing an article
> about a Winform utility that generated stored procedure .net calling code.
>
> i.e. if you have stored proc had 5 params it would generate some text you
> could copy & paste into your code, like this (just a sample, may be typos):
>                 SqlCommand myComm = new SqlCommand("sp_test", myConn);
>                 myComm.CommandType = CommandType.StoredProcedure;
>
>                 SqlParameter emailParam = new SqlParameter("@Email",
> SqlDbType.VarChar, 255);
>                 SqlParameter passwordParam = new SqlParameter("@Password",
> SqlDbType.VarChar, 255);
>                 SqlParameter userIDParam = new SqlParameter("@UserID",
> SqlDbType.Int);
>                 userIDParam.Direction = ParameterDirection.Output;
>                 SqlParameter isAdminParam = new SqlParameter("@IsAdmin",
> SqlDbType.Bit);
>                 isAdminParam.Direction = ParameterDirection.Output;
>                 SqlParameter returnValue = new SqlParameter("@RETURN_VALUE",
> SqlDbType.Int);
>                 returnValue.Direction = ParameterDirection.ReturnValue;
>
>                 myComm.Parameters.Add(emailParam);
>                 myComm.Parameters.Add(passwordParam);
>                 myComm.Parameters.Add(userIDParam);
>                 myComm.Parameters.Add(isAdminParam);
>                 myComm.Parameters.Add(returnValue);
>
> Basically, it generated a pretty good skeleton of code that you could add
> upon. You just picked the stored proc from a list of available ones, and it
> clicked generate.
>
> Anybody know of such a beast?
>
> Thanks,
> Brian

You could use CodeSmith and write a template to generate this sort of code
for you. The API is pretty fully functional allowing you to derive a lot of
information about your database, tables and columns

--
Bits.Bytes
http://bytes.thinkersroom.com

2 May 2007 7:02 AM

WenYuan Wang [MSFT]

Hi Brian,
Thanks for Jim and Rad's suggestion.

CodeSmith should be a great tool for you.
I searched on
http://msdn.microsoft.com/msdnmag/code/codeDownload.aspx?year=07. However,
I did not find the tool which you mentioned in initial post. But, as far as
I know, there are some tools in MSDN and CODEPROJECT website. You may check
it. Hope this helps.
http://www.codeproject.com/cs/database/dbhelper.asp
[SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET]
http://msdn2.microsoft.com/en-us/library/ms973259.aspx
[Auto-Generating Wrapper Classes for Stored Procedures, Part 1: Using the
AutoSproc Tool]

Have a great day.
Sincerely,
Wen Yuan
Microsoft Online Community Support