Entity Framework to read SQLite database from user's application data folder

Question

I created installer project to my .NET application. The installer installs also the required SQLite file into special folder -> user's application data roaming folder (C:\Users\Xxxx\AppData\Roaming\MyCompany).

The application uses Entity Framework to access that SQLite file and during the development I have configured Entity Framework to work with SQLite on my local computer folder. I don't know how to configure Entity Framework to read/write from/to user's application data folder.

Mongi HAMMAMI Mongi HAMMAMI · Accepted Answer · 2013-10-04T15:46:29

The connection string to the SQLite file does not allow relative path. When application is deployed u have 2 options:

1- Manually change the path to ur SQLite database file in ur app.config related file -> connectionString section. Not a clean solution i think.

2- Try to change the config programmatically on the start of the application or on deployement. Here a sample code on how to achieve that:

string _EntityContainerName = "";//Ur entity container name
                Configuration _Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
                //DataSource
                System.Data.SQLite.SQLiteConnectionStringBuilder _SqlBuilder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                _SqlBuilder.DataSource = @"C:\Users\Xxxx\AppData\Roaming\MyCompany\{UrSQLiteFileName}.s3db";
                string _ProviderString = _SqlBuilder.ToString();
                //ConnectionString
                EntityConnectionStringBuilder _DataEntityBuilder = new EntityConnectionStringBuilder();
                _DataEntityBuilder.Provider = "System.Data.SQLite";
                _DataEntityBuilder.ProviderConnectionString = _ProviderString;
                _DataEntityBuilder.Metadata = string.Format(@"res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl", _EntityContainerName);
                string _DataConnectionString = _DataEntityBuilder.ToString();
                _Configuration.ConnectionStrings.ConnectionStrings[_EntityContainerName].ConnectionString = _DataConnectionString;
                _Configuration.ConnectionStrings.ConnectionStrings[_EntityContainerName].ProviderName = "System.Data.EntityClient";
                _Configuration.Save(System.Configuration.ConfigurationSaveMode.Modified, true);
                ConfigurationManager.RefreshSection("connectionStrings");

Hope will help.

Entity Framework to read SQLite database from user's application data folder

1 Answers