Everything SQL Server Compact

Review of “Windows Phone 7.5 Data Cookbook”

noreply@blogger.com (ErikEJ) — Tue, 24 Jan 2012 14:50:00 +0000

The “Windows Phone 7.5 Data Cookbook” is a collection of more that 30 so-called recipes for working with with data in Windows Phone 7.5 applications. It covers all data related tasks from UI data binding and MVVM to using WCF Data Services.

A recipe is a step-by-step walkthrough of a sample mini application, that covers the subject at hand. All the samples are available for download from the publishers web site for registered users.

The book covers the following topics:
- Data binding, including element binding, DataContext, data templates, converters.
- Isolated storage
- Using XML files
- Consuming OData, and performing CRUD against OData sources
- Using on-device databases, including Perst, SQLite and SQL Server Compact with LINQ to SQL
- Consuming REST feeds, and creating a REST service
- WCF Data Services
- MVVM

The hands-on approach is useful for any developer new to the Windows Phone platform, and can also be used a samples for seasoned developers in need of a quick solution to a problem.

You can also download a sample chapter of the book, Chapter 2 – Isolated Storage to get a feel for the format.

Windows Phone Local Database tip: Initializing the database

noreply@blogger.com (ErikEJ) — Mon, 23 Jan 2012 14:34:00 +0000

There are several options for initializing the database for Window Phone, depending on your approach. By “initializing” I mean preparing the database for first use, just after the application has ben installed.

Whether you create the DataContext by hand or use RAD “Database First” with the SQL Server Compact Toolbox or the ExportSqlCe command line utility is not relevant.

You can read more about Database First here: http://erikej.blogspot.com/2012/01/generating-linq-to-sql-datacontext-with.html and more about Code First here: http://msdn.microsoft.com/en-us/library/hh202876(v=VS.92).aspx

I will assume you have used one of my tools to create the DataContext classes.

Option 1: No database file included

In this case, there is no initial data in the local database, it will be populated either by getting data from the web, or by manual entry by the user. The database must be writable, and must therefore reside in Isolated Storage. The connection string look like this: "Data Source=isostore:/Chinook.sdf"

To create the database based on the definition in the DataContext classes, you can use the following code, for example for each database call, or during app start:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    if (!db.DatabaseExists())
        db.CreateDatabase();
}
OR:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    db.CreateIfNotExists();
}

Option 2: Database file included, read and write

With this option, you include a database (.sdf file) in your project as en embedded resource:

You database must reside in Isolated Storage to be writable, and it must be copied to Isolated Storage as required. The connection string looks like this: "Data Source=isostore:/Chinook.sdf"

To copy the database to Isolated Storage, you can use the following code, for example for each database call, or during app start:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    db.CreateIfNotExists();
}

Option 3: Database file included, read only

With this option, you use the database for reference data only, and it can reside in the program files folder as a read-only resource. You include the database (.sdf file) as Content in your project:

You do not need to copy the database anywhere, it is installed with the other content in your application. The connection string looks like this: "Data Source=appdata:/Chinook.sdf;File Mode=Read Only;"

To connect to the database, use the following code:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionStringReadOnly))
{
    db.LogDebug = true;
    // More data access here...
}

I hope this article gave you an overview over the various options for database initialization with Local Database on Windows Phone.

Generating a LINQ to SQL DataContext for VS Express for Windows Phone

noreply@blogger.com (ErikEJ) — Mon, 16 Jan 2012 11:05:00 +0000

As you may know, you can generate a Windows Phone DataContext based on an existing database for use with Windows Phone Mango “Local Database”, as described here and here. But this feature is only available if you own a paid Visual Studio edition (Pro or higher), as the Express editions do not support add-ins.

But users of Visual Studio 2010 Express for Windows Phone can now also take advantage of all the productivity benefits of the “Database First” approach to Local Database development on Windows Phone.

Provided you have a SQL Server Compact Database ready, designed according to the guidelines here, and possibly created by using a tool, you can follow this simple procedure to generate and add the DataContext to your Windows Phone solution.

Anyone using the Express tools will notice that SQLMetal (which is used behind the scenes to create the DataContext file) is not included. You can get it from the Microsoft SDK bundle http://www.microsoft.com/download/en/details.aspx?id=8279. You only need to choose the following options to get the required files.

Then go to the download page for my ExportSqlCe tool, and download the latest version of the Exportsqlce.exe command line utility.

You can now generate a DataContext file from the command line, as documented here.

In this example, I will generate a DataContext based on the Chinook sample database.

exportsqlce wpdc "Data Source=C:\projects\Chinook\Chinook.sdf" C:\temp\Chinook.cs

Notice that the format of the database must be SQL Server Compact 3.5.

In Visual Studio, open your Windows Mango project, and from Solution Explorer, select Add, Existing Item, and browse to the file the was just created.

Remember to add a reference to System.Data.Linq to your project.

You can now start using the generated DataContext in your project!

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    db.LogDebug = true;

    var artist = db.Artists.FirstOrDefault();
    artist.Name = Guid.NewGuid().ToString();

    db.SubmitChanges();
}

Happy coding…

SQL Server Compact Toolbox 2.6.1–Visual Guide of new features

noreply@blogger.com (ErikEJ) — Sun, 08 Jan 2012 16:00:00 +0000

After more than 85.000 downloads, version 2.6.1 of my SQL Server Compact Toolbox extension for Visual Studio 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

Edit Table Data

This feature, which allows you to edit the table data in a grid, and allows column sorting, image import/export and quick search, has been enhanced with the following new features:

The grid now only loads the first 200 rows, to better handle large tables. The number of rows initially loaded can be configured via the Options dialog:

In addition, you can use any SELECT statement as the basis for the grid, by entering this statement using the SQL button at the bottom of the grid:

Pressing this button will give you access to enter any SQL SELECT statement:

Windows Phone DataContext

The Windows Phone DataContext feature allows you generate a DataContext for use in Windows Phone projects, based on an existing database, saving you a large coding effort. This feature has been enhanced as follows:

You can now generate a DataContext in VB code, in addition to C# code:

The generated DataContext contains to new useful properties:

using (PostCodesContext db = new PostCodesContext(PostCodesContext.ConnectionStringReadOnly))
{
    if (System.Diagnostics.Debugger.IsAttached)
    {
        db.LogDebug = true;
    }
    _allItems = db.PostCode.ToList();
}

ConnectionStringReadOnly allows you to include a database for reference data, and using the appdata:// syntax on the connection string, this database can reside in your programs folder, no need to copy to Isolated Storage.

LogDebug allows you to log any SQL statement generated to the Visual Studio debug output window, as described in my blog post here. A big thanks to Nicolò Carandini, who insipred me by mail and this blog post to add this feature.

Explorer Tree

The Tables node now has a Refresh menu item, allowing you to refresh only objects in a single database:

In addition, when running ALTER, DROP and CREATE scripts, a refresh will be attempted.

Object Descriptions

You can now edit all the descriptions for a table in a single window, thanks to new contributor giddy:

Scripting entire database – table selection

When scripting an entire SQL Server or SQL Server Compact database, you now have the option to exclude tables from being scripted:

Other minor improvements

All Dialogs cleaned up for VS UI compliance ( Thanks to new contributor giddy )

Stopped handling unhandled exceptions, had undesired side effects

Warning when Primary Keys were missing when creating DataContext and EDMX

Using a "nicer" property grid

See this overview of the work items in this release.

CSV to SQL Server Compact scripting utility

noreply@blogger.com (ErikEJ) — Wed, 04 Jan 2012 09:35:00 +0000

My SSMS and Visual Studio add-ins for SQL Server Compact includes the option to import CSV files. But this GUI based feature does not work so well with larger CSV files, as the generated INSERT script can become very large. So I have now made available a command line utility that performs the same functionality, but works better with larger CSV files (and allows you to automate the process).

