Sunday, December 15, 2013

Document your SQL Server database with free tools: SQL Server Compact Toolbox and DB>Doc

In this blog post I will highlight an overlooked feature in my SQL Server Compact Toolbox (Visual Studio add-in or standalone), the ability to create interactive relationship diagrams of SQL Server databases. And in combination with the free, open source DB>Doc for SQL Server, you have a full suite of documentation tools.

In order to create a diagram showing tables, columns and relationships for your SQL Server database, install the Toolbox.

Right click the root node of the Toolbox window, and select “Create SQL Server Database Graph (DGML)”:

image

Select the database that you want to diagram (the tool needs SELECT permission on the system views in the database)

image

You can also select to diagram only a subset of the tables in the database:

image

You will be prompted for a location to save the DGML file, enter this, and click OK, and Visual Studio will show the generated diagram in the DGML viewer, that comes with Visual Studio 2012 Pro or higher:

image

The viewer is quite advanced, from the Layout menu you can choose between various layouts of the dependency graph, and other advanced features to analyse the graph, for example. From the viewer you can Share as Image or XPS.
UPDATE: With the DGML Power Tools installed, you can also share a .SVG, which you can view in any web browser.

image

Click on a table, and select the down arrow button to expand the table into columns, and refer to the Legend also displayed (via the Legend menu item in the viewer):

image

More information on working with the graphs is available on MSDN:

Browse and Rearrange Dependency Graphs

Edit and Customize Dependency Graphs

To create HTML/XML based documentation of your database columns, you can use the open source DB>Doc tool. The main idea [of this tool] is to serve as a complementary tool to database diagrams, focusing on columns in tables and their meaning (common database diagrams focus to entity relationships).

I also use this tool for the documentation feature in the Toolbox for SQL Server Compact database files, which produces output similar to this:

image

Happy documenting! And feel free to provide feedback at https://sqlcetoolbox.codeplex.com/WorkItem/Create

Monday, December 2, 2013

The trouble with Any CPU–Prefer 32 bit–BadImageFormatException

In my previous 2 blog posts here and here, I have briefly mentioned the new default Target Platform for new .NET 4.5/4.5.1 projects in Visual Studio 2012/2013. To be perfectly honest, it was not exactly clear why this was an issue with the SQL Server Compact ADO.NET provider, so I decided to dig a little deeper.

To read more about the new default Platform target option introduced in .NET 4.5, see the MSDN documentation here, and the blog post here.

image

Why is this Target platform an issue for the SQL Server Compact provider?

