cyberponictech-blog

[cyberponictech-blog]

Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB

Now that SQL Server 2014 and SQL Server Compact 4 has been released, some developers are curious about the differences between SQL Server Compact 4.0 and SQL Server Express 2014 (including LocalDB) I have updated the comparison table from the excellent discussion of the differences between Compact 3.5 and Express 2005 here to reflect the changes in the newer versions of each product. Information about LocalDB comes from here and SQL Server 2014 Books Online. LocalDB is the full SQL Server Express engine, but invoked directly from the client provider. It is a replacement of the current “User Instance” feature in SQL Server Express. Deployment/ Installation Features SQL Server Compact 3.5 SP2 SQL Server Compact 4.0 SQLite, incl. ADO.NET Provider SQL Server Express 2014 SQL Server 2014 LocalDB Installation size 2.5 MB download size 12 MB expanded on disk 2.5 MB download size 18 MB expanded on disk 10 MB download, 14 MB expanded on disk 120 MB download size > 300 MB expanded on disk 32 MB download size > 160 MB on disk ClickOnce deployment Yes Yes Yes Yes Yes Privately installed, embedded, with the application Yes Yes Yes No No Non-admin installation option Yes Yes Yes No No Runs under ASP.NET No Yes Yes Yes Yes Runs on Windows Mobile / Windows Phone platform Yes No Yes No No Runs on WinRT (Phone/Store Apps) No No Yes No No Runs on non-Microsoft platforms No No Yes No No Installed centrally with an MSI Yes Yes Yes Yes Yes Runs in-process with application Yes Yes Yes No No (as process started by app) 64-bit support Yes Yes Yes Yes Yes Runs as a service No – In process with application No - In process with application No - In process with application Yes No – as launched process Data file features SQL Server Compact 3.5 SP2 SQL Server Compact 4.0 SQLite, incl. ADO.NET Provider SQL Server Express 2014 SQL Server 2014 LocalDB File format Single file Single file Single file Multiple files Multiple files Data file storage on a network share No No No No No Support for different file extensions Yes Yes Yes No No Database size support 4 GB 4 GB 140 TB 10 GB 10 GB XML storage Yes – stored as ntext Yes - stored as ntext Yes, stored as text Yes, native Yes, native Binary (BLOB) storage Yes – stored as image Yes - stored as image Yes Yes Yes FILESTREAM support No No No Yes No Code free, document safe, file format Yes Yes Yes No No Programmability SQL Server Compact 3.5 SP2 SQL Server Compact 4.0 SQLite, incl. ADO.NET Provider SQL Server Express 2014 SQL Server 2014 LocalDB Transact-SQL - Common Query Features Yes Yes No Yes Yes Procedural T-SQL - Select Case, If, features No No Limited Yes Yes Remote Data Access (RDA) Yes No (not supported) No No No ADO.NET Sync Framework Yes No (not supported) No Yes Yes LINQ to SQL Yes No (not supported) No Yes Yes ADO.NET Entity Framework 4.1 Yes (no Code First) Yes Yes Yes Yes ADO.NET Entity Framework 6 Yes (fully) Yes (fully) Yes (limited) Yes Yes Subscriber for merge replication Yes No No Yes No Simple transactions Yes Yes Yes Yes Yes Distributed transactions No No No Yes Yes Native XML, XQuery/XPath No No No Yes Yes Stored procedures, views, triggers No No Views and triggers Yes Yes Role-based security No No No Yes Yes Number of concurrent connections 256 (100) 256 Unlimited Unlimited Unlimited (but only local) There is also a table here that allows you to determine which Transact-SQL commands, features, and data types are supported by SQL Server Compact 3.5 (which are the same a 4.0 with very few exceptions), compared with SQL Server 2005 and 2008.

[cyberponictech-blog]

SQLite Toolbox 4.0 – Visual Guide of Features

