Thursday, August 20, 2009

Running SQL Compact from CD-ROM (read only media) on all Windows platforms

In order to create a read only application to be run from CD-ROM (or similar) in managed code, there are some issues that must be considered in order to support all current Microsoft Windows platforms (XP (if you can call that “current”) and Vista/Win7), and both x86 and AMD64 (x64) processor architectures.

The user will need to have .NET Framework 2.0 installed, it will already be installed if the OS is Vista or later.

For the SQL Compact runtime engine, simply include the SQL Compact managed and unmanaged DLL files in the application folder. Make sure to set the platform of your .exe to x86, or include both the x86 and AMD64 runtimes. Doing either of these 2 options will allow the SQL Compact engine to run on all Windows x86 and x64 platforms (XP/2003 and later).

For the procedure for including the relevant runtime files in your project, see this blog post: http://blogs.msdn.com/stevelasker/archive/2008/10/22/privately-deploying-sql-server-compact-with-the-ado-net-entity-provider.aspx and this sample from Steve Lasker: Running Compact from Read Only Media (DVD, CD, Locked USB Key).

As reported here by the SQL Compact team (sadly, not before April 1st (!) 2009), the runtime needs to re-create indexes if a SDF file has been moved from Vista/Server 2008 to XP/Server 2003. On read-only media, this cannot happen, and you will get an obscure “Permission denied” error message.

A possible workaround for this would be to open the file on both platforms (using Virtual PC or similar, if need be), and distribute 2 SDF files on the read only media.

In order to open a SDF file form read-only media, you also need to add two additional parameters to the connection string in use: “Mode=Read Only” and “Temp Path=<path>”

The sample code below illustrates how you could implement the 2 paragraphs above when creating your connection string. In addition, the path to the SDF file is dynamically created.

 

using System;
namespace ReadOnly
{
class Program
{
static void Main(string[] args)
{
string connStr;
string sdfName;
// Choose file based on OS version
if (System.Environment.OSVersion.Version.Major < 6)
{
// This SDF was created on XP or Win2003
sdfName = "Northwind5.sdf";
}
else
{
// This SDF was created on Vista or Win7
sdfName = "Northwind.sdf";
}

// Notice the "Mode = Read Only" and "Temp Path=%TEMP%" options added to the connection string
connStr = String.Format(@"Data Source = {0}\{1};Mode = Read Only;Temp Path={2}",
System.IO.
Path.GetDirectoryName(System.Reflection. Assembly.GetExecutingAssembly().GetName().CodeBase),
sdfName,
System.IO.
Path.GetTempPath());
}
}
}

 

Wednesday, August 12, 2009

Getting started with a CodeSmith nettiers data access layer for your SQL Compact database

The CodeSmith code generation tool has recently been updated to support SQL Server Compact Edition 3.5 (from version 5.1), and the nettiers Object/Relational Mapper (ORM) included with the CodeSmith product has also been enhanced with support for creating a data access layer based on SQL Compact. I have posted a feature request that the excellent PLINQO product (which also comes with CodeSmith) should be updated to support SQL Compact.

This blog post demonstrates how to create a data access layer for a Windows forms application, with all the nettiers features, and highlights the special considerations you have to apply when using SQL Compact. It is not a general overview of the nettiers code generation process.

Please note the flowing limitations when using SQL Compact and SQL Compact with nettiers:

- SQL Compact with ASP.NET applications is not supported (SQL Compact engine limitation).

-SQL Compact does not have stored procedures and views (SQL Compact engine limitation).

- Paging with Get… methods is not supported (all rows are returned)

- You must use Enterprise Library version 4.1 (see “Using SQL Server CE” section in link)

First, download and install the latest version of CodeSmith (5.1.4). It includes everything that you need to get started with nettiers and SQL Compact usage.

Code generation

After installation, locate the nettiers templates in the folder shown below, making sure it contains a “DataAccessLayer.SqlCeClient” folder. If not, you can extract the nettiers templates from the samples.zip in the C:\Program Files\CodeSmith\v5.1\Samples folder.

clip_image002

Double click the NetTiers.cst file, and wait for CodeSmith to compile the templates.

