Subsonic - How to use SQL Schema / Owner name as part of the namespace?

Question

I've just started using Subsonic 2.2 and so far very impressed - think it'll save me some serious coding time.

Before I dive into using it full time though there is something bugging me that I'd like to sort out.

In my current database (a SQL2008 db) I have split the tables, views, sps etc. up into separate chunks by schema/owner name, so all the customer tables are in the customer. schema, products in the product. schema etc., so a to select from the customers address table i'd do a select * from customer.address

Unfortunately, Subsonic ignores the schema/owner name and just gives me the base table name. This is fine as I've no duplicates between schemas (e.g Customer.Address and Supplier.Address don't both exist) but I just feel the code could be clearer if I could split by schema.

Ideally I'd like to be able to alter the namespace by schema/owner - I think this would have least impact on SubSonic yet make the resulting code easier to read.

Problem is, I've crawled all over the Subsonic source and don't have a clue how to do this (doesn't help that I code in VB not C# = yes I know, blame the ZX Spectrum!!)

If anyone has tackled this before or has an idea on how to solve it, I'd be really grateful,

Thanks in advance.

Ed

Answer 1

I was going to suggest the multiple provider approach too. But a lot of the plumbing is already in subsonic for ownership. If you edit a couple of lines in CS_ClassTemplate.aspx you can create a namespace for each owner profile. Change around line 58 (I'm using v2.1) to

namespace <%=provider.GeneratedNamespace%><%=owner%>

where owner is

string owner = "." + tbl.SchemaName;
if(owner == ".dbo")
  owner = "";

You put that up above, around line 14. This way you can have a namespace for every owner like: Northwind.Suppliers, Northwind.Customers, etc. I left dbo as just Northwind so all the tests would compile without a lot of editing. I ran a simple select query and I think it will work the way you want.