This weeks code snippet is prompted by a StackOverflow question, regarding how to detect if the database that was backing an Entity Framework DbContext was SQL Server Compact.
My proposed check simply uses the "is" operator to determine if the Database.Connection property is compatible with the SqlCeConnection class.
so my check is:
context.Database.Connection is SqlCeConnection
In this blog post I will demonstrate a couple of improvements for adding many entities to a Entity Framework based database. You can read more about the beta 1 release here, and Julie Lerman highlights some of the features that were available in the alpha here. For all full list of EF 6 features, see the list here.
Here we will look at getting started with Entity Framework 6 beta 1, and a couple of improvements that makes adding many rows to a SQL Server Compact database via Entity Framework feasible, and also have look at using my SqlCeBulkCopy library to do the same.
I will use a console app for this project in order to focus on the Entity Framework code. To get started, launch Visual Studio, and create a new Console Application. Lets call it EF6Test.
Now let’s add Entity Framework 6 beta 1 SQL Server Compact package. Launch the NuGet Package Mangager Console (from Tools, Other Windows) and run this command:
PM> Install-Package EntityFramework.SqlServerCompact -Pre
(The –Pre switch allows you to install pre-release packages)
You should now see several messages in the window, the last one being:
Successfully added 'EntityFramework.SqlServerCompact 6.0.0-beta1-20603' to EF6Test.
This process has added a number of DLL references to the project, and added an app.config file to the project, with an entityFramework section that specifies the SQL Server Compact default connection factory:
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
<parameters>
<parameter value="System.Data.SqlServerCe.4.0" />
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
</providers>
</entityFramework>
Now add a using statement:
using System.Data.Entity;
And add the following 2 classes before “class Program”, these define our single test table and our DbContext:
public class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
}
public class StudentContext : DbContext
{
public DbSet<Student> Students { get; set; }
}
Now add the following code to the Main method:
1: Stopwatch sw = new Stopwatch();
2: bool useSqlCeBulkCopy = false;
3: var students = CreateStudents(); 4: 5: Database.SetInitializer(new DropCreateDatabaseAlways<StudentContext>());
6: 7: using (var db = new StudentContext())
8: {9: db.Database.Initialize(true);
10: if (!useSqlCeBulkCopy)
11: { 12: sw.Restart();13: //AddRange rulez, no need for db.Configuration.AutoDetectChangesEnabled = false;
14: db.Students.AddRange(students); 15: sw.Stop(); 16: 17: Console.WriteLine(18: "Added 8000 entities in {0}", sw.Elapsed.ToString());
19: 20: sw.Restart();21: int recordsAffected = db.SaveChanges();
22: sw.Stop(); 23: 24: Console.WriteLine(25: "Saved {0} entities in {1}", recordsAffected, sw.Elapsed.ToString());
26: 27: } 28: Console.ReadKey(); 29: } 30: } 31: 32: private static List<Student> CreateStudents()
33: {34: var students = new List<Student>();
35: for (int i = 0; i < 8000; i++)
36: {37: var student = new Student { Name = Guid.NewGuid().ToString() };
38: students.Add(student); 39: }40: return students;
41: }The CreateStudents method simply creates a List object with 8000 Student objects. A new database is created on each run (line 5) and the students are added to the StudentContext DbContext, using the excellent new AddRange method, similar to the LINQ to SQL InsertAllOnSubmit method. With EF5 you only had the Add method, and to get reasonable performance, you had to use the cryptic db.Configuration.AutoDetectChangesEnabled = false statement.
With SQL Server Compact and EF5, inserting 8000 rows takes about 58 seconds on my PC, and it may even time out on yours… Thanks to the fact that Entity Framework is now open source on CodePlex I was able to submit a bug fix, which got accepted for EF6, so the process now takes about 8 seconds on my PC.
To compare, let’s add the SqlCeBulkCopy NuGet package and perform the same process using that. In the Package Manager Console. type
PM> Install-Package ErikEJ.SqlCeBulkCopy
Before Console.ReadKey, paste the following code:
else
{
using (SqlCeBulkCopy bcp = new SqlCeBulkCopy(db.Database.Connection.ConnectionString))
{
bcp.DestinationTableName = "Students";
sw.Restart();
bcp.WriteToServer(students);
sw.Stop();
Console.WriteLine(
"Saved {0} entities using SqlCeBulkCopy in {1}", students.Count, sw.Elapsed.ToString());
}
}
And set useSqlCeBulkCopy = true.On my machine this takes about 150 ms! So despite the improvements made in EF6 beta 1, for larger data loads, I suggest you use SqlCeBulkCopy, and as you can see from the code above, it is very easy to integrate in an Entity Framework context.
You can download the completed project from here: http://sdrv.ms/18NaRmW
Under normal operation, SQL Server Compact keeps all pending disk writes in memory, and flushes them to disk at least every 10 seconds. The connection string property to control this is called “Flush Interval”, and valid values are between 1 and 1000 seconds. But you may want to flush to disk immediately under certain circumstances, and this weeks code snippet demonstrates how to do just that. This is possible via the CommitMode property on the SqlCeTransaction object Commit method, as demonstrated below:
using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\data\AdventureWorks.sdf;"))
{
conn.Open();
// Start a local transaction; SQL Server Compact supports the following
// isolation levels: ReadCommitted, RepeatableRead, Serializable
using (SqlCeTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
using (SqlCeCommand cmd1 = conn.CreateCommand())
{
// To enlist a command in a transaction, set the Transaction property
cmd1.Transaction = tx;
try
{
cmd1.CommandText = "INSERT INTO FactSalesQuota " +
"(EmployeeKey, TimeKey, SalesAmountQuota) " +
"VALUES (2, 1158, 150000.00)";
cmd1.ExecuteNonQuery();
// Commit the changes to disk immediately, if everything above succeeded;
// Use Deferred mode for optimal performance; the changes will
// be flashed to disk within the timespan specified in the
// ConnectionString 'FLUSH INTERVAL' property (default 10 seconds);
//
tx.Commit(CommitMode.Immediate);
}
catch (Exception)
{
tx.Rollback();
}
}
}
}
Another entry in the scripting API samples, you will find an overview of getting started with the API here.
This weeks entry demonstrates the general pattern for scripting smaller chunks of SQL based on a single table, in this case a INSERT statement for each row in the table.
using (IRepository repository = new DBRepository(@"Data Source=C:\Northwind.sdf"))
{
Generator generator = new Generator(repository, null);
foreach (var tableName in repository.GetAllTableNames())
{
generator.GenerateTableContent(tableName, false);
}
System.IO.File.WriteAllText(@"C:\script.sqlce", generator.GeneratedScript);
}
For SQL Server Compact 4.0, the scripting library is now available on NuGet, making it even easier to get started. https://nuget.org/packages/ErikEJ.SqlCeScripting
A question that keeps re-appearing in the forum is, how can I reseed/reset an IDENTITY column in SQL Server Compact, as no DBCC command is available. You can simply use a special syntax of the ALTER TABLE command to do this, as follows:
ALTER TABLE [MyTableName] ALTER COLUMN [Id] IDENTITY (1, 1)
This will make the value of the next generated IDENTITY value 1 and increment with 1. Notice that you do not specify the column type, only the IDENTITY specification.
Your app/web site may require a specific build version of the SQL Server Compact runtime, due to dependency on a bug fix, for example. This weeks code snippet will demonstrate how to get that information. I have an older blog post here, that goes into deep details about the various ways to get SQL Server Compact related version information.
To get the SQL Server Compact build version for 3.5 SP2 and 4.0, you can simply use:
var ver = new System.Data.SqlServerCe.SqlCeConnection().ServerVersion;
For SQL Server Compact 4.0 SP1, this returns: 4.0.8876.1.
And for SQL Server Compact 3.5 SP2 CU6, the value would be: 3.5.8088.0
Despite my tooling for this having been available for more than 18 months, I have never posted a dedicated blog post for this subject. This post intends to remedy this.
The Local Database (a SQL Server Compact database accessed via LINQ to SQL) is a data access API available on Windows Phone 7.5 and 8.
The Microsoft documentation (listed here on my blog, together with many other useful Windows Phone Local Database links) always describes a Code First workflow, which makes it cumbersome to reuse existing effort in creating a SQL Server Compact database schema for Windows Mobile or desktop, and also makes it hard to distribute a database prepopulated with data together with your app. My tooling, which is available with the SQL Server Toolbox Visual Studio add-in, and also available in a simpler form with the new standalone edition of the Toolbox for SQL Server Compact 3.5 (currently in alpha), enables both scenarios. The standalone edition is useful for VS Express users and when you do not wish to install Visual Studio on a PC (it is a single .exe file, so very simple to distribute)
In the following walkthrough, using Visual Studio 2012, I will demonstrate how to use the SQL Server Compact Toolbox to take an existing SQL Server database and use it as an included Windows Phone database in an new (or existing) Windows Phone 8 App. The process to do this requires these steps:
- Create the SQL Server Compact database from the server database and add it to the Windows Phone project
- Generate the LINQ to SQL DataContext and releated classes.
- Use the database from code
I assume you have Visual Studio 2012 Pro or higher with the Windows Phone 8 SDK installed.
I have created a new Windows Phone Databound App for this sample, and selected Windows Phone OS 8.0 as the target OS.
I then use the Toolbox to create a new SQL Server Compact 3.5 database in the folder where the Phone project resides, (you can determine the folder from by using the “Open Folder in File Explorer” context menu item).
I then click Create, navigate to the project folder, and type PostCodes.sdf, press OK.
Click OK, and a new, empty database will be added to the database list in the Toolbox:
Now we need to connect to the SQL Server database, and script it, then run the script against the new, empty database.
Create and save the database script using the Toolbox menu item above, and then open the SQL Editor against the PostCodes.sdf database file:
Use the Open button in the editor to load the script, and the press the Execute button to run the script.
Now the database contains a PostCode table (the script is available here), which has all Danish postcodes.
The final step is adding the database file to the Phone project. In Solution Explorer, select “Show all files”, and include PostCodes.sdf. In this sample scenario, we would like the database to become writable on the Phone, so include it a “Embedded Resource” – it could also be included as Content, if it was just a read-only database, read more here.
In order to generate the DataContext based on the database, right click it in the Toolbox, and select “Add Windows Phone DataContext to current project”.
If this menu item is disabled, verify that the database file is in 3.5 format, and that the SQL Server Compact 3.5 SP2 runtime is properly installed, you can check this via the About dialog. “Yes” is required in both places, if that is not the case, repair/re-install.
Let’s walk through the various options on this dialog:
Context name: The name of the generated DataContext class
Namespace: Allows you to specify another namespace for the generated code
Language: You can generate C# or VB code.
Pluralize: If checked, will rename tables (Person => People) etc.
Create a file per table: Normally, just a single file is created
Advanced options:
Add schema version table: If you would like to include the database file a a writeable file, and allow use of the DatabaseSchemaUpdater class in a future app version select this option .
Add rowversion column to all tables: Checking this will ensure that all tables have a rowversion column (formerly timestamp), which enhances performance when doing UPDATE and DELETE (see my blog posts here and here)
Include ConnectionStringBuilder: Will add a LocalDatabaseConnectionStringBuilder class to the project, to help with building connection strings in a strongly typed fashion.
For this sample project, just click OK, and a PostCodesContext.cs file will be added to the project, and we are done.
Finally, to demonstrate that we are able to include data with the app, alter the DataTemplate in MainPage.xaml as follows:
<DataTemplate>
<StackPanel Margin="0,0,0,17">
<TextBlock Text="{Binding Zip}" TextWrapping="Wrap" Style="{StaticResource PhoneTextExtraLargeStyle}"/>
<TextBlock Text="{Binding City}" TextWrapping="Wrap" Margin="12,-6,12,0" Style="{StaticResource PhoneTextSubtleStyle}"/>
</StackPanel>
</DataTemplate>
Replace the OnNavigatedTo event handler in MainPage.xaml.cs with the following code:
protected override void OnNavigatedTo(NavigationEventArgs e)
{
using (PostCodesContext ctx = new PostCodesContext(PostCodesContext.ConnectionString))
{
ctx.CreateIfNotExists();
ctx.LogDebug = true;
MainLongListSelector.ItemsSource = ctx.PostCode.ToList();
}
}
This code initialize a new PostCodesContext instance (embraced in “using”, as it is Disposable). The CreateIfNotExists method extracts the PostCodes.sdf embedded resource from the project, and copies it to isolated storage (feel free to look at the code). Setting LogDebug to true will show all SQL statements as text in the Debug window while debugging:
SELECT [t0].[Id], [t0].[Zip], [t0].[City], [t0].[Street], [t0].[Company], [t0].[IsProvince], [t0].[rowguid] AS [Rowguid], [t0].[ts] AS [Ts]
FROM [PostCode] AS [t0]
And finally, calling ToList() will execute the SELECT and return a list of PostCode objects, that is the bound to the ItemsSource property of the LongListSelector.
Result:
Let us finish with a summary of advantages of this approach:
- Use desktop database tools for data population and schema design
- Saves time doing 1:1 mapping between database tables and DataContext classes
- DataContext class and entity classes are partial and can be extended
- The generated DataContext contains Index definitions (which SqlMetal does not support, as this is a Windows Phone extension)
- The generated DataContext contains the CreateIfNotExists method, that optionally extracts an included database (prepopulated with data) to Isolated Storage
- The generated DataContext includes the LogDebug property, that allows you to see all SQL statements generated on the device in the debug window
- Optionally split the generated Data Context classes into multiple files
- Optionally add a Version table if you include the table with your app, and want to enable use of the schema updater functionality.
- Optionally add rowversion columns to improve UPDATE and DELETE performance
- Optionally include a ConnectionStringBuilder class to build a valid connection string in a strongly typed way, using advanced connection string options (see some of my Phone blog posts for candidates)
Hope you find it useful.