Much Ado About Data
Bringing SQL Server 2005 Compact Edition to the desktop can be a wonderful thing -- if you keep your versions straight.
"Previously on 'NYPD Blue'..." I always loved that part of the
show. So, if you remember from my previous
, the most important thing in getting connected to a SQL Server 2005
Compact Edition database is the connect string:
Would it confuse you if I told you that the connect string could also look like this?
On a device, there's no concept such as drive letters so at first glance this connect string might seem odd. But remember that the SQL Server 2005 Compact Edition can also host its files on a desktop computer in addition to devices.
Other than the connection string, everything else about using the CE database
on the desktop is the same (see Listing
The beauty of this is that you can share a code base that reads and writes
to the CE database across platforms. You can use this when you want to use the
data collected on the device with a desktop application, for example.
Speaking of the desktop, it's also possible to use the System.Data.SqlClient
namespace on the device to directly query a SQL Server database (see Listing
Notice also that it's possible, on the device, to call stored procedure in
SQL2005. The fact of "no stored procedures in Compact Framework" still
exists, but in this case you're not accessing the compact database but a SQL
Server database; therefore, you can use any functionality of the server-side
The biggest caveat when it comes to using a direct connection to a SQL database
lies in the connected state of the device. If the connection is of poor quality,
then your device will have a difficult time getting, or sending, complete data
from/to the server. This type of data transfer is best done when the device
has a hard connection to the network either through a WAN connection or via
an ActiveSync connection through a desktop system network connection.
There's Always Something
In this series of articles, I've talked about SQL Server 2005 Compact Edition
in a version-agnostic manner. But it's important to bring up one version-related
problem that you may encounter.
SQL CE 3.5 is currently in beta and SQL CE 2 is the production version of the
Compact Edition. This is the scenario: You open SQL Management Studio, then
create and deploy a database to your application. You run your application from
Visual Studio and get an error about your database requiring an upgrade. No
problem, you say, so you open the database in SQL Management Studio to do the
upgrade...but you can't.
What's happening here is that the assemblies on the device are SQL CE 3.5 instead
of 2.0, which SQL Management Studio used to create the database. One way to
perform the database upgrade is in your code, which looks very much like what's
shown in Listing
As you can see, it's a simple coding hack to upgrade to 3.5. But here's the
rub: 3.5 has enough differences internally -- after all, it is part of SQL Server
2008, so you would expect differences -- such that SQL Server Management Studio
2005 will not open the database file. This leaves you with the unenviable task
of either making database changes through the QueryAnalyzer on the device or
getting involved with the SQL beta.
I'm not saying that this is a bug or something that Microsoft has done just
to trip us up. I just want to point it out as one of things you may run into
as you move from one version of a product to another.
So where does that leave us now? We have a database that we can use either
on a device or on the desktop. We can manipulate data to the database from either
the device or the desktop, and we can make calls directly to SQL Server when
we're connected. In the coming weeks, we'll look at how you can get data from
or to your device when it's in its most common state, mostly disconnected.
Dan Fergus is the chief architect at Forest Software Group, developing .NET applications, including Pocket PC sports team applications. He speaks at major conferences, does consulting, and teaches Compact Framework, VB.NET, and ASP.NET courses. He coauthored The Definitive Guide to the .NET Compact Framework (Apress). Reach him at email@example.com.