none
Using a Compact Database 4.0 as a project file format with EF 4.1 RRS feed

  • Question

  • I have been trying to use EF 4.1 as a project file database, in other words is there is not a single SQL CE database but one per user project; from the user perspective the files are just like a normal application documents. The part that I have not been able to figure out is when I have a current project open with EF 4.1 and I want to do a Save As of the project, that would require a disconnect and copy form the current open file (database) to destination location and then connect to the destination and save the pending changes EF had in memory. I have used SQL CE 4.0 by itself without any problem as a project file. So what require is a way to implement the save as functionality, without losing the in memory changes and preserving the original file as a normal save as would do. I have been able to do this using SQL CE and standard SQL commands, but it would be nice to be able to use EF.

    Tuesday, September 6, 2011 3:19 PM

Answers

  • I found a way that seems to work, I made a simple test case and it worked:

    I am closing the EF connection to copy the file and then reconnecting to the new copy, it seems to do the trick.

     CopyTo((SqlCeConnection)_projectContext.Database.Connection, dialog.FileName);
     _projectContext.SaveChanges();

     public static void CopyTo(SqlCeConnection connection, string destination)
            {
                if (destination == null)
                    throw new ArgumentNullException("destination");
                var source = connection.Database;
    
                if (String.Equals(source, destination, StringComparison.OrdinalIgnoreCase))
                    throw new IOException("Unable to copy project to myself");
    
                File.Delete(destination);
    
                connection.Close();
                try
                {
                    File.Copy(source, destination);
                    connection.ConnectionString = GetConnectionString(destination);
                }
                finally
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (SqlCeException) { }
                }
            }
    


     


    • Edited by Hugo Rumayor Tuesday, September 6, 2011 7:51 PM
    • Marked as answer by Hugo Rumayor Tuesday, September 6, 2011 8:04 PM
    Tuesday, September 6, 2011 7:47 PM

All replies

  • This might be able to be done in EF.  What you would have to do is every time you create a new database, you would need to create a connection string to your new database and then open up a new ObjectContext passing the new connection string as a parameter.

    Your in-memory changes (Entity objects) would have to be attached to the new ObjectContext and then persisted into that context.  You would have to do something like this:

    var ctx = new MyEntities(old_connection_string);
    
    //do some stuff with the context, get data, populate screens, etc
    ...
    
    //now when you're ready to Save As:
    
    //load your entities with data that user has on the screen
    var cust = new Customer()
    {
       CustomerID = txtCustID.txt,
       Address = txtCustAddress.txt,
       Name = txtCustName.txt,
       ...
    }
    
    //drop the old context
    ctx.Dispose();
    
    //create the new database and return its connection string
    string new_connection_string = CreateNewCEDatabase();
    
    //create a new context using the new connection string
    ctx = new MyEntities(new_connection_string);
    
    //attach changed entity(s) to new context and save changes
    ctx.Customers.Attach(cust);
    ctx.ObjectStateManager.ChangeObjectState(cust,EntityState.Modified);
    cts.SaveChanges
    
    
    
    

     

     

     

     


    Tom Overton
    Tuesday, September 6, 2011 4:11 PM
  • Hi Tom,

    This approach would probably work, but it is error prone if there are many entity types as it has to be manually detached and retched to perform the copy, I hoped there would some way to copy all the state at once like some kind of disconnect, with out disposing the context. What would be ideal is closing the connection in the context making the copy and setting a new connection to the context, but I think that is not possible because once the context is created you can't change the connection.

    Tuesday, September 6, 2011 5:48 PM
  • Hugo,

    Yes i agree it could be problematic and error prone, especially if you will have a bunch of pending changes to entities that need to be persisted that are in various states (updated, added, deleted, etc). 

    Instead of doing a "Save as" approach, could you do a "Start new project" approach?  Meaning, the user has to know up front they will be creating a new project before they even start entering any data.  You basically do the "Save as" up front with the user selecting their current project to use as the base for the new project.  I know it would be a change in the process of how the users currently do it and that's always something you want to avoid but from a technical standpoint it would simply things greatly


    Tom Overton
    Tuesday, September 6, 2011 6:15 PM
  • I found a way that seems to work, I made a simple test case and it worked:

    I am closing the EF connection to copy the file and then reconnecting to the new copy, it seems to do the trick.

     CopyTo((SqlCeConnection)_projectContext.Database.Connection, dialog.FileName);
     _projectContext.SaveChanges();

     public static void CopyTo(SqlCeConnection connection, string destination)
            {
                if (destination == null)
                    throw new ArgumentNullException("destination");
                var source = connection.Database;
    
                if (String.Equals(source, destination, StringComparison.OrdinalIgnoreCase))
                    throw new IOException("Unable to copy project to myself");
    
                File.Delete(destination);
    
                connection.Close();
                try
                {
                    File.Copy(source, destination);
                    connection.ConnectionString = GetConnectionString(destination);
                }
                finally
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (SqlCeException) { }
                }
            }
    


     


    • Edited by Hugo Rumayor Tuesday, September 6, 2011 7:51 PM
    • Marked as answer by Hugo Rumayor Tuesday, September 6, 2011 8:04 PM
    Tuesday, September 6, 2011 7:47 PM
  • Hugo,

    Are you using the context.Detach() or DeleteDatabase to disconnect from the database?  I wasn't sure if that would still keep the database locked.


    Tom Overton
    • Edited by Tom_Overton Tuesday, September 6, 2011 7:57 PM
    Tuesday, September 6, 2011 7:55 PM
  • Hi Tom,

    I am just closing context the open connection, making a copy and using the same connection change the connection string to the copy then opening the connection again, you can't replace a connection form the context but you can change the connection string of a connection that is closed; at the end I call the SaveChanges to save on the copied database.

    Hugo


    Tuesday, September 6, 2011 8:00 PM
  • Okay I see what you're doing, just working with the underlying reference to the SqlCeConnection object.  That's great if it works, i thought EF might not like it if the database was swapped from under it.
    Tom Overton
    Tuesday, September 6, 2011 8:01 PM
  • I made some basic tests and it works, I had made the same assumption that it would not like it, but it is being replaced for an exact copy so probably that is the reason it does not complain.

    Tanks for the help Tom.

    Tuesday, September 6, 2011 8:03 PM
  • One thing to remember is that because you have opened the connection after the copy (instead of EF doing it like it usually does), EF isn't going to close it automatically when the context gets disposed.  So you'll have to remember to close the connection manually otherwise you might end up with memory leaks and using up database sessions.  Sounds like this solution is a good one and it's nice to see EF is flexible enough to handle this.  


    Tom Overton
    Tuesday, September 6, 2011 8:14 PM