Going Mobile

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 column, the most important thing in getting connected to a SQL Server 2005 Compact Edition database is the connect string:

"DataSource=\Program Files\1105\Data\DBExample.sdf"
 
Would it confuse you if I told you that the connect string could also look like this?

 "DataSource=F:\MobileApps\1105\Data\DBExample.sdf"
 
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 1).

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 2).

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 database.

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 3.

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.

About the Author

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 danf@forestsoftwaregroup.com.

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.