Hi,

I have multiple databases - one for each "company", say A, B and C. Each of these databases have the exact same schema, but their data is different. Now when the user selects a particular company from a dropdown, I would want to be able to connect to that company's database and fetch data.

This is how much I've achieved: 1. I have created a LLBLGen project using company A's database. 2. In my web.config, I have a generic connection string as:

<add key="Generic.ConnectionString" value="Server=server1;initial catalog={0};integrated security=SSPI;persist security info=False;packet size=4096"/>

1. In my aspx, I construct the actual connection string with the above key and the include the database name as below:

A.HelperClasses.DbUtils.ActualConnectionString = string.Format(GenericConnectionString, Company.DatabaseName);

Company.DatabaseName is derived from the drop down list.

But when I try something like:

CompanyOrderCollection Orders = new CompanyOrderCollection(); Orders.GetMulti(null);

I'm getting the orders from company A, instead of B.

What's wrong?

Thanks for any help Arun

if it's not too late the project I would recommend switching to the Adapter model instead of Self Service. Adapter is better suited for this scenario. 1 set of entities 3 adapters (1 for each database) you could then create a simple adapter factory class to generate an instance of the adapter you want

public static class AdapterFactory
{
    public static IDataAccessAdapter Create(string dbName)
    {
        switch(dbName)
        {
              case "DB1":
                   return new [NameSpace1].DBSpecific.DataAccessAdatper();
              case "DB2":
                   return new [NameSpace2].DBSpecific.DataAccessAdatper();
              case "DB3":
                   return new [NameSpace3].DBSpecific.DataAccessAdatper();
              default:
                   throw new ArguementInvalidException();
        }
    }
}

then in the code it doesn't matter which database you access the entity's logic would be the same

 string dbName = "DB2"; 
MyEntity e = new MyEntity();
e.Field1 = ...
e.Field2 = ...
using (IDataAccessAdapter adapter = AdapterFactory.Create(dbName))
{
    adapter.SaveEntity(e);
}

Case #1: You are having the 3 databases in the same server, each with a different database/catalog name

• You may use Catalog name overwriting (SqlServer only) to specify an empty string. In that case, the DQE will not specify a catalog name in the generated SQL elements, which will make the SQL target the catalog specified in the connection string. And then the connection string can be set at runtime using DbUtils.ActualConnectionString which will direct all the coming database calls to this connection string until you re-set it to something else.

Case #2: They have the same database/catalog name, but they reside on different servers

• You'll only need to set the DbUtils.ActualConnectionString to the specified connection string at runtime,

Walaa,

Can you please elaborate on the case 1 option? Where do I find the option/feature of Catalog name overwriting and yes I'm using SqlServer databases. Please post a sample code for the same. I believe all this can be done in SelfServicing group itself.

Thanks Arun

Walaa said:

You may use Catalog name overwriting (SqlServer only) to specify an empty string. In that case, the DQE will not specify a catalog name in the generated SQL elements, which will make the SQL target the catalog specified in the connection string.

Please refer to the manual: "Using the generated code -> Application configuration through .config files" check the Catalog name overwriting section.

Walaa said:

And then the connection string can be set at runtime using DbUtils.ActualConnectionString which will direct all the coming database calls to this connection string until you re-set it to something else.

Please refer to the manual: "Using the generated code -> SelfServicing -> DbUtils functionality"

I believe all this can be done in SelfServicing group itself.

The above solution is already for SelfServicing.