Unable to open database (sqlite) in asp.net mvc3 app using spring.net

Question

I am writing an asp.net mvc3 web application. I want to use a sqlite database. Unfortunatly I get an SqliteException saying "unable to open database".

Spring-Version: 1.3.2

NHibernate-Version: 3.2

DbProvider/Connectionstring:

<db:provider id="employeesDbProvider"
           provider="SQLite-1.0.72"
           connectionString="Data Source=.\Database\Employees.db3;Version=3;">
</db:provider>

Stacktrace:

bei System.Data.SQLite.SQLite3.Open(String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLite3.cs:Zeile 239.
bei System.Data.SQLite.SQLiteConnection.Open() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:Zeile 1022.
bei NHibernate.Connection.DriverConnectionProvider.GetConnection()

I even tried setting the DataSource to an absolut path (C:\temp\database\Employees.db3) but the same SqLiteException is thrown.

Update:

Stacktrace shown on error page:

[SQLiteException (0x80004005): Unable to open the database file]
   System.Data.SQLite.SQLite3.Open(String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLite3.cs:239
   System.Data.SQLite.SQLiteConnection.Open() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:1022
   NHibernate.Connection.DriverConnectionProvider.GetConnection() +155
   NHibernate.Tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.Prepare() +41
   NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.GetReservedWords(Dialect dialect, IConnectionHelper connectionHelper) +105
   NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.Update(ISessionFactory sessionFactory) +151
   NHibernate.Impl.SessionFactoryImpl..ctor(Configuration cfg, IMapping mapping, Settings settings, EventListeners listeners) +1012
   NHibernate.Cfg.Configuration.BuildSessionFactory() +162
   Spring.Data.NHibernate.LocalSessionFactoryObject.NewSessionFactory(Configuration config) in c:\_prj\spring-net\trunk\src\Spring\Spring.Data.NHibernate20\Data\NHibernate\LocalSessionFactoryObject.cs:952
   Spring.Data.NHibernate.LocalSessionFactoryObject.AfterPropertiesSet() in c:\_prj\spring-net\trunk\src\Spring\Spring.Data.NHibernate20\Data\NHibernate\LocalSessionFactoryObject.cs:706
   Spring.Objects.Factory.Support.AbstractAutowireCapableObjectFactory.InvokeInitMethods(Object target, String name, IConfigurableObjectDefinition definition) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractAutowireCapableObjectFactory.cs:1294
   Spring.Objects.Factory.Support.AbstractAutowireCapableObjectFactory.ConfigureObject(String name, RootObjectDefinition definition, IObjectWrapper wrapper) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractAutowireCapableObjectFactory.cs:1890
   Spring.Objects.Factory.Support.WebObjectFactory.ConfigureObject(String name, RootObjectDefinition definition, IObjectWrapper wrapper) in c:\_prj\spring-net\trunk\src\Spring\Spring.Web\Objects\Factory\Support\WebObjectFactory.cs:450
   Spring.Objects.Factory.Support.AbstractAutowireCapableObjectFactory.InstantiateObject(String name, RootObjectDefinition definition, Object[] arguments, Boolean allowEagerCaching, Boolean suppressConfigure) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractAutowireCapableObjectFactory.cs:921

[ObjectCreationException: Error creating object with name 'employeesSessionFactory' defined in 'file [C:\Develop\ASP.Net-Schulung\Mitarbeiterverwaltung.Web\bin\Config\Spring.Database.config.xml] line 16' : Initialization of object failed : Unable to open the database file]
   Spring.Objects.Factory.Support.AbstractAutowireCapableObjectFactory.InstantiateObject(String name, RootObjectDefinition definition, Object[] arguments, Boolean allowEagerCaching, Boolean suppressConfigure) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractAutowireCapableObjectFactory.cs:938
   Spring.Objects.Factory.Support.AbstractObjectFactory.CreateAndCacheSingletonInstance(String objectName, RootObjectDefinition objectDefinition, Object[] arguments) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractObjectFactory.cs:2144
   Spring.Objects.Factory.Support.WebObjectFactory.CreateAndCacheSingletonInstance(String objectName, RootObjectDefinition objectDefinition, Object[] arguments) in c:\_prj\spring-net\trunk\src\Spring\Spring.Web\Objects\Factory\Support\WebObjectFactory.cs:299
   Spring.Objects.Factory.Support.AbstractObjectFactory.GetObjectInternal(String name, Type requiredType, Object[] arguments, Boolean suppressConfigure) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractObjectFactory.cs:2065
   Spring.Objects.Factory.Support.AbstractObjectFactory.GetObject(String name) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\AbstractObjectFactory.cs:1826
   Spring.Objects.Factory.Support.DefaultListableObjectFactory.PreInstantiateSingletons() in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Objects\Factory\Support\DefaultListableObjectFactory.cs:505
   Spring.Context.Support.AbstractApplicationContext.Refresh() in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Context\Support\AbstractApplicationContext.cs:1017
   Spring.Context.Support.MvcApplicationContext..ctor(MvcApplicationContextArgs args) in c:\_prj\spring-net\trunk\src\Spring\Spring.Web.Mvc\Context\Support\MvcApplicationContext.cs:75
   Spring.Context.Support.MvcApplicationContext..ctor(String name, Boolean caseSensitive, String[] configurationLocations) in c:\_prj\spring-net\trunk\src\Spring\Spring.Web.Mvc\Context\Support\MvcApplicationContext.cs:47
   _dynamic_Spring.Context.Support.MvcApplicationContext..ctor(Object[] ) in c:\_prj\spring-net\trunk\src\Spring\Spring.Web.Mvc\Context\Support\MvcApplicationContext.cs:50
   Spring.Reflection.Dynamic.SafeConstructor.Invoke(Object[] arguments) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Reflection\Dynamic\DynamicConstructor.cs:116
   Spring.Context.Support.RootContextInstantiator.InvokeContextConstructor(ConstructorInfo ctor) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Context\Support\ContextHandler.cs:563
   Spring.Context.Support.ContextInstantiator.InstantiateContext() in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Context\Support\ContextHandler.cs:508
   Spring.Context.Support.ContextHandler.InstantiateContext(IApplicationContext parentContext, Object configContext, String contextName, Type contextType, Boolean caseSensitive, String[] resources) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Context\Support\ContextHandler.cs:351
   Spring.Context.Support.ContextHandler.Create(Object parent, Object configContext, XmlNode section) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Context\Support\ContextHandler.cs:289

[ConfigurationErrorsException: Error creating context 'spring.root': Unable to open the database file]
   System.Configuration.BaseConfigurationRecord.EvaluateOne(String[] keys, SectionInput input, Boolean isTrusted, FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult) +199
   System.Configuration.BaseConfigurationRecord.Evaluate(FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult, Boolean getLkg, Boolean getRuntimeObject, Object& result, Object& resultRuntimeObject) +1153
   System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) +1468
   System.Configuration.BaseConfigurationRecord.GetSection(String configKey) +41
   System.Web.HttpContext.GetSection(String sectionName) +52
   System.Web.Configuration.HttpConfigurationSystem.GetSection(String sectionName) +57
   System.Web.Configuration.HttpConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String configKey) +6
   System.Configuration.ConfigurationManager.GetSection(String sectionName) +78
   Spring.Util.ConfigurationUtils.GetSection(String sectionName) in c:\_prj\spring-net\trunk\src\Spring\Spring.Core\Util\ConfigurationUtils.cs:71
   Spring.Context.Support.WebApplicationContext.GetContextInternal(String virtualPath) in c:\_prj\spring-net\trunk\src\Spring\Spring.Web\Context\Support\WebApplicationContext.cs:335
   Spring.Context.Support.WebApplicationContext.GetRootContext() in c:\_prj\spring-net\trunk\src\Spring\Spring.Web\Context\Support\WebApplicationContext.cs:223
   Spring.Context.Support.WebSupportModule.Init(HttpApplication app) in c:\_prj\spring-net\trunk\src\Spring\Spring.Web\Context\Support\WebSupportModule.cs:175
   System.Web.HttpApplication.InitModulesCommon() +172
   System.Web.HttpApplication.InitModules() +43
   System.Web.HttpApplication.InitInternal(HttpContext context, HttpApplicationState state, MethodInfo[] handlers) +828
   System.Web.HttpApplicationFactory.GetNormalApplicationInstance(HttpContext context) +304
   System.Web.HttpApplicationFactory.GetApplicationInstance(HttpContext context) +107
   System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +327

Answer 1

I did a quick test with SQLite, Spring.Net and it fails with the same error, however when I used slashes instead of backslashes in the connection string it magically starts to work.

<db:provider id="employeesDbProvider" 
    provider="SQLite-1.0.72"
    connectionString="Data Source=c:/temp/Database/Sample.db3;Version=3;">
</db:provider>

Edit: I tested a bit further to find out where the file is stored if I use the dot prefix e.g. ./Employees.db3. In IISExpress it is stored in C:\Program Files (x86)\IIS Express most likely not what you did expect.

In IIS7 it simply fails with the Unable to open the database file error, ihmo because it tries to write to the Temporary ASP.NET Files folder.

The safest bet is to use

<db:provider id="employeesDbProvider" 
    provider="SQLite-1.0.72"
    connectionString="Data Source=|DataDirectory|Sample.db3;Version=3;">
</db:provider>

Wich is the App_Data folder of your MVC application.

I used SQLite 1.0.80.0 for my tests. Keep in mind that SQLite does not create the directory and needs ACL write rights.