SQL DMO based code generator

Question

I already have a code generator based on SQL DMO, that writes the a C# function for any stored procedure in by SQL Server 2008 database. Currenly however the code generator can only handle stored procedures that have input and output parameters. For stored procedures that return multiple records, the code generator returns a datatable with rows that each represent a output record.

Is there a way using SQL DMO to determine the fields that would be returned by a stored procedure if the output of a stored procedure is select * from Member where MemberID=1?

Thanks

Answer 1

My guess is that you cannot do this in DMO, since DMO relies on meta information stored in SQL Server, and the result set description of an SP is not stored in that way (as far as I know).

What you can do, however, is to have your generator execute the stored procedure inside a transaction, and analyze the resulting SqlDataReader. Have a look at the GetName(), GetFieldType() and GetSchemaTable() methods to construct your result class.

After execution, rollback the transaction (in case the SP makes any changes to the database).

You also might consider upgrading your generator to SMO, as MSDN states that DMO will not be supported in the future.