Please give the utility a try, and provide feedback here.

Windows Phone Local Database tip: Exploring INSERT performance–5 power tweaks

noreply@blogger.com (ErikEJ) — Thu, 22 Dec 2011 08:19:00 +0000

Josué Yeray Julián, (blog in Spanish here), has done some interesting tests on INSERT performance, both on the emulator (like I started doing in my previous blog post) and on two real devices, an HTC Mazaa and Nokia Lumia 800. (Sadly, I do not own a real device – yet!)

In summary, the following parameters can improve performance for doing INSERTs of 500.000 rows:

1: Set “Max Database Size” on your connection string to an expected size in advance (default is 32 MB, max is 512 MB on Windows Phone)

2: Increase “Max Buffer Size” to a value higher than the default of 384 KB. On the tested real devices the max of 4096 worked best.

3: Run the INSERT process in a background thread.

4: Remove any Version columns (if only INSERTSs matter, not UPDATEs)

5: Use InsertAllOnSubmit instead of InsertOnSubmit

These tweaks reduced the total processing time on the Nokia Lumia 800 from 29:20 to 16:04!

But - keep in mind that you can use Database first development with the SQL Server Compact Toolbox add-in and then include the database with your application package. You can also use the SqlCeBlukCopy API to insert 1.000.000 rows (twice as many as tested) in 6 seconds on the desktop!

You can read the Microsoft Translator versions of his 2 blog posts here and here.

Windows Phone Local Database tip: Batch INSERT performance

noreply@blogger.com (ErikEJ) — Sun, 11 Dec 2011 17:55:00 +0000

You could imagine a scenario, where one of the tables in your local database need to be periodically updated. For Windows Phone Local Database, there is no support for Sync Framework or similar technologies, so you may have to simply add a bunch of rows to your table, based on data retrieved from a web service. This blog post explores how SQL Server Compact on the device does INSERTs, and how batch INSERTs can be tweaked for optimum performance.

Let’s start by using the DebugWriter class from my previous post, and examine what INSERT statements look like. For this test, I have included a copy of the Chinook database with the phone application as an embedded resource, so the test table already contains 275 rows. Then use this code to view INSERT statements.

private void button1_Click(object sender, RoutedEventArgs e)
{
    CleanDatabase();
    using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
    {
        db.LogDebug = true;

        var artist = new Artist();
        artist.Name = Guid.NewGuid().ToString();

        db.Artist.InsertOnSubmit(artist);
        db.SubmitChanges();
    }
}

private static void CleanDatabase()
{
    using (var store = IsolatedStorageFile.GetUserStoreForApplication())
    {
        store.Remove();
    }

    using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
    {
        db.CreateIfNotExists();
    }
}

This results – surprisingly – in the following output:

-- CURSOR BASED INSERT [Artist]
-- [Name] <= [2807fb28-49d7-4a25-8350-e4e40245934a]

-- AUTOSYNC [ArtistId] <= [276]

This indicates that in order to perform INSERTs to the Local Database, the query processor is bypassed (no “INSERT” SQL statement is shown). Instead, the LINQ to SQL implementation on the device uses the SqlCeResultSet and SqlCeUpdateableRecord classes (or equivalent), that allows you to access the database tables without the overhead of the query processor, similar to what I use in the SqlCeBulkCopy library for performing fast INSERTs in SQL Server Compact databases on the desktop. So for INSERTs, the device implementation is already optimized in that respect.

Let’s then look at the speed of “many” INSERTs, in this case 10000. The following code implements this test:

CleanDatabase();

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    List<Artist> artistList = new List<Artist>();

    for (int i = 0; i < 10000; i++)
    {
        var artist = new Artist();
        artist.Name = Guid.NewGuid().ToString();

        artistList.Add(artist);
    }

    db.Artist.InsertAllOnSubmit(artistList);
    System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
    sw.Start();
    db.SubmitChanges();
    sw.Stop();
    System.Diagnostics.Debug.WriteLine(sw.ElapsedMilliseconds);            
}

On my emulator, this takes about 2500 ms for each run. So 2.5 seconds to insert 10.000 rows – no too bad. Let’s see if there are any knobs available in the connection string parameters that will allow us to tweak this. The connection string parameters available for Windows Phone Local Database are documented here.

Inspired by this article by Joao Paulo Figueira, we could try to increase the Max Buffer Size from it’s default value of 384 K to 1024 K.

This can be done in the connection string like this:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString + ";max buffer size=1024"))

And lo and behold, the same process of inserting 10000 rows now takes 1.6 seconds on my emulator, a 36% improvement! (Your mileage may vary, of course).

Note: Increasing above 1024 buffer size had no positive effect.

Hope you found this useful.

Speaking of Data on Windows Phone, I will be reviewing the Windows Phone 7.5 Data Cookbook soon, you can download a sample chapter on Isolated Storage from here.

Windows Phone Local Database tip: Viewing the SQL generated by LINQ to SQL

noreply@blogger.com (ErikEJ) — Mon, 28 Nov 2011 17:43:00 +0000

It may sometimes by useful to be able to see the SQL statements generated by an ORM, in order to understand better what is going on, and why things perform the way they do. With LINQ to SQL on desktop, you can assign any TextWriter object to the DataContext Log property, and this will allow you to log to a file or the console.

On Windows Phone, this gets a little bit complicated, as files must be stored in Isolated Storage, making them hard to get at, and there is no Console. This MSDN blog includes a TextWriter implementation, that writes to the console of a attached debugger, for example the Visual Studio Debug output Window. This class (which I have renamed to DebugWriter) will be part of the DataContext generated by the next SQL Server Compact Toolbox release. So now you can add the following while debugging your LINQ to SQL statements:

db.Log = new DebugWriter();

We can now try some various LINQ queries against our database to find out how they have been implemented in SQL (I am using the excellent Chinook sample database, available for download here):

db.Album.Where(al => al.AlbumId == 10).SingleOrDefault();

And the output in the Debug output window:

db.Album.FirstOrDefault();

Becomes:

SELECT TOP (1) [t0].[AlbumId], [t0].[Title], [t0].[ArtistId]
FROM [Album] AS [t0]

db.Album.Where(al => al.Title.StartsWith("For those%")).FirstOrDefault();

Becomes:

SELECT TOP (1) [t0].[AlbumId], [t0].[Title], [t0].[ArtistId]
FROM [Album] AS [t0]
WHERE [t0].[Title] LIKE @p0

db.Album.Skip(50).Take(25).ToList();

Becomes:

SELECT [t0].[AlbumId], [t0].[Title], [t0].[ArtistId]
FROM [Album] AS [t0]
ORDER BY [t0].[AlbumId] OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY

Notice that the last statement uses OFFSET and FETCH, a syntax only introduced in SQL Server Compact 4.0, but apparently retrofitted into the version 3.5 engine running on Windows Phone.

Happy SQL debugging!

SQL Compact 3rd party tools

noreply@blogger.com (ErikEJ) — Mon, 14 Nov 2011 15:53:00 +0000

This is an attempt at a comprehensive list of SQL Server Compact 3rd party tools, both commercial and non-commercial. Please let me know if I have missed anything, and I will add it.

Just to summarize, the current Microsoft supplied tools are the following:

Visual Studio 2010 (3.5 SP2)
Visual Studio 2010 SP1 + 4.0 Tools (3.5 SP2 + 4.0)
WebMatrix (4.0)

New or updated entries are noted with (new) or (updated)
”Last updated” indicates when the tool was last updated by it’s author.

Commercial tools

Name: Data Port Console
Link: http://primeworks-mobile.com/Products/DataPortConsole.html
SQL CE Versions: 2.0, 3.0, 3.5, 3.5, 4.0
Last updated: Current
Platforms: Windows CE, Win32
”Manage all your SQL Compact Edition databases from one central location with the new Data Port Console.”

