Selfservicing with two catalogs and mdf files

Posts   
 
    
Meteor
User
Posts: 67
Joined: 06-Apr-2007
# Posted on: 11-Nov-2009 06:32:55   

I'm using SelfServicing on a couple of SqlExpress 2008 databases. Normally I manage these via SqlExpress Management Studio and they just appear as a two separate db's. I have reference tables from both these databases in my LLBLGen project, and even though I only have one "Main.ConnectionString" entry in my WinForms app.config file, there's never a problem accessing either database.

Now I've got the stage of deploying the project, and I need to attach to the mdf files using "AttachDbFilename" and "User Instance = true". Suddenly I'm having problems connecting to the database. I'm not surprised, but I need to find a way to have LLBLGen utilise the correct connectionString. I've placed another entry in app.config, and I'm hoping that I can use a combination of "sqlServerCatalogNameOverwrites" and DbUtils.ActualConnectionString to connect.

I'm assuming that things worked previously because I was running SqlExpress almost like a normal database server, and as long as the Catalog name was correct, the tables could be accessed with the same connectionString. Now that I'm using User Instance, I need to somehow handle the connection at runtime. Can I do this in the DAL (I have two separate DAL's which reference the same LLBLGen project)?

TIA

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 11-Nov-2009 10:24:20   

Did you try to use sqlServerCatalogNameOverwrites, and set the new catalog name to an empty string. then whatever you set for DbUtils.ActualConnectionString will be used as is.

Meteor
User
Posts: 67
Joined: 06-Apr-2007
# Posted on: 11-Nov-2009 22:17:09   

Thanks Walaa.

I'm going to try using sqlServerCatalogNameOverwrites to set the catalog name to an empty string, then set DbUtils.ActualConnectionString to the appropriate connection in each of the two DALs.

Meteor
User
Posts: 67
Joined: 06-Apr-2007
# Posted on: 13-Nov-2009 06:02:01   

FYI: * sqlServerCatalogNameOverwrites now sets the catalog names to "PrimaryDb" and "SecondaryDb" * The single 'AppSettings/Main.ConnectionString' value was removed and replaced with two "AttachDbFilename" type connectionstrings containing references to the two mdf files. The "Database=PrimaryDb" and "Database=SecondaryDb" syntax was used to give matching names to the instances. I removed "User Instance=true" from the connectionString because it was too slow. * The constructors of each of the two DALs were modified to take a connectionString to set a private variable (m_actualConnectionString) * The first line of each method in the DALs was changed to "DbUtils.ActualConnectionString = m_actualConnectionString" * SqlExpress 2008 command-line installation had to specify "SqlSvrAccount='NT AUTHORITY\System', to allow SqlExpress to have enough privileges to create the .ldf file in the filesystem (next to the mdf files).

So now, the LLBLGen project contains catalogs from two databases which are deployed as attached files and which still work as they did on the dev machine - where they were just normal Sql databases.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 13-Nov-2009 11:59:34   

Thank you very much for the detailed feedback.

I just have a couple of question:

1- Do you need to use the first step (sqlServerCatalogNameOverwrites )?

2-

  • The constructors of each of the two DALs were modified to take a connectionString to set a private variable (m_actualConnectionString)

I thought you only had one DAL. Do you mean you have 2 classes/dlls using the same generated SelfServicing code? If so, why then did you create one LLBLGen project for the 2 catalogs? You could have created one for each catalog/DAL.

Meteor
User
Posts: 67
Joined: 06-Apr-2007
# Posted on: 14-Nov-2009 00:41:40   

1 - I probably don't need the first step but it makes the configuration file more readable because I can give the databases intuitive names.

2 - Yes, I could have used two LLBLGen projects but initially I had a single DAL, and I wanted to see if I could reference tables from two catalogs in the one project. Then later I started using two DALs instead of one, so I probably made things harder for myself.