After more than 300.000 downloads, version 4.0 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio  “14”, 2013, 2012 and 2010 is now available for download and can be install from the Tools/Extensions and Updates dialog in Visual Studio. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker. The focus for this release is support for SQLite databases, and this is also the focus of this blog post. Overview This release includes extensive support for SQLite databases, but please bear in mind, that this is v1 in terms of support for SQLite, so there is most likely both room for additional SQLite related features and improvements to the current. I plan to release a “bug fix” update before or around the VS “14” release. Getting used to SQLite has also been a steep learning curve for me! Root level SQLite features The about dialog has been enhanced with SQLite related information: The version of the SQLite ADO.NET provider included with the Toolbox is displayed, and the presence of the DbProvider indicates that SQLite is installed in GAC (not required by the Toolbox) “Add SQLite Connection” will allow you to connect to an existing SQLite database file, or create a new blank one. “Script SQL Server Database Schema and Data for SQLite” will create a SQLite compatible script (.sql file) from a SQL Server database, allowing you to migrate a database from SQL Server to SQLite. For how to use the generated script, see my blog post here. Database level SQLite features When you right click (or press Shift+F10) at the database level, you will get the options above. Let us go through them one by one (notice that all these features are also available for SQL Server Compact database files!) “Open SQL editor” – will open a SQL editor, where you can execute ad hoc SQL statements, and this editor is also used for any scripts created at the table level 8see below). The editor has a toolbar with various buttons: Open: Open a saved script (.sql) file Save As: Save the text in the editor as a SQL file Execute: Run the commands in the editor, and display results below – results can be displayed as either text or in a grid (grid is slower) – set via Options in the Toolbox, Estimated plan: Will run EXPLAIN QUERY PLAN for the statements Search: Search for text in the editor window The editor status bar displays: Query duration, number of rows returned, and SQLite engine version in use. “Build Table” gives you a UI to generate CREATE TABLE statements: “Script Database” will generate various .sql files that you can run using sqlite3.exe. “Create Database Graph” will generate a DGML interactive graph of your tables and their relations and columns: “Create Database Documentation” will generate a html file with documentation of all tables in the database: “Add sqlite-net model.cs to current Project” will code generate a model.cs file with classes for each table in the database, for use with the sqlite-net Nuget package. I will blog in detail about this later, think of it a basic productivity “scaffolding” in this release. “Database information” generates a script with basic database information in the SQL editor. ”Copy database file” will allow you to paste the file from the file system into your project, for example if you want to include a database file as content with your app. ”Remove connection” will remove the connection from the Toolbox (will not affect the underlying file).   Table level SQLite features (Notice that all these features are also available for SQL Server Compact database files!) "Edit Top x Rows” will open the table in a grid, and allow you to edit and add data to the table, provided it has a primary key. In addition to standard Navigation, Add, Delete and Save buttons, the bottom toolbar also contains a Quick Search and free text SQL feature. You can change the limit of rows via Options. “View Data as Report” will open a Microsoft Report Viewer with the table data. In addition to view and print the data, you can also export as PDF, Excel and Word: To use this feature, you may have to install the Report Viewer, which you can download from here. “Script as …” will generate a DML (data manipulation language) and DDL (data definition language) script in the SQL editor for the selected table. In addition, Script as Data (INSERTs) will script all data in the table as INSERT statements in the SQL editor. “Import Data from CSV” will import a CSV file, that has heading that matches the column names in the current table, and generate INSERT statements. ”Rename” will (unsurprisingly) rename the current table. Other fixes and improvements Support for VS "14" Improved saving of connections with "complex" passwords Improved handling of missing MS ReportViewer dll files

[cyberponictech-blog]

Getting and building the Entity Framework 7 alpha bits – step by step

The Entity Framework version 7 bits are slowly coming together, for a demo of some features, look at the presentation by Rowan Miller from the Entity Framework Team at the recent dotNetConf. This blog post will show you how to get the source code and build it on your own machine, in order to better understand EF 7, and maybe give it a early run. Keep in mind that a number of features are still broken/not implemented, keep an eye on the list: https://github.com/aspnet/EntityFramework/wiki/Getting-Started-with-Nightly-Builds There are instructions on Getting and Building the Code available on the EF7 Wiki: https://github.com/aspnet/EntityFramework/wiki/Getting-and-Building-the-Code But I have stumbled upon a few issues, that might also hit you. 1: Get the code You can either do this as clone of the repository as stated in the link above, you you can simply download a .zip file via the Download .zip button on this page. If you download a .zip, DO use a tool like 7Zip to unpack the file to a folder, do NOT use the built-in Windows zip extractor, in order to avoid files being marked as “Blocked”. Either way, you will now have a folder called “EntityFramework” on your system, with contents like this: 2: Initialize This will download all the required NuGet packages that EF7 depends on, and reference them from the projects. Before you do this, launch Visual Studio 2013 and verify that: You are running VS 2013 Update 2 (check Help, About): In Package Manager Settings, check that the official NuGet feed is configured and enabled (the AspNetVNext feed is added by the build process) Now launch a VS 2013 Developer Command Prompt as Administrator: Now navigate to the “EntityFramework” folder and run: build initialize If the build initialize process succeeds, you will see this message: Build succeeded. If the message does not appear, double check the VS 2013 required settings. 3: Build and run tests The next step will build the EntityFramework projects, and run all the tests in the solution. Before today, running unit tests were not possible on non-US systems, but I and MrJingle have had a few pull requests accepted to enable this (this, this and this). Let me know if you encounter any related issues, and I will be happy to submit a pull request to get it fixed. Again, from a VS 2013 Administrator command prompt, run: build If the build process succeeds, you will see this message: 4: Work in Visual Studio You can of course also open the solution in Visual Studio and build there. In order to run tests in Visual Studio, I found that the built-in Test Window did not detect any tests on my PC, but TestDriven.NET worked well. You can now add some unit tests of your own in order to give EF7 a run! Hope you manage to build, and good luck exploring the EF7 source.

[cyberponictech-blog]

SQL Server Compact Toolbox 3.7.3 – Visual Guide of new features

After nearly 290.000 downloads, version 3.7.3 of my SQL Server Compact Toolbox extension for Visual Studio  “14”, 2013, 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker. New features Export SQL Server database to SQLite script (beta) This feature enables you to create a script of all tables and data in an existing SQL Server database, that can be run using the SQLite3.exe tool as described in my blog post here, allowing you to quickly migrate to SQLite, for example for use in Windows Store/Phone Apps. “Set Password” option added to Maintenance menu This feature enables you to set or change the password of an existing database. Enable entry of multiline text in DataGridView This feature allows you to enter multi line text in the data edit grid, by pressing Shift+Enter to get to a new line. Support for VS “14” CTP Support for VS “14” was actually already enabled, but there was a minor issue with registration of the simple DDEX providers, that has now been fixed. UPDATE: Microsoft has blocked add-ins that claim to support VS 14 from VS 2013, so in the meantime you can download a build for VS “14” from here: https://sqlcetoolbox.codeplex.com/releases/view/123666   Bug Fixes Result Grid column headers were displayed without the first _ (underscore) character if they had one Download Count now fetched async, so the About dialog opens faster. Add Column - defaults to Allow Null "Migrate" and "Export" features broken for large databases with multiple script files

