Subsonic 3.0.0.3 not generating parameters for stored procedures

Question

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?

Answer 1

To debug a T4 template file...

T4 Tutorial: Debugging Code Generation Files

Using the project in the SubSonic-30-Templates that points to an instance of Northwind in SqlExpress I added the stored procedure above. Re-generated the StoredProcedures.tt and it happily created...

public StoredProcedure prc_Sample(string FileName){
    StoredProcedure sp=new StoredProcedure("prc_Sample",this.Provider);
    sp.Command.AddParameter("FileName",FileName,DbType.AnsiString);
    return sp;
}

Though I use the latest and greatest build, I've not noticed issues with parameters missing.

Can you post your Settings.ttinclude and possibly SqlServer.ttinclude file? Or maybe a link to them? The StoredProcedures.tt may be good too.

Answer 2

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.

Answer 3

Similar to Kon M's answer I changed the line in SQLServer.tt to:

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

This resolved the problem for me.

Answer 4

Another possible cause of this is that the Database has no dbo assigned.

Answer 5

For me the change was.. string[] restrictions = new string[3] { null, null, spName };

And all is working now. THANK-YOU!!