When my Winforms application reaches the first Entity Framework data "request" I get the following error.
Message: The underlying provider failed to open.
InnerException: Data source cannot be empty.
My connection string is being built dynamically to cater for the user's %appdata%
directory path. I am building it at first runtime and updating the app.config
with the connection string once it is built. The code that handles this is below:
public void ConstructEntityFrameworkConnectionString(string dbFileName)
{
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
if (config.ConnectionStrings.ConnectionStrings["RemManagerDBEntities"] == null)
{
// Specify the provider name, server and database.
var provider = "System.Data.SQLite.EF6";
var providerName = "System.Data.EntityClient";
var attacheddbfilename = dbFileName;
// Initialize the connection string builder for the underlying provider.
SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder {
AttachDBFilename = attacheddbfilename,
IntegratedSecurity = true,
MultipleActiveResultSets = true,
ConnectTimeout = 30,
ApplicationName = "RemManagerDBEntities"
};
// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();
// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder {
Provider = provider,
ProviderConnectionString = providerString,
Metadata = @"res://*/EntityFramework.RemManagerDBModel.csdl|res://*/EntityFramework.RemManagerDBModel.ssdl|res://*/EntityFramework.RemManagerDBModel.msl"
};
var cs = new ConnectionStringSettings("RemManagerDBEntities", entityBuilder.ConnectionString, providerName);
if (config.ConnectionStrings.ConnectionStrings["RemManagerDBEntities"] == null)
{
config.ConnectionStrings.ConnectionStrings.Add(cs);
config.Save(ConfigurationSaveMode.Modified);
ConfigurationManager.RefreshSection("connectionStrings");
}
}
}
An example connection string that it creates:
metadata=res://*/EntityFramework.RemManagerDBModel.csdl|res://*/EntityFramework.RemManagerDBModel.ssdl|res://*/EntityFramework.RemManagerDBModel.msl; provider=System.Data.SQLite.EF6; provider connection string="AttachDbFilename=C:\Users\MacbookPro\AppData\Roaming\RemManager\Datasource\RemManagerDB.db; Integrated Security=True; MultipleActiveResultSets=True; Connect Timeout=30; Application Name=RemManagerDBEntities"
The method is called as part of the main method in the program.cs file.
static void Main()
{
var conController = new ConnectionController();
conController.ConstructEntityFrameworkConnectionString($"{Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)}\\RemManager\\Datasource\\RemManagerDB.db");
// Start the application
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new RemManager());
}
I've got the following NuGet packages installed:
Initially I have based the setup procedure from the following site with an amendment made by the second link shown.
https://www.codeproject.com/Tips/1056400/Setting-up-SQLite-and-Entity-Framework-Code-First
https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider
Below is an example of the calls that I am making to the database via entity framework. When stepping through the code the exception begins on the db.tblGroups.Select
line.
public class GroupController : IGroup
{
public List<tblGroup> SelectAllGroups()
{
using (var db = new RemManagerDBEntities())
{
return db.tblGroups.Select(g => g).ToList();
}
}
...
}
The final piece of the puzzle is the app.config. The current config is shown below.
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<connectionStrings>
<!-- See Datasource.ConnectionController for the connection string builder.-->
</connectionStrings>
<entityFramework>
<providers>
<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
<provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
</providers>
</entityFramework>
<appSettings>
<add key="databaseFilePath" value="" />
<add key="ClientSettingsProvider.ServiceUri" value="" />
</appSettings>
<system.web>
<membership defaultProvider="ClientAuthenticationMembershipProvider">
<providers>
<add name="ClientAuthenticationMembershipProvider" type="System.Web.ClientServices.Providers.ClientFormsAuthenticationMembershipProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" />
</providers>
</membership>
<roleManager defaultProvider="ClientRoleProvider" enabled="true">
<providers>
<add name="ClientRoleProvider" type="System.Web.ClientServices.Providers.ClientRoleProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" cacheTimeout="86400" />
</providers>
</roleManager>
</system.web>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SQLite.EF6" />
<add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
<add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
</DbProviderFactories>
</system.data>
</configuration>
For what else it is worth, entity framework is working from a database first configuration. I am also running Entity Framework 6.2 and SQLite 1.0.106.0.
I've tried my best to find a valid solution on other sites that caters for the fact that I am combining Entity Framework and SQLite, but I haven't been able to come up with much. When I take the SQLite out of the equation, I still haven't been able to come up with much. One of the better questions I had found was here, but I am already doing what the suggested answer recommends.
I had a similar problem earlier, except with localdb. The application has been working previously on the localdb, however this issue has started since I have been endeavouring toward making the application more self contained and user friendly (e.g. not needing to modify the exe.config manually once installed).
Any suggestions would be greatly appreciated.
You cannot use a SqlConnectionStringBuilder with SQLite, use SQLiteConnectionStringBuilder instead, and set the DataSource property to the database file name