[cyberponictech-blog]

Entity Framework 6 and SQL Server Compact (9) –SqlCeFunctions and DbFunctions

One of the major improvements to the SQL Server Compact Entity Framework provider in version 6 is the addition of the SqlCeFunctions class, and enhanced support for the so-called “canonical” database functions (DbFunctions/EntityFunctions). Just to repeat, the SQL Server Compact providers are delivered in the EntityFramework.SqlServerCompact (for 4.0) and EntityFramework.SqlServerCompact.Legacy (for 3.5 SP2) NuGet packages. The DbFunctions (previously named EntityFunctions) in the System.Data.Entity namespace define a set of (CLR) methods that expose conceptual model canonical functions in LINQ to Entities queries. Before EF6.1, the SQL Server Compact provider only supported the functions defined for Entity Framework 1, not it supports all the functions listed here, except the following: Date/Time functions with micro and nanosecond precision (as only datetime exists as a data type in SQL Server Compact), StDev, StDevP, Var, VarP, Reverse, CurrentUtcDateTime, CurrentDateTimeOffset, GetTotalOffsetMinutes. This means you can now have the SQL Compact engine excute LINQ to Entities expressions like String.Contains, String.EndsWith, String.Left etc. The System.Data.Entity.SqlServerCompact.SqlCeFunctions class allows you to call database specific functions directly in LINQ to Entities queries, and the following functions have been implemented (for documentation of these, see the equivalent functions for SQL Server listed here): String functions CharIndex NChar PatIndex Replicate Space StringConvert Stuff Unicode Math functions Acos Asin Atan Atan2 Cos Cot Degrees Exp Log Log10 Pi Radians Rand Sign Sin SquareRoot Tan Date functions DateAdd DateDiff DateName DatePart GetDate, Other DataLength So you can compose LINQ to Entities queries like:var result = db.Album.Where(a => SqlCeFunctions.DataLength(a.Title) > 20).ToList(); And the resulting SQL will look like this: SELECT     . AS ,     . AS ,     . AS     FROM AS     WHERE (DATALENGTH(.)) > 20

[cyberponictech-blog]

Entity Framework 6 & SQL Server Compact (8) –Logging SQL statements

I previously blogged about how to enable logging of INSERT/UPDATE/DELETE statements with SQL Server Compact and Entity Framework 4. Keep in mind that there is no “SQL Profiler” equivalent for SQL Server Compact, which makes this feature a vital tool for debugging, understanding and improving queries and CUD operations. In Entity Framework version 6.1, this has been made much simpler, thanks to the implementation of the new System.Data.Entity.Infrastructure.Interception.DatabaseLogger class. In version 6.0, you could enable logging in code by using db.Database.Log = Console.Write; In version 6.1, it is now possible to enable logging by adding entries to your app.config file, making it possible to add logging to a deployed application. Enabling logging is as simple as adding the following section to your app.config/web.config file in the entityFramework section. The first parameter is the name of the file to log to (if this is not specified, logging will be sent to Console.Out). The second parameter specifies that the file should be appended to rather than being overwritten (default). I am sure you will find this new feature useful.

[cyberponictech-blog]

Entity Framework ”reboot” – EF7 – Get a sneak peek via TechEd US live stream

The next version of Entity Framework has just been named “EF7” (code name EF EveryWhere). Before the presentation, which will be streamed live on Channel 9 during TechEd US on Wednesday May 14 at 1:30 PM CDT (Houston, Texas), allow me to summarize what we know so far about the next version of Entity Framework. The Entity Framework team has already published some thoughts about the new Framework here, and based on this and other sources, we can summarize the following (some or maybe most of which is me guessing, of course): - A completely new codebase, will not be based on the 1 million+ line codebase of EF6 - Will be open source, and accept pull requests and other community feedback - Will support Windows Phone, Windows Store, Windows Desktop/Server/Cloud, and also support Mono/Xamarin platforms - Will be based on a provider model, so SQL and NoSQL data sources can “plug in”. Will initially support SQLite on Phone and Store apps. Will also support SQL Server and Azure Table Storage - Will only include a productive subset of the current, huge API – Code First Mappings, DbContext, POCO classes, and less mapping schemes. So current investments in DbContext, Code First/Second and POCO will be forward reusable. - Will not contain ObjectContext, Entity SQL, EntityConnection, EntityCommand etc. Phew. Sounds ambitious and great to me. Can’t wait to see some code and start playing! UPDATE: The source code is now available on Github as part of ASP.NET vNext. And Rowan Miller, Program Manager on the EF team speaks about and demonstrates EF7 in this TechEd session available online.

[cyberponictech-blog]

“Shop Talk” with Eric Sink, Zumero for SQL Server founder

