Sunday, November 22, 2009

How to save and retrieve Images using LINQ to SQL with SQL Compact

The code below demonstrates several features of working with System.Drawing.Image object together with LINQ to SQL, which maps a byte[] to the System.Data.Linq.Binary type.

In addition, the code also demonstrates how to convert between byte[] and System.Drawing.Image. Hope you find it useful.

 

The CREATE TABLE statement for the sample database looks like this:

-- Script Date: 22-11-2009 15:03  - Generated by ExportSqlCe version 2.2.0.3
CREATE TABLE [Images] (
  [ImageName] nvarchar(100) NOT NULL
, [Image] image NULL
);
GO
ALTER TABLE [Images] ADD PRIMARY KEY ([ImageName]);
GO
CREATE UNIQUE INDEX [UQ__Images__000000000000000A] ON [Images] ([ImageName] ASC);
GO

- thank to Georgi Yankov for inspring this blog entry!

 

using System;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace StoreImagesToSqlCe
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void buttonStoreImageToDb_Click(object sender, EventArgs e)
{
// Open the DataContext
Database1 db = new Database1("Data Source=Database1.sdf");
try
{
// Convert System.Drawing.Image to a byte[]
byte[] file_byte = ImageToByteArray(pictureBox1.Image);
// Create a System.Data.Linq.Binary - this is what an "image" column is mapped to
System.Data.Linq.Binary file_binary = new System.Data.Linq.Binary(file_byte);
Images img = new Images
{
Image = file_binary,
ImageName = "Erik testing "
};
db.Images.InsertOnSubmit(img);
}
finally
{
// Save
db.SubmitChanges();
}
}

private void buttonRetireveImageFromDb_Click(object sender, EventArgs e)
{
// Open the DataContext
Database1 db = new Database1("Data Source=Database1.sdf");

// Get as single image from the database
var img = (from image in db.Images
where image.ImageName == "Erik testing"
select image).Single();
// Convert the byte[] to an System.Drawing.Image
pictureBox1.Image = ByteArrayToImage(img.Image.ToArray());
}

private byte[] ImageToByteArray(System.Drawing.Image imageIn)
{
using (MemoryStream ms = new MemoryStream())
{
imageIn.Save(ms, System.Drawing.Imaging.ImageFormat.Gif);
return ms.ToArray();
}
}

public Image ByteArrayToImage(byte[] byteArrayIn)
{
using (MemoryStream ms = new MemoryStream(byteArrayIn))
{
Image returnImage = Image.FromStream(ms);
return returnImage;
}
}

}
}


 



 

Wednesday, November 11, 2009

Two great tutorials

Recently, 2 tutorials involving SQL Compact have been published.

The first article describes how to use WPF data binding with SQL Compact, and also how to use ADO.NET Sync Services with SQL Compact, Visual Studio 2008 SP1 and SQL Server 2008.

The second article describes how to retrieve the identity from new rows from a SQL Compact database, a problem often encountered when working with SQL Compact and DataSets.

Wednesday, October 28, 2009

Join me for lunch!

I will be hosting a Birds-of-a-Feather Lunch at the PASS  Summit, Seattle, on Tuesday, November 3, 2009 at 11:30am-1:30pm.

My subject of course will be “SQL Server Compact - The little database that could”, where I will be present to discuss the small and big features of SQL Server Compact, and note any ideas, issues and feedback you have for the product. I addition, I would like to hear any wishes that you may have for the next version of SQL Compact for Tools and Programmability. I will gather your ideas and feedback and pass them on to the product team.

See you there!

Sunday, October 25, 2009

New release of ExportSqlCE SSMS add-in available

A new release of my SSMS add-in is available at CodePlex.

It adds 3 new menu items to the Table context menu in Object Explorer:

"Show Table Data" (Edit table data in DataGridView)
"Import" (Import of CSV file - generates INSERT statements)
"Rename" (table name only)

Please try it out, and provide any feedback here.