Next, set the required configuration properties:

clip_image004

Click the button on the ChooseSourceDatabase grid line.

clip_image006

Click the button next to the Data Source drop down.

clip_image008

Click add to create a data source pointing to your SQL Compact database file.

clip_image010

Give you data source a name, and select the SqlCompactSchemaProvider in the Provider Type dropdown. You can type the SQL Compact connection string below, or use the (simple) connection string builder by clicking the button next to the Connection String text box. For this sample I am using the the repaired Northwind, which Roger Jennings has created for his ADO.NET 3.5 book.

clip_image012

clip_image014

Click OK and Close

clip_image016

Select the Northwind data source and click Select

clip_image018

Back at the CodeSmith properties, I have now selected the Northwind data source, and changed the OutputDirectory and RootNameSpace properties.

In the following I will only mention non-default properties that are recommended for working with SQL Compact.

clip_image020

You must use Microsoft Enterprise Library version 4.1, older versions are not supported with SQL Compact. You can use any supported DotNetVersion (2, 3, 3.5).

I suggest using the ServiceLayer and also recommend creating a Unit test project. This will help you get the proper app.config and entlib.config files for your Winforms project. Remaining properties are left at their defaults.

clip_image022

Do not generate any ASP.NET projects, so I set GenerateWebLibrary, GenerateWebService , GenerateWebsite and GenerateWebSiteAdmin to False.

clip_image024

Set GenerateWinLibrary to True, to get some strong typed RAD User Controls generated for use in our Winforms project. Click Generate to create the Visual Studio 2008 solution and projects.

Your output folder should look like this:

clip_image026

This folder contains the projects that implements the nettiers data access layer, the Unit Test project and the Windows Forms User Controls.

Visual Studio integration

Open the solution (Northwind.sln) in Visual Studio, and add a new Winforms project to the solution, in order to consume the generated code:

clip_image028

Add references to the following projects from the Winforms project:

clip_image030

Your Solution Explorer should look like this now:

clip_image032

Build the solution, and from the Toolbox, add the CustomersDataGridView user control to the form:

clip_image034

Set the Dock property to Fill on the Property Grid for the customersDataGridView1:

clip_image036

Your Form1 now looks like this:

clip_image038

Open the Form1.cs code. Add the following using statements:

using Northwind.Entities;

using Northwind.Services;

Add a Form1_Load event handler, with the following code:

customersDataGridView1.CustomersList = new CustomersService().GetAll();

clip_image040

Notice the other GetBy… methods that have been generated for you, based on indexes and foreign keys in the database.

Finally, you need to add the proper Enterprise Library configuration setting to your application. This can be done by copying the Northwind.UnitTests.dll.config from the UnitTests project to your UI project, and rename this file to app.config.

The SQL Compact specific app.config section looks like this – notice the special providerInvariantName value.

  <connectionStrings>
    <add name="netTiersConnectionString" connectionString="Data Source=C:\NetTiers\Northwind.sdf;" />
  </connectionStrings>

  <netTiersService defaultProvider="SqlCeNetTiersProvider">
    <providers>
        <!--
        *** SqlCeClient Provider ***  this is the Sql Compact .netTiers provider
        connectionStringName: the connection string to the db
        useStoredProcedure: always false, so we use parametrized queries that are embedded.
    -->
      <add
        name="SqlCeNetTiersProvider"
        type="Northwind.Data.SqlCeClient.SqlCeNetTiersProvider, Northwind.Data.SqlCeClient"
        connectionStringName="netTiersConnectionString"
        useStoredProcedure="false"
        providerInvariantName="System.Data.SqlServerCe.3.5"
        entityFactoryType="Northwind.Entities.EntityFactory"
        useEntityFactory="true"
        enableEntityTracking="false"
        enableMethodAuthorization="false"
        defaultCommandTimeout="30"
        />
    </providers>

Also copy the entlib.config file from the UnitTests project to the UI project.

Right click the UI project in Solution Explorer and select “Set as StartUp project”.

Now your solution should look like this:

clip_image042

Press F5 to build and run the application:

clip_image044

In order to save any changes made to the data bound grid, use this single line of code (in an event handler):