Name: DesktopSqlCe
Link: http://primeworks-mobile.com/Products/DesktopSqlCe.html
SQL CE Versions: 2.0, 3.0, 3.5
Last updated: 2010
Platforms: Windows CE, Win32
”DesktopSqlCe is a desktop .NET Framework component to remotely access SQL Server Compact Edition, SQL Mobile and SQL CE 2.0 databases on mobile or embedded devices.”

Name: Data Port Wizard
Link: http://primeworks-mobile.com/Products/DataPortWizard.html
SQL CE Versions: 2.0, 3.0, 3.5, 4.0
Last updated: Current
Platforms: Windows CE, Win32
”Quickly and easily transfer databases from the desktop to the Mobile Device and back. Data Port Wizard will make full copies of your Microsoft Access or Microsoft SQL Server databases into SQL Server Compact Edition 3.5 and 3.0, and SQL CE 2.0 databases - and back.”

Name: Data Port Command
Link: http://primeworks-mobile.com/Products/DataPortCommand.html
SQL CE Versions: 2.0, 3.0, 3.5
Last updated: 2010
Platforms: Windows CE, Win32
”Quickly and easily transfer databases from the desktop to the Mobile Device and back. Data Port Command will make full copies of your Microsoft Access or Microsoft SQL Server databases into SQL Server Compact Edition databases - and back.”

Name: SQLCE Desktop Manager v2.00
Link: http://www.b3team.com/sqlcedesktopmanager.html
SQL CE Versions: 2.0, 3.0, 3.5
Last updated: 2007
Platforms: Windows CE, Win32
“SQLCE Desktop Manager v2.00 is available! This application will help you manage your SQL Server Compact Edition, Mobile Edition, SQL Server CE and SQLite 3 databases on PC or Windows CE/Mobile devices directly from desktop computer. With it you can create databases, design tables, edit data and run SQL queries.”

Name: SQLCE Database Viewer
Link: http://www.pocketpctoolkit.com/index.php
SQL CE Versions: 2.0, 3.0, 3.5
Last updated: 2007
Platforms: Windows CE, Win32
”The easiest way to view and edit your device's SQLCE databases, directly on your Desktop ! View and edit the tables, columns and data in your device's SQLCE 2.0 and SQL Server Mobile, and SQLCE Compact Edition v3.5 tables. Export their data structures (and data) into any ODBC data source. View and edit your device's registry entries & processes. Built-in file explorer - the easiest way to view, open and execute the files on your device.”

Name: SDF Viewer
Link: http://www.flyhoward.com/SDF_Viewer.aspx
SQL CE Versions: 3.0, 3.5, 4.0
Last updated: Current
Platforms: Win32
“The simple way to create, view, edit or export the data in your Windows Mobile or Pocket PC device's SQLCE database, from the convenience of your Desktop. Run SQL commands on your SQLCE databases, and view the results instantly.”

Name: Pocket DB
Link: http://www.cdsoftwaresolutions.com/PocketDB.aspx
SQL CE Versions: 3.5
Last updated: 2009
Platforms: Windows Mobile 6
“a database program designed for the new Windows Mobile 6.0 Pocket PCs, the Sprint HTC Touch in specific, and all WM6 devices with a 240x320 screen in general. The program uses the latest Microsoft SQL Server Compact 3.5 database engine. Pocket DB includes a desktop utility to convert your Access databases (ACCDB or MDB) to the SQL Compact SDF format.”

Name: RemoteSqlCe
Link: http://www.gui-innovations.com/html/remotesqlce.html
SQL CE Versions: 2.0, 3.0
Last updated: 2007
Platforms: Windows CE, Win32
”SqlServerCE is a great database for Pocket PC’s, but designing, maintaining and querying databases on the PPC, especially whilst developing, can be wearing. We developed this tool to help us when developing for SqlServerCE. With RemoteSQLCe you can completely manage your SqlServerCE database from the desktop. RemoteSQLCE works in the cradle, and over ethernet/bluetooth.”

Non-commercial (free) tools

My CodePlex Tools

Name: SQL Server Compact Toolbox (updated)
Link: http://sqlcetoolbox.codeplex.com/
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 29th June 2010
”Add-in for Visual Studio 2010 (for 3.5 and 4.0). Standalone app (for 4.0)
Adds several features to help your SQL Server Compact development efforts:
- Scripting of tables, including data.
- Script of entire schema, optionally with data, both of SQL Server Compact and SQL Server 2005 or later databases, including SQL Azure.
- Import to SQL Compact 3.5 from a server database or CSV file.
- Basic, free form query execution.
- Generation of DGML files for visualizing table relationships
- Database Diff Script
- Detect database file version
- Generate EDMX in any project type
- Generate Windows Phone DataContext
- Fix broken connection definitions”

Name: SqlCeScripting.dll (updated)
Link: http://exportsqlce.codeplex.com/
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 29th June 2010
”Scripting library for inclusion in your own application, enumerates SQL Compact objects and allows scripting of these.”

Name: SqlCeCmd
Link: http://sqlcecmd.codeplex.com/
SQL CE Versions: 3.5, 4.0
Last updated: 2010
Platforms: Win32, Win64
”Administer SQL Compact databases directly from the command line, without having Visual Studio or SQL Server Management Studio installed. Only requires NetFx 2.0 and SQL Compact installed. Command line options similar to sqlcmd. Runs scripts generated by ExportSqlCe.”

Name: ExportSqlCe
Link: http://exportsqlce.codeplex.com/
SQL CE Versions: 3.0, 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
”This command line utility helps you better manage your SQL Compact development efforts. If allows you to script schema and data from any SQL Compact database file to a .sql file, which can be used in any context. The utility is developed in C# with Visual Studio 2008. You can use the resulting script for documentation, programmatic scripting of SQL Compact database files, or migration of schema and data to SQL Server (Express) 2005 or later.

Name: Export2SqlCe
Link: http://exportsqlce.codeplex.com/
SQL Server Versions: 2005, 2008
Last updated: Current
Platforms: Win32
Added to list: 4th March 2010
”A command line utility to script an entire SQL Server 2005 or later database with or without data to a SQL Compact T-SQL script.”

Name: ExportSqlCe SSMS 2008 Addin
Link: http://exportsqlce.codeplex.com/
SQL CE Versions: 3.5
Last updated: 2011
Platforms: Win32
”Script database/table schema/data and also DML (SELECT, INSERT, UPDATE, DELETE) directly from the Object Explorer in SQL Server 2008 Management Studio (Express). In addition, Edit table data, Import data from as CSV file and Rename the table.”

Name: SqlCeBulkCopy
Link: http://sqlcebulkcopy.codeplex.com/
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: .NET library, .NET CF library
Added to list: 4th March 2010
”.NET Library for loading data fast (doing bulk inserts) into a SQL Server Compact database file. Attempts to mimic the SQLClient SqlBulkCopy API. Includes support for BULK INSERTing any IEnumerable”

Query and database management tools

Name: CompactView (new)
Link: http://sourceforge.net/p/compactview/home/Home/
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 14th November 2011
CompactView is a viewer for Microsoft® SQL Server® Compact Edition (SQLCE) database files (*.sdf). CompactView can open database files of versions 3.1, 3.5 and 4.0

Name: SQL Compact Query Analyzer
Link: http://sqlcequery.codeplex.com/
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 3rd July 2011
Features:
- Execute SQL Queries against a SQL Server Compact Edition database
- Table Data Editor to easily edit the contents of the database
- Supports SQLCE 3.0, 3.1, 3.5 and 4.0
- Execute multiple SQL queries (delimited by a semi colon ;)
- Display query result as XML
- Shrink and Compact Databases
- Generate Schema and Data Scripts
- Display database and schema information