In addition, I have updated the project documentation.

Some sample screenshots below:

 

fig4

 

fig5

 

fig7

fig8

Monday, October 19, 2009

Microsoft Sync Framework 2.0 released to web!

The Microsoft Sync Framework 2.0 SDK is now available for download.

The major new features in Database Providers include (my highlights in bold)

  • New Database Providers (SQL Server and SQL Server Compact): Enable hub-and-spoke and peer-to-peer synchronization for SQL Server, SQL Server Express, and SQL Server Compact. Sync Framework automatically creates all of the commands that are required to communicate with each database. You do not have to write synchronization queries as you do with other providers. The providers support: flexible initialization options; batching of changes based on data size; and maintenance tasks, such as metadata cleanup and server database restore.
  • Robust Memory-Based Batching: Previous versions of Sync Framework and Sync Services for ADO.NET provided a way for developers to define their own batching logic but there were a lot of limitations, including significant complexity, excessive chattiness, out of memory issues, and restrictions on usage. Sync Framework 2.0 addresses all of these issues by providing a more complete and robust batching API. Developers no longer have to write batching logic themselves because Sync Framework divides changes into batches based on several properties in the API. Batches are now defined by memory consumption rather than the number of rows synchronized, which has eliminated out-of-memory issues for most common scenarios.
  • Provisioning and Management APIs: Provisioning and initialization activities that were previously exposed only through Visual Studio tooling have now been added to the database provider APIs. This includes the ability to provision an existing database by adding the change-tracking tables and triggers that are required by Sync Framework. It also includes the ability to start with an empty database, create the user schema, and provision that schema based on another server or client database that has already been provisioned.
  • Performance Improvements: The new database providers in this release have been thoroughly tested in large-scale scenarios in which a single server supports thousands of clients with hundreds of concurrent synchronization operations. This testing resulted in a large number of internal performance improvements that enable Sync Framework database providers to perform as well as other Microsoft technologies like Remote Data Access (RDA) while offering a wide range of capabilities that compete with end-to-end solutions like merge replication.

The on-line documentation has not been updated yet.

Wednesday, October 7, 2009

Attending PASS Summit

From Nov 2-5, 1,000s of SQL Server pros and I will be in Seattle at PASS Summit. See you there! http://summit2009.sqlpass.org

Friday, September 25, 2009

Is RDA (Remote Data Access) still relevant

Rob Tiffany asks this rhetorical question, and the answer is a resounding YES – there are many sync scenarios, in particular on devices, where RDA is the right technology. Keep in mind, that RDA has not been updated to support the new data types in SQL Server 2008.

Sunday, September 13, 2009

Interview with SQL Server Compact Program Manager Himadri Sarkar

SQL Compact Program Manager Himadri Sarkar was interviewed on the Connected Show in July 2009. He discusses the histroy of SQL Server Compact, from only Windows Mobile/CE support to support for all Windows desktop OSes. He also describes the extensive tooling support both in Visual Studio and SQL Server Management Studio. Listen to the 30 minute interview here. (Starting at about 30 minutes into the show)

Tuesday, September 1, 2009

Which SQL Compact version is included with Visual Studio 2010 beta 1 and SQL Server 2008 R2 August CTP?

The version of SQL Compact included with these beta/CTP releases is 3.5.5692.0 – that means 3.5 SP1 RTM. But there are indications that this will change to a more recent build/SP in VS 2010 beta 2 (and later).

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.

Wednesday, July 8, 2009

ADO.NET Sync Services for Devices sample

A new sample has been made available at CodePlex, SyncComm. This sample/reusable framework demonstrates how to make WCF communication work the best possible way when using Compact Framework. It implements the following features: Correct WCF service configuration, use of zip compression for data transfer and batching of updates. All in order to improve performance and ease developer pain. Good stuff – while we are waiting for version 2 (and later)!

Wednesday, July 1, 2009

SqlCeCmd tutorial part three – query options

