Saturday, August 26, 2006 10:46 PM bart

Getting started with SQL Server 2005 Everywhere Edition

Introduction

Last week I had the requirement for a small footprint database on computers to store information in. An XML file was one of the possibilities I was considering but some rich querying capabilities and some relational characteristics in the requirement made me look a little further. Some time ago I blogged about SQL Server 2005 Everywhere Edition, and finally last week I started to use it. A report...

What's in a name?

SQL Server 2005 Everywhere Edition is what it says it is: a version of SQL Server that can run virtually everywhere. Basically, the technology is the evolution of the SQL Server Mobile Edition that you could run on mobile devices, also known as SQL CE. SQL Server 2005 Everywhere Edition goes a little further and can be loaded in-process inside an application as kind of an "invisible relational store" with a very low memory footprint of about 5MB.

A list of interesting features includes:

  • High performance query processor and storage engine
  • Transactional integrity (ACID)
  • 128-bit file-level encryption
  • Low on-disk footprint of 2MB and easy deployment using MSI, ClickOnce or xcopy
  • Remote Data Access (RDA) to synchronize with SQL Server 2005
  • Maximum database size of 4 GB
  • Management of databases via SQL Server 2005 Management Studio
  • Runs on all recent Windows desktop platforms and Windows Mobile devices
  • Easy to use managed API similar to System.Data.SqlClient

You can download the CTP build as well as the Books Online on http://www.microsoft.com/sql/ctp_sqlserver2005everywhereedition.mspx.

Getting started

Time to do some coding in Visual Studio 2005. The first thing to do when working with SQL Server 2005 Everywhere Edition is to add a reference to the managed assembly that comes with the product. It's called System.Data.SqlServerCe.dll and lives in the installation folder (typically %programfiles%\Microsoft SQL Server Everywhere\v3.1). The current CTP build number is 3.0.5235.0. You are free to redist the .dll files of Everywhere Edition with your application (see the EULA file).

Step 1 - Creating a database file

Before we can work with a database, we need to create one of course. This is done programmatically as follows:

using System.Data.SqlServerCe;

//...

string connStr = "Data Source={0};Password={1};";
connStr =
String
.Format(connStr, file, pwd);

using (SqlCeEngine engine = new SqlCeEngine
(connStr))
{
   engine.CreateDatabase();
}

This assumes you have defined two variables: file (e.g. c:\temp\bla.sdf) and pwd (e.g. <whatever you choose>).

Step 2 - Defining the database schema

An easy way to define the database schema (tables etc) for a new (simple?) database is to use the SqlCeCommand class as shown below.

using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
   string sql = "CREATE TABLE Computers (Computer nvarchar (255) NOT NULL PRIMARY KEY, MacAddress nchar (17), DateAdded datetime NOT NULL DEFAULT (getdate()), Approved bit NOT NULL DEFAULT 0)"
;

   SqlCeCommand cmd = new SqlCeCommand
(sql, conn);

   conn.Open();
   cmd.ExecuteNonQuery();
}

Step 3 - Using the database

The database has been defined, time to use it. Nothing special in here: just do the same things as you did with System.Data.SqlClient. Usage of a SQL Server 2005 Everywhere Edition database in .NET is very similar to using big brother System.Data.SqlClient. Commonly used types include SqlCeConnection, SqlCeCommand, SqlCeTransaction, SqlCeDataAdapter, SqlCeDataReader, SqlCeParameter, SqlCeResultSet. All should look more or less familiar if you've been working with the SqlClient in the past.

How to store the password?

One additional difficulty might be storage of the password. The best solution would be not to store it and ask it to the user. If you can, you should choose this solution. However, in the context of a Windows Service that's not possible to do (and if it would, it would be a showstopper to boot the service requiring a password every time). An empty password or a hardcoded password are no options either; data theft would be made very easy if that was the case. So, a random password seems to be the best option. Upon creation of the database, a random password is generated as follows:

private string GeneratePassword(int n)
{
   StringBuilder sb = new StringBuilder
();
   RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider
();
   byte[] b = new byte
[4];
   for (int
j = 0; j < n; j++)
   {
      rng.GetBytes(b);
      int i = Math.Abs(BitConverter
.ToInt32(b, 0));
      sb.Append(pwdChars[i % pwdChars.Length]);
   }
   return
sb.ToString();
}