Name: LINQPad
Link: http://www.linqpad.net/
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 14th December 2010
Linqpad allows you to query various data sources via LINQ, including SQL Server Compact 3.5 and 4.0. See this blog post.

Name: Database .NET
Link: http://fishcodelib.com/Database.htm
SQL CE Versions: 3.1, 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 20th September 2010
“Database .NET is a simple and intuitive database management tool for multiple databases. Making Local and Remote Database Administration Easier, With it you can Create databases, Design tables, Edit data, Export data, Generate scripts and run SQL queries.”

Name: Compact Query
Link: http://www.yohz.com/
SQL CE Versions: 3.0, 3.5
Last updated: 2010
Platforms: Win32
Added to list: 4th March 2010
”Compact Query is our FREE native code IDE for SQL Server Compact. Have you ever wanted to deploy a lightweight IDE for SQL Server Compact, to allow users to troubleshoot data integrity issues? Then Query Compact is the tool for you. It's magnitudes smaller than Management Studio, is a native code application (so it does not require the .Net framework to be installed)”

Name: SQL CE Browser
Link: http://ericwillis.com/notes/2009/12/sql-ce-browser-v-110/
SQL CE Versions: 3.5
Last updated: 2010
Platforms: Win32, Win64 (?)
Added to list: 4th March 2010
”Open regular and encrypted/passworded databases for viewing or editing. Write and work with any number of queries at once via tabs. SQL syntax highlighting and SQL syntax verification. Explore columns, keys, and indexes“

Name: Mini SQL Query
Link: http://minisqlquery.codeplex.com/
SQL CE Versions: 3.5
Last updated: Current
Platforms: Win32, Win64 (?)
Added to list: 4th March 2010
”Mini SQL Query is a minimalist SQL query tool for multiple databases (MSSQL, MSSQL CE, SQLite, OLEDB, MS Access/Excel files etc). The tool uses a generic schema engine taking advantage of the ADO.NET providers. Mini SQL Query is also easily extended with plugins.“

Name: SQL Server CE Query Tool
Link: http://cequery.codeplex.com
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64 (?)
”CEQuery is written in C# with the help of Visual Studio 2008 platform. Some of the features of this tool are described below:
User is able to create CE database. If the schema is provided, the tool is able to create the tables and columns. User is able to overview an existing CE database with its table and column structure in tree format. User can design a query by dragging and dropping tables and columns from the database tree. User can manipulate [Insert/Update/Delete] records in a selected table. User is able to generate scripts from a single table or of whole databases. [Both for SQL CE or SQL Server]. User is able to open multiple SQL Server CE files simultaneously. User is able to convert a SQL Server database to a SDF file with schema and data. User is able to change password of a SQL CE DB. The tool is now capable of handling the database with no password.“

Name: SeasonStar Database Management(SSDM)
Link: http://ssdm.codeplex.com/
SQL CE Versions: 3.5?, 4.0
Last updated: Current
Platforms: Win32
”With support for all major database formats, top-class export functions and advanced feaures like Linq Data Context availabe, SSDM is probably your best choice for DB management. It's even free software!”

Name: SQL CE Database Editor
Link: http://sqlcedatabaseeditor.codeplex.com/
SQL CE Versions: 3.5
Last updated: 2009
Platforms: Win32
“Small lightweight application to browse and edit Microsoft SQL CE server databases. Does not require SQL CE server to be installed and can run custom queries.”

Name: DataBoy
Link:http://databoy.codeplex.com/
SQL CE Versions: 3.0, 3.5
Last updated: 2008
Platforms: Win32
“Small footprint, portable and simple database query tool. Support SQL CE 3.1, SQL CE 3.5 and SQL Server.”

Name: SqlCeExplorer
Link:http://sqlceexplorer.codeplex.com/
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32
“A Simple GUI for SQL CE Server, using Microsoft .Net windows forms. This utility will help developers to create, and manage SQL CE database, without Visual Studio or SQL Server Management studio.”

Other tools and libraries

Name: SQLite2CE (new)
Link: http://sqlite2ce.codeplex.com/
SQL CE Versions: 3.5
Last updated: Current
Platforms: Win32, Win64
Added to list: 14th November, 2011
“A tool that converts a SQLite database into SQL CE while simultaneously creating the default data representation classes needed to easily incorporate the new database into your Windows Phone application. The tool migrates the Schema, Table Data, and Views ( but not triggers). The tool handles the differences in data types and does an appropriate migration that is configurable in the settings.xml.”

Name: SQLCE SCRIPTER (new)
Link: http://sqlmgmt.com/sql/SQLCE-Scripter
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 14th November, 2011
“Create SQL schema scripts for tables, indexes and referential relations. Create data scripts so that CE data can be moved to SQL Server or other data store. User can select specific tables only. User can select Script Schema, Script Data or both.”

Name: SQLCE Code Generator
Link: http://sqlcecodegen.codeplex.com/
SQL CE Versions: 3.5, 4.0
Last updated: Current
Platforms: Win32, Win64
Added to list: 27th March, 2011
“Contains a stand alone GUI application and a Visual Studio Custom Tool for automatically generating a .NET data access layer code with Unit tests for objects in a SQL Server Compact Edition database. Also generates a Windows Phone DataContext”

Name: OpenNETCF.ORM Framework
Link: http://orm.codeplex.com/
SQL CE Versions: 3.5
Last updated: Current
Platforms: WinCE, Win32, Win64
Added to list: 27th March, 2011
“The OpenNETCF.ORM library is a lightweight object-relational mapping framework designed for use with the .NET Compact Framework.The framework is extensible to allow any type of backing store. An initial implementation using SQL Server Compact is provided.”

Name: SQL Metal Open Source User Interface
Link: http://sqlmetalosui.codeplex.com/
SQL CE Versions: 3.5
Last updated: 2008
Platforms: Win32
“This project provides an user interface for SqlMetal.exe tool.
SQLMetalOSUI allows users to generate code and mapping files for their SQL Server Compact Edition databases. SQL Server Compact Edition 3.5 is fully compatible with LINQ to SQL but the designer does not. This project will be focused to reach this initial purpose.”

Name: MDB2SDF
Link: http://www.aroooy.com/mdb2sdf/
SQL CE Versions: 3.0
Last updated: 2006
Platforms: N/A
”A web page that allows you to upload an Microsoft Access MDB file and converts it to a SQL Compact 3.0 file”

SQL Server Compact 3.5 SP2 now supports Merge Replication with SQL Server 2012

noreply@blogger.com (ErikEJ) — Wed, 09 Nov 2011 10:56:00 +0000

A major update to SQL Server Compact 3.5 SP2 has just been released, version 3.5.8088.0, disguised as a “Cumulative Update Package”. Microsoft knowledgebase article 2628887 describes the update.

The update contains updated Server Tools, and updated desktop and device runtimes, all updated to support Merge Replication with the next version of SQL Server, SQL Server 2012.

For a complete list of Cumulative Updates released for SQL Server Compact 3.5 SP2, see my blog post here.

It is nice to see that the 3.5 SP2 product, with it’s full range of device support and synchronization technologies is kept alive and kicking.

NOTE: I blogged about this update earlier, but it was pulled back. Now it is finally available, and all downloads can be requested. (I have downloaded all the ENU ones, anyway)

SQL Server Compact Toolbox 2.5–Visual Guide of new features

noreply@blogger.com (ErikEJ) — Mon, 07 Nov 2011 17:40:00 +0000

After more than 66.000 downloads, version 2.5 of my SQL Server Compact Toolbox extension for Visual Studio 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

Properties of selected Database and Table displayed in Properties window

When you navigate the list of SQL Server Compact databases and database objects, the Toolbox now displays properties for a Database and a Table.

The Database properties are the following:

And the table properties are these:

DGML file (database graph) now also contains object descriptions