This is the third part of a 3 part series with some examples of sqlcecmd usage. The first part is here and the second here. This third part deals with querying database objects and formatting query output. (In other words, running SELECT statements)

To query a table

image

To  get query output, simply use the q- option, followed by the SELECT statement. This will give you a result listing the column names and table data. Note that long fields are truncated to 256 chars, use the xml option if you need all data in the field (see below).

To output to a file

You can use pipe characters (> or | ), or use the –o option to direct output to a file:

image

To avoid column names and dotted lines

image

Use the –h option. The number after the –h indicates the number of rows to print between the column headings. Using 0 (zero) causes the heading not to be printed.

To compress output

image

To make output more compact, use the –s option, which must be followed by the character you wish to use a separator, in this case semicolon. This is combined with the –w option, which removes any trailing spaces from the output to make it more compact.

To output all data as xml

image

Finally, the –x option will output the query results in XML DataSet format.

You can mix the various formatting options (-s, –w and –h) to get the result you desire.

This concludes the 3 part series on using SqlCeCmd to manage SQL Compact databases directly from the command line.

Monday, June 29, 2009

Merge replication performance tips

The SQL Server Support team has posted some advice for improving initial subscription performance with SQL Compact 3.5, which for various reasons has decreased significantly compared to SQL CE 2.0 with SQL Server 2000. Note, that when pre-creating on the desktop you must use Windows XP/Server 2003 and not Vista/Server 2008, or index rebuild will occur on the device.

Wednesday, June 24, 2009

Propose / Vote for new features on CodePlex

If you have any ideas and suggestions for the future versions of the ExportSqlCe command line utility or SSMS 2008 addin, please go here and vote for or propose new features. Any ideas and suggestions are welcome, to the benefit for all users of the tools.

Sunday, June 21, 2009

Scripting SQL datetime fields and avoiding localization problems

If you need to export you data to a .sql file for various reasons, you have different options when “serializing” datetime fields. Often they are scripted as: ‘2009-05-11 11:00’ or ‘May 5 2009 12:00AM’. This will break if the script is run with a tool/server that uses a different locale (like a European/Asian), and cause either run-time errors or even worse, incorrect dates in the database.

The Database Publishing Wizard / Publish to Provider… uses this format:

CAST(0x00008C3300000000 AS DateTime)

which is probably correct and portable, but not very notepad friendly.

I use the ODBC Escape sequence formatting, which looks like this, and works across any locale (and is more friendly on the eye!):

{ts ‘2009-05-11 23:00:00’}

Here’s the .NET code to create this format:

_sbScript.Append("{ts '");
_sbScript.Append(date.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture));
_sbScript.Append("'}");

Sunday, June 7, 2009

Script SQL Compact data and schema directly from SQL Server 2008 Management Studio (Express)

I have just released ExportSqlCE support for scripting SQL Compact data and schema directly from the SQL Server Management Studio Object Explorer. Read more about it here.

Friday, June 5, 2009

SQL Compact version on Windows Mobile 6.5

I have just installed the Windows Mobile 6.5 emulator, and just wanted to check the version of SQL Compact installed on this OS – the version is: 3.0.5300.8, which corresponds to this hotfix: http://support.microsoft.com/kb/960142 (and all hotfixes prior to this build).

Thursday, June 4, 2009

New features in Sync Framework

A 90 minute webcast detailing the new features in the next version of the Microsoft Sync Framework (version 2) is now available for offline viewing and PPT download. The new version with the features presented here will RTM in 2nd half of 2009 (and a CTP will come very soon – June (?)).

Notable new features (relating to SQL Compact) include:

P2P Sync with SQL Compact 23:00– both on desktop and devices – provides ability to Sync 2 SQL Compact databases with each other! Can’t wait to try that out!

Snapshot Initialization 39:40 pre generate the SQL Compact file on the server and then download to the client, and only incremental changes need to be downloaded afterwards. Limits time to get initial data down, and decrease server load. Initially all data is downloaded to the web service in a “source” SQL Compact file, and based on this a special snapshot SQL Compact file is created. The client know that this is a snapshot, and will only download incremental changes after getting the snapshot.