Let’s find out how the provider detects if it is running on a x86 or x64 (AMD64) system. In .NET 4.0, two new properties were introduced, System.Environment.Is64BitProcess and System.Environment.Is64BitOperatingSystem (for a nice overview of their implementation see this blog post. But the provider code is old, and uses a different method: It looks at the PROCESSOR_ARCHITECTURE environment variable, and assumes that it reflects the value of the current process bitness (possible values are x86 and AMD64 on a Intel x64 system). In fact, it first looks in the .exe folder for a valid version of sqlceme40.dll, and if not found, then in a subfolder named after the processor architecture (ie x86 or AMD64).

In order to test how an app responds, I then made a small .NET 4.5 console app to test the values of the new Environment properties and the PROCESSOR_ARCHITECTURE value. I added the SQL Server Compact NuGet package (Microsoft.SqlServer.Compact) to test how the engine responds using Private Deployment under the four different Target Platform options. And I uninstalled the SQL Server Compact 4.0 runtime from Add/Remove Programs, so it is no longer in the GAC.

string path = @"c:\temp\test.sdf";
Console.WriteLine("Is64BitOS: " + System.Environment.Is64BitOperatingSystem);
Console.WriteLine("Is64BitProc: " + System.Environment.Is64BitProcess);
Console.WriteLine("PROCESSOR_ARCHITECTURE: " + System.Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE"));
Console.ReadKey();
if (System.IO.File.Exists(path))
System.IO.File.Delete(path);
using (SqlCeEngine engine = new SqlCeEngine(string.Format("Data Source={0}", path)))
{
engine.CreateDatabase();
}





1: Output with Any CPU, Prefer 32 bit:


Is64BitOS: True
Is64BitProc: False
PROCESSOR_ARCHITECTURE: AMD64


And getting error: Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8876.

As you can see even if it is a 32 bit process, the PROCESSOR_ARCHITECTURE variable has the “wrong” value, causing the SQL Server Compact ADO.NET provider System.Data.SqlServerCe.dll to try to load the dll files from the AMD64 folder in a 32 bit process.


2: Output with Any CPU, Prefer 32 bit unchecked:


Is64BitOS: True
Is64BitProc: True
PROCESSOR_ARCHITECTURE: AMD64


- and no errors


3: Output with x64:


Is64BitOS: True
Is64BitProc: True
PROCESSOR_ARCHITECTURE: AMD64


- and no errors


4: Output with x86:


Is64BitOS: True
Is64BitProc: False
PROCESSOR_ARCHITECTURE: x86


- and no errors


Hopefully this blog post will prevent some form getting bitten by this “issue”, which also affects the SQLite ADO.NET provider.

Monday, November 25, 2013

Entity Framework 6 & SQL Server Compact 4 (2)–Easy Private Desktop Deployment

UPDATE: Instead of using the NuGet package mentioned below, use the package mentioned in this newer blog post: http://erikej.blogspot.dk/2014/10/entity-framework-6-and-sql-server.html

In this post I will describe a simplified approach to SQL Server Compact Private Desktop Deployment with Entity Framework 6, for an overview blog post on Private Deployment with SQL Server Compact, see my blog post here.

When using Private Deployment with Entity Framework 6, due to the fact that the EntityFramework.SqlServerCompact also installs the SQL Server Compact NuGet package, we must use a slightly different approach from what I described here in order to keep thing simple. The main difference is that we must force the project to use x86 Target Platform rather than AnyCPU, Prefer 32 bit. This is due to the fact that when running Prefer 32 bit, the SQL Server Compact ADO.NET provider loads the wrong files, as the PROCESSORPLATFORM in this case remains AMD64 on 64 bit systems, but the executing .NET Framework is the x86 version.

To read more about the new default Platform target option introduced in .NET 4.5, see the MSDN documentation here, and the blog post here.

In addition, I will demonstrate how to use my SQL Server Compact Toolbox to quickly get started with a Database First workflow.

For the sake of simplicity, and in order to focus attention on the private deployment aspects, I will demonstrate with a console application, but the same approach will also work for WinForms and WPF applications. The approach will NOT work for dll output projects, like for example add-ins and similar, as it relies on configuration entries in app.config. And this blog post is about deployment, not layered architectures. 

In short, we need to do the following:

1: Create a project and set Target Platform to x86

2: Add the EntityFramework.SqlServerCompact.PrivateConfig NuGet package to the project

3: Add our database file as project content

4: Create EDMX (or Code First classes) and adjust connection string

5: Add code to properly deploy the database file during app launch

But let’s expand on each task below.

Before you get started, make sure you have the following installed:

1: Visual Studio 2013 Pro or higher.

2: The latest version of  my SQL Server Compact Toolbox add-in (Install via Tools/Extensions in VS). The Toolbox requires the SQL Server Compact 3.5 SP2 and 4.0 SP1 runtimes to be installed (the 3.5 requirement will be lifted in the next release of the Toolbox)

3: An existing SQL Server Compact database file, I will use Chinook

With that in place, let us open Visual Studio and get started:

Create new console application

Go to File, New Project, and create a new Windows Console application. Make sure to set the target platform to 4.0 or newer.

image_thumb3

Now set the Target Platform to x86 (this is an important step, if you forget to do this you will get a BadImageFormat exception during runtime/debug)

Go to the project properties, and select the Build section/tab, and choose x86:

image

Install Entity Framework 6 from NuGet

To get the latest and greatest Entity Framework 6 bits, always install from NuGet. And thanks to the amazing package dependency resolution features of NuGet, just install the single required package, and the remaining packages will be installed as dependencies. In this case, the only thing that is missing from the EntityFramework.SqlServerCompact package (which depends on SQL Server Compact 4 and EF6) is a DbProvider registration in the app.config file, that enables you to run without the SQL Server Compact runtime installed in GAC and machine.config. To fix this “"misssing link”, I have created the http://www.nuget.org/packages/EntityFramework.SqlServerCompact.PrivateConfig/ 

package, which simply adds the missing config entries and in turn depends on all other required packages. It adds this to the app.config file:

  <system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.4.0" />
<add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>









In other words, simply install the EntityFramework.SqlServerCompact.PrivateConfig package. Right click the References folder in your project, and select Manage NuGet Packages. Search for the package name and install it. And all required packages and references will be installed. (I am working on getting these config entries added to the base EntityFramwork.SqlServerCompact package, in order to make things more streamlined)




Add your database file to the project


I will use Chinook, which you can install from NuGet. But of course feel free to use your own database file.


Find the Chinook SQL Server Compact database package, and install it:


image




Make sure to mark the database file as Content, Copy Always in the project. I will describe later in this post how to place it correctly on the end users PC.




image




 Create the Entity Data Model (EDMX) and adjust the connection string




Build the project.




Now in the SQL Server Compact Toolbox, connect to the database file in your project folder:




image




Right click the database, and select the “Add Entity Data Model (EDMX) to current project” menu item:




image




Click OK, and the EDMX file and various other files will be added to your project:




image




Build the project.




Now let us add some test code to the Main method in order to verify that everything works so far:

using (var context = new ChinookEntities())
{
foreach (var album in context.Album.ToList())
{
Console.WriteLine(album.Title);
}

}
Console.ReadKey();





















We can now access the database via the generated object model, and do not have to type SQL, but can use LINQ to Entities to query the database. In addition, we can update the database (INSERT, UPDATE, DELETE) via methods on the derived DbContext class, ChinookEntities. In app.config, the following connection string has been added:

  <connectionStrings>
<add name="ChinookEntities" connectionString="metadata=res://*/Chinook.csdl|res://*/Chinook.ssdl|res://*/Chinook.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=C:\Users\erik.COMMENTOR\Documents\Visual Studio 2013\Projects\ConsoleApplication4\Chinook.sdf&quot;"
providerName="System.Data.EntityClient" />
</connectionStrings>









In order to make the connection string user and folder independent, change the data source as follows:

<connectionStrings>
<add name="ChinookEntities" connectionString="metadata=res://*/Chinook.csdl|res://*/Chinook.ssdl|res://*/Chinook.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=|DataDirectory|\Chinook.sdf&quot;"
providerName="System.Data.EntityClient" />
</connectionStrings>



Deploy the database file


The final step will be done to ensure that the database file will be located in a writeable location on the users machine when deployed/installed. We will simply do this in code in order to not depend on any install actions and issues. We will use the same approach that I have already used in my blog post here, which takes advantage of the DataDirectory connection string macro. So add this piece of code to the Program class:

private const string dbFileName = "Chinook.sdf";
private static void CreateIfNotExists(string fileName)
{
string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
// Set the data directory to the users %AppData% folder
// So the database file will be placed in: C:\\Users\\<Username>\\AppData\\Roaming\\
AppDomain.CurrentDomain.SetData("DataDirectory", path);

// Enure that the database file is present
if (!System.IO.File.Exists(System.IO.Path.Combine(path, fileName)))
{
//Get path to our .exe, which also has a copy of the database file
var exePath = System.IO.Path.GetDirectoryName(
new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);
//Copy the file from the .exe location to the %AppData% folder
System.IO.File.Copy(
System.IO.Path.Combine(exePath, fileName),
System.IO.Path.Combine(path, fileName));
}
}















Remember to add a call to CreateIfNotExists as the first line in the Main method:

static void Main(string[] args)
{
CreateIfNotExists(dbFileName);









You can now use ClickOnce, XCopy or an Installer to deploy your app (the files in the release folder), with no other requirements than the target .NET Framework version. Uninstall the 4.0 runtime from your PC in order to test (and/or test on another PC without SQL CE 4.0 installed)




What we have achieved:




- Simple, self contained deployment of a single user desktop app of any type to any .NET 4.0 or higher platform (not ARM, though)




- Automated creation of the required settings in app.config via NuGet packages




- RAD (Rapid App Development) “Database First” access to a well-performing, well-documented and powerful ORM.




You can download the complete solution (without packages) from here.

Friday, November 15, 2013

Entity Framework 6 & SQL Server Compact 4 (1)–Workflows and tooling

In this new series, I will give an introduction to the new tooling and some of the new features in Entity Framework 6 (EF6). But lets begin with an overview of workflow and tooling option in the various Visual Studio versions, that EF6 supports.

EF6 consist of 2 major parts:

- the EF6 runtime, available via NuGet, which support Visual Studio 2010, 2012 and 2013 (version 6.0.0. of the runtime is included in the VS 2013 install, but always update to the latest released NuGet version)

- the EF Tools, which is mainly the Entity Data Model Wizard and the graphical EDMX editor and Model Browser in Solution Explorer. The EF Tools have been updated and are included with VS 2013, and you can also grab the EF6 Tools for Visual Studio 2012 here. For VS 2010, there are no updated tools.

Pawel Kadluczka (@moozzyk) from the Entity Framework team has posted an excellent, detailed blog post about changes to the tooling.

Workflows

As you may be aware, there are 4 available workflows available with Entity Framework:

Database First, where you reverse engineer an Entity Data Model (EDMX) from an existing database.

Model First, where you “draw” the data model in an empty EDMX, and the can generate database objects based on the model.

Code First, where you define data classes, and either decorate with attributes of use fluent configuration, and also define a class that inherits from DbContext.

Code Second, where you generate Code First classes based on an existing database. You can use for example the EF Power Tools to do this

You can get more information about the different workflows and how to choose one here.

SQL Compact workflows in Visual Studio

In this section I will describe how to perform each workflow in VS 2010, 2012 and 2013, as there are differences due to varying degrees of tooling support. For any of the workflows below, the first thing to do is install the Entity Framework 6 SQL Server Compact NuGet package in your project, the package name is EntityFramework.SqlServerCompact:

image

This package will add the following references to your project:

EntityFramework

EntityFramework.SqlServer (not needed for our scenarios)

EntityFramework.SqlServerCompact

System.Data.SqlServerCe (not really needed for EF scenarios)

- the reason why System.Data.SqlServerCe is not needed, is that EF6 relies on the DbProviderFactory registration of SQL Server Compact, either in machine.config or your app.config/web.config.

The package will also add required app/web .config settings in the current project, to register the SQL Server Compact Entity Framework provider for EF6. (Not the DbProvider registration, but I have a solution for that in my next blog post)

Visual Studio 2010

Since the EF6 Tools are not available for VS 2010, the only available workflow using Entity Framework 6 (which fully supports .NET 4.0) is Code First. There is an overview of Code First with SQL Server Compact, EF6 and VS 2010  available here on CodeProject. (You can of course continue to use the EF designer with EF 4 based projects). I also have a blog post on Code First here.
Please note that my recommend approach is simply to install the EntityFramework.SqlServerCompact package first, as this will include all other required packages in the project for you, rather than installing the EntityFramework package first as done in the CodeProject blog post.

Visual Studio 2012

VS 2012 has a DDEX (Server Explorer) provider for SQL Server Compact, which makes all workflows simple.

Database First: Connect to a database file in Server Explorer, and then add an “ADO.NET Entity Model” to your project. (Or connect to/create one during the wizard)

Model First: Add an Empty “ADO.NET Entity Model” to your project. Add entities etc. to the Model. When prompted during script generation (Generate Database from Model), connect to or create a SQL Server Compact database file. I have a fix for the .tt script template to make the generated script more compatible with the Toolbox. My fix will be included with the EF 6.1 tooling.

Code Second: Install the Entity Framework Power Tools from Tools/Extensions (I believe they require the EF6 Tools to be installed to work)

Visual Studio 2013

VS 2013 has no DDEX (Server Explorer) provider for SQL Server Compact, which makes all workflows a bit more complicated.There is a VS UserVoice item to bring back the DDEX provider, if you feel like voting for it.

UPDATE: The latest version of my Toolbox add-in (3.7.1, currently in beta) now installs a simple DDEX provider in VS 2013, that enables the same workflows as in VS 2012 described above. Download from here and read the release notes carefully.

Database First: Install my SQL Server Compact Toolbox add-in via Tools/Extensions. Also install SQLCE 3.5 SP2 and SQLCE 4.0 SP1 if not already present. Right click the database and create an Entity Data Model from the context menu.

Model First: Install my SQL Server Compact Toolbox add-in via Tools/Extensions. Also install SQLCE 3.5 SP2 and SQLCE 4.0 SP1 if not already present. Create an empty database file. Right click the database and create an Entity Data Model from the context menu. Add entities etc. to the Model. A SQL Compact compatible script will be generated when selecting “Generate Database from Model”. I have a fix for the .tt script template to make the generated script more compatible with the Toolbox.

Code Second: I am working on getting a pull request accepted for the EntityFramework Reverse POCO Code First Generator project. This project is an alternative to the EF Power Tools Reverse Engineer feature, but presently does not support SQL Server Compact. Once it has been accepted, you can generate Code Second classes from a SQL Server Compact database file with this tool. UPDATE: Latest version of this template now supports SQL Server Compact

NEXT: Easy Private Desktop Deployment with SQL Server Compact and Entity Framework 6.

Monday, November 11, 2013

SQL Server Compact breaks when upgrading from Windows 8 to Windows 8.1

Symptoms

1: The SQL Server Compact Toolbox now longer works after upgrading to Windows 8.1

2: Entity Framework based SQL Server Compact solutions no longer work after upgrading to Windows 8.1

Issue

When upgrading a machine that already has SQL Server Compact 3.5 SP2 and/or 4.0 installed, the DbProvider registration, which is stored in machine.config is broken, probably due to the fact, that a newer version of .NET Framework is installed (version 4.5.1) which simply overwrites the existing maching.config file. Both the SQL Server Compact Toolbox and Entity Framework depend on this registration.

Fix

The fix is to repair/re-install SQL Server Compact 3.5 SP2 and/or 4.0 SP1, both versions are fully supported on Windows 8.1/Windows Server 2012 R2.

You can download 3.5 SP2 from here: http://www.microsoft.com/en-us/download/details.aspx?id=5783

You can download 4.0 SP1 from here: http://www.microsoft.com/en-us/download/details.aspx?id=30709

Friday, October 25, 2013

SQL Server Compact Toolbox 3.6–Visual Guide of new features

After more than 210.000 downloads, version 3.6 (skipping version 3.5 to avoid confusion) 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


Table Builder

In order to improve the experience for Toolbox users, I have now started adding features to the Toolbox found in the Server Explorer tooling, starting with a Table Builder. This is due to the fact, that Server Explorer support in VS 2012 and VS 2013 is limited (in 2012 SQL Compact 4.0 only, and in VS 2013 none, where as Server Explorer in VS 2010 supports both 3.5 and 4.0).

To use the Table Builder, right click a database, and select Build Table:

image

Then specify the columns to be built, and click Script!

image

A CREATE TABLE script will then be displayed in the SQL editor. In the current version, it is not possible to modify an existing table, maybe next time?

If you have both VS 2012 and VS 2013, feel free to contact me for a way to re-enable Server Explorer support for SQL Server Compact 4.0 in VS 2013.

Report Viewer

You can now view the data in any table via the Microsoft Report Viewer, which also enables you to export data in Excel, Word and PDF format.

image

Right click any table, and select View Data as Report

image

You can right click the report (or use the Toolbar) to Print/Export the table data.

This featured is based on this Stackoverflow reply: http://stackoverflow.com/questions/267091/best-way-to-view-a-table-with-lots-of-columns

Generate Entity Framework 6 Entity Data Model (EDMX) in Visual Studio 2013

This feature is very similar to the existing feature for Visual Studio 2010, allowing you to do Database First development with SQL Server Compact and Entity Framework 6. The new implementation for Visual Studio 2013 takes advantage of assets from the version 6 Entity Framework designer, this includes using the included .tt files to code generate a DbContext and related POCO classes rather than a legacy ObjectContext. To use this feature, first install the EntityFramework.SqlServerCompact NuGet package in your project, and the simply right click the database you would like to generate the model for:

image

image

Then you will be presented with a very basic “Entity Data Model” dialog, which allows you to select which tables to include in the model, and to specify other relevant options:

image
Clicking OK will generate an EDMX file and releated .tt files etc, in the project based on the Entity Framework 6 designer way of doing this. image

Other improvements and bug fixes

Scripting: Improved parsing of SELECT statements
Scripting; Proper scripting of float and real values
Scripting: Fixed some schema diff scripts bugs
UI: Improved display of database size and space available
Integration: Improved VS 2013 RTM support

Monday, October 7, 2013

SQL Server Compact 4 desktop app with simple Private Deployment and LINQ to SQL

In this post I will describe a simplified approach to SQL Server Compact Private Deployment, for an overview blog post on Private Deployment with SQL Server Compact, see my blog post here.

By forcing your app to run using x86 always (Prefer 32-bit), which is the new default Platform target option  for apps targeting .NET Framework 4.5, deployment of SQL Server Compact with you app becomes simpler, but must follow different guidelines from what I have previously blogged about. (The same approach will also work with apps targeting .NET 4.0, just set the Platform target to x86 in the location shown below. And the same approach will also work with the SQL Server Compact 3.5 DLL files.)

image

To read more about the new default Platform target option introduced in .NET 4.5, see the MSDN documentation here, and the blog post here.

In addition, I will demonstrate how to use LINQ to SQL with SQL Server Compact 4.0, a low overhead, fast performing ORM.

For the sake of simplicity, and in order to focus attention on the private deployment aspects, I will demonstrate with a console application, but the same approach will also work for WinForms and WPF applications.

Before you get started, make sure you have the following installed:

1: Visual Studio 2010/2012/2013 Pro or higher 

2: SQL Server Compact Toolbox add-in (Install via Tools/Extensions in VS)

3: An existing SQL Server Compact database file, I will use Chinook, which you can download from here

4: The SQL Server Compact 4.0 SP1 runtime

(You could also use the free Visual Studio 2010/2012/2013 for Windows Desktop with the standalone SQL Server Compact Toolbox for 4.0, which also supports LINQ to SQL code generation)

With that in place, let us open Visual Studio and get started:

Create new console application

Go to File, New Project, and create a new Windows Console application. Make sure to set the target platform to 4.0 or newer.

image

Include the SQL Server Compact binaries in your project

Now include the SQL Server Compact 4.0 binaries and ADO.NET Provider as content in your app. Copy C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\System.Data.SqlServerCe.dll to your project folder, and then copy all files and folders in C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\x86 also to your project folder.

In the Solution Explorer, select Show All Files, and include the new folder and the seven files just copied in the Project:

image

Now mark all the copied files (also the files in the Microsoft.VC9.CRT folder) and mark them as Content, Copy Always:

image

Finally, add a Reference to the System.Data.SqlServerCe.dll file in your project folder:

image

(Make sure to check the file location and the Version, should be 4.0.0.1)

Add your database file to the project

Make sure it is also Content, Copy Always – we use the”Database First” workflow here.

Generate the LINQ to SQL DataContext

Next,we will generate a LINQ to SQL DataContext class and related Table classes based on the database, so connect to the database in SQL Server Compact Toolbox, using the Add SQL Server Compact 4.0 Connection menu item:

image

Then right click the database and select “Add LINQ to SQL DataContext to current project”:

image

(I am just using ChinookContext as Context name)

Click OK, and a DataContext class file will be added to your project, and the required reference to System.Data.Linq will be added to the project.

Now let us add some test code to the Main method in order to verify that everything works so far, so the Program.cs code looks like this:

using System;
using System.Data.SqlServerCe;

namespace LinqToSqlCePrivateDeploy
{
class Program
{
private const string
dbFileName = "Chinook_SqlServerCompact_AutoIncrementPKs.sdf";

private static string dbConnectionString =
string.Format("Data Source=|DataDirectory|{0};Max Database Size=4091", dbFileName);
static void Main(string[] args)
{
using (var connection =
new SqlCeConnection(dbConnectionString))
{
using (var context = new ChinookContext(connection))
{
//To log SQL statements, use:
//context.Log = Console.Out;
foreach (var album in context.Album)
{
Console.WriteLine(album.Artist.Name);
Console.WriteLine(album.Title);
}
}
}
Console.Read();
}
}
}





We can now access the database via the generated object model, and do not have to type SQL, but can use LINQ to query the database. In addition, we can update the database (INSERT, UPDATE, DELETE) via methods on the DataContext.

Notice that the DataContext must be constructed with a SqlCeConnection object, in order for LINQ to SQL to work with SQL Server Compact 4.0.

Deploy the database file

The final step will be done to ensure that the database file will be located in a writeable location on the users machine when deployed/installed. We will simply do this in code in order to not depend on any install actions and issues. In addition, we can do this without storing any connection strings in app.config, making the app more self-contained. We will use the same approach that I have already used in my blog post here, which takes advantage of the DataDirectory connection string macro.

private static void CreateIfNotExists(string fileName)
{
string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
// Set the data directory to the users %AppData% folder
// So the database file will be placed in: C:\\Users\\<Username>\\AppData\\Roaming\\
AppDomain.CurrentDomain.SetData("DataDirectory", path);

// Enure that the database file is present
if (!System.IO.File.Exists(System.IO.Path.Combine(path, fileName)))
{
//Get path to our .exe, which also has a copy of the database file
var exePath = System.IO.Path.GetDirectoryName(
new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);
//Copy the file from the .exe location to the %AppData% folder
System.IO.File.Copy(
System.IO.Path.Combine(exePath, fileName),
System.IO.Path.Combine(path, fileName));
}
}



Remember to add a call to CreateIfNotExists as the first line in the Main method:

static void Main(string[] args)
{
CreateIfNotExists(dbFileName);



You can now use ClickOnce, XCopy or an Installer to deploy your app, with no other requirements than the target .NET Framework version.


What we have achieved:


- Simple, self contained deployment of a single user desktop app of any type to any .NET 4.0 or higher platform (not ARM, though)


- No need for special incantations in app.config


- RAD (Rapid App Development) “Database First” access to a well-performing, well-documented and simple ORM.


You can download the complete solution from here; http://sdrv.ms/179QBaa

Monday, September 23, 2013

SQL Server Compact Code Snippet #20 : change database password

The SqlCeEngine class (in the System.Data.SqlServerCe ADO.NET provider) includes a number of useful methods for handling global database chores, like verifying the integrity of the database, and shrink the database file. It also includes the Compact method, which creates a new database file (optionally in-place) of an existing database file. During this process, you can specify a number of database creation parameters, namely the collation order, the case sensitivity or the password of the database. In code:

    using (SqlCeEngine engine = 
new SqlCeEngine(@"Data Source=C:\temp\mysecretdb.sdf;Password=oldpassword"))
{
engine.Compact("Data Source=;Password=newpassword");
}


Notice the special data source key used, specifying that the new database should be created in-place.

Saturday, September 21, 2013

SQL Server Compact Code Snippet #19 : migrate a SQL Server database to SQL Compact

This snippet again demonstrates my SQL Server Compact scripting API, some initial guidance here. For a blog post describing the opposite direction, see this. Notice, that is you are scripting a SQL Server Compact 4.0 database file, you can install the required DLL files via NuGet (ErikEJ.SqlCEScripting).

This time I will demonstrate how to migrate a complete SQL Server (LocalDB/Express/Full) database to SQL Server Compact. The requirements are simply that the current user has read access to the SQL Server database. Then all tables, constraints, indexes and data will be moved to an empty SQL Compact database, all in just 6 lines of code:

using (IRepository serverRepository = new ServerDBRepository4(@"Data Source=.;Trusted_Connection=true;Initial Catalog=Chinook"))
{
string fileName = Path.GetTempFileName();
var generator = new Generator4(serverRepository, fileName);
generator.ScriptDatabaseToFile(Scope.SchemaData);

var helper = new SqlCeHelper4();
var sqlCeConnectionString = @"Data Source=C:\temp\newdb.sdf";
helper.CreateDatabase(sqlCeConnectionString);

using (IRepository sqlCeRepository = new DB4Repository(sqlCeConnectionString))
{
sqlCeRepository.ExecuteSqlFile(fileName);
}
}



The code requires the following using statements:


using ErikEJ.SqlCeScripting;
using System.IO;


The ServerDBRepository constructor simply requires any valid SQL Server ADO.NET connection string.


The ScriptDatabaseToFile creates a script file with all content of the database, and the ExecuteSqlFile method runs the script against a SQL Server database.


Notice the use of the SqlCeHelper4 class, which creates an empty database file.

Thursday, September 5, 2013

Primeworks, supplier of tools for SQL Server Compact, is closing, making all products free, open source

João Paulo Figueira, owner of Primeworks, http://primeworks-mobile.com/, just announced that the company is closing. In a blog posting João announced yesterday: “The business volume has decreased so steeply that it can no longer support the product development and support.”

Primeworks offers complete set of excellent tools for working with all versions of SQL Server Compact database files, both on the desktop and directly on a Windows Mobile/CE device.

Luckily, the tools will continue to be available for download, and eventually even become open source.  João states: “In the very near future, all products will be removed from our online business platform and the licensing requirements removed from the distribution installers. All products will be essentially free (copyrights retained). Next, I will devote some time to publish all the source code and convert these products into open source projects.”

I am sorry to see Primeworks leave the market, but very happy that the excellent tools will now become available for free.

Monday, August 26, 2013

FAQ: Why is opening my SQL Server Compact database slow?

You may experience, that opening a connection to a SQL Server Compact database file using SqlCeConnection.Open() (or doing this via EntityFramework or LINQ to SQL) can be unexpectedly slow (up to 30 seconds, for example). In this blog post I will detail the known reasons for this to occur, and outline solutions and workarounds.

1: The database has been created on another platform

One of the most common issues is that the database file has been created and populated on another platform than the one where the app is running .Open(). For example: a database file is included with the app, a database file is downloaded from a server or similar. Different platforms are for example Windows 8 versus Windows XP, and Windows 7 versus Windows Mobile. Notice, that Windows XP, Server 2003 and Windows Mobile are compatible platforms. And Windows 8 and Windows Phone appear t be compatible in most cases also. The product team did a blog post about the issue many years ago, but the issue is otherwise poorly documented, and therefore pops up again and again. There is no solution to the issue, only workarounds.

Workarounds:
Create the database file on the platform after app installation, using for example my scripting APIs.
Include a database file per target platform, that is already built/opened on the target platform (in particular an issue for read-only databases, see my blog post here)
- Avoid indexes with string based keys (probably not possible in most cases)

2: The ACL (Access Control List) on the RSA folder is corrupt

This issue appears to happen only on Windows XP system and only with encrypted databases. The ACL on the C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys folder are corrupted, and users are unable to access file in this central folder.

Solution: Reset the ACL to allows all users Read access, either using the UI or via the xcacls command line tool.

3: Invalid Internet Proxy configuration

This appears to occur for SQL Server Compact 4.0 on any system with an invalid proxy configuration, as a Certificate Revocation List check is run each time the engine is loaded (which happens on the first call to .Open()).

Solution: To avoid this delay, which probably affects any signed app on the system in question, you must fix the configuration or disable the check. The check can be disabled via UI or via registry settings, as described here.

Hope this helps you troubleshooting slow database opening with SQL Server Compact.

Friday, July 19, 2013

SQL Server Compact Code Snippet of the Week #18 : handling SqlCeExceptions

Often I see developers complain that the error messages from SQL Server Compact does not provide any information about the error (in SqlCeEcxeption.Message). This is due to to way that the SqlCeException object is shaped, as it contains a SqlCeErrors collection and other properties you must inspect in order to get “full disclosure” on the error information. Getting this complete information of course helps a lot in troubleshooting the root causes for the errors. (The exact same pattern applies to SqlException, which also has a collection of SqlCeErrors)

Remember in your error handling to catch any specialized exceptions (like SqlCeException) before the more general Exception object:

            try
{
//Data access code here
}
catch (SqlCeException scx)
{ }
catch (Exception ex)
{ }




For getting full error information, I use the code below:

        public static string ShowErrors(System.Data.SqlServerCe.SqlCeException e)
{
System.Data.SqlServerCe.SqlCeErrorCollection errorCollection = e.Errors;

StringBuilder bld = new StringBuilder();
Exception inner = e.InnerException;

if (!string.IsNullOrEmpty(e.HelpLink))
{
bld.Append("\nCommand text: ");
bld.Append(e.HelpLink);
}

if (null != inner)
{
bld.Append("\nInner Exception: " + inner.ToString());
}
// Enumerate the errors to a message box.
foreach (System.Data.SqlServerCe.SqlCeError err in errorCollection)
{
bld.Append("\n Error Code: " + err.HResult.ToString("X", System.Globalization.CultureInfo.InvariantCulture));
bld.Append("\n Message : " + err.Message);
bld.Append("\n Minor Err.: " + err.NativeError);
bld.Append("\n Source : " + err.Source);

// Enumerate each numeric parameter for the error.
foreach (int numPar in err.NumericErrorParameters)
{
if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
}

// Enumerate each string parameter for the error.
foreach (string errPar in err.ErrorParameters)
{
if (!string.IsNullOrEmpty(errPar)) bld.Append("\n Err. Par. : " + errPar);
}
}
return bld.ToString();
}

Saturday, June 29, 2013

SQL Server Compact Toolbox 3.4–Visual Guide of new features

After nearly 190.000 downloads, version 3.4 of my free, open source 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


Data compare

The major new feature of this release is the table based data compare feature, that will generate a script with INSERT, UPDATE and DELETE statements to make two tables contains the same data. The two tables must have compatible schemas and same names. The feature works across both SQL Server and SQL Server Compact tables.

To try out this new (beta) feature, right click on a table and select “Compare Data…”:

clip_image002

Select the target database:

clip_image003

A script with the required statements will then open in the SQL editor.

Database Information

This feature will script information about the selected database in the SQL editor, both general information about the database, including Locale ID and case sensitivity, and also list number of rows for all user tables.

clip_image004

clip_image005

Maximum column width in Edit grid

This new option allows you to set a limit on the column width in the edit grid, useful if you have some columns with very long text string, and you want them all to be visible.

clip_image007

After setting the option to for example 200 pixels:

clip_image009

Visual Studio 2013 support

Server Explorer in Visual Studio 2013 no longer supports SQL Server Compact 4.0, and other tools that depend on Server Explorer (DDEX) will no longer work with SQL Server Compact 4.0. However, you can still use the SQL Server Compact Toolbox in Visual Studio 2013, both with version 3.5 and 4.0 database files. The only requirement is that you have the relevant SQL Compact runtime MSIs installed. In addition, the Toolbox supports code generation of LINQ to SQL DataContext classes, both for Windows Phone 7.5/8 and Desktop apps. For Entity Framework, no code generation is required provided you use the Code First workflow.

clip_image010

Other improvements and bug fixes

Validate Connections improved
Merge Replication save properties fixed
CSV import unicode issue fixed + improved error message
milliseconds included in datetime in text results
SQLite script contained GO separators, they are no longer there
Avoided scripting spatial indexes (SQL Server scripting)

Sunday, June 23, 2013

SQL Server Compact Code Snippet of the Week #17 : using wildcards with a parameterized query

This “week”’s code snippet simply demonstrates how to use a parameterized query with LIKE and a search string containing wildcards. The simple solution is basically to add the wildcard character (% or ?) directly to the search string.

public static List<string> GetCompletionList(string prefixText = "%orch%")
{
//TODO Add error handling
List<string> Names = new List<string>();
using (SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\projects\Chinook\Chinook40.sdf"))
{
con.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT Name FROM Artist WHERE Name LIKE @Name", con))
{
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 120).Value = prefixText;
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Names.Add(reader[0].ToString());
}
}
}
}
return Names;
}

Wednesday, June 19, 2013

SQL Server Compact Code Snippet of the Week #16 : detect Entity Framework database type

This weeks code snippet is prompted by a StackOverflow question, regarding how to detect if the database that was backing an Entity Framework DbContext was SQL Server Compact.

My proposed check simply uses the "is" operator to determine if the Database.Connection property is compatible with the SqlCeConnection class.

so my check is:

context.Database.Connection is SqlCeConnection

Monday, June 3, 2013

INSERTing many rows with Entity Framework 6 beta 1 and SQL Server Compact

In this blog post I will demonstrate a couple of improvements for adding many entities to a Entity Framework based database. You can read more about the beta 1 release here, and Julie Lerman highlights some of the features that were available in the alpha here. For all full list of EF 6 features, see the list here.

Here we will look at getting started with Entity Framework 6 beta 1, and a couple of improvements that makes adding many rows to a SQL Server Compact database via Entity Framework feasible, and also have look at using my SqlCeBulkCopy library to do the same.

I will use a console app for this project in order to focus on the Entity Framework code. To get started, launch Visual Studio, and create a new Console Application. Lets call it EF6Test.

image

Now let’s add Entity Framework 6 beta 1 SQL Server Compact package. Launch the NuGet Package Mangager Console (from Tools, Other Windows) and run this command:

PM> Install-Package EntityFramework.SqlServerCompact -Pre

(The –Pre switch allows you to install pre-release packages)

You should now see several messages in the window, the last one being:

Successfully added 'EntityFramework.SqlServerCompact 6.0.0-beta1-20603' to EF6Test.

This process has added a number of DLL references to the project, and added an app.config file to the project, with an entityFramework section that specifies the SQL Server Compact default connection factory:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="System.Data.SqlServerCe.4.0" />
    </parameters>
  </defaultConnectionFactory>
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
  </providers>
</entityFramework>

Now add a using statement:

using System.Data.Entity;
And add the following 2 classes before “class Program”, these define our single test table and our DbContext:
    public class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
}

public class StudentContext : DbContext
{
public DbSet<Student> Students { get; set; }
}

Now add the following code to the Main method:


   1:              Stopwatch sw = new Stopwatch();
   2:              bool useSqlCeBulkCopy = false;
   3:              var students = CreateStudents();
   4:   
   5:              Database.SetInitializer(new DropCreateDatabaseAlways<StudentContext>());
   6:              
   7:              using (var db = new StudentContext())
   8:              {
   9:                  db.Database.Initialize(true);
  10:                  if (!useSqlCeBulkCopy)
  11:                  {
  12:                      sw.Restart();
  13:                      //AddRange rulez, no need for db.Configuration.AutoDetectChangesEnabled = false;
  14:                      db.Students.AddRange(students);
  15:                      sw.Stop();
  16:                      
  17:                      Console.WriteLine(
  18:                          "Added 8000 entities in {0}", sw.Elapsed.ToString());
  19:                      
  20:                      sw.Restart();
  21:                      int recordsAffected = db.SaveChanges();
  22:                      sw.Stop();
  23:   
  24:                      Console.WriteLine(
  25:                          "Saved {0} entities in {1}", recordsAffected, sw.Elapsed.ToString());
  26:                      
  27:                  }
  28:                  Console.ReadKey();
  29:              }
  30:          }
  31:   
  32:          private static List<Student> CreateStudents()
  33:          {
  34:              var students = new List<Student>();
  35:              for (int i = 0; i < 8000; i++)
  36:              {
  37:                  var student = new Student { Name = Guid.NewGuid().ToString() };
  38:                  students.Add(student);
  39:              }
  40:              return students;
  41:          }



