Determine whether to generate ExecuteNonQuery or ExecuteReader from schema

Question

I am attempting to create a stored procedure/ADO.NET mapping mechanism where a stored procedure with parameters becomes

object MyStoredProcedure.Execute(out returnValue, param1, param2, ...)

The problem comes when trying to generate the actual data retrieval method. I can easily obtain most of the schema information I need from the Information Schema views, but I can't reliably find what type of return (output param vs. SELECT/SqlDataReader vs. both) should come from the procedure and whether to call ExecuteNonQuery or ExecuteReader.

Worst-case, I can probably parse the procedure's text, but there are all kinds of funky things that could go wrong there.

The reason for the code generation is that the application database contains many hundreds of stored procedures. And we inherited this project, so there is no way to wrap our heads around that many procs that we didn't create.

I actually have two main goals for the ADO.NET generation:

1) Remove all string literals (stored proc names in SqlCommand creation, parameter names in SqlParameter creation, etc.) from the application. This way, when a procedure or database schema changes, we can regenerate the ADO.NET wrappers, and any errors resulting from those changes will be caught at compile time.

2) Remove the need to dig through a proc to determine params, returns types, etc. So basically, the database itself becomes an API, with all of the internal stored procedure details abstracted away.

Answer 1

Yup; that isn't easy. For simple cases you can try running the sp (awooga!) passing nulls for all the parameters, and using SET FMTONLY ON - a bit risky (extended sprocs are stll executed, for example) and not robust since the TSQL could branch on the input. But an option.

The "out" ets should be available via metadata; the "old" way would be syscolumns (there is probably an info-schema alternative for doing it the right way).

---

Just as an update; if you want the database to describe itself as an API, perhaps consider UDFs for the selects; advantages:

• the metadata for the return values is rigid and easy to query
• it is composable at the caller

Or; just use an ORM. LINQ-to-SQL will work happily with this type of setup (including composability); Entity Framework will certainly do all that hard work for you for stored procedures. Or any of the others; NHibernate, LLBLGen Pro, etc. They have all tackled this exact problem. It isn't trivial; why re-invent it?

Answer 2

I don't think you should be worried about it.
Wouldn't it be better to provide overloads & let the user decide which method to call?

Let me know, if I have not understood the question correctly.

Answer 3

Assuming that all of your stored procedures are consistent, that is, each returns at most one result-set, with the same column-set irrespective of it's parameter values or the data-state of the database, and all OUTPUT parameters are always written to, ..

And also assuming that this is a development or at latest a build-time tool (and not a run-time tool) and that you have some control over Srored Procedure's content, .. THEN here is what I would try:

1. Institute a retroactive Stored Procedure standard that requires all sProcs to have a comment of the following form, that must work correctly in the test (or development?) database:

   'TEST: EXEC spThisProc [, ...]

2. Write your tool to extract the list of stored procedures, their parameters, data types and output parameters from the system catalogs (you can use the INFORMATION_SCHEMA tables ROUTINES and PARAMETERS for this).

3. Also retrieve the script for all sProcs (from the ROUTINE_DEFINITON column of the INFORMATION_SCHEMA.ROUTINES table, for one place), then extract the text of the "'TEST:" command (above) and ExecuteReader that command against the Test database. Then test the Resultset returned to see if it contains any datasets (or is it "Tables"?). If not, then mark this sProc as needing ExecuteNonQuery, otherwise, extract the column definitions of the returned dataset and use than to generate the corresponding ExecuteReader code for your Class definitions.

Answer 4

hope this could help retrieving the type of params

  select s.id, s.name, t.name as [type], t.length,s.isoutparam
  from syscolumns s inner join systypes t 
  on s.xtype = t.xtype 
  where id = (select id from sysobjects where name =  'SP NAME')

And check out this excellent link - get the list of columns returned from the SQL select

this is a sample query

USE tempdb
GO

IF OBJECT_ID('dbo.TestSP') IS NOT NULL
        DROP PROCEDURE dbo.TestSP;
GO

IF NOT EXISTS (SELECT * FROM sys.servers WHERE name = 'Loopback')
BEGIN
        EXEC sp_addlinkedserver @server = 'Loopback',  @srvproduct = '',
                                           @provider = 'SQLOLEDB', @datasrc = @@servername
END
GO

CREATE PROC dbo.TestSP
AS
        SELECT 1 as [ID], 'Name' as [Name], 'Boston' as [City]
GO

DECLARE @MyXML XML
SELECT @MyXML = (SELECT * FROM
        (SELECT * 
        FROM OPENQUERY(Loopback,'SET FMTONLY ON; 
                  EXEC tempdb.dbo.TestSP; 
                  SET FMTONLY OFF')) vw FOR XML AUTO, XMLSCHEMA)

SELECT @MyXML

SET FMTONLY--> It will allow you to run a SP, but only get back metadata

Cheers

Ramesh Vel

Answer 5

Also, you can try looking at SqlServer SMO http://msdn.microsoft.com/en-us/library/ms162169.aspx for querying information on your database without having to write queries against the information schema directly.