Opt out of ADO.NET DataSets – mapping to a developer defined payload. (Not in version 2)

Using SqlCeResultSet for client side inserts – to improve performance.

UPDATE: CTP2 is now available for download.

Friday, May 29, 2009

Teaser – Scripting SQL Compact in SSMS 2008

How about if you could do this?:

script1

Or this?:

script2 

Keep watching this space…

(Comments/wishes welcome)

Sunday, May 24, 2009

ExportSqlCE 1.9.2 – bug fix

I noticed this forum post, where the poster reports 2 Sync Framework bugs. I knew I was affected by the last one, as I use the same LIKE clause (LIKE ‘__sys%’) to exclude system columns from the ExportSqlCE generated script. I assumed that this clause would exclude all columns beginning with 2 underscores and the letters “sys” – it does indeed, but the like clause also includes any column names that contain the 3 letters, “sys”, like “IsSystem” and “tbSysConfig”  at postion 3. This is due to the fact that the “_” (underscore) is actually a LIKE wildcard character, as documented here.   :-(   -  A possible solution (which is implemented in version 1.9.2), is the following WHERE statement:

SUBSTRING(COLUMN_NAME, 1,5) <> '__sys'

Go and get version 1.9.2 here.

Friday, May 15, 2009

ExportSqlCe 1.9.1 released

This release contains major improvements in performance, I have measured at least 50 % improvement in time to export (on a large database from 12 to 5 seconds).

In addition to performance improvements, the following fixes and changes have been implemented:

- Fixed large file related bug
- Shows execution time in seconds
- Implementation of patch from user rymenbe:
     (performance improvements, by caching column names and keeping a single connection open)
- ran FxCop against the project, fixed various Globalization issues

Go get it. And provide any feedback here.

Tuesday, May 12, 2009

SqlCeCmd tutorial part two – Creating database objects and adding data

This is the second part of a 3 part series with some examples of sqlcecmd usage. The first part is here. This second part deals with creation of database objects and adding data.

To create a table

sqlcecmd -d "Data Source=C:\test2.sdf" -q "CREATE TABLE NewTable (Id int NOT NULL, Info nvarchar(50) NULL) "

To create other objects

To create other objects (foreign keys, indexes) use the appropriate CREATE or ALTER statement as documented in SQL Compact BOL.

To rename a table

sqlcecmd -d "Data Source=C:\test2.sdf"
-q "sp_rename 'NewTable', 'New table' "

So in other words, any valid SQL Compact SQL statement (including UPDATE and DELETE) can be executed form the command line with the “q” option.

To insert data in a table

sqlcecmd -d "Data Source=C:\test2.sdf"
-q "INSERT INTO [New Table] (Id, Info) VALUES (1, N’Test’)"

To run multiple statements in a single “batch”

To run multiple staements in a batch, use the “i” option to specify an input file. The input file should be a text file with the desired SQL statements, each separated by the word GO on a line of it own:

INSERT INTO [New Table] (Id, Info) VALUES (2, N'Test2')
GO
INSERT INTO [New Table] (Id, Info) VALUES (3, N'Test3')
GO
INSERT INTO [New Table] (Id, Info) VALUES (4, N'Test4')
GO

(Contents of c:\input.txt)

Once the file has been created, for example with help from the Database Publishing Wizard (in VS 2008) or ExportSqlCe, you can run the commands in the file like so:

sqlcecmd -d "Data Source=C:\test2.sdf" -i c:\input.txt

Next installment: Querying data…

Monday, May 11, 2009

SQL Compact 3.5 knowledge base articles

This blog entry contains links to the Microsoft public knowledge base articles related to SQL Compact 3.5. For a link to all kb articles relating to SQL Compact, see the link in the left column.

 

Error message when you use the Visual Studio 2008 IDE to manipulate a SQL Server Compact 3.5 Database file which is larger than 128 Megabytes: "The database file that is larger than the configured maximum database size" (968436)

 

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 (970269)

 

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 (969858)

 

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 (967963)

 

FIX: 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" (968864)

 

FIX: 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=CPWerx mobile.exe" (963060)

 

FIX: Error message when you synchronize data for a SQL Server Compact Edition subscriber in SQL Server 2005 and in SQL Server 2008: "A call to SQL Server Reconciler failed. Try to resynchronize. HRESULT 0x80004005 (29006)" (962003)

 

FIX: 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" (959697)

 

FIX: 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" (958478)

 

After you execute the SqlCeEngine.Compact method against a SQL Server Compact 3.5 client database that is synchronized to a central database server, some changes to the client database may not be uploaded (967502)

 

FIX: The SQL Server Compact 3.5 Service Pack 1 update for Windows CE Platform Builder 5.0 is available (956362)

 

Description of cross-version compatibility scenarios for merge replication in SQL Server Compact 3.5 (956208)

 

Description of SQL Server Compact 3.5 Service Pack 1 (955965)

 

Description of the improvements that SQL Server Compact 3.5 Service Pack 1 provides for logging, for enabling Client Agent log settings, and for configuring log file rotation settings (955968)

 

Error message when you try to connect to SQL Server Compact 3.5 Service Pack 1 from SQL Server Management Studio in SQL Server 2008: "Unable to load Microsoft SQL Server Compact" (952218)

 

SQL Server Compact 3.5 Service Pack 1 Server Tools are not installed in WOW mode when you run IIS 7.0 on a 64-bit Windows Server 2008-based computer or on a 64-bit Windows Vista-based computer (955966)

 

Description of the various build versions of SQL Server Compact Edition (950550)

 

A file and some registry entries are missing when you upgrade SQL Server Compact 3.5 Beta to SQL Server Compact 3.5 (945317)

 

You cannot compile a SQL Server Compact-based OLE DB application for Windows Mobile 5.0, and the Transact.h file cannot be found (945374)

 

Exception error message when you use Visual Studio 2008 to deploy a SQL Client-based application to a mobile device or to an emulator: "Missing method Exception" (945371)

 

The column of the rowversion data type is replicated to the subscriber after you synchronize the data between a SQL Server 2005 publisher and a SQL Server Compact 3.5 subscriber (945372)

 

Error message when you try to drag a table from a SQL Server Compact 3.5 database file to a Windows form in Visual Studio 2008: "An error occurred while performing the drop: Exception has been thrown by the target of an invocation" (945375)

 

A smart device application cannot be started on an emulator or on a mobile device if you develop the application on a computer that has Visual Studio 2008 and Visual Studio 2005 installed (945316)

 

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" (947002)

Saturday, April 11, 2009

Recent Sync Framework articles

Links to a few recent Sync Framework articles:

My First Microsoft Sync Framework Application

A tutorial on getting started with the ADO.NET Sync Services Framework

Manage Your Data Effectively With The Microsoft Sync Framework

MSDN Magazne article, that gives an overview of the Sync Framework, and gives some code samples for ADO.NET Sync Services implementation

Walkthrough: Extending the Local Database Cache to Support Bidirectional Synchronization

Important documentation update, describing the steps required to enable 2-way sync (a common requirement in real life)

Wednesday, April 8, 2009

SqlCeCmd tutorial part one – Managing database files

This tutorial will show various samples of using SqlCeCmd, which is available at http://sqlcecmd.codeplex.com – sqlcecmd is the “equivalent” of sqlcmd for the full SQL Server product.

The series will be in three parts, the first one demonstrates how to manage entire database files, the second how to create database objects and insert/update data, the third relating to querying data.

To get information about usage

Simply type “sqlcecmd” at the command prompt:

image

Notice that the –d parameter is always required. It is a SQL Compact connection string as documented here: http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspx

To create a new database file with default settings

sqlcecmd –d “Data Source=C:\test.sdf” –e create

To create a new database file with password and case sensitivity

sqlcecmd
-d "Data Source=C:\test.sdf;Case Sensitive=True;Password=p"
-e create

To upgrade a version 3.0/3.1 database file to 3.5 format

sqlcecmd -d "Data Source=C:\Northwind.sdf" -e upgrade

To compact a database file

sqlcecmd -d "Data Source=C:\Northwind.sdf" -e compact
Similarly you can shrink or repair a database file, with the shrink, repairdelete and repairrecover options.

To change database options

You can change the following four database options on an  existing database: Change password, Change encryption mode, Change locale id, Change case sensitivity

Possible values for encryption mode are:
engine default or platform default or  ppc2003 compatibility

sqlcecmd
-d "Data Source=C:\test.sdf;Case Sensitive=True;Password=p"
-z "Data Source=;Encryption Mode=ppc2003 compatibility;Case Sensitive=false"

To drop a database

del c:\test.sdf

:-)

