Autogenerate Stored Procedure parameters...

[Brett Wickard]

Anyone know of a good way to autogenerate stored procedure parameters to put
into c# code? I don't need to do it on the fly, just while coding. I've
got some SPs with a lot of parameters, so it would save some time to be able
to make the code for the SqlParameter object automatically.

 

[Steven Nagy]

Sorry I don't know any particular products to do it, but it sounds like
you might want to go down the code generation avenue.

Its pretty simple to write an app that connects to a database and
generates a data layer for all those sorts of things. SQLDMO lets you
expose elements of a SQL Server database in nicely typed collections.
Or you can query sysobjects yourself to get the information you need.
Not sure what the alternative is in SQL Server 2005.

Also can use products like CodeSmith (2.8 is free I think) to create
reusable code templates. Its quite user friendly and well documented.

I use such tools to create wrappers to all stored procedures and to
also create CRUD methods for tables. I then use ObjectDataBinding in
..NET2.0 to connect to those custom generated classes.

Hope this helps at all.

Regards,
Steven Nagy

 

[Nicholas Paldino [.NET/C# MVP]]

Brett,

Have you worked with the Server Explorer? You can establish a
connection to your database, and then drag the stored procedure from the
explorer (under the Data Connections) to a DataSet designer and it should
create wrappers for making calls to the stored procedure.

Hope this helps.

 

[Guest]

You could use the SQLCommandBuilder to get the parameters at runtime. if you
used the Enterprise library, it has all this built in to it so you just need
to parse in the parameters as a params object[] and it gets the parameters
from the db and loads them with the values. it also caches the parameters
lists so it doesnt have to query the db the second time. It doesn make this a
lot easier.

HTH

Ciaran O'Donnell