The CreateStudents method simply creates a List object with 8000 Student objects. A new database is created on each run (line 5) and the students are added to the StudentContext DbContext, using the excellent new AddRange method, similar to the LINQ to SQL InsertAllOnSubmit method. With EF5 you only had the Add method, and to get reasonable performance, you had to use the cryptic db.Configuration.AutoDetectChangesEnabled = false statement.


With SQL Server Compact and EF5, inserting 8000 rows takes about 58 seconds on my PC, and it may even time out on yours… Thanks to the fact that Entity Framework is now open source on CodePlex I was able to submit  a bug fix, which  got accepted for EF6, so the process now takes about 8 seconds on my PC.


To compare, let’s add the SqlCeBulkCopy NuGet package and perform the same process using that. In the Package Manager Console. type


PM> Install-Package ErikEJ.SqlCeBulkCopy


Before Console.ReadKey, paste the following code:

                else
{
using (SqlCeBulkCopy bcp = new SqlCeBulkCopy(db.Database.Connection.ConnectionString))
{
bcp.DestinationTableName = "Students";
sw.Restart();
bcp.WriteToServer(students);
sw.Stop();

Console.WriteLine(
"Saved {0} entities using SqlCeBulkCopy in {1}", students.Count, sw.Elapsed.ToString());
}
}




And set useSqlCeBulkCopy = true.On my machine this takes about 150 ms! So despite the improvements made in EF6 beta 1, for larger data loads, I suggest you use SqlCeBulkCopy, and as you can see from the code above, it is very easy to integrate in an Entity Framework context.


You can download the completed project from here: http://sdrv.ms/18NaRmW