Stay tune for the next part, creating and modifying database objects.

Crystal Reports with SQL Compact

I stumbled across this post by Jason Irwin, that describes how to solve the mystery of connecting Crystal Reports to data from a SQL Compact file – by elegantly using a dataset as the “middleman” or common language, if you like.

Monday, April 6, 2009

SQL Compact Schema and Data Export utility updated

Version 1.9 fixes 3 bugs reported by users. Thanks and keep looking!

Fixed date formatting bug in INSERT statements (thanks to user spainb)
Fixed missing update and delete rules on multi field foreign keys (thanks to user samunro)
Fixed too many Primary and Foreign keys returned bug (thanks to user samunro)

The utility has now been downloaded more than 1000 times – thanks for your support, and hope you find it useful.

Thursday, April 2, 2009

New SQL Compact 2005 hotfix

A hotfix for SQL Server 2005 Compact Edition (version 3.0/3.1) for 32 bit desktops, that fixes an issue related to encryption is now available:

FIX: Error message when you try to create an encrypted database of SQL Server 2005 Compact Edition: "The operating system does not support encryption" (968171).

Tuesday, March 24, 2009

SQL Compact and Entity Framework woes

Julie Lerman, Entity Framework guru, blogs about issues encountered when using SQL Compact with Entity Framework, including the classical “where did my data go after debugging” issue.