The database graph (DGML) file has been enhanced to display descriptions of Database, Table and Column objects, based on the documentation feature introduced in version 2.3:

Entity Data Model dialog now allows adding configuration for Private Desktop Deployment to app.config

When using Entity Framework with Private Deployment of SQL Server Compact, some entries in app.config are required (for Desktop application), as described here and here. This is required, as Entity Framework build in the DbProvider interfaces, which requires some configuration entries.

These settings can now be added when creating an EDM as shown below:

Ability to add 3.5 Connections from Toolbox

It is now possible to add 3.5 Database Connections to the Server Explorer and the Toolbox directly from the Toolbox, rather than having to go to Server Explorer, add the connection, and then Refresh the Toolbox. You can now do this without leaving the Toolbox, and the Toolbox will refresh “automagically”.

Improved VS UI Guidelines compliance

The Toolbars, SQL Editor Font, Dialogs (frame, background and buttons) have been overhauled to comply better with my recent discovery of “Visual Studio UI Guidelines”, available for download here. In addition, the Toolbox now follows the chosen Visual Studio Theme (to some extent, anyway).

This is the “new look” for the Toolbox Explorer and SQL Editor:

Other minor improvements and fixes

- Explorer Treeview: ArgumentException when getting connections
- WinPhone DataContext: Split files failed with empty database
- SQL editor: Check if .sqlplan is supported
- SQL editor: Save button was not working
- SQL editor: Results pane not always cleared
- SQL editor: Results as text improved formatting
- SQL editor: Text scrollbar was overlaid by splitter bar

As usual, the full source code for these new features is available on CodePlex for you to re-use or maybe improve!

SQL Server Compact 4.0 under ASP.NET Hosting– common issues

noreply@blogger.com (ErikEJ) — Thu, 27 Oct 2011 16:18:00 +0000

This blog post covers some of the issues that can be encountered when using SQL Server Compact under ASP.NET, in particular in a hosted environment, where the environment can be restricted in unexpected ways.

In order to be able to troubleshoot issues with ASP.NET hosting of web sites using SQL Server Compact 4.0, it can be useful to understand how Medium Trust works.

Medium Trust only works under .NET 4.0 (ASP.NET 4.0), as it depends on a configuration in the global web.config.

The global web.config is located at C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config or C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config, and contains the following setting, that was configured when .NET 4.0 was installed:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <location allowOverride="true">
    <system.web>
      <fullTrustAssemblies>
        <add
            assemblyName="System.Data.SqlServerCe"
            version="4.0.0.0"
            publicKey="0024000004800000940000000602000000240000525341310004000001000100272736ad6e5f9586bac2d531eabc3acc666c2f8ec879fa94f8f7b0327d2ff2ed523448f83c3d5c5dd2dfc7bc99c5286b2c125117bf5cbe242b9d41750732b2bdffe649c6efb8e5526d526fdd130095ecdb7bf210809c6cdad8824faa9ac0310ac3cba2aa0523567b2dfa7fe250b30facbd62d4ec99b94ac47c7d3b28f1f6e4c8"
                />
      </fullTrustAssemblies>
      <partialTrustVisibleAssemblies />
    </system.web>
  </location>

So this was added during installation of .NET 4.0, long before SQL Server Compact 4.0 was released in January 2011. The fullTrustAssemblies element is new in .NET 4.0: http://weblogs.asp.net/asptest/archive/2010/04/23/what-is-new-in-asp-net-4-0-code-access-security.aspx

If this section has been removed from the global web.config file, SQL Server Compact 4.0 will not run under medium trust.

It is possible to configure similar functionality under .NET 3.5 SP1, as described here: http://msdn.microsoft.com/en-us/library/ms174612(v=SQL.110).aspx

Other issues you may encounter when running ASP.NET and SQL Server Compact 4.0 can be:

- The process identity running your web application must have write access to the App_Data folder or the folder where your database file resides:

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/6c1c8798-85d7-4d8f-9908-e9f7dc1c17e4

- SQL Server Compact database files must be located on a local drive, not shared folders:

http://stackoverflow.com/questions/7791345/sqlce-4-ef4-1-internal-error-cannot-open-the-shared-memory-region/7804049#7804049

- If SQL Server Compact is not installed locally, a number of files must be included in the bin folder of your web site:

http://stackoverflow.com/questions/3223359/cant-get-sql-server-compact-3-5-4-to-work-with-asp-net-mvc-2/3223450#3223450

http://msdn.microsoft.com/en-us/library/gg286946.aspx

- If only .NET 4.0 is present on the server, you may be missing the Visual C++ 2008 SP1 Redistributable Package (which is installed only with .NET 3.5 SP1)

I will update this blog post if I notice more “common issues”.

SQL Server Compact books

noreply@blogger.com (ErikEJ) — Tue, 11 Oct 2011 16:06:00 +0000

A number of SQL Compact (or a least SQL Compact related) books have appeared on the market.

I will attempt to keep this list of books up to date as new material appears.

“Building Standalone Data-Driven Applications using SQL Server Compact 4.0 and Visual Studio 2010”

“Microsoft SQL Server Compact 4.0 (SQL Compact onwards) is a free SQL Server embedded database ideal for building standalone desktop, web, and mobile applications that need local data storage.”

http://www.c-sharpcorner.com/Ebooks/

“Microsoft SQL Server Compact 4.0 Books Online”

Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. Download the documentation to learn about SQL Server Compact 4.0 and to build applications for it.

http://www.microsoft.com/download/en/details.aspx?id=21880

“La réplication du fusion avec SQL Server Compact: Implémentation and conseils”

“Free eBook in French about implementation and advise regarding Merge Replication – in many ways similar to the Rob Tiffany book.”

http://www.wygwam.com/Documents/ReplicationFusionSQLServerCompact.pdf

“.NET Compact Framework 3.5 Data Driven Applications”

“Develop data-driven mobile applications from the ground up on top of the Oracle Lite and SQL Server Compact”

http://www.packtpub.com/data-driven-applications-with-.net-compact-framework-3-5/book

“Microsoft SQL Server Compact 3.5 Service Pack 2 Books Online”

This download contains the Books Online for the SP2 version of SQL Server Compact 3.5.

http://www.microsoft.com/downloads/details.aspx?familyid=746C3A6E-FFB1-4C92-93FA-B3BA41FDE681&displaylang=en

“SQL Server MVP Deep Dives”
This book with contribution from 53 SQL Server MVPs includes 2 chapters relating to SQL Compact:
Placing SQL Server in your pocket by Christopher Fairbairn and Mobile data strategies by John Baird
http://www.manning.com/nielsen/

"Enterprise Data Synchronization with SQL Server 2008 and SQL Server Compact 3.5"

An update of the previous book, equally excellent and hands-on.

Available from Amazon

"Microsoft Mobile Development Handbook"

This book covers development using .NET Compact Framework 2.0 in broad terms, and has two chapters relating to SQL Compact:

Chapter 3 describes how to persist data using SQL Compact and how to bind SQL Compact data to UI elements and chapter 7 briefly covers using RDA and merge replication with SQL Compact.

http://www.microsoft.com/MSPress/books/10294.aspx

"Windows Mobile Data Synchronization with SQL Server 2005 and SQL Server Compact 3.1"

I have already recommended the excellent Merge Replication book by Rob Tiffany here:

http://erikej.blogspot.com/2007/10/rob-tiffany-publishes-mobile-merge.html

Available from amazon

"Microsoft® SQL Server 2005 Compact Edition"

This book covers the product in full, including a chapter on Native development, and several chapters on replication. The main perspective of this this book is a DBA approach, with only a single chapter covering development per se. There are also useful chapters on security and performance tuning.

http://www.informit.com/store/product.aspx?isbn=0672329220

