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

Question

Hi there,

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

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.

Answer 2

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.

Answer 3

Hi,

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

Answer 4

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.

Answer 5

(I don't have enough reputation to comment/followup on your post, so posting this as a separate response)

@Rob - Any thoughts on integrating this into the base templates? One of the issues I've run into pretty consistently is when a database contains multiple tables of the same name but each with a different owner. It seems natural to use the Owner as the namespace (this is, in some respects, what that represents on the database).

I've had some trouble getting this working properly, but I haven't spent too much time and sure it's probably simple. Any chance you can post an example set of templates?

On a related note, I noticed that there's an apparent bug with UpdateCommands - this doesn't include the Schema/Owner, so it fails.