In my last blog post I introduced you to Zumero, an Enterprise solution, that allow you to replicate and sync SQL Server data across all your team's mobile devices. I had the pleasure of meeting up with Zumero founder, Eric Sink, @eric_sink at the recent SQL Saturday event in Copenhagen. We agreed to do a short interview by mail, so I could share my Zumero questions with my blog readers. So without further ado, let’s get started. I know you have been involved with other software products (SourceGear). What is the story behind Zumero? We think of Zumero as a new start-up that is incubating inside SourceGear, so there is much shared history. We started SourceGear in 1997, initially as a contracting shop building custom software for mobile devices.  We did a lot of work with Blackberry devices and some very early Motorola smart phones. Then we got into version control tools, which has been a great business for us over the last 15 years.  We are still serving a large customer base with on-going active development of SourceGear Vault. But in the last few years, the world of mobile has been drawing us back in.  :-) And from a certain point of view, our work on version control means that we have been doing "sync" for 15 years. And SourceGear Vault has always been built on SQL Server. So that's how we got the three main ingredients of Zumero:  SQL Server, mobile, and sync. Some of the features that Merge Replication provide are: - Minimal client code, secure, compressed transfer over https, server side filtering, read-only tables, conflict handling. How does Zumero stack up? I /think/ we stack up pretty well, although we still have many ideas for how to make our product better. "minimal client code"  -- The Zumero Client SDK tries to make everything easy.  Just call the Zumero sync function and Zumero will synchronize changes in both directions. "secure" -- Zumero is designed to work over SSL/https.  The protocol includes authentication.  And on the client, Zumero is compatible with bulk encryption libraries such as SQLCipher. "compressed transfer over https" -- All packages exchanged between Zumero client and server are compressed.  In fact, the packages from server to client are often "deltified" to make them very small indeed. "server side filtering" -- Zumero does support the ability to exclude tables, or columns from tables, or to filter rows on a custom WHERE clause. "read only tables" -- This can be accomplished with Zumero's authentication and permissions. "conflict handling" -- Zumero handle conflict resolution on the server.  The default rules seem to be appropriate for most cases, but those rules can be customized to do whatever is needed for a given situation. In addition, Zumero adds a few things that SQL CE merge replication does not, most notably, support for iOS and Android devices. OTOH, Zumero is built on SQLite, which does not resemble SQL Server as closely as SQL CE does.  We are constantly working to provide Zumero customers with a smooth experience by improving features, tooling and documentation.  My blog series on the differences between SQL Server and SQLite is a part of those efforts: Rob Tiffany has demonstrated scaling Merge Replication. How does Zumero scale? This is one area where Zumero's youth is evident, and I shall not pretend.  There is much more experiential knowledge about scaling with merge replication.  Rob Tiffany's "cheat sheet" is a gold mine of information: And some of his advice would be applicable to Zumero as well. However, we are quite happy with the scalability results we have achieved so far and we continue to push further. (a)  We do a lot of "crowd testing", using lots of clients to abuse the Zumero server so we can tune it for reliability and performance. (b) We are currently working with some customers who are integrating Zumero into an environment where merge replication is already in use (as a scaling solution for the SQL Server backend, not as a mobile sync solution).  We have some additional testing to do here, but we hope to able to publish some guidelines about the compatibility of Zumero with this kind of situation. (c) Finally, we have tested and verified another way of scaling out, by having multiple Zumero servers talking to a single instance of SQL Server.  This can make a significant difference and is not difficult to set up behind a round robin DNS. You are using Triggers and tracking tables on SQL Server, and not the built-in Change Tracking. Why? The real reason is that Zumero's core sync code existed before we made the decision to make SQL Server the primary focus of the product. That said, the documentation for Change Tracking raises questions for us.  We wonder if it would require changes to our sync algorithm.  We wonder if our support for tracking schema changes would still work.  We wonder about the apparent dependence on snapshot isolation. We may explore this further in the future, but right now, we've got a solution which is robust, so we don't feel much pressure to change it. Do you provide a Windows Mobile (.NET Compact Framework) client library? Currently, no, but we are planning to do so.  The implementation work for Windows Mobile 6 support is done, but it has not yet been released as part of the product.  If a Zumero customer needs this, we would be ready to discuss and coordinate making it available. What do you recommend for use as data access API towards SQLite on Windows platforms? Right now, there is no API we can recommend without caveats.  Or rather, there are several good choices, but no clear winner. For desktop Windows only, the core SQLite team products System.Data.SQLite, an ADO.NET provider which is fully compatible with EF6.  If you're not using mobile, this is almost certainly the best choice. Frank Krueger's sqlite-net wrapper is quite popular, and for some very good reasons.  But last I knew, none of the various PCL forks have been folded back in, and that has been an issue for some.  Nonetheless, this is the wrapper we encounter most often. WinRT without System.Data is a story with a big hole in it.  We remain hopeful that Microsoft has some more good stuff in the pipeline.   Looking beyond Windows to include iOS and Android as well, things can get complicated.  Both of these other platforms include a version of SQLite as part of the mobile OS.  Many apps use those.  Some bundle their own.  Some replace SQLite with SQLCipher.  Things can get tricky.  In addressing this set of problems with our customers, we've built some stuff that we are planning to make available to the community as open source (to be announced on my blog, soon, I hope).

[cyberponictech-blog]

An alternative to Merge Replication with SQL Server and SQL Server Compact – Zumero for SQL Server