"Hitchhiker's Guide to Microsoft SQL Server 2005 Compact Edition"
For desktop developers diving into SQL Compact, the e-book by Bill Vaughn is an excellent introduction, which covers many aspects and pitfalls of developing with SQL Compact edition in a desktop application.

http://www.hitchhikerguides.net/EBooks/5582.aspx

Comparison of SQL Server Compact, SQL Server Express 2008 R2 and LocalDB

noreply@blogger.com (ErikEJ) — Mon, 03 Oct 2011 15:43:00 +0000

Now that SQL Server Compact 4 has been released, some developers are curious about the differences between SQL Server Compact 4.0 and SQL Server Express 2008 R2.

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 “Denali” CTP3 Books Online. LocalDB is the full SQL Server Express engine, but invoked directly from the client provider. It does not support Fulltext Search, and I doubt it support Merge Replication. It is a replacement of the current “User Instance” feature in SQL Server Express.

UPDATE: Updated data on LocalDB, based on additional information and feedback from the LocalDB Program Manager.

Feature	SQL Server Compact 3.5 SP2	SQL Server Compact 4.0	SQL Server Express 2008 R2	SQL Server ”Denali” LocalDB
Deployment/Installation Features
Installation size	2.5 MB download size 12 MB expanded on disk	2.5 MB download size 18 MB expanded on disk	74 MB download size > 300 MB expanded on disk	32 MB download size > 160 MB on disk (TBD)
ClickOnce deployment	Yes	Yes	Yes	Yes
Privately installed, embedded, with the application	Yes	Yes	No	No
Non-admin installation option	Yes	Yes	No	No
Runs under ASP.NET	No	Yes	Yes	Yes
Runs on Windows Mobile / Windows Phone platform	Yes	No	No	No
Installed centrally with an MSI	Yes	Yes	Yes	Yes
Runs in-process with application	Yes	Yes	No	No (as process started by app)
64-bit support	Yes	Yes	Yes	Yes
Runs as a service	No – In process with application	No - In process with application	Yes	No – as launched process
Data file features
File format	Single file	Single file	Multiple files	Multiple files
Data file storage on a network share	No	No	No	No
Support for different file extensions	Yes	Yes	No	No
Database size support	4 GB	4 GB	10 GB	10 GB
XML storage	Yes – stored as ntext	Yes - stored as ntext	Yes	Yes
Binary (BLOB) storage	Yes – stored as image	Yes - stored as image	Yes	Yes
FILESTREAM support	No	No	Yes	No
Code free, document safe, file format	Yes	Yes	No	No
Programmability
Transact-SQL - Common Query Features	Yes	Yes	Yes	Yes
Procedural T-SQL - Select Case, If, features	No	No	Yes	Yes
Remote Data Access (RDA)	Yes	No (not supported)	No	No
ADO.NET Sync Framework	Yes	No	Yes	Yes
LINQ to SQL	Yes	No	Yes	Yes
ADO.NET Entity Framework 4.1	Yes (no Code First)	Yes	Yes	Yes
Subscriber for merge replication	Yes	No	Yes	No
Simple transactions	Yes	Yes	Yes	Yes
Distributed transactions	No	No	Yes	Yes
Native XML, XQuery/XPath	No	No	Yes	Yes
Stored procedures, views, triggers	No	No	Yes	Yes
Role-based security	No	No	Yes	Yes
Number of concurrent connections	256 (100)	256	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.

Analyzing SQL Server Compact queries using Visual Studio 2010 Premium/Ultimate

noreply@blogger.com (ErikEJ) — Fri, 30 Sep 2011 17:40:00 +0000

f you are the happy owner of Visual Studio 2010 Premium or Ultimate, there is a hidden tool that allows you to run and analyze queries against SQL Server Compact 3.5 and 4.0 databases. (Support for 4.0 requires Visual Studio 2010 SP1 + the SQL Server Compact Tools update). This blog post will walk through how to access and use this “hidden” tool.

NOTE: If you only have Visual Studio Professional, you can use my SQL Server Compact Toolbox in combination with the free SQL Server 2008 R2 Management Studio Express to perform similar query analysis.

To access the tool, go to the Data menu, and select Transact-SQL Editor, New Query Connection… (The tool is part of the so-called “Data Dude” features)

In the Connect to Server dialog, select SQL Server Compact:

You can select an existing database, or even create a new one. This dialog will automatically detect if the specified file is a version 3.5 or 4.0 file.

Once connected, you can perform functions similar to what you may know from SQL Server Management Studio:

SQL Server Compact Toolbox available for Visual Studio 11

noreply@blogger.com (ErikEJ) — Thu, 22 Sep 2011 17:38:00 +0000

Visual Studio 11 Developer Preview is now available for testing. As one of the first third party add-ins, a build of the SQL Server Compact Toolbox version 2.4 that supports this Visual Studio Preview version is available via Extension Manager or in the Visual Studio Gallery.

In order to add support for Visual Studio version 11 in an existing add-in, all you need to do is modify the source.extension.vsixmanifest file as shown below:

    <SupportedProducts>
      <VisualStudio Version="10.0">
        <Edition>Pro</Edition>
      </VisualStudio>
      <VisualStudio Version="11.0">
        <Edition>Pro</Edition>
      </VisualStudio>
    </SupportedProducts>

The result of this change is that the add-in can now be installed for several versions of Visual Studio.

I have had to make some changes, as the Toolbox currently depends on SQL Server Compact 3.5 SP2 to store it’s connections, and only SQL Server Compact 4.0 is included with Visual Studio 11. In the Developer Preview the version of SQL Server Compact included is the 4.0 RTM version, so no changes there for now.

To detect which version of Visual Studio you are running, you can use the following code in your Package.cs class:

public Version VisualStudioVersion
{
    get
    {
        var dte = this.GetServiceHelper(typeof(EnvDTE.DTE)) as EnvDTE.DTE;
        string root = dte.RegistryRoot;

        if (root.Contains("10.0"))
        {
            return new Version(10, 0);
        }
        else if (root.Contains("11.0"))
        {
            return new Version(11, 0);
        }
        else
        {
            return new Version(0, 0);
        }
    }
    set
    {
        this.VisualStudioVersion = value;
    }
}

I am currently not bringing forward any 4.0 connections defined in the VS 2010 edition of the add-in. Please let me know if a feature to import these connections to the VS 11 Server Explorer would be useful.

Also, would it be of interest to be able to manage 3.5 databases in VS 11, even though they are not supported in Server Explorer?

As always, please provide any feedback in the comments or via the Codeplex issue tracker.

SQL Server Compact Toolbox 2.4–Visual Guide of new features

noreply@blogger.com (ErikEJ) — Fri, 09 Sep 2011 17:28:00 +0000

After more than 50.000 downloads, version 2.4 of my SQL Server Compact Toolbox extension for Visual Studio 2010 is now available for download. This blog post is a visual guide to the new features included in this release.

Edit Table Data (beta)

The tools that are included with Server Explorer for SQL Server Compact 3.5 and 4.0 already include a feature to edit table data, called Show Table Data:

But the grid has some limitations that I have lifted on the new “Edit Table Data” feature:

- Ability to sort data by clicking a column heading

- Ability to Import/Export/Delete content of image columns

- Ability to locate data in a column (QuickSearch)

Split Windows Phone DataContext into multiple files

You can now select to have the Windows Phone DataContext generated as a DataContext class file, and a class file per table in your database.

Select tables to include in Entity Data Model

You can now select which tables to include in the Entity Data Model created from your SQL Server Compact database. (Thanks to the Extended WPF Toolkit Project)

Explore and script primary and foreign keys

The tree view now lists the primary and foreign keys belonging to a table, and it is also possible to script these individually.

Other fixes

Improved Add-in Update detection (for users behind proxies)
Improved handling of password protected files for non-English SQL Compact runtime
Some icons were not transparent
CreateDataIfExists (Windows Phone DataContext) now returns bool if database was created
Latest scripting library, with fix for missing SET IDENTITY INSERT with multiple files (from large tables)

