C# Entity Framework + SQLite - selecting database file by user.

Question

I made WPF app which uses Entity Framework and SQLite. At the beggining I loaded my database via ADO.NET Entity Data Model. Everything was working quite good but now I want to add option for users to choose from local files with database (all tables in databases should be the same but data could be diffrent for diffrent files). So to test this solution I copied my database file and made some changes in code:

I edited DbContext file by adding: (I found this here https://stackoverflow.com/a/23105811)

    public appDBEntities(string filename)
     : base(new SQLiteConnection() { ConnectionString =
        new SQLiteConnectionStringBuilder()
            { DataSource = filename, ForeignKeys = true }
        .ConnectionString }, true)
    {
    }

The users is selecting database file by using this function:

    private void testButton_Click(object sender, RoutedEventArgs e)
    {
        // Create OpenFileDialog 
        Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

        // Set filter for file extension and default file extension 
        dlg.DefaultExt = ".db";
        dlg.Filter = "Database files (*.db)|*.db";

        // Display OpenFileDialog by calling ShowDialog method 
        Nullable<bool> result = dlg.ShowDialog();

        // Get the selected file name
        if (result == true)
        {
            // Open document 
            publicVariables.databasePath = dlg.FileName;
        } 
    }

Than i want to test if everything is ok by displaying some data from database with this function:

    public static void showParticipants(DataGrid participantsDataGrid, string connectionString)
    {
        using (var db = new appDBEntities(connectionString))
        {
            var query = from c in db.Participant
                        join gender in db.Sex on c.Sex equals gender.id
                        join club in db.Club on c.Club equals club.id
                        join nation in db.Nation on c.Nationality equals nation.id
                        select c.id;

            var data = query.ToList();
            participantsDataGrid.ItemsSource = data;
            db.Dispose();
        }

    }

Like this:

showParticipants(testDataGrid, publicVariables.databasePath);

But this function

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

Is throwing this exception:

The context is being used in Code First mode with code that was generated from an EDMX file for either Database First or Model First development. This will not work correctly. To fix this problem do not remove the line of code that throws this exception. If you wish to use Database First or Model First, then make sure that the Entity Framework connection string is included in the app.config or web.config of the start-up project. If you are creating your own DbConnection, then make sure that it is an EntityConnection and not some other type of DbConnection, and that you pass it to one of the base DbContext constructors that take a DbConnection. To learn more about Code First, Database First, and Model First see the Entity Framework documentation >here:http://go.microsoft.com/fwlink/?LinkId=394715

score 0 · Accepted Answer · answered Aug 23 '17 at 16:19

I have a similar task working by changing config entry, maybe it would work for you:

var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
var connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings");

connectionStringsSection.ConnectionStrings["DbStatisticsEntities"].ConnectionString =
    "metadata=res://*/Models.DbStatisticsModel.csdl|res://*/Models.DbStatisticsModel.ssdl|res://*/Models.DbStatisticsModel.msl;provider=System.Data.SQLite.EF6;provider connection string='data source=" + this.DBFileName + "'";
connectionStringsSection.ConnectionStrings["DbStatisticsEntities"].ProviderName = "System.Data.EntityClient";
config.Save();
ConfigurationManager.RefreshSection("connectionStrings");

Properties.Settings.Default.Save();
Properties.Settings.Default.Reload();

C# Entity Framework + SQLite - selecting database file by user.

1 Answers1