Thursday, March 29, 2012

SQL Server Compact Toolbox hits 100.000 downloads–new release planned

My SQL Server Compact Toolbox add-in for Visual Studio 2010 and 11 beta has now had more than 100.000 downloads. (Actually, more than 105.000, but who’s counting.)

Thanks to all the users of this tool, and your continous feedback. I always think that the most recent release will be the last, but I am already perparing for the next relase, which will add features like:

- Provision a 3.5 database for Sync Framework
- Ability to Migrate to a SQL Server/Express/LocalDB database in a single click (like what you can do with WebMatrix, but from within Visual Studio)
- Support for SQL Server Compact 3.5 connections with Visual Studio 11 beta (to support Windows Phone users and others)
- Option to add a ConnectionStringBuilder class to Windows Phone projects
- When exporting table data, the tables are now ordered topologically
- The Server based DGML files now respect schemas
- Server date and datetime2 columns are now converted to datetime

And in connection with the MVP Summit I got the opportunity to appear on the Visual Studio Toolbox show on Channel 9, where I demo some of the Toolbox features. Maybe you will discover features you did not know about during the demo.

Thursday, March 8, 2012

Windows Phone Local Database tip: Working with encrypted database files

SQL Server Compact on Windows Phone supports encryption of the database file. By specifying a password on the ConnectionString during encryption and subsequent openings, the contents of the database file will be encrypted.
Beware that the encryption will degrade database performance, due to the extra processing required to decrypt and encrypt data.
Also keep in mind when using encryption, that unless you protect your connection string (the password), anyone that knows it can open the database file.

Some Phone developers (see this forum post, for example) have encountered issues when moving databases from device to desktop and vice versa, when encryption is involved. In this blog post I will try to explore what can and cannot be done in this respect.

From the root context menu in the SQL Server Compact Toolbox, select “Add 3.5 connection” and select Create:

image

Specify a password of “zyx” and click OK. Now we have a connection to a desktop encrypted database file.

Open the SQL Editor, and create a basic table:

CREATE Table Test
(
Id int PRIMARY KEY,
Data nvarchar(10)
);

Now create a Windows Phone 7.1 Application, and add the database file from above as Content:

image

Right click the database connection in the SQL Server Compact Toolbox, and select “Add Windows Phone DataContext…” and click OK, the WP7EncryptContext.cs file has now been added to the project.

Add a reference to System.Data.Linq, and compile the project.

Add a button called “Connect”, and add a click event handler, with this code:

            using (WP7EncryptContext db = new WP7EncryptContext(WP7EncryptContext.ConnectionStringReadOnly + ";Password=zyx"))
{
var list = db.Test.ToList();
}



Now run the app in the emulator by pressing F5 (debug):


image


Obviously the desktop encryption is not compatible with the device database engine!


Now remove the database file from the project, and create the database file in isolated storage – change the click event handler code:

using (WP7EncryptContext db = new WP7EncryptContext(WP7EncryptContext.ConnectionString + ";Password=zyx"))
{
db.CreateIfNotExists();
var list = db.Test.ToList();
var test = new Test();
test.Id = 2;
test.Data = "ErikEJ";
db.Test.InsertOnSubmit(test);
db.SubmitChanges();

}



Now the database will be created in Isolated Storage on the device. Run with F5 again, and no errors will occur. And we have “pre-populated” the database with a single row. Let’s try to move this database file to the desktop, I use Windows Phone Power Tools from CodePlex to do this:


image 


When you add a connection to the file form the device, and click “Test Connection”, you will get this error message:


image


We already know that some version 4.0 features have been made available in the 3.5 Windows Phone database engine, like support for OFFSET/FETCH to allow paging. Has the enhanced version 4.0 encryption also been move to the device?


In the Toolbox, select “Add 4.0 Connection” and try to connect to the database from the device:


image


(Remember to specify the password!)


image
Reply Yes! And now we can browse and inspect both schema and data in the file – keep in mind that this file cannot be moved back to the device.


image


Now, GET the file again from the device via the Power Tools, and add the new copy to the project again, also as Content (as above, but the database file comes “pre-populated” from the device.)


Again, change the button click event handler as follows:

using (WP7EncryptContext db = new WP7EncryptContext(WP7EncryptContext.ConnectionStringReadOnly + ";Password=zyx"))
{
var list = db.Test.ToList();
if (list.Count > 0)
MessageBox.Show("It works " + list[0].Data);

}



Conclusions:


You CANNOT:


- Use a desktop encrypted database on the device
- Open a device encrypted database on the desktop


You CAN:


- Prepopulate an encrypted database (but only on a device)
- Convert a device encrypted database to 4.0 format on the desktop, and browse it.


Credits: Thanks to Marc-Anthon Flohr for prompting me to write this blog post. His overview is nice, although his conclusions are slightly different.

Friday, March 2, 2012

SQL Server Compact 4.0 SP1 CTP1 available

(Nice short name!) SQL Server Compact 4.0 SP1 CTP1 is now available for download: http://www.microsoft.com/download/en/details.aspx?id=29037 – is is also installed with Visual Studio 11 Ultimate beta. Installing this .MSI will replace your current SQL Server Compact 4.0 binaries in Program Files, and change the version to 4.0.8854.1.

No blog post or KB article yet, I will update this post when they become available. I the meantime, I have skimmed though the code in the ADO.NET provider, which reveals the following changes (all is just guesses, and it is beta software, of course):

New connection string keyword: “File Access Retry Timeout” – will reattempt connection open from 0 – 30 seconds, with a default value of 0.

Improved local C++ runtime verification.

SqlCeConnection.GetSchema bug fixes

SqlCeParameter.Add(String parameterName, Object value) has been deprecated.  Use AddWithValue(String parameterName, Object value) instead.

According to this thread, this bug has been fixed: Getting exception "The data area passed to a system call is too small." in SQL Server Compact 4