Useful Windows Phone advice from Nick Randolph

noreply@blogger.com (ErikEJ) — Mon, 05 Sep 2011 17:33:00 +0000

Fellow MVP Nick Randolph (@BTRoam) publishes an excellent blog, Nick’s .NET Travels, often with articles that relate to SQL Server Compact and synchronization technologies, but also very useful articles for any Windows Phone developer, with a practical, hands-on approach. Highly recommended.

He has recently published the following articles:

Windows Phone LINQ to SQL and the INotifyPropertyChanged and INotifyPropertyChanging Interfaces

This article demonstrates the importance of implementing the INotifyPropertyChanging interface on your DataContext classes to improve memory management. As he points out, you should use SQLMetal to generate your DataContext, as this will avoid missing to implement these interfaces. Or even better use the SQL Server Compact Toolbox Visual Studio add-in, as it adds the following features on top of SQLMetal:

1. Removes unneeded/unsupported constructors

2. Adds any [Index] attributes to each table

3. Adds the CreateDatabaseIfExists method

4. Optionally splits the generated files into a file per table (in next version (2.4), currently available in beta)

Change Tracking with SQL Server Compact (LINQ to SQL) on Windows Phone

This article demonstrates how to get started using SQL Server Compact Change Tracking with a Windows Phone SQL Server Compact database, despite the fact that the Change Tracking APIs are not available on Windows Phone. He also gets thrown in how to use the Windows Phone SDK ISETool to move the database from the Phone (Emulator) to your local disk. Good stuff.

SqlCeBulkCopy, a library for fast SQL Server Compact INSERTS released

noreply@blogger.com (ErikEJ) — Thu, 01 Sep 2011 19:27:00 +0000

Version 2.1 of my SQL Server Compact Bulk Insert Library has now been released. This library exposes an API similar to the SqlBulkCopy API implemented for the SqlClient (working against SQL Server). The library allows you to quickly load data inot a SQL Server Compact database.

New features in this release include:

3 editions of the library:
One for .NET Compact Framework for version 3.5 databases - ErikEJ.SqlCe.NetCF.dll
One for full .NET Framework for version 3.5 databases - ErikEJ.SqlCe.dll
One for full .NET Framework for version 4.0 databases - ErikEJ.SqlCe40.dll

- New overloads of the WriteToServer method allows you to load any .NET list that implements IEnumerable or IEnumerable<T>

- API Documentation is now available in HTML format here. The API documentation was created very easily using the open source ImmDoc.NET utility. All this command line utility requires is XML comments file and the DLL file(s).

- NuGet package available

A NuGet package, that includes the SQL Server Compact 4.0 library is now available via the NuGet Package Manager.

If you need to load data fast from a DataTable, DataReader or .NET List to a SQL Server Compact database file, this is the library for you.

Major update to SQL Server Compact 3.5 SP2 available

noreply@blogger.com (ErikEJ) — Mon, 15 Aug 2011 15:35:00 +0000

A major update to SQL Server Compact 3.5 SP2 has just been released, disguised as a “Cumulative Update Package”. Microsoft knowledgebase article 2553608 describes the update. The update contains the following product enhancements:

Support for Windows Embedded CE 7.0

The update contains updated device components. This expand the supported device platforms to this impressive list: Pocket PC 2003 Software, Windows CE, Windows Mobile 5.0, Windows Mobile 6, Windows Mobile 6.1 , Windows Mobile 6.5 Professional, Windows Mobile 6.5 Standard, Windows Embedded CE 7.0

Support for Merge Replication with SQL Server “Denali” CTP3

The update contains new Server Tools, that support Merge Replication with the next version of SQL Server, codename “Denali”. The replication components also work with Windows Embedded CE 7.0.

For a list of fixes in the Cumulative Updates released for SQL Server Compact 3.5 SP2, see my blog post here.

It is nice to see that the 3.5 SP2 product, with it’s full range of device support and synchronization technologies is kept alive and kicking.

NOTE: Currently, the only download available is the desktop runtime, I will update this blog post and tweet (@ErikEJ) when the other downloads are available.

Viewing SQL statements created by Entity Framework with SQL Server Compact

noreply@blogger.com (ErikEJ) — Thu, 11 Aug 2011 15:28:00 +0000

Sometimes it can be useful to be able to inspect the SQL statements generated by Entity Framework against your SQL Server Compact database. This can easily be done for SELECT statements as noted here. But for INSERT/UPDATE/DELETE this method will not work. This is usually not a problem for SQL Server based applications, as you can use SQL Server Profiler to log all SQL statements executed by an application, but this is not possible with SQL Server Compact.

This forum thread contains an extension method, that allows you to log INSERT/UPDATE/DELETE statements before SaveChanges is called on the ObjectContext. I have updated and fixed the code to work with SQL Server Compact 4.0, and it is available in the updated Chinook sample available below in the ObjectQueryExtensions class in the Chinook.Data project.

You can now use code like the following to inspect an INSERT statement:

using (var context = new Chinook.Model.ChinookEntities())
{ 
    context.Artists.AddObject(new Chinook.Model.Artist { ArtistId = Int32.MaxValue, Name = "ErikEJ" });
    string sql = context.ToTraceString();
}

The “sql” string variable now contains the following text:

--=============== BEGIN COMMAND ===============

declare @0 NVarChar set @0 = 'ErikEJ'

insert [Artist]([Name])

values (@0)

; select [ArtistId]

from [Artist]

where [ArtistId] = @@IDENTITY

--=============== END COMMAND ===============

This statement reveals some of the magic behind the new support for “server generated” keys with SQL Server Compact 4.0 when used with Entity Framework 4.0. SQL Server Compact is “tricked” into executing multiple statements in a single call.

SQL Server Compact Toolbox 2.3–Visual Guide of new features

noreply@blogger.com (ErikEJ) — Sun, 07 Aug 2011 10:09:00 +0000

After more than 44.000 downloads, version 2.3 of my SQL Server Compact Toolbox extension for Visual Studio 2010 is now available for download. This blog post is a visual guide to the new features included in this release

Generate database documentation

This feature allows you to create documentation of all tables and columns in your database, in HTML or XML (raw) format, for use with product documentation etc. If you have added descriptions to database, table or column, these will also be included.

From the database context menu, select Create Database Documentation…

You will be prompted for a filename and can choose between HTML and XML format. The generated document will then open in the associated application (for example your browser).

The format of the HTML and XML file comes from the excellent DB>doc for Microsoft SQL Server CodePlex project. You can use the XML file as the data in your own documentation format.

By default, tables beginning with __ are not included in the documentation (this includes the table with object descriptions). They can optionally be included via a new option:

Please provide any feedback for this new feature to the CodePlex issue tracker

Handle password protected files better

When trying to open a password protected file, where the password is not saved with the connection string, you are now prompted to enter the database password, instead of being faced with an error.

Show result count in status bar

The query editor status bar now displays the number of rows returned.

Other fixes

Improvements to Windows Phone DataContext generation, improved error handling to prevent Visual Studio crashes, and the latest scripting libraries included.

Using PowerShell to manage SQL Server Compact database files

noreply@blogger.com (ErikEJ) — Wed, 13 Jul 2011 08:30:00 +0000

I noticed that someone asked if you could manage SQL Server Compact from PowerShell, and yes you can, based on the reply here I put together the following sample:

[Reflection.Assembly]::LoadFile(“C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll”)

$connString = "Data Source=C:\data\sqlce\test\roads.sdf"
$cn = new-object "System.Data.SqlServerCe.SqlCeConnection" $connString

