provided by: 
Originally published at Internet.comFor 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.
Author: Mike Gunderloy
Read article at Internet.com site