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

Australorp

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.

Acclamation

You also have to alter the ODSController and Structs generator a little, but it's very easy. – Acclamation

I've got a similar situation. However it's complicated further by having some tables having the same name in different schemas. Is there any way around it? – Vibrate

Answer 2

You could do this in 3.0 as well using our t4 templates (but it's 3.5 only). This is a really good bit of feedback - we should build this in by default perhaps!

Glad you got some help here.

Outnumber

Answer 3

You could try doing separate providers that have the same underlying database connection, like so:

<SubSonicService defaultProvider="DBData">
<providers>
<clear/>
     <add name="DBData" type="Subsonic.SqlDataProvider, SubSonic" connectionStringName="LocalSqlServer" generatedNamespace="DBData" includeTableList="table_a,table_b" spStartsWith="app,get,set" viewStartsWith="v_" />
     <!--CMS Provider-->
     <add name="CMS" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="LocalSqlServer" generatedNamespace="CMS" stripTableText="CMS_" includeTableList="CMS_Content,CMS_Page" useSPs="false"/>
</providers>
</SubSonicService>

I don't think you can use the schema itself as a key in this way, but you could at least work around the issue with a combination of includeTableList and generatedNamespace. You said that you don't have duplicate table names across the different schemas, so it just might work.

Psychopharmacology

Hi, thanks for the quick reply :-) I have 6 schemas in total ranging from heavily used to light (config stuff) use. Bit worried about 6 separate connections to the same db. Little guy in the back of my head saying this is a bad idea but not sure.. – Australorp

Answer 4

Just to let you know I have this now working - or at least, compiling! :-) To get the owner solution to work fully though you'll need to make more changes to the Class Template as otherwise the table/key functions sit within the wrong namespace.

I've also hacked around with the stored procedure template. I couldn't (in the short time I have) work out how to split into separate files/namespaces for each owner so instead i've prefixed each sp function with the owner and an underscore.

However, just in case you have the same problem you'll know its possible to fix.

Ed

Australorp