The Darkside

Shedding light on things and stuff

 
  Home :: Contact :: Syndication  :: Login
  87 Posts :: 0 Stories :: 56 Comments :: 2 Trackbacks

Ads

 

Donate via PayPal...

...if you feel the site helped.

Archives

Post Categories

Open Source Projects

Other Blogs

A potential requirement of an application might be to retrieve lookup data from a seperate server/database. Castle ActiveRecord provides 2 mechanisms that allow the developer to connect to multiple databases, and the third option I've listed here, making use of SQL2005 synonyms, could be an easier alternative. I've included a sample project download that demonstrates the first two options in this post.

Download(s)

ActiveRecordMultiDB Sample

ActiveRecord Library Files

The Config File

The configuration options for ActiveRecord allow you to add multiple config elements to the activerecord custom config section. You need to specify an abstract base class (that inherits from ActiveRecordBase or ActiveRecordBase<>) in the type attribute. By default, the first (unadorned) config section has a abstract base class of ActiveRecord.

<activerecord>
    <config>
        <add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
        <add key="hibernate.dialect"                 value="NHibernate.Dialect.MsSql2000Dialect" />
        <add key="hibernate.connection.provider"     value="NHibernate.Connection.DriverConnectionProvider" />
        <add key="hibernate.connection.connection_string" value="Data Source=.;Initial Catalog=TestA;Integrated Security=SSPI" />
    </config>
 
    <config type="ActiveRecordMultiDB.Library.SecondDBAbstractClass`1, ActiveRecordMultiDB.Library">
        <add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
        <add key="hibernate.dialect"                 value="NHibernate.Dialect.MsSql2000Dialect" />
        <add key="hibernate.connection.provider"     value="NHibernate.Connection.DriverConnectionProvider" />
        <add key="hibernate.connection.connection_string" value="Data Source=.;Initial Catalog=TestB;Integrated Security=SSPI" />
    </config>
</activerecord>

You may notice the "`1" in the type attribute; this is the notation for a generic base class that I've implemented. This can be omitted if you don't make use of a generic abstract base class.

You also need to include this abstract class in the initialisation section.

ActiveRecordStarter.Initialize(
        ActiveRecordSectionHandler.Instance, 
        typeof(TestTableDatabaseA),     
        typeof(TestTableDatabaseB), 
        typeof(SecondDBAbstractClass<>) //The registration of our abstract base class 
                );                      //will cause the second config section to be used

You'll notice that the usage of the classes hasn't changed at all, but the TestTableDatabaseB class fetches information using the configuration section the matches the abstract base class it inherits from. 

//Fetches data from TestA..TestTable
TestTableDatabaseA test1 = TestTableDatabaseA.Find(1);
Console.WriteLine(test1.Title);
 
//Fetches data from TestB..TestTable
TestTableDatabaseB test2 = TestTableDatabaseB.Find(1);
Console.WriteLine(test2.Title);

Using DifferentDatabaseScope

Another option to use is the DifferentDatabaseScope class. This class takes an IDbConnection object in the constructor which needs to be open, and makes use of this database connection to attempt to retrieve the data.

At present (in the RC3 build), there is a comment which notes that this isn't bullet proof. I'm sure they just left off the "yet" in the comments.

An example of doing this in code is: 

IDbConnection connection = new SqlConnection("Data Source=.;Initial Catalog=TestB;Integrated Security=SSPI");
connection.Open();
using (new DifferentDatabaseScope(connection))
{
    TestTableDatabaseA test3 = TestTableDatabaseA.Find(1);
    Console.WriteLine(test3.Title);
}

I'm doing a Find on exactly the same primary key (1), on the class TestTableDatabaseA, but you'll notice that the output is the same as that of the second output in the first example.

SQL2005 Synonyms

After testing out the two scenarios above, it dawned upon me that I could make use of SQL 2005 synonyms to achieve much the same result, with less code.

I've included the script to create the synonym on the TestA database.

USE [TestA]

GO

CREATE SYNONYM [dbo].[TestTableB] FOR [(local)].[TestA].[dbo].[TestTable]

posted on Monday, January 21, 2008 12:05 PM

Feedback

# re: Castle ActiveRecord - Connecting to Multiple Databases 1/26/2008 9:49 PM Nik
Didn't know about the SYNONYM functionality - very useful.

# re: Castle ActiveRecord - Connecting to Multiple Databases 5/25/2008 5:34 PM Nick
What a very useful article! Thanks a bunch!

Comments have been closed on this topic.