CODEWITHSUNDEEP
sqlitenhibernate
Thomas Levesque
Thomas Levesque

Reputation: 292685

NHibernate, SQLite and ATTACH DATABASE

I'm trying to do something slightly unusual... I currently have a SQLite database, accessed with NHibernate. This database is frequently uploaded to a server. I have a new requirement to create a new table for reporting purposes, which is expected to become pretty big. This table doesn't need to be uploaded to the server, so I'd like to put it in a separate database, and use ATTACH DATABASE to access it transparently from my main database.

The problem is I don't know how to do that with NHibernate... How can I tell NHibernate to attach the other database when it connects? I can't find any connection string parameter or NH configuration property allowing to do that... is it even possible?

An acceptable option would be to manually execute the ATTACH DATABASE command when the connection is open, but I don't know how to do that. When I build the NH session factory, it immediately tries to update the schema (hbm2ddl.auto = update), and I don't have an opportunity to do anything with the connection before that. So it will just try to create the new table on my main database, which of course is not what I want...

Has anybody ever done that before? How did you do it?

Thanks

EDIT: In case someone needs to do the same, here's my solution, inspired by Diego's answer

Connection provider:

public class AttachedDbConnectionProvider : DriverConnectionProvider
{
    private string _attachedDbAlias;
    private string _attachedDbFileName;

    public override IDbConnection GetConnection()
    {
        var connection = base.GetConnection();
        if (!string.IsNullOrEmpty(_attachedDbAlias) && !string.IsNullOrEmpty(_attachedDbFileName))
        {
            using (var attachCommand = connection.CreateCommand())
            {
                attachCommand.CommandText = string.Format(
                    "ATTACH DATABASE '{0}' AS {1}",
                    _attachedDbFileName.Replace("'", "''"),
                    _attachedDbAlias);
                attachCommand.ExecuteNonQuery();
            }
        }
        return connection;
    }

    public override void Configure(IDictionary<string, string> settings)
    {
        base.Configure(settings);
        settings.TryGetValue("connection.attached_db_alias", out _attachedDbAlias);
        settings.TryGetValue("connection.attached_db_filename", out _attachedDbFileName);
    }
}

Configuration file:

<property name="connection.provider">MyApp.DataAccess.AttachedDbConnectionProvider, MyApp.DataAccess</property>
<property name="connection.attached_db_alias">reportdb</property>
<property name="connection.attached_db_filename">mydatabase.report.db</property>

Now, to map a class to a table in the attached database, I just need to specify "reportdb." in the mapping file

Upvotes: 1

Views: 650

Answers (1)

Diego Mijelshon
Diego Mijelshon

Reputation: 52745

This might help...

public class MyConnectionProvider : DriverConnectionProvider
{
    public override IDbConnection GetConnection()
    {
        var connection = base.GetConnection();
        var attachCommand = connection.CreateCommand();
        attachCommand.CommandText = "ATTACH DATABASE FOO";
        attachCommand.ExecuteNonQuery();
        return connection;
    }
}

Config:

<property name="connection.provider">MyConnectionProvider, MyAssembly</property>

Upvotes: 1

Related Questions