Subsonic 3 Saving into different databases

Question

Using subsonic 3 I have a project with a bit of a weird scenario. I have a .net windows service that needs to connect to a master database which stores connections to other database servers and also a set of tables for processing automated SMS messages. Then I have identical databases on the other servers (the connection string table is empty on the other db's) that handle the messages for other applications.

So, subsonic can call to all of the DB's just fine using the connection string/provider name options.

        public static List<SMSRequestWithResponseList> SMSRequestListGetAll(string applicationName)
    {
        string connStr = GetConnectionStringByApplicationName(applicationName);

        List<SMSRequestWithResponseList> response = new List<SMSRequestWithResponseList>();

        List<DAL.CDYNESMSRequest> lst = DAL.CDYNESMSRequest.All(connStr, providerName).ToList();

        foreach (DAL.CDYNESMSRequest mitm in lst)
        {
            SMSRequestWithResponseList itm = new SMSRequestWithResponseList(mitm, mitm.CDYNESMSResponses.ToList(), string.Empty);
            response.Add(itm);
        }

        return response;
    }

The issue is saving...Insert seems to be working.

DAL.CDYNESMSRequest itm = new DAL.CDYNESMSRequest(connStr, providerName).;
            itm.KeyCode = KeyCode;
            itm.ApplicationName = ApplicationName;
            itm.BatchTransaction = BatchTransaction;
            itm.AssignedDID = GetParameter("AssignedDID");
            itm.PhoneNumber = PhoneNumber;
            itm.MessageDetail = MessageText;
            itm.MessageCancelled = false;
            itm.MessageQueued = false;
            itm.MessageSent = false;
            itm.IsImmediate = SendImmediate;
            itm.InQueue = false;
            itm.ScheduledDateTime = ScheduledDateTime;
            itm.CreateDT = dt;
            itm.ModifiedDT = dt;
            itm.Save();

But it doesn't seem to want to update...

                DAL.CDYNESMSRequest itm = DAL.CDYNESMSRequest.SingleOrDefault(x => x.RequestID == requestID, connStr, providerName);
            if (itm != null)
            {
                itm.MessageID = messageGUID;
                itm.MessageCancelled = messageCancelled;
                itm.MessageQueued = messageQueued;
                itm.ReferenceID = messageReferenceID;
                itm.MessageSent = messageSent;
                if (messageSentDT < new DateTime(1753, 1, 1, 0, 0, 0))
                    itm.MessageSentDT = null;
                else
                    itm.MessageSentDT = messageSentDT;
                itm.MessageSMSError = messageSMSError;
                itm.ModifiedDT = dt;
                itm.Save();
            }

I'm calling using the connection string from the correct database but it doesn't update the record. If I'm saving it incorrectly please let me know. I did try to create a new provider and set it on the save but it barked at me saying it already had an open connection. Thanks!

Answer 1

Don't use ActiveRecord pattern, but the SimpleRepository which allows you to setup multiple repos and you can specify a connectionstring per repo.

// not sure if this is the right constructor or if it's providerName, connectionString
var repo1 = new SimpleRepository(connectionString1, providerName);
var repo2 = new SimpleRepository(connectionString2, providerName);

var item = repo1.Single<Product>(1);

if (repo2.Exists<Product>(x => x.Id == item.Id))
    repo2.Update(item);
else
    repo2.Add(item);