For years Microsoft-oriented developers have struggled with the problem
of what to use for a database in single-user applications with modest data
storage requirements. The choices for this embedded database problem space
have boiled down to Microsoft Access or MSDE (or its current replacement
SQL Server Express), or to non-Microsoft applications such as SQLite or
SQLBase. On the one hand, Access and SQL Server Express are rather
heavyweight when all you want is a simple storage engine that’s easy to
redistribute. On the other, the non-Microsoft alternatives require
bringing another vendor into the picture and worrying about such issues as
long-term support and compatibility with the changing landscape of
development tools coming from Redmond.
Starting this fall there’s going to be a new alternative: Microsoft SQL
Server Everywhere. SQL Server Everywhere provides an extremely lightweight
(under 2MB disk footprint), easy-to-redistribute, free database from
Microsoft with a syntax that’s a subset of full SQL Server Transact-SQL.
While it does have some limitations, SQL Server Everywhere is an ideal
solution for the simple application that just needs to store a bit of data
for a single user, and it’s worth adding it to your list of potential
tools for the future.
Not Really Version 1.0
You can
download the first CTP build of SQL Server Everywhere right now, but
don’t let the version numbering confuse you: this isn’t actually the first
release of this product. Even though this is the first ever build of SQL
Server Everywhere to be released to the wild, this is not a new product.
Rather, Microsoft has taken the SQL Server Mobile codebase (which has been
around for a number of years as a product for Windows Mobile) and rebuilt
it for the desktop.
Or rather, they’ve publicly rebuilt it for the desktop. It turns
out that their internal testing used desktop builds all along. So what we
have here is a relatively mature database engine, and an executive
decision to finally give it away as a means to give Microsoft an instant
presence in the small-footprint embedded space. On the one hand this gives
them a database that you can use consistently across anything from a
PocketPC to (if you really wanted) a clustered Windows server; on the
other, it provides an engine with at least rough-and-ready compatibility
with full SQL Server. And of course, these days, it’s
.NET-programmable.
Understanding the Architecture
So what do you get in this little package? For starters, you get a
compact database engine that supports a respectable subset of SQL Server’s
T-SQL language. That is, while you shouldn’t expect every query you can
write in the full version of SQL Server to run on SQL Server Everywhere,
you should expect any query you write in SQL Server Everywhere to run fine
on full SQL Server if you ever find yourself upgrading a database or
migrating data from an embedded database to a server environment. But
because of the product’s roots in the mobile environment, you get more
than that – in particular, there’s a nice connectivity architecture for
synchronizing embedded databases with server databases.
Whether on a mobile device or a desktop, a SQL Server Everywhere
database can use HTTP, with or without ActiveSync, to replicate data with
a full SQL Server database via IIS. You get full programmatic control over
this connectivity, and can use it for both always-connected and
occasionally-connected scenarios.
The new release also sports good integration with Microsoft’s current
developer toolset. This means a data provider for use with the .NET
Compact Framework and support for ADO.NET on the desktop, management with
SQL Server 2005’s SQL Server Management Studio, and Visual Studio 2005
integration, among other things. If you already know how to use, say, SQL
Server 2005 data with .NET, you should have no trouble making the
transition to SQL Server Everywhere.
Let’s See Some Code
After installing the SQL Server Everywhere CTP, fire up Visual Studio
2005 and create a new C# Windows Forms application. Add a reference to
System.Data.SqlServerCe.dll – this is the main SQL Server Everywhere
engine, which is necessary for your application to use SQL Server
Everywhere databases. Having done this, you can make the rest of the
code a bit simpler by adding a using statement:
using System.Data.SqlServerCe;
The first thing you’ll probably want to do is create a SQL Server
Everywhere database. Like Access databases, SQL Server Everywhere
databases are just files, with (by convention) the extension .sdf. You can
create one by using the SqlCeEngine object:
SqlCeEngine eng = new SqlCeEngine("Data Source='zoo.sdf';");
if (!(File.Exists("zoo.sdf")))
eng.CreateDatabase();
After you’ve got a database, your existing ADO.NET knowledge will come
in handy. The System.Data.SqlServerCe namespace contains
SqlCeConnection and SqlCeCommand objects that
behave as you’d expect. For example, you can create a new table:
SqlCeConnection cnn = new SqlCeConnection("Data Source='zoo.sdf';");
cnn.Open();
SqlCeCommand cmd = cnn.CreateCommand();
string CreateTableString = @"CREATE TABLE ZooAnimals
(AnimalID INTEGER IDENTITY (1,1) PRIMARY KEY,
AnimalName NvarChar(50))";
cmd.CommandText = CreateTableString;
cmd.ExecuteNonQuery();
Insert a row of data into the new table:
SqlCeConnection cnn = new SqlCeConnection("Data Source='zoo.sdf';");
cnn.Open();
SqlCeCommand cmd = cnn.CreateCommand();
string InsertString = @"INSERT INTO ZooAnimals
(AnimalName) VALUES ('Giraffe')";
cmd.CommandText = InsertString;
cmd.ExecuteNonQuery();
And get the data back out:
SqlCeConnection cnn = new SqlCeConnection("Data Source='zoo.sdf';");
cnn.Open();
SqlCeCommand cmd = cnn.CreateCommand();
string SelectString = @"SELECT * FROM ZooAnimals";
cmd.CommandText = SelectString;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string AnimalName = dr.GetValue(1).ToString();
MessageBox.Show(AnimalName);
}
You’ll also find the SqlCeDataAdapter object in the
namespace, making DataSet-based programming possible. The net
result is that if you’ve done any work with Access or SQL Server in .NET,
a few minutes work with IntelliSense or the Object Browser will get you up
and running with equivalent functionality in SQL Server Everywhere.
One Size Doesn’t Quite Fit All
You should understand, though, that SQL Server Everywhere is not meant
to be the database for everything. It’s clearly not meant to handle
complex multiuser scenarios or heavy transaction loads. Here are some
limits to keep in mind as you try to understand where this is the
appropriate technology:
- You can’t use SQL Server Everywhere in ASP.NET applications.
- SQL Server Everywhere files don’t support any code. That means no
stored procedures, views, triggers, macros, or similar objects. - SQL Server Everywhere does not allow multiple simultaneous users
to access the database. - SQL Server Everywhere databases are limited to 4GB of data.
These constraints should give you the general idea: SQL Server
Everywhere is targeted for single-user desktop scenarios, where you’ve got
an application that needs to store data and possibly synchronize it with a
server. Within those bounds, though, it’s a very attractive solution – and
one that you can actively experiment with today.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the Senior Technology Partner for Adaptive Strategy, a
Washington State consulting firm. When
he’s not writing code, Mike putters in the garden on his farm in eastern
Washington state.