Thursday, March 12, 2009

Tuesday, March 3, 2009

SqlCeCmd alpha 0.2 release available

This release contains the following features and improvements in addition to what is listed here.

1. Added "-e upgrade" option for in-place upgrade from 3.1 to 3.5 database format
2. In query output, NULL values are displayed as "NULL"
3. Better field size detection, by using .GetSchemaTable() method.

Saturday, February 28, 2009

SQL Compact 3.5 SP1 – 2 new hotfixes released

The SQL Compact team has made 2 hotfixes for SQL Compact 3.5 SP1 x86 available. The 2 hotfixes fixes bugs first reported on the MSDN Forum, namely this and this thread.

The first fix, which updates SQL Compact to version 3.5.5692.1) is for an error with the SQL Compact Entity Framework support, resulting in the following error message: The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates.

The second fix (which I assume also includes the first fix, as it updates the SQL Compact version to 3.5.5692.2), fixes a problem with database files on read-only media: Internal Error using read only database file.

You can see a complete list of all SQL Compact 3.5 related KB articles here.

Thursday, February 26, 2009

SqlCeCmd alpha release now available

The initial alpha release of the SqlCeCmd command line query utility is now available on CodePlex. SqlCeCmd is intended as the SQL Compact equivalent to sqlcmd, that is used with SQL Server. SqlCeCmd can be used to manage and query SQL Compact databases without the installation of Visual Studio or SQL Server Management Studio. It can be run from the command line or be called from batch files and scripts.

The utility use the SQL Compact ADO.NET provider, and only requires .NET 2.0 and SQL Compact 3.5 runtime to be installed.