new CustomersService().Save(customersDataGridView1.CustomersList);

See the nettiers documentation for more samples on how to use the generated data access layer – and happy coding!

Tuesday, August 4, 2009

Script SQL Server Compact tables in Management Studio just like SQL Server tables

One of the notable features missing from the SQL Server Management tools for SQL Compact is the ability to script DML (data manipulation language) that is: SELECT, UPDATE, INSERT, DELETE and DDL (data definition language): CREATE, DROP, ALTER when browsing SQL Server Compact tables in SQL Server Management Studio (SSMS) (Figure 1 below). Now, 1 year after the release of SQL Server 2008, you can now get the same features for SQL Compact tables (Figure 2 below) by installing the latest version of the free ExportSqlCE add-in for SSMS, available for download on CodePlex.

As always, let me know if there are any issues and/or feature requests by providing feedback here.

image

Figure 1: This is available in SSMS when browsing SQL Server tables.

image

Figure 2: With version 2 of the ExportSqlCE SSMS add-in, this is available when browsing SQL Server Compact tables.

Monday, August 3, 2009

Dealing with very large SQL Compact database files

On the SQL Server support blog, there is a new article, that documents two hot fixes relating to working with “very large” (in this case larger than 128 MB) SQL Compact database files.

SQL Compact Merge Replication “bible” updated

Rob Tiffany has just published the updated version of his SQL Compact Merge Replication book, http://blogs.msdn.com/robtiffany/archive/2009/07/29/enterprise-data-synchronization-with-microsoft-sql-server-2008-and-sql-server-compact-3-5-mobile-merge-replication.aspx. Strongly recommended for anyone who is involved in Merge Replication projects of any size.

SQL Server 2005 Compact Edition downloadable hotfix list

A number of SQL Compact 3.1 hot fixes have been released and are publicly available at the Microsoft support site. The table below gives an overview of the hotfixes. Note that the hotfixes are cumulative, meaning that the latest hotfix contains all previous hotfixes. Some hotfixes are only applicable for a particular platform, however. All hotfixes apply to SQL Compact 3.1, version 3.0.5300.0.

The number column indicates the revision number, so 7 would be version 3.0.5300.7 and so on.

No KB Title Platforms
0 934386 FIX: You cannot use SQL Server 2005 Compact Edition on an ARMV4i-based device that is running Windows CE .NET 4.2 wce4
1 936978

FIX: An access violation occurs when you run the SqlCeEngine.Verify method to check for the consistency of a database in SQL Server 2005 Compact Edition or in SQL Server 2005 Mobile Edition

wce4
1 936487

FIX: Managed applications that use SQL Server 2005 Compact Edition 3.1 may stop unexpectedly on Windows Mobile 5.0 ARMV4i-compatible devices if the location of the SQL Server Compact Edition DLLs changes

wce5, wce5-ppc
1 935769 FIX: Error message when you upgrade a database from SQL Server 2000 Windows CE Edition 2.0 to SQL Server 2005 Mobile Edition: "The database file is larger than the configured maximum database size" wce4-ppc, wce5, wce5-ppc
2 938786

FIX: A .NET Compact Framework 2.0-based device application that calls the SqlCeEngine.Verify method against a SQL Server 2005 Compact Edition database stops responding

wce4, wce4-ppc
3 947002 FIX: Error message when you try to delete the rows from the table in SQL Server 2005 Compact Edition or in SQL Server Compact 3.5: "Major Error 0x80004005, Minor Error 0. Attempted to divide by zero" wce4, wce4-ppc, wce5-ppc, wce5-phone
4 950440

FIX: The application seems to stop responding when an application executes a large query that has parameters in predicates in SQL Server 2005 Compact Edition

x86
5 951932

FIX: Error message when you create a subscription in SQL Server 2005 Compact Edition to a merge publication on SQL Server 2000: "The table name is not valid. [Token line number (if known) = 1,Token line offset (if known) = 22,Table name = <TableName>]"

x86
7 953259 FIX: Error message when you run an SQL statement that uses the Charindex function in a database that uses the Czech locale in SQL Server 2005 Compact Edition: "The function is not recognized by SQL Server Compact Edition" wce4-ppc, wce5-ppc
8 960142

