R – Subsonic 3.0.0.3 not generating parameters for stored procedures

stored-proceduressubsonicsubsonic3templates

I have a SQL Server 2008 database with a bunch of stored procedures. When I use the ActiveRecord Template provided with Subsonic 3.0.0.3, it generates methods for all of my stored procedures, but they do not have any parameters. I am dbo on the server and can execute the stored procedures with no issue from Management studio.

Example Stored Procedure

CREATE PROCEDURE [dbo].[prc_Sample]
    @FileName   VARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF EXISTS ( SELECT * FROM Sample WHERE FileName = @FileName )
    BEGIN
        RETURN -1
    END

    RETURN 0

END

Sample Generated Method

public StoredProcedure prc_Sample(){
    StoredProcedure sp=new StoredProcedure("prc_Sample",this.Provider);
    return sp;
}

If I check SQLServer.ttinclude, I can see that all of methods for getting stored procedures are there, but for some reason they are not generating. And this isn't an an issue of having underscores in the stored procedure names – this is broken with and without underscores.

Any ideas or anyone know how to debug a template file?

Best Answer

I had the same issue, and I had to tweak the SQLServer.ttinclude to get it working. Inside that file find the GetSPParams() method and change one line:

from 

string[] restrictions = new string[4] { DatabaseName, null, spName, null };

to

string[] restrictions = new string[3] { null, null, spName };

.

BlackMael's answer has a helpful link that helped me figure out how to debug and step through the template code.

Now I'm not 100% sure yet that my change is a good one (there may be some adverse effects). I just haven't had a chance to test it thoroughly and need to read up some more on Restrictions as they pertain to the GetSchema() method. But for now, that solved my problem and I can successfully pass in my stored proc parameter.

Update: This may have something to do with the fact that my DB file is embedded in the VS solutin in App_Data. Perhaps this works better out of the box with a stand-alone SQL Server instance.

Related Topic