The following features are available in this initial test release:

- Create new database.
- Maintain the database with the SqlCeEngine: Compact, Shrink, Repair and Recover.
- Change database options: Password, locale id, encryption mode and case sensitivity
- Run a query (DDL or DML)
- Run script files generated by ExportSqlCe
- Run any script file with multiple commands separated by “GO”

Options relating to output formatting have not yet been implemented. Please try it out and submit any comments, features requests and proposals on the CodePlex project site.

Saturday, February 21, 2009

SQL Server 2008 Management Studio Express

For managing SQL Compact 3.5 (SP1) database files is now available as a stand-alone download - “only” 170 MB!

Tuesday, February 17, 2009

ExportSqlCe version 1.7 released

This latest release contains the following changes and improvements:

Added Update and Delete Rules on Foreign Keys
Improved error handling
Fixed output file naming bug
Binary and varbinary columns were missing size in CREATE TABLE statement

- and it now has a nice icon :-)

Go and get it here.

Monday, February 16, 2009

SqlCeCmd – SQL Compact Command Line Tool

I have started a new project on CodePlex, http://www.codeplex.com/SqlCeCmd. This project is intended to be the SQL Compact equivalent to the SQL Server SQLCMD utility. It will allow you to administer SQL Compact databases on the desktop without having SQL Server Management Studio or Visual Studio installed. You can review the intended features here. Please provide any comments you may have at the CodePlex site. A quick syntax overview:

sqlcecmd

[ { -d SQL Compact connection string} ]

[ -e shrink | compact | create | repairdelete | repairrecover ]

[ -z new password ]

[ -q "cmdline query" ]

[ -e echo input ]

[ -i input_file ]

[ -o output_file ]

[ -R use client regional settings ]

[ -h headers ][ -s col_separator ] [ -w column_width ]

[ -W remove trailing spaces ]

[ -k [ 1 | 2 ] remove[replace] control characters ]

[ -y display_width ] [-Y display_width ]

[ -b on error batch abort ]

[ -c cmd_end ]

[ -? show syntax summary ]

Sunday, February 1, 2009

SQL Compact 3.5 Schema Provider for CodeSmith now available

I have modified, improved and updated the existing SQL Compact 2005 CodeSmith Schema Provider. It is available here: http://community.codesmithtools.com/forums/p/9303/34491.aspx#34491

New/enhanced features include:

GetDatabaseName:
Now returns the sdf file name without extension

GetTableColumns:
Support for all 3.5 data types, including rowversion
Excludes system columns
Several extended properties added

GetTableIndexes:
Bug fix for same index name on different tables

GetTableKeys:
Added - gets all foreign keys

GetTablePrimaryKey:
Bug fix for returning not only primary keys.

GetTables:
Excludes system tables

Tested with Nettiers 2.3.b2 (with a slightly modified Nettiers.cst).

The Schema Provider in action:

image 

Technorati Tags: ,,

Sunday, January 25, 2009

Binding SQL Compact data to WPF in less than 100 lines of code

In this walkthrough, I will show how you can bind a WPF Listview to data from a SQL Compact database table using LINQ to SQL, and also show how persist changes to the list in the SQL Compact database. I am using Visual Studio 2008 SP1 and SQL Compact 3.5 SP1.

Start by creating a new C# WPF project, called SQLCeWPF. Do not create a directory for the solution. You should now have a folder like this:

image 

Then copy the 3.5 Northwind.sdf from %ProgramFiles%\C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples to the project folder (in my case c:\projects\SQLCeWPF).

Then generate the LINQ to SQL dbml file:

1. Open a Visual Studio 2008 Command Prompt (shortcut under Programs, VS 2008, VS Tools)

2. Make the project directory folder the current folder:

cd c:\projects\sqlcewpf

and run this SQLMetal command:

sqlmetal /dbml:Northwind.dbml Northwind.sdf

and close the command window.

