Subsonic: dynamic connections

Question

Hi guys,

I have an ancient mess I'm trying to shovel into tiers using subsonic. Trouble is, I have this scenario:

When Hal logs in, his login uses database X for lookup data, database Y for his accounts and database Z for his contacts.

When Barry logs in, his login uses database X for lookupdata, database Q for his accounts and database R for his contacts.

X,Y,Z,Q and R are all on the same server. Y has an indentical schema to Q, and and Z has an identical schema to R. Don't get me started on how stupid a setup this is :)

I have to make my .NET Winforms App (using subsonic) point to the correct databases.

As far as I can tell, I'll have to get my hands dirty changing the SubSonic source (and maintain those changes with every subsonic release), so it can accept parameters rather than use app.config. Can anyone see an alternative to this?

Answer 1

You shouldn't need to modify the source, you should just be able to use multiple providers for talking to the different databases and switch between them at runtime. Have a look at the following question to see how to do that:

http://stackoverflow.com/questions/828736/seperate-read-write-connection-for-subsonic/828825#828825

Answer 2

1. Subsonic can create a dal for multiple databases. Just add a provider refering to another connectionstring to your app.config. Modify the namespace for each provider.

Now you should be able to access: My.Namespace.R... My.Namespace.X...

1. You can easily change connection on the fly:

   using (SharedDbConnectionScope scope = new SharedDbConnectionScope("new connectionstring "))
   {
   }
   

If you don't need more than one connection at a time, this is possible, too. We use this to logoff and logon to another server (Another side effect is, that you don't need to have a app/web.config.

Found this example here: http://www.digvijay.eu/post/2008/10/30/SubSonic-Trick-Specify-connection-string-at-runtime!.aspx

Everytime you call InitSubsonic the connection is changed.

public class MySubsonicProvider
{

    private static bool _bIsInitialized = false;

    public static void InitSubsonic(string server, string schema, string user, string password)
    {

        DataService.Providers = new DataProviderCollection();

        // here you can get settings from anywhere and make up a connection string :)
        MyDataProvider provider = new MyDataProvider("Server={0};Database={1};Uid={2};Password={3}", server, schema, user, password);
        DataService.Providers.Add(provider);
        DataService.Provider = provider;

        _bIsInitialized = true;

        // Clear cached values
        ClearSubSonicCache();

    }

    public class MyDataProvider : MySqlInnoDBDataProvider
    {

        private string _server;
        private string _schema;
        private string _user;
        private string _password;

        public string Server { get { return _server; } }
        public string Schema { get { return _schema; } }
        public string User { get { return _user; } }
        public string Password { get { return _password; } }

        public MyDataProvider(string connectionString, string server, string schema, string user, string password)
        {
            DefaultConnectionString = String.Format(connectionString, server, schema, user, password);

            _server = server;
            _schema = schema;
            _user = user;
            _password = password;

        }

        public override string Name
        {
            get { return "MyDataProvider"; }
        }

    }
}

Answer 3

I've have never considered 'SharedDbConnectionScope' easy or suitable for my applications

Yet that's is the answer isn't it? It's why we put it in there - and yes I've heard your requests for connection changing on the fly and I've built it in, as I keep telling you in every thread that we have.

Changing the connections on the fly is a request we've had a for a long, long time and we added it in there a long while back - and as far as I know it works like a charm.

For SubSonic 3 I'm plugging in about 10 different ways that you can change your DBs connection whenever you want, to whatever platform you want with whatever object set you want.

Answer 4

Thanks guys. I already had the multiple providers worked out, but didn't know how to set them at runtime (and I already searched this forum. Must have used the wrong keywords).

SharedDBConnectionScope seems perfect for on the fly, but didn't seem designed to set the provider for the whole user session.

So I did more searching based on your answers above, and came up with the following solution:

1) Add three providers for lookups, accounts and contacts, and generate the DAL.

2) add this to the DAL:

public static void SetProvider(string strProvider,string strConnectionString)
{
    DataService.GetInstance(strProvider).DefaultConnectionString = 
                                                     strConnectionString;
}

3) call it on login, once my app has worked out which databases the user uses, eg

MyDAL.SSProvider.SetProvider("Lookups", 
            "server=10.123.456.78;port=3306;uid=whatever;pwd=blah;database=X")

MyDAL.SSProvider.SetProvider("Accounts", 
      "server=10.123.456.78;port=3306;uid=whatever;pwd=blah;database=Y")

MyDAL.SSProvider.SetProvider("Contacts",    
               "server=10.123.456.78;port=3306;uid=whatever;pwd=blah;database=Z")

And off it goes.

Answer 5

Another example of changing providers on the fly (at runtime). This one reads items from one database and saves them to another.

Use:

CopyToAnotherDB<Dock>(Dock.Columns.Id, Dock.BarcodeStringColumn);

Method:

    public static void CopyToAnotherDB<E>(string identifierColumnName, TableSchema.TableColumn fakeDirtyColHack)
        where E : ActiveRecord<E>, new()
    {
        SqlQuery q = new Select().From<E>();
        q.ProviderName = SERVU_PROVIDER;
        IList<E> list = q.ExecuteTypedList<E>();

        string connStr = ConfigurationManager.ConnectionStrings[ARCHIVE_PROVIDER].ConnectionString;
        using (SharedDbConnectionScope scope = new SharedDbConnectionScope(connStr))
        {
            foreach (E item in list)
            {
                int itemID = (int)item.GetColumnValue(identifierColumnName);
                SqlQuery qry = new Select(identifierColumnName).From<E>().Where(identifierColumnName).IsEqualTo(itemID);
                int recCount = qry.GetRecordCount();
                E obj = item.Clone();

                obj.IsNew = recCount == 0; //determines if adding or updating
                obj.IsLoaded = !obj.IsNew; //determines if adding or updating
                obj.DirtyColumns.Add(fakeDirtyColHack);
                obj.Save();
            }
        }
    }