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

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_%'";

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.