This will generate a LINQ to SQL dbml file in the project folder. Now include this file in your project, select Show All Files in Solution Explorer and include the dbml file (do NOT include the sdf file).

In the WPF Designer, add a listbox, a textbox and 3 buttons to the form. Rename the buttons from buttonx to add, delete and save. The form now looks like so in the designer:

image

Add event handlers for the 3 buttons in the code behind file by double clicking each button.

Now add a class file to the project, name it ObservableShippers.cs. This class connects the UI to the LINQ to SQL generated datacontext, and persists changes to the database.

using System;
using System.Linq;
using System.Collections.ObjectModel;
using System.Data.Linq;

namespace SQLCeWPF
{
public class ObservableShippers : ObservableCollection<Shippers>
{
private Northwind dataDC;
private bool ignoreEvents;

public ObservableShippers()
{
this.ignoreEvents = true;
this.dataDC = new Northwind("Data Source=C:\\Projects\\SQLCeWPF\\Northwind.sdf");
var shipperList = from shipper in dataDC.Shippers
orderby shipper.CompanyName
select shipper;
// Cannot add multiple items to ObservableCollection in single step :-(
foreach (Shippers shipper in shipperList)
{
this.Add(shipper);
}
this.ignoreEvents = false;
}

protected override void OnCollectionChanged(System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
{
if (!ignoreEvents)
{
switch (e.Action)
{
case System.Collections.Specialized.NotifyCollectionChangedAction.Add:
foreach (Shippers shipper in e.NewItems)
{
dataDC.Shippers.InsertOnSubmit(shipper);
}
break;
case System.Collections.Specialized.NotifyCollectionChangedAction.Remove:
foreach (Shippers shipper in e.OldItems)
{
dataDC.Shippers.DeleteOnSubmit(shipper);
}
break;
case System.Collections.Specialized.NotifyCollectionChangedAction.Replace:
break;
default:
break;
}
}
base.OnCollectionChanged(e);
}

public void Save()
{
if (this.dataDC != null)
{
this.dataDC.SubmitChanges();
}
}
}
}

 







This class inherits from ObservableCollection, which exposes the OnCollectionChanged event. Handling this events allows synchronization with the LINQ to SQL DataContext.



Now bind the WPF form to this collection like so, in the Window1.xaml.cs file (add the lines in bold):



private ObservableShippers shippers;



public Window1()

{


    InitializeComponent();


    ShowShippers();


}



private void ShowShippers()

{


      shippers = new ObservableShippers();


       this.listBox1.ItemsSource = shippers;


}



Press F5 to run the app, and it should look something like this:



image



(Don’t worry - We fix the Shipper name later).



Now add the button event handler code:



Add button:

if (textBox1.Text.Length > 0)

{


    Shippers newShipper = new Shippers();


    newShipper.CompanyName = textBox1.Text;


    shippers.Add(newShipper);


    textBox1.Text = "";


}



Delete button:

if (this.listBox1.SelectedItem != null)

           {


               shippers.Remove((Shipper)listBox1.SelectedItem);


           }



Save button:

shippers.Save();

ShowShippers();



Now to show the Supplier name in the list, override the Shippers class ToString() method. To do this, add  a new class file to the project, called Shippers.cs with the following content:



namespace SQLCeWPF

{


    public partial class Shippers


    {


        public override string ToString()


        {


            return this.CompanyName;


        }


    }


}



Now try to run the app. Shippers can be added and deleted from the Shippers table using WPF, and the project does not contain any reference to System.Data.SqlServerCe. All in less than 100 lines of (your own) code. The complete sample can be downloaded from here:



http://cid-47e59e4beb70472b.office.live.com/self.aspx/SQL%20Compact/SQLCeWPF.zip



Thanks to these 2 CodeProject articles for inspiration:



http://www.codeproject.com/KB/WPF/binding_in_linq-sql.aspx



and



http://www.codeproject.com/KB/linq/Compact_LINQ.aspx



Technorati Tags: ,