FIX: An error message is logged, and the synchronization may take a long time to finish when you use an application to synchronize a merge replication that contains a SQL Server 2005 Compact Edition subscriber – see this for API changes

wce4, wce4-ppc, wce5, wce5-ppc, x86
9 967963 FIX: Some rows are deleted when you repair a database by using the Repair method together with the RepairOption.RecoverCorruptedRows option in SQL Server 2005 Compact Edition and in SQL Server Compact 3.5 wce4, wce4-ppc
11 968171 FIX: Error message when you try to create an encrypted database of SQL Server 2005 Compact Edition: "The operating system does not support encryption" x86
13 970915

FIX: Error message when you synchronize a merge replication with SQL Server 2005 Compact Edition subscribers: "A column ID occurred more than once in the specification. HRESULT 0x80040E3E (0)"

wce4 (all), wce5 (all), x86
14 971970 FIX: You cannot insert rows or upload changes into the SQL Server 2005 Compact Edition subscriber tables after you run the "sp_changemergearticle" stored procedure or you add a new merge publication article when another article has an IDENTITY column wce4 (all), wce5 (all), x86

 

Please let me know if there are errors or omissions.

SQL Compact 3.5 SP1 downloadable hotfix list

A number of SQL Compact 3.5 hot fixes have been released recently and are publicly available at the Microsoft support site. The table below gives an overview of the hotfixes. Note that the hotfixes are cumulative, meaning that the latest hotfix contains all previous hotfixes. Some hotfixes are only applicable for a particular platform, however. All hotfixes apply to SQL Compact 3.5 SP1, version 3.5.5692.0.

The number column indicates the revision number, so 7 would be version 3.5.5692.7 and so on.

No KB Title Platforms
1 958478 Error message when you run a "LINQ to Entities" query that uses a string parameter or a binary parameter against a SQL Server Compact 3.5 database: "The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses" x86, x64
2 959697 Error message when you try to open a database file from a CD in SQL Server Compact 3.5 with Service Pack 1: "Internal Error using read only database file" x86
3 963060 An error message is logged, and the synchronization may take a long time to finish when you synchronize a merge replication that contains a SQL Server Compact 3.5 subscriber: "UpdateStatistics Start app=<UserAppName>.exe"
UPDATE: More info about required code changes and new api here
x86, wce4, wce4-ppc, wce5, wce5-ppc
5 967963 Some rows are deleted when you repair a database by using the Repair method together with the RepairOption.RecoverCorruptedRows option in SQL Server 2005 Compact Edition and in SQL Server Compact 3.5 wce5, wce5-ppc
7 968864 Error message when you run a query in SQL Server Compact 3.5: "The column name cannot be resolved to a table. Specify the table to which the column belongs" x86, wce5-ppc, wce5-phone
7 969858

FIX: Non-convergence occurs when you synchronize a SQL Server Compact 3.5 client database with the server by using Sync Services for ADO.NET in a Hub-And-Spoke configuration

Same fix as above
9 970269 FIX: Access violations occur when you run an application under heavy load conditions after you install the 64-bit version SQL Server Compact 3.5 Service Pack 1 x64
11 970414 FIX: Initial synchronization of a replication to SQL Server Compact 3.5 subscribers takes significant time to finish wce4-ppc, wce5-ppc
11 971273

You do not receive error messages when you run a query in a managed application that returns columns of invalid values in SQL Server Compact 3.5

wce5, wce5-ppc
12 971027 FIX: Error message when you upgrade a very large database to SQL Server Compact 3.5: "The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only" x86, x64
12 972002 FIX: Error message when you try to create an encrypted database in SQL Server Compact 3.5: "The operating system does not support encryption" x86
13 972390 FIX: The application enters into an infinite loop when you run an application that uses Microsoft Synchronization Services for ADO.NET to synchronize a SQL Server Compact 3.5 database½ x86
14 974068 FIX: Error message when an application inserts a value into a foreign key column in SQL Server Compact 3.5: "No key matching the described characteristics could be found within the current range" x86, x64

 

Please let me know if there are errors or omissions.