Asp – subsonic 3.0.0.3 multiple database connection failover

asp.netasp.net-mvcsubsonicsubsonic3

am using MVC and Subsonic 3.0.0.3 but i cant seem to pin down a specific point for multiple database connection.

normally in normal .net i would have my 2 strings in the web.config file
and have a database class for my project, within this db class i would do something like this:

try
        {
            conn.ConnectionString = server1;
            conn.Open();
        }
        catch (MySqlException)
        {
            conn.ConnectionString = server2;
            conn.Open();
        }

I am trying to pin down the one place in subsonic's created files where something like this would be best to place and maybe an up to date example on how to achieve it. I have googled etc but the examples shown are for an older subsonic.

many thanks

Best Answer

If you look in Context.tt at line 35 you'll see the following code:

public <#=DatabaseName#>DB() 
{ 
    DataProvider = ProviderFactory.GetProvider("<#=ConnectionStringName#>");
    Init();
}

This is where the provider is getting setup for you so if you add a BackupConnectionStringName variable in Settings.ttinclude after the ConnectionStringName at line 20 then you should be able to check your connection is working and user your fallback if not. For example:

public <#=DatabaseName#>DB() 
{ 
    DataProvider = ProviderFactory.GetProvider("<#=ConnectionStringName#>");
    Init();
    try
    {
        DataProvider.CreateConnection();
    }
    catch(SqlException)
    {
       DataProvider = ProviderFactory.GetProvider("<#=BackupConnectionStringName#>");
       Init(); 
    }
}

NB You may need to do some clean up to make sure a connection is not left open by CreateConnection.

Related Solutions

Inserting a new record pattern in SubSonic 3

First and foremost: the t4 templates are there for you to change as needed with SS3. That was the main idea with using T4 - I don't want to back you into my silliness :).

To the question at hand - I think this might be a bug in our templates that refuses to stick a value into the PK field:

    ISqlQuery BuildInsertQuery(T item) {
        ITable tbl = _db.FindTable(typeof(T).Name);
        Insert query = null;
        if (tbl != null) {
            var hashed = item.ToDictionary();
            query = new Insert(_db.Provider).Into<T>(tbl); ;
            foreach (string key in hashed.Keys) {
                IColumn col = tbl.GetColumn(key);
                if (!col.IsPrimaryKey) {
                    query.Value(key, hashed[key]);
                }
            }
        }
        return query;
    }

In this, our check should actually be...

                if (!col.IsPrimaryKey && !col.AutoIncrement) {
                    query.Value(key, hashed[key]);
                }

In this way, the non-identity will be inserted. But in reading your issue here, it sounds to me like you're not trying to insert into a non-identity.

The email you sent me doesn't say anything about PKs as identity - your PK was a thing called "NAme" which is a string type and not and identity (auto-increment).

I'm wondering about when I cannot get around this issue--when I have to add a new record to the database and have an identity as my primary key.

This is what SubSonic assumes - that your PK is an IDENTITY column. If you ONLY have an IDENTITY column, we can't help you because this is a deadlocked table in that you can't insert any value into it, therefor you can't tick the IDENTITY column. Your only recourse at this point is to SET IDENTITY INSERT="off", which defeats the purpose.

Hopefully this will answer your question? If I'm not getting it - can you do this for me:

One sentence: what can't you do and what's the error
What did you expect

Thanks Will and I hope I'm not being thick.

Subsonic 3.0.0.3 Crash

I experienced the exact same scenario. I was able to insert, but not update.

The cause, I found, was that a primary key was not set on the table. Once the primary key was set, I was able to update.

Best Answer

Related Solutions

Inserting a new record pattern in SubSonic 3

Subsonic 3.0.0.3 Crash

Related Topic