# create the command
$cmd = new-object "System.Data.SqlServerCe.SqlCeCommand"
$cmd.CommandType = [System.Data.CommandType]"Text"
$cmd.CommandText = "SELECT TOP (100) * FROM Road"
$cmd.Connection = $cn

#get the data
$dt = new-object "System.Data.DataTable"

$cn.Open()
$rdr = $cmd.ExecuteReader()

$dt.Load($rdr)
$cn.Close()

$dt | Out-Default | Format-Table

Windows Phone / SQL Server Compact resources

noreply@blogger.com (ErikEJ) — Sun, 03 Jul 2011 19:18:00 +0000

This blog post collects links to relevant blog posts and articles about Windows Phone support for SQL Server Compact. You can also follow me on Twitter (@ErikEJ) to get notified of any SQL Server Compact related news.

MSDN

Local Database Overview for Windows Phone

How to: Create a Basic Local Database Application for Windows Phone

How to: Create a Local Database Application with MVVM for Windows Phone

How to: Deploy a Reference Database with a Windows Phone Application

Walkthrough: Updating a Local Database Application for Windows Phone

Local Database Best Practices for Windows Phone

Local Database Connection Strings for Windows Phone

Local Database Migration Overview for Windows Phone

LINQ to SQL Support for Windows Phone

Video: SQL Server Compact and User Data Access in Mango

PowerPoint slides: SQL Server Compact and User Data Access in Mango

Video + PPT: New Data Access Features Coming to Windows Phone

Windows Phone Mango Application Storage Jumpstart PDF

Get to Mango #8 - Using a Local Database (Screencast)

ErikEJ

Populating a Windows Phone “Mango” SQL Server Compact database on desktop

SQL Server Compact Toolbox 2.2–Visual Guide of new features

Windows Phone Local Database tip: Viewing the SQL generated by LINQ to SQL

Windows Phone Local Database tip: Batch INSERT performance

Windows Phone Local Database tip: Exploring INSERT performance–5 power tweaks

Generating a LINQ to SQL DataContext for VS Express for Windows Phone

Windows Phone Local Database tip: Initializing the database

Jesse Liberty

Coming in Mango–Sql Server CE

Coming In Mango–Local DB Part 2- Relationships

Best Practices For Local Databases

Yet Another Podcast #43–Sean McKenna and Windows Phone Data

Sean McKenna and Windows Phone "Mango" Database Support

Rob Tiffany

New Windows Phone Mango Data Access Features @ Tech Ed North America 2011

Alex Golesh

Windows Phone Mango–What’s New? (“Local Database” - Part 1 of 8)

Windows Phone Geek

Windows Phone Mango Local Database- mapping and database operations

Using SqlMetal to generate Windows Phone Mango Local Database classes

Performance Best Practices: Windows Phone Mango Local Database

Windows Phone Mango Local Database(SQL CE): Introduction

Windows Phone Mango Local Database(SQL CE): Linq to SQL

Windows Phone Mango Local Database(SQL CE): [Table] attribute

Windows Phone Mango Local Database(SQL CE): [Column] attribute

Windows Phone Mango Local Database(SQL CE): [Association] attribute

Windows Phone Mango Local Database(SQL CE): Database mapping

Windows Phone Mango Local Database(SQL CE): DataContext

Windows Phone Mango Local Database(SQL CE): Connection Strings

Windows Phone Mango Local Database(SQL CE): Creating the Database

Windows Phone Mango Local Database(SQL CE): Database Queries with LINQ

Windows Phone Mango Local Database(SQL CE): How to Insert data

Windows Phone Mango Local Database(SQL CE): How to Update data

Windows Phone Mango Local Database(SQL CE): How to Delete data

Arsahnt

Distributing a SQL CE database in a WP7 Mango application

Arsanth Daily – May 25th

Arsanth Daily – May 27th

Windows Phone 7 SQL CE – Column inheritance

Windows Phone 7 SQL CE – DataContext Tables

Working with pre-populated SQL CE databases in WP7

LINQ to SQL CE performance tips for WP7

Arsanth Daily – June 6th

Arsanth – August 16th

Arsanth – August 18th

How To: Log LINQ to SQL activity on WP7

Kunal Chowdhury

Windows Phone 7 (Mango) Tutorial - 22 - Local Database Support, Create DataContext

Windows Phone 7 (Mango) Tutorial - 23 - Local Database Support, Configuring Project

Windows Phone 7 (Mango) Tutorial - 24 - Local Database Support, CRUD operation with Demo

Windows Phone 7 (Mango) Tutorial - 25 - Learn about Database Connection String

Sergey Barskiy

SQL CE on Windows Phone 7.1 (Mango)

SQL CE in Mango–Updating the Schema

Derik Whittaker

Using SQL CE on WP7 Mango–Getting Started

Using SQL CE On WP7 Mango–Working with Associations

Using SQL CE On WP7 Mango–Working with Indexes

Mark Artega

Windows Phone and Database Support

Rabeb

Mango- Baby Steps: Creating an application with a local Database

JeffCren

WP7 App First Run Logic

Matt Lacey

Simplifying use of SQL CE in Mango

Corrado

Using Local Database in WP7-Mango

Max Paulousky

Windows Phone (Mango) DB Engines Performance Testing

Nick Randolph

Change Tracking with SQL Server Compact (LINQ to SQL) on Windows Phone

Windows Phone LINQ to SQL and the INotifyPropertyChanged and INotifyPropertyChanging Interfaces

Understanding LINQ to SQL on Windows Phone 7.5

Chris Sainty

WP7.5 Mango–Compiled Queries

Anton Swanvelder

Writing an Image to SQL CE / Linq to SQL

Rafa Serna

Performance Improvements in SQL CE environments – I

Debug mode

Local Database application for windows Phone

Shazaml Design, LLC

More Complete WP7 Mango Database Update Walkthrough

Jerry Nixon

Mango Sample: Database Part 1:2

Mango Sample: Database Part 2:2

Mango Sample: SQL Toolbox

Mango- Database (screencast)

Bhaskar Shrestha

Using SQL Server Compact in Windows Phone 7.1 apps – Part 1

Using SQL Server Compact in Windows Phone 7.1 apps – Part 2

Using SQL Server Compact in Windows Phone 7.1 apps – Part 3

Using SQL Server Compact in Windows Phone 7.1 apps – Part 4

SQL Server Compact Private Deployment tweaks

noreply@blogger.com (ErikEJ) — Tue, 14 Jun 2011 15:15:00 +0000

As a follow up to my previous post about Private Deployment (the concept that you can simply include the SQL Server Compact DLL files with your application as content, so to speak), I will show you a couple of tweaks that the .NET Framework enables.

Forcing an existing application to use the private DLL files

If you have an existing application, that is compiled against the centrally deployed DLL files, for example assembly version 3.5.1.0 or 4.0.0.0, you can force the application to use the private assembly version files instead (3.5.1.50 and 4.0.0.1), via an application configuration file. Lets take ExportSqlCe40.exe as an example. This application is complied against assembly version 4.0.0.0, so it will not work unless SQL Server Compact 4.0 runtime is centrally installed.

To force this application to use Private Deployment only, create a .config file named ExportSqlCe40.exe.config, with the following contents:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.0.1" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

If you run the application now, you will get this error:

Now copy all files from the C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private folder to the folder where the Exportsqlce40.exe file resides:

Now the application runs, and uses only the private DLL files.

Isolating the SQL Server Compact runtime files in a separate folder

Continuing the sample above, to be neater, it would be nice to have the SQL Server Compact DLL files in a subfolder below the .exe file location. This can be done by moving the files to a separate folder, for example named SqlCe4. Now I have moved all the SQL Server Compact files and folders to that folder:

Now modify the .config file as follows:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <probing privatePath="SqlCe4"/>
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.0.1" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

Notice the Probing setting above, that has been added. Also notice that this comes before the bindingRedirect.

Hop you find this useful.