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?

Tlemcen

I have the same problem. I can definitely see the code the .tt file that should be looping through the parameters of my stored proc, but the final generated .cs has no code that adds that parameters. Definitely need to resolve this. – Glycosuria

Mike, I deleted my answer (so this will appear as 0 answers and hopefully someone might be able to figure it out. My answer and our digging didn't lead to any solution. thanks – Rangy

Answer 1

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.

Glycosuria

This worked. My database is not embedded, but just hosted on a SQL Server 2008 Standard Edition Server. Using Subsonic 3, I have had it generate stored procedures for all other databases except for this particular one. I have even attempted to create it from scratch as a 2008 databse and it still resulted in this issue. Thanks again for the fix. – Tlemcen

Answer 2

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.

Flosi

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.

Gabbie

Answer 4

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

Monition