While looking for a migration path for a customer currently using Merge Replication with SQL Server and Windows Mobile, I came across the recently released solution from Zumero, Zumero for SQL Server. As mentioned in my previous blog post, Merge Replication between SQL Server Compact and SQL Server 2014 is no longer possible, and in addition, SQL Server Compact 3.5 SP2 only supports a limited number of client platforms (Windows Mobile/CE and Windows desktop). Microsoft is promoting Azure Mobile Services with Offline support, but for obvious reasons, this solution does not work for on premise databases. Zumero for SQL Server enables you to synchronize any mobile device with tables on a central SQL Server, using essentially the same components that we know from Merge Replication: 1: Configuration of tables to be synchronized, and added metadata/tracking to those. Before: Using SQL Server Management Studio to create a Merge Publication with articles (tables) Now: Using ZSS Manager to create a DBFile with Tables 2: An IIS based agent, that exposes a http(s) endpoint for performing the sync process. Before: Configure Web Synchronization Wizard Now: ZSS Server 3: Client library for performing sync. Before: System.Data.SqlServerCe.SqlCeReplication class, part of the SQL Server Compact ADO.NET provider Now: Zumero Client SDK and SQLite.using Zumero; ZumeroClient.Sync( "/path/to/sqlite.db", // full path to local db file null, "http://MY_ZUMERO_SERVER:8080", "test", // remote DBFile name null, null, null); To get started testing out Zumero, simply follow the instructions here: http://zumero.com/get-started/ or start by watching the short, introductory videos here: http://zumero.com/howto/ Notice that Zumero works with any edition of SQL Server 2008 R2 Express or higher/later. Zumero Server is not free, but works with the free SQL Server Express for small scale solutions. On the client side, the following impressive list client Operating Systems are supported: Android (native and PhoneGap) iOS (native and PhoneGap) Xamarin Windows, WinRT and Windows Phone 8 Mac OS X Linux (Ubuntu 12.04+, Fedora 17+) In my next blog post, I will be doing an interview:  “Hard Talk” with Eric Sink, Zumero founder. Disclaimer: I am simply a Zumero user, and not affiliated in any way.

[cyberponictech-blog]

Merge Replication with SQL Server Compact 3.5 SP2 and SQL Server 2014 and 2012

Time for a SQL Server 2014 related blog post to celebrate the release of this product. (And to celebrate me entering my 6th year a s Microsoft MVP). SQL Server 2012 SQL Server Compact based Merge Replication is "supported" by SQL Server 2012, but there are a few "gotchas" that you need to be aware of before implementing this. This blog post covers these in detail. As you may know, there are 3 components involved in a replication setup: Client, Web Server and Database Server. For each of these components, certain requirements must be met for the solution to work. And each component does not “just work” out of the box. Client (Windows desktop/server/Mobile) The client, which can be a Windows desktop or Windows Mobile device, must run a recent build of the SQL Server Compact runtime files. The file version/ServerVersion of the runtime must be 3.5.8088 or later. You can find download locations for the installers of the recent runtime here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html Web Server (IIS) The SQL Server Compact agent (sqlcesa35.dll) must be version 3.5.8088 or later. You can find download locations for the installers of the most recent Server Agent here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html In addition if you have a separate web and database server, you must install the SQL Server 2012 Database Engine component (and most likely you want to disable the SQL Server service after install) in order to get the required SQL Server COM files installed.  You must also install SP1 and the latest CU, as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/ Database Server (SQL Server 2012) You must install Service Pack 1 and the latest CU (Cumulative Update), as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/ SQL Server 2014 SQL Server 2014 does NOT support merge replication with SQL Server Compact: http://msdn.microsoft.com/en-us/library/bb500342(v=sql.120).aspx – end of story. But I am working on a blog post about an alternative solution that “just works” and enables you to Synchronize from any device, including Windows desktop/server, WinRT, Windows Mobile, Windows Phone, Android, iOS, Mac OS X and Linux to SQL Server 2008 R2, 2012 and 2014 (even SQL Server Express). Stay tuned, and follow me on Twitter @ErikEJ and subscribe to my blog RSS feed.

[cyberponictech-blog]

Entity Framework 6 & SQL Server Compact (7) –New features and fixes in version 6.1

Entity Framework 6.1 is now available on NuGet, and this blog post by Rowan Miller, Program Manager for the Entity Framework team, covers the major new features in this release. Most of the new features are also applicable to SQL Server Compact users, for example Code First Model from Database in the EDM Wizard, which is also available with SQL Server Compact if the latest SQL Server Compact Toolbox version is  installed. Rather than repeating the blog post from Rowan, this blog post will look at some of the minor features in EF 6.1 affecting SQL Server Compact. (A few of which I have had Pull Requests accepted) #824 Designer: DDL Generation Template for SQL CE should separate each statement with GO This fix allows you to run the script created by the “Generate Database from Model” EF Tools feature in the latest version of the Toolbox, as described here. #898 Reverse Engineer Code First: Using database name as entity container name causes issues when database name has invalid chars (bad for SQL Compact) This fix effectively makes “Code First from Database” work with SQL Server Compact, as it was previously broken. #1322 SqlCePropertyMaxLengthConvention should be updated to reflect changes in the standard PropertyMaxLengthConvention This fix makes code more portable between database engines #1852 Migration for EF 6.0 wrong migration script for altering property (required->non required) for SQL CE 4 This fix ensures that NULL or NOT NULL is always specified in migration SQL when changing between and not required #1863 Add DbProvider registration to SQL Server Compact NuGet package This fix enables private deployment of SQL Server Compact, both for use in desktop, web and unit test scenarios (and makes the workaround I describe here obsolete) #1878 Add SQL Server Compact 3.5 provider + NuGet package This feature enables you to use SQL Server Compact 3.5 with Entity Framework 6.x, via the EntityFramework.SqlServerCompact.Legacy NuGet package, and includes all the same fixes and features as the SQL Compact 4.0 provider. I blogged about the new provider earlier. #1962 SQL Server Compact data provider for EF does not support some Entity SQL canonical functions It turned out, that the current SQL Server Compact data provider only supported the “canonical” functions defined for EF 1.0  – this fix adds support for most applicable functions from EF 4. The following functions are not supported due the SQL Server Compact having a limited number of data types and built-in functions:  StDev, StDevP, Var, VarP, Reverse, CurrentUtcDateTime, CurrentDateTimeOffset, GetTotalOffsetMinutes, CreateDateTimeOffset, CreateTime, AddMicroseconds, AddNanoseconds, DiffMicroseconds, DiffNanoseconds

[cyberponictech-blog]

Entity Framework 6 & SQL Server Compact 4.0 (4) - Restoring full EF Tools support in Visual Studio 2013

As described in my blog post here, Entity Framework Tools support for SQL Server Compact 4.0 in Visual Studio 2013 is broken, because SQL Server Compact is no longer supported by Server Explorer in VS 2013. The latest release of my SQL Server Compact Toolbox, version 3.7.1, currently available in beta release from here, restores the Entity Framework Tools support for SQL Server Compact 4.0 by installing a simple DDEX provider. Developing DDEX providers is a bit of a dark art, which has put me off even thinking about it, but a recent discussion on CodePlex drew my attention to the DDEX sample provider included with the Entity Framework source code. In particular the fact that the Entity Framework Tools does not get any information from the DDEX provider apart from the connection information, so all Tables, Columns etc. are enumerated via code in the Entity Framework provider, not the DDEX provider. Quote from the included Word document: “Thus, the information shown in the wizard GUI comes directly from the EF provider. The wizard lets users select which tables, views and stored procedures to include in the model”. So my “simple” provider enables you to use the Entity Framework Tools, for example “Generate Model from Database”, “Generate Database from Model”, “Update Model from Database” and also the EF Power Tools Reverse engineer feature. (Soon to be included directly in the EF Tools). Once the DDEX Provider has been installed, (which consists of placing the provider DLL on disk, and add a number of registry entries), you can add a connection to any SQL Server Compact 4.0 database from the Toolbox, and it will be added to Server Explorer: (If you do not see the label at the bottom, but potentially a warning instead, one of the requirements for using the provider have not been fulfilled) The requirements for the provider are: - Visual Studio 2013 Pro or higher (it is not possible to add 3rd party DDEX providers to any Express edition, please correct me if I am wrong) - The Simple DDEX provider must be registered (may require a VS restart after launching with version 3.7.1 the first time), you can verify via About dialog in Toolbox. - The 4.0 DbProvider must be properly registered, use the About dialog in the Toolbox to confirm: Finally, you can of course confirm the presence of the provider in the Server Explorer Choose Data Source dialog: To connect via Server Explorer, select the “SQL Server Compact 4.0 (Simple by ErikEJ)” data source, click continue, and all you normally would need to enter is the path to the database file: Notice that this UI does not let you create a new database file, but you can do that from the “Add Connection…” dialog in the Toolbox. With that in place, you have access to the exact same Entity Framework Tools features that you had in Visual Studio 2012. Happy coding (and please let me know if your encounter any issue with this feature)

[cyberponictech-blog]

SQL Server Compact Toolbox 3.7.2–Visual Guide of new features

After more than 260.000 downloads, version 3.7.2 of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker. The focus for this release is a number of features that help improve the experience when using the new support for SQL Server Compact 3.5 (supplied by yours truly) in Entity Framework 6.1 and the related Entity Framework 6.1 Tools. Run “Generate from Model” script without errors When using the “Generate Database from Model” option in the Entity Data Model Tools, the tool generates a script to DROP and CREATE the generated objects in the database. If you have tried to run the script until now in the SQL Server Compact Toolbox, the script would fail. A new option “Ignore DDL errors” in the Toolbox SQL Editor will allow you to run the script without any errors: DDEX install requires VS restart The simple DDEX providers supplied by the Toolbox for VS 2012 and VS 2013 are installed when the Toolbox is activated, and therefore require VS to be restarted to be used. A notification is now available to notify you of this. You can read more about the DDEX providers in my previous blog post here and here. (Also, notice the new, “modern” icons in the toolbar of the Toolbox.) Preserve SQL Server date types A new option is now available to control scripting of the SQL Server date, datetime2 and datetimeoffset data types. The default behaviour is to always convert these column types to datetime (SQL Server Compact does not have these datatypes). But this can cause data loss (as datetime has limited precision) or overflow errors, as datetime only supports dates from 1753 to 9999, unlike date, datetime2 and datetimeoffset, that all support dates from 0001 to 9999. Improved About dialog The About dialog has been improved for readability, and contains a counter for the current number of Toolbox downloads: Bug fixes EDMX generation feature failed if only VS 2012 and not VS 2013 was installed. "Refresh" of tables, including refresh after schema changes, was broken Scripting API improvements - improved SQLCE runtime check - datetimeoffset columns scripted as datetime by default now, not nvarchar() - sql_variant and hierarchyid handling improved, to avoid getting wrong ordinal for identity and timestamp - sqlite indexes are now always given unique names The scripting API improvements are also included in the latest version of my SQL Compact command line export tool and Scripting API, available here. Feedback As usual, please provide feedback if you have any feature requests, ideas or encounter any issues (or even bugs!!) by using the CodePlex Issue tracker.

[cyberponictech-blog]

Entity Framework 6 & SQL Server Compact (6)–Entity Framework Reverse POCO Code First Generator

The Entity Framework Reverse POCO Code First Generator is a customizable T4 template, that reverse engineers an existing database and generates Entity Framework Code First POCO classes, Configuration mappings and DbContext It is available directly in Visual Studio via the Tools, Extensions and Updates menu item. It can be used with VS 2010 or later, and the latest version supports Entity Framework 6 and both SQL Server and SQL Server Compact (both 3.5 and 4.0). This T4 template is similar to the Entity Framework Power Tools “Reverse Engineer Code First” feature, but much more versatile and flexible, and constantly updated by the developer. You can see how this project compares with the Power Tools feature here. To get started using the template with SQL Server Compact, follow these simple steps: Install the relevant Entity Framework NuGet package: EntityFramework.SqlServerCompact.PrivateConfig (EF 6.0.2 with SQL CE 4.0) EntityFramework.SqlServerCompact (EF 6.1 when released with SQL CE 4.0) EntityFramework.SqlServerCompact.Legacy (EF 6.1 when released with SQL CE 3.5 – currently available as beta) Add a connection string to your app.config/web.config: In Visual Studio, right click project and select "add - new item". Select Online, and search for "reverse poco".  Select the "EntityFramework Reverse POCO Code First Generator" template Give the file a name, such as Database.tt and click Add. Edit the Database.tt file and specify the connection string name as "MyDbContext" which matches your name in app.config. Save the Database.tt file, which will now generate the Database.cs file. There are many options you can use to customise the generated code. All of these settings are in the Database.tt files with comments attached.

[cyberponictech-blog]

SQL Server Compact Toolbox 3.7.1–Visual Guide of new features

After 250.000 (!) downloads, version 3.7.1 of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download (and available via Tools/Extensions in Visual Studio). This blog post is a visual guide to the few new features included in this minor release, which all centre around Server Explorer (DDEX) , including support for Visual Studio 2012 and 2013.

  Simple DDEX providers for Visual Studio 2012 and 2013

I already blogged about this feature here, it has been extended to also support SQL Compact 3.5. Follow these steps to use the providers with Entity Framework Tools: - Install this version of the toolbox and launch Visual Studio 2012/2013. - Open the Toolbox (this will silently run the “installation” of the DDEX provider if required). - Restart VS 2012/2013. - Add new Toolbox connection to a new or existing SQL Server Compact 4.0 or 3.5 database file. - Add EntityFramework.SqlServerCompact or EntityFramework.SqlServerCompact.Legacy NuGet package to project and build project. - You can now use this connection for Generate Model from Database, Generate Database from Model and Update Model from Database, and with the Power Tools. With this release, the DDEX support matrix looks like this:   SQL Server Compact 3.5 SQL Server Compact 4.0 Visual Studio 2010 Microsoft None Visual Studio 2010 SP1 + SQL CE 4.0 Tools Microsoft Microsoft Visual Studio 2012 ErikEJ Microsoft Visual Studio 2013 ErikEJ ErikEJ “Microsoft” means full featured, Microsoft supplied provider ”ErikEJ” means simple, basic support, enough to enable Entity Framework Tools, provided by this version of SQL Server Compact Toolbox. Currently, the 3.5 DDEX provider does not fully work with the Entity Framework Tools, due to a bug (my bug!) in the EntityFramework.SqlServerCompact.Legacy Entity Framework provider; I am working on getting it fixed.

Scan Solution for sdf files

This new menu item will scan the active Solution for any sdf files, and add those to the Toolbox:

Prefer DDEX

This new option allows you to always use the Toolbox supplied dialogs for connecting to a SQL Server Compact database file, which will allow you to connect to any file, and does not require an .sdf file type. (By choosing not to prefer!)

Bug fixes and enhancements

Add connection will now always use DDEX provider if available Handle apostrophe in file and folder names when saving connections Fixed error: The specified table does not exist. (Databases) Make it clearer that sqlmetal dependent features requires 3.5 to be installed Please report any issues you encounter and provide feature suggestions and requests here.

[cyberponictech-blog]

Entity Framework 6 and SQL Server Compact 4.0 – “Proper” private desktop deployment

About a year after I published the blog post Entity Framework 6 & SQL Server Compact 4 (2)–Easy Private Desktop Deployment it has occurred to me that the solution proposed in that blog post is far from perfect – my apologies! The suggestions for location of the database file etc. are still valid points, however. TL;DR – Use my new NuGet package for Private Desktop deployment with SQL Server Compact 4.0 and Entity Framework 6, and make sure that: - The binding redirect is set to: oldVersion="4.0.0.0-4.0.0.1" - Prefer32Bit project option is disabled The goal First, let’s clarify what the goal behind “private desktop deployment” with SQL Server Compact is: The goal is to be able to drop a folder of files on any PC and just run  a .NET application that includes a self-contained relational database and a sophisticated ORM over that database. This goal is achievable with SQL Server Compact 4.0 and Entity Framework 6, as long as the PC in question has .NET Framework 4.0 installed. The issues First, let us look at the issues with the approach in the above blog post: 1: This approach uses the Microsoft.SqlServer.Compact package, which explicitly uses the non-private assembly version of System.Data.SqlServerCe.dll (which is 4.0.0.0). And it assumes that your project targets AnyCPU. This means that if you have SQL Server Compact installed in GAC, then the version from there will be picked up. That will work fine, as long as you have the exact same build of SQL Server Compact in both GAC and your application folder. But this leads us to issue number 2! 2: The EntityFramework.SqlServerCompact package does not depend explicitly on a release version of the Microsoft.SqlServer.Compact NuGet package and will pick up build 8854, which is 4.0 SP1 CTP1, instead of build 8876, which is 4.0 SP1 proper. (I wish that the 8854 builds were no longer visible on NuGet) – I am working on a fix for this, which is planned for EF 6.1.2. So the chances of not having the same version in GAC and locally are relatively big, and this will cause an error to be thrown, preventing your app from working: Possible file version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality. Manual fix If you would like to ensure that your desktop application (console, Windows Forms or WPF) app can run anywhere use proper private deployment, follow these steps (in outline): 1: Install EntityFramework.SqlServerCompact 2: Copy files from Private folder in C:Program Files (x86) to the project folder (as described in my Private deployment with LINQ to SQL blog post) 3: Make project x86 only (or at least remove the “Prefer 32 bit” option if you include all unmanaged dll files  4: Disable post build event (via Project Properties/Build events tab) to prevent the 4.0.0.0 dll from being deployed 5: Modify app.config a) add assembly redirect, as the EntityFramework.SqlServerCompact.dll references version 4.0.0.0, and we want to use 4.0.0.1: b) With version 4.0.0.0 => 4.0.0.1 in DbProviderFactories section:   “Automatic” fix I have published a NuGet package EntityFramework.SqlServerCompact.PrivateDeployment  that performs most of the actions above, specifically 1, 2 (via a clever package that performs a build step), 3 (via an additional command in install.ps1), 4, and partly 5 (for some reason NuGet generates an incorrect bindingRedirect, so it must be fixed manually). Currently in pre-release, please provide feedback! This package includes both the EntityFramework.SqlServerCompact provider for EF 6.1.1 and the SQL Server Compact 4.0 files for private deployment. I intend to update the package with the upcoming version 6.1.2 release of Entity Framework.

