SQL Views in SubSonic 3.0

Question

Is there any way that I can access my SQL views in SubSonic 3.0? The code generation seems to skip views altogether

Christoper

Answer 1

To include views in your project

simply open SQLServer.ttinclude Find the query that load the tables ( search form 'const string TABLE_SQL') then change it to

const string TABLE_SQL=@"SELECT *
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE' 
    union
    select Table_catalog, table_schema, table_name, 'View' table_type 
    from information_schema.views";

if you are using it in an asp.net project you can exclude the aspnet table and views like so

const string TABLE_SQL=@"SELECT *
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE' 
        and table_name not like '%aspnet_%'
    union
    select Table_catalog, table_schema, table_name, 'View' table_type 
    from information_schema.views
    where table_name not like '%aspnet_%'";

Rashidarashidi

Only problem is then you have Save(),Delete(), etc methods on the view objects that don't really apply to Views – Cookshop

I have not tried to save or delete a view, but sql wise it's possible to update a view. I guess the best solution would be to create a template for view and return read-only objects. – Rashidarashidi

Answer 2

The SubSonic 3 templates don't generate code for views yet. You could add the functionality yourself quite easily though, have a look at the LoadTables and GetSPs methods in SQLServer.ttinclude to see how SubSonic builds lists of tables\stored procedures.

Monadnock