In this code, pwdChars refers to an array containing valid password characters. You should certainly include all alphanumeric ones but you need to be a little careful with some other characters such as ; in order to be able to use it in the connection string. Notice the usage of the RNGCryptoServiceProvider (instead of System.Random) and the StringBuilder (instead of string concatenation, to avoid having multiple partial copies of the password in memory). Usage of SecureString would be a better option, but SQL Server 2005 Everywhere Edition can't deal with it.

One last question that remains is how to store the password. The answer can be the registry but an additional encryption using DPAPI is highly recommended. This is done using the ProtectedData class. To deal with strings, we're going to work with base64 encoding as shown below:

private string applicationEntropy = "Supply some entropy here. A famous quote perhaps?";

private string
Encrypt(string
plain)
{
   byte[] bPlain = Encoding
.UTF8.GetBytes(plain);
   byte[] bEntropy = Encoding
.UTF8.GetBytes(entropy);

   byte[] bCipher = ProtectedData.Protect(bPlain, bEntropy, DataProtectionScope
.LocalMachine);

   return
Convert.ToBase64String(bCipher);
}

private string Decrypt(string
cipher)
{
   byte[] bCipher = Convert
.FromBase64String(cipher);
   byte[] bEntropy = Encoding
.UTF8.GetBytes(entropy);

   byte[] bPlain = ProtectedData.Unprotect(bCipher, bEntropy, DataProtectionScope
.LocalMachine);

   return Encoding
.UTF8.GetString(bPlain);
}

I guess that storing something in the registry shouldn't be much of a problem (but: use a low-privileged service account and store the encrypted password in the HKCU hive of the registry).

Manage the database

Managing the database can be done using the SQL Server 2005 Management Studio by connecting to a SQL Mobile type of database as shown below:

General management actions such as setting the password, shrinking and compacting the database and performing a repair operation can be done using the database's properties dialog:

Furthermore you can execute queries against the database, add or modify tables and other objects, etc just as you can do with regular SQL Server 2005 databases.

Happy coding!

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Filed under: ,

Comments

# re: Getting started with SQL Server 2005 Everywhere Edition

Saturday, September 09, 2006 10:50 AM by Tommy Carlier

Nice intro to SQL Server Everywhere. One of the possible scenario's is to use SQL/e as a file format for your application. If you create a word processor, or a similar document oriented app, instead of creating a custom file format, you could use a SQL/e file, where the elements are stored in tables.

# re: Getting started with SQL Server 2005 Everywhere Edition

Tuesday, September 19, 2006 3:29 PM by Jeff

This is an excellent article on SQL Server 2005 Everywhere Edition.  Congrats

# Getting started with SQL Server 2005 Everywhere Edition

Saturday, October 28, 2006 11:36 PM by JRB Technology

Don&#8217;t you hate the fact the Microsoft nearly forced you to use their database system when creating database driven applications? They want you to use the Microsoft SQL Server Desktop Engine when making even the most basic applications. MSDE is a

# Hot releases: .NET Framework 3.0, Windows SDK, 2007 Office System + VSTO, ASP.NET AJAX, SQL Server 2005 Compact Edition

Tuesday, November 07, 2006 8:01 AM by B# .NET Blog

A big date for Microsoft with the release of a lot of exciting technologies, some of which still in beta

# SQL Server 2005 Compact Edition RC1

Wednesday, November 08, 2006 12:49 PM by thirstycrow.net

Evidencing no particular skill, I stumbled upon this little nugget. Release candidate 1 of SQL Server

# Recipe: CF 2.0 and MSSQL Compact Edition 3.1 installations

Thursday, December 07, 2006 7:04 AM by Dan Miser

# Getting Started with SQL Compact Edition (Formerly “SQL Everywhere")

Monday, January 01, 2007 8:38 AM by Guy Burstein's Blog

I have recently joined with a project that looks for a In-Memory Database for client-side caching solution.

# מה הרייטינג שלך?

Monday, March 12, 2007 1:36 PM by Guy Burstein's Blog

פוסט זה הוא פוסט המשך בסדרה " טיפים וטריקים: כתיבת פוסטים באתר הבלוגים ". כשהתחלתי לכתוב בבלוג שלי, פרסמתי

# rimonabantexcellence site title

Sunday, July 21, 2013 9:01 AM by rimonabantexcellence site title

Pingback from  rimonabantexcellence site title