Issue with SubSonic and Multiple Providers

Question

Hi all,

I have two Subsonic-generated Data Access Layers for 2 different databases that I use in one project and so I have the following in my web.config:

<SubSonicService>
    <providers>
      <add name="BLLDB" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="BLLDB" generatedNamespace="BLLDB" useSPs="true" />
      <add name="BLLDB2" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="BLLDB2" generatedNamespace="BLLDB2" useSPs="true" />
    </providers>
  </SubSonicService>

yet, anytime I call the code for either DAL it always ends up using the second data Provider listed ("BLLDB2"), and so gives an error like "Invalid object name 'dbo.Users'" when it should be reading from "BLLDB" (despite me explicitly specifying "BLLDB" in the Select())

e.g. check the following code for the "BLLDB" DAL:

Dim mySelect As New
SubSonic.Select(Databases.BLLDB)
mySelect.From(Of User)()

"mySelect.ProviderName" returns a string value: "BLLDB2"

whereas "Databases.BLLDB" returns a string value: "BLLDB"

what gives??

Answer 1

One of your provider is failing. Subsonic is not good about telling you why and where its failing.

I usually debug in couple of ways.

1. Use only one provider at a time and comment the other one. Check if you are able to see the namespace. If both of them load fine, then you atleast know its not the database.

2. Check if any of you tables start with -, _, or numbers. This can cause it fail as well.

Let me know how it goes.

Answer 2

You can specify which provider is default by using:

< SubSonicService defaultProvider="BLLDB" >

Answer 3

Hi all and thanks for the replies... Rob if I specify the default provider of "BLLDB" this ends up doing the same thing, but for BLLDB instead of BLLDB2

i.e. it only reads from BLLDB and not BLLDB2

CodeToGlory, I'm using the latest dlls and usually have no issues running SubSonic until I came across this prob.

Would it be possible for you to post up the web.config entries you use for subsonic where you have both providers working please?

Thanks a million!

P.S. It's also rather odd that when I specify which data provider to use directly in my Select() function:

e.g.

Dim mySelect As New SubSonic.Select(Databases.BLLDB)
mySelect.From(Of User)()

and then I do:

"mySelect.ProviderName", this returns a string value: "BLLDB2" (not correct)

whereas when I output the value for "Databases.BLLDB" this returns a string value: "BLLDB" (correct)

This could actually be the key to the problem...

Answer 4

Just answering my own question here .. it seems to indeed be NOT possible to use multiple providers when working through VB.NET.

This possibly only works in C# (as CodeToGlory mentioned), as I've tested several different scenarios from scratch and cannot get 2 Subsonic-generated DALs to work side-by-side

Will have to hack my own one together for one of them so.. Cheers for the advice though!

Answer 5

Hello, I am new to Subsonic (using version 2.2 with C#). I spent a lot of time trying to get the same configuration as Stimpy to work. I think I figured it out - my solution below. If this is correct, it would be great to add this information to the Select Queries documentation. so others can resolve this multiple data provider issue earlier. Here's the web.config

<SubSonicService enableTrace="false" templateDirectory="">
<providers>
  <clear/>
  <add name="DB1" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="DB1" excludeProcedureList="*" generatedNamespace="DB1" includeTableList="TableA" tableBaseClass="RepositoryRecord"/>
  <add name="DB2" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="DB2" excludeProcedureList="*" generatedNamespace="DB2" includeTableList="TableB,TableC" tableBaseClass="RepositoryRecord"/>
</providers>

Here's the statement that doesn't work (taken from the Select Queries documentation examples). SQL Server can't find "TableA" because it's looking for it in DB2 instead of DB1:

DB1.TableA doesntWork = new Select().From<DB1.TableA>().                                  
            Where("idCol").IsEqualTo(1).ExecuteSingle<DB1.TableA>();

(My assumption had been that the dataProvider for each table would have been generated as a property of the table class).

Here's the modification to make this work:

 Select mySelect = DB1.DB.Select();
 DB1.TableA works = mySelect.From<DB1.TableA>().
            Where("idCol").IsEqualTo(1).ExecuteSingle<DB1.TableA>();

OR, this also works:

DB1.TableA worksAlso = new Select(DataService.GetInstance(Databases.DB1)).From<DB1.TableA>().                                  
            Where("idCol").IsEqualTo(1).ExecuteSingle<DB1.TableA>();

It seems that if you have a single dataProvider OR you specify in the SubSonicService config. that the default dataProvider is the one you're trying to use, everything works fine:

<SubSonicService enableTrace="false" defaultProvider="DB1" templateDirectory="">

But, if you leave out the "defaultProvider", it defaults to the last one in the providers list (in this case, DB2)

Another important piece of information for the multiple DAL case - if you generate the code into different folders for each provider, be sure to “include” in your project only ONE of the "AllStructs.cs" files that automatically gets generated into each folder (otherwise, compile errors).

FYI: kudos to the developers of this open source alternative to Codesmith. So far (other than this issue), it's been easy to get started and make it work (especially with SubStage). Also, I think it will end up being a lighter weight, cost-effective solution for my clients. Thank you!

Answer 6

Anyone get this working with VB.Net yet? And I am using SPs not selects so that might only add to my problems.