IT Staff

Blog về chuyên ngành IT

Castle ActiveRecord – Connecting to Multiple Databases

leave a comment »

Castle ActiveRecord – Connecting to Multiple Databases

Active Record Logo

Active Record Logo

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]

Written by Xavier

Tháng Mười Một 6, 2008 lúc 7:00 chiều

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

%d bloggers like this: