Can anyone provide a C# example of SQLite "SetTimeout"?

Question

I'm using SQLite (system.data.sqlite v. 1.0.60.0) on a Fluent NHibernate project.

I have one program that writes to the DB, and another that reads from it. Occasionally, I get SQLITE_BUSY exceptions, and I need to fix this.

I found several Google references to sqlite_busy_timeout, which (if I understand correctly) will cause SQLite to re-try before throwing the exception. This would probably be sufficent for my needs.

However, this does not appear to be in the system.data.sqlite assembly.

When I search for SetTimeout using the Object Browser, I get two hits:

System.Data.SQLite.SQLite3.SetTimeout(int)

System.Data.SQLite.SQLiteBase.SetTimeout(int)

but I can't seem to use them in my code - they don't show up in Intellisense, and VS2008 shows a red underline for SQLite3, with the message "Can't access internal class here".

Can anyone give me a sample (in C#) that shows the exact syntax for this method?

Or is this even the right approach? I could probably check for SQLITE_BUSY in my code, but have not found any good examples demonstrating that approach either.

Finally, do Fluent NHibernate or NHibernate have any mechanisms to provide simple shared access to a SQLite database?

Answer 1

SetTimeout is a member of both NHibernate query interfaces, ICriteria and IQuery.

Example:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    var items = session.CreateQuery("select something complex from a big table")
                       .SetTimeout(600) // 10 minutes
                       .List();
    tx.Commit();
}

Answer 2

Just like you said, SetTimeout() is internal, so you (or NHibernate) can't call it. The method only wraps
sqlite_busy_timeout and throws, and you definitely don't want to use those unsafe methods in your application code.

According to this, the SQLite provider should retry for 30 seconds.