Opening an SQL CE file at runtime with Entity Framework 4
- by David Veeneman
I am getting started with Entity Framework 4, and I an creating a demo app as a learning exercise. The app is a simple documentation builder, and it uses a SQL CE store. Each documentation project has its own SQL CE data file, and the user opens one of these files to work on a project.
The EDM is very simple. A documentation project is comprised of a list of subjects, each of which has a title, a description, and zero or more notes. So, my entities are Subject, which contains Title and Text properties, and Note, which has Title and Text properties. There is a one-to-many association from Subject to Note.
I am trying to figure out how to open an SQL CE data file. A data file must match the schema of the SQL CE database created by EF4's Create Database Wizard, and I will implement a New File use case elsewhere in the app to implement that requirement. Right now, I am just trying to get an existing data file open in the app.
I have reproduced my existing 'Open File' code below. I have set it up as a static service class called File Services. The code isn't working quite yet, but there is enough to show what I am trying to do. I am trying to hold the ObjectContext open for entity object updates, disposing it when the file is closed.
So, here is my question: Am I on the right track? What do I need to change to make this code work with EF4? Is there an example of how to do this properly?
Thanks for your help.
My existing code:
public static class FileServices
{
#region Private Fields
// Member variables
private static EntityConnection m_EntityConnection;
private static ObjectContext m_ObjectContext;
#endregion
#region Service Methods
/// <summary>
/// Opens an SQL CE database file.
/// </summary>
/// <param name="filePath">The path to the SQL CE file to open.</param>
/// <param name="viewModel">The main window view model.</param>
public static void OpenSqlCeFile(string filePath, MainWindowViewModel viewModel)
{
// Configure an SQL CE connection string
var sqlCeConnectionString = string.Format("Data Source={0}", filePath);
// Configure an EDM connection string
var builder = new EntityConnectionStringBuilder();
builder.Metadata = "res://*/EF4Model.csdl|res://*/EF4Model.ssdl|res://*/EF4Model.msl";
builder.Provider = "System.Data.SqlServerCe";
builder.ProviderConnectionString = sqlCeConnectionString;
var entityConnectionString = builder.ToString();
// Connect to the model
m_EntityConnection = new EntityConnection(entityConnectionString);
m_EntityConnection.Open();
// Create an object context
m_ObjectContext = new Model1Container();
// Get all Subject data
IQueryable<Subject> subjects = from s in Subjects orderby s.Title select s;
// Set view model data property
viewModel.Subjects = new ObservableCollection<Subject>(subjects);
}
/// <summary>
/// Closes an SQL CE database file.
/// </summary>
public static void CloseSqlCeFile()
{
m_EntityConnection.Close();
m_ObjectContext.Dispose();
}
#endregion
}