[cyberponictech-blog]

“Database First” with SQLite in a Universal App using the SQLite Toolbox and sqlite-net

In this blog post I will describe how to use the reverse engineering feature of the SQLite Toolbox to generate code for use with the sqlite-net codebase in a Universal App. A Universal App is a special solution template, that allows you to shared code and resource assets between a Windows Phone 8.1 and Windows Store 8.1 Application, in order to minimize code duplication and increase code reuse, if your application targets both these platforms. My fellow MVP Nicolò Carandini has a couple of great blog posts on how to get started with SQLite in a Universal App. Since he posted this, the SQLite extension SDKs have been updated and are now directly available in the Extension Manager in Visual Studio 2013, so go and install these two downloads – go to Tools, Extensions and Updates, select Online and search for “sqlite”: Install these three extensions: - SQLite for Windows Phone 8.1 - SQLite for Windows Runtime (Windows 8.1) - SQL Server Compact/SQLite Toolbox The two other SQLite extensions are for version Windows/Windows Phone 8.0. Notice that in order to use the SQLite Toolbox, currently you must have SQL Server Compact 4.0 or 3.5 installed, this will not be required in the next release. Now restart Visual Studio. Follow the steps in Nicolo’s blog until you have the two SQLite-net files available in the .Shared “project”: Instead of typing the table classes by hand, the SQLite Toolbox can help you generate the required classes for you based on an existing SQLite database file. You could even then include the SQLite database file with your application, for example if any of the tables contain reference data. I describe how to handle reference data in my previous blog post here. Notice that the sqlite-net classes are not a full blown OR-M, but rather a simple SQL to class translator. It does not support for example change tracking, relationships (foreign keys), Unit of Work, multi column primary keys etc. Those features will be present and available for Universal apps in the new Entity Framework release coming beginning of 2015, EF7. Open the SQLite Toolbox from Server Explorer or the Tools menu, and connect to an existing SQLite database. For an overview of all SQLite features in the Toolbox, see my blog post here. Select the shared project in Solution Explorer, then right click the database and select “Add sqlite-net Model.cs to current project” The generated code (Model.cs) contains a SQLiteDb class with a method to create all the tables in the database (if you just want to use the existing database file as a template), and class definitions for each table in the database: public partial class Album { public Int64 AlbumId { get; set; } public String Title { get; set; } public Int64 ArtistId { get; set; } } As you can see, attributes like MaxLength, Index and NotNull help define the table. Hope you find this timesaving feature and the other SQLite features in the Toolbox helpful, and please provide any feedback here.