Accessing Mobile SQL Databases in VB2005 - Windows Apps

I have both used the Data Connection Wizard and tried programatically to
connect to a Mobile SQL Database from within a Windows Application and keep
on getting the same Error at the connection point

"Unspecified error [ sqlcese30.sys.dll ]"
The Debug message is

" A first chance exception of type 'System.Data.SqlServerCe.SqlCeException'
occurred in System.Data.SqlServerCe.dll"

I have added a reference to
C:\ Program files\Microsoft Visual Studio 8\Common7\IDE\Public
assemblies\System.Data.SqlServer.Ce.dll.

I need to access the data from the pocket pc on a desktop computer. Is this
possible under VS2005 using VB or do I in actual fact need to purchase SQL
Server 2005 (not acceptable !!!) to achieve this. It was easy in VB6 using ActiveSync to move from cdb to mdb but there seems to be no such converter for sdf to mdf and no apparent ability to open sdf from within Windows Apps.

On reading the posts in this forum this topic seems to be a familiar problem
- access to SQL data between window apps and mobile apps.

Any help is greatly appreciated.

Regards
David

Code enclosed

Dim FileSpec As String = "Data Source = C:\Program Files\Microsoft
Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf"
Dim MyConnection As New SqlServerCe.SqlCeConnection(FileSpec)
MyConnection.Open()
Dim da As New SqlServerCe.SqlCeDataAdapter("SELECT * FROM Orders",
MyConnection)
Dim ds As DataSet = New DataSet
da.Fill(ds, "Orders")

David,

The second half or so of this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=19879&SiteID=1

addresses this question.

Remember, you cannot use SQL Mobile in a WinForms app unless it is running on Windows XP Tablet PC Edition.

-Darren|||

Darren

Thanks for your prompt reply. A couple of comments which I invite you to respond to.

I have been programming using Microsoft products for 32 years but held off converting to .Net because it was too complicated. VB6 served my requirements well. Now I find that a simple task such as connecting a desktop application to a mobile device database is not available without purchasing SQL Server.

Whats going on?

Even if you had an export facility in mobile development. My understanding is that you cannot write sequential files (eg csv) in mobile application development.

I have spent 2 weeks on various programming forums searching for an answer including MS forums. Nobody had one because the answer as I now understand is NO!!! If you purchase VS 2005 to develop mobile database applications you cannot maintain them on your desktop application (unless you additionally purchase SQL Server at $3700 +). On looking at your reply and the link it is very obvious that there is no license to supply a desktop application to manipulate an SDF file within VS 2005.

In making this post you must understand that I am very disappointed in Microsoft for 2 reasons

1. It appears that the company is very elusive about help in achieving the solution to Mobile Data Access on desktop. Just look at the various forums with similar questions.

2. How could you sell VS2005 as a solution for Mobile device development but not include support for access to Mobile SQL (except on mobile devices). There's not even an option to develop in Mobile Access (CDB). At least Activesync converted these to MDB. Where's the conversion from SDF to MDF?

Please shoot me down in flames if my assumptions are incorrect and accept my apologies in advance.

I invite others to comment on my post also.

Regards
David

|||Darren

I have tried the workaround (non licenced) that was described in the link and copied the files into the application \bin directory and added the reference before setting the data source and still have the same errors as described in my first post.

David|||I've also been looking forward to a solution on this limitation for about 2 weeks too. For now, I have to use the Pocket PC device to rebuild my databases by code to SDF files and it takes hours for 100,000 records.

Looks like there won't be any solution/alternative anytime soon.|||Dave,

A few comments on your post -

"Connecting a desktop application to a mobile device database is not available without purchasing SQL Server" - not true, you can access SQL Mobile databases from C# or VB.NET WinForms apps, but only on TabletPC due to licensing restrictions.

At the MVP Summit in Sept, myself and about six other .NET CF MVPs escalated that this limitation in licensing (TabletPC only) should be reconsidered for a variety of reasons. Mainly that the whole concept of Smart Clients is sterile without off-line relational data caching. SQL Express is hard to deploy to smart clients and setting up any kind of replication is non-trivial and requires deep expertise in RMO programming (while I can replicate a SQL Mobile database with 9 lines of code!).
Microsoft listened and is seriously considering the (spirited) feedback we provided.

So we are working with Microsoft to extend the licensed "reach" of SQL Mobile.

"How could you sell VS2005 as a solution for Mobile device development but not include support for access to SQL Mobile?" - VS2005 has incredbile integration with SQL Mobile and you can build a managed Compact Framework data-driven application that leverages SQL Mobile without ever leaving VS2005. In fact, the process is identical to developing against any other ADO.NET datasource. If you ever had to build a CF app using SQL CE with VS2003, you would be delighted with the new integration with SQL Mobile we now have in VS2005.

My intention here is not to debate anything with you. I want to help you understand that while you may not have the code working at this point, it is entirely possible to have a .NET WinForms app work with SQL Mobile on TabletPC. I'm not entirely clear on why you need this - I build a *LOT* of CF apps for clients that include SQL CE and SQL Mobile and have never had a need to work with the .sdf file from a desktop app. If you are trying to find an effective way to load the initial database with data, my recent webcast shows how to do that using nothing but SQL Server 2005 Management Studio. You can also write a very simple CSV loader in your CF app that loads your initial database from CSV files on device (and yes, you can read and write sequential files within a CF application - System.IO provides the classes to accomplish this).

Help me understand what you are trying to get done at a higher level and maybe we can get you there a little faster. Mobile development always has it's special challenges and frustrations, but one of the great things about the Windows Mobile world is that someone has almost certainly solved whatever problem you're up against.

Thanks,
Darren|||

Thanks for your reply Darren.

My requirements are simple.

The handheld is used for maintaining a transactional database eg. an invoicing app. and the master files are maintained on the desktop. eg. clients, products, G/L accounts.

What is the simple solution? I'm still searching for the way to post the transactional file to the desktop. What use is giving functionality to a tablet PC only? From my continued research, there is no help from Microsoft in creating an export file on the handheld. I shouldn't need to purchase SQL Server for such a basic function.

Regards

David

|||

David,

So chances are the database on your device needs to have a copy of some subset of the master files on your server. Since you mentioned not wanting to purchase SQL Server, I'll assume these master files are either in another relational database or in file format. You also need a way to move transactions that you capture on device in the field from the device to the server.

So your options for moving the data from device to server are:

1. (Requires IIS and SQL Server) - Remote Data Access

2. (Requires IIS and SQL Server) - Merge Replication

3. (Requires IIS) - Web Services - very easy with the .NET Compact Framework

4. Write your data out to a file (this is very easy - not sure what you mean when you say Microsoft cannot provide you with samples - I'll be happy to) and manually move the file from device to server with either a tethered or wireless Activesync connection. You can also manually move the file to a network share independent of ActiveSync using a wired or wireless ethernet connection.

5. Use socket communications as illustrated in the IBuySpyStore sample SQL CE app available on MSDN.

6. Write a custom ActiveSync provider that knows how to pick up your data file from device and move it to some folder on the server. From here, a .NET Service (using a directory watcher) picks up the file and posts it to your server-side data repository

So there are a variety of options available. If you need a code sample of taking data from SQL CE or SQL Mobile and writing it to a file, let me know. Very easy using System.IO in the Compact Framework.

Your options for moving the reference data from the server to device are the same with the addition of simply deploying your CF app with the SQLCE/SQLMobile .sdf file included as Content in the project. Effectively you are deploying a "starter" database. It is typical to deploy the app with a starter set of reference data in the database and then use the techniques above to keep that reference data up to date over time.

-Darren

|||

Hi Darren

All I need is how to get the data into files which are both readable on the desktop and mobile device since VS Net 2005 does not support SDF on destop nor does it support MDF on smart device.

No providers, no socket communications, no active sync, no IIS, no MS Server just a common file format (eg. sequential text files). How difficult is that?

Regards

David

|||

Dave,

Here's an example of writing out the contents of a dataset to a CSV file on device. You can fill your dataset with the contents of a table in SQL Mobile using a SqlCeDataAdapter. Executing this in reverse on the server side to post the data to an RBMS is trivial. This example assumes you do not need any self-description of the data to process it on the server - in that case you can use XML instead.

-Darren

using System.IO;

private void _serializeDataSet()
{
// TODO - exception handling
string dataFileName = Globals.GetInstance().DataFilePathAndName;

StreamWriter sw = new StreamWriter(dataFileName, false);
string line = string.Empty;
foreach (DataRow dr in DSDemoRecords.Tables[0].Rows)
{
for (int i = 0; i < dr.ItemArray.Length; i++)
{
line += _safeString(dr.ToString());
if (i < dr.ItemArray.Length - 1)
line += ",";
}

sw.WriteLine(line);
line = string.Empty;
}
sw.Close();
}

private string _safeString(string s)
{
string retVal = s;

if (s.IndexOf(",") > -1)
retVal = s.Replace(",", "");

return retVal;
}

|||

Hi Darren

I can't understand your code. I presume it was written in C#. Anyway, I'll look up the system.io object in VB2005 help.

My point is that it is ridiculous that you have to resort to sequential text files to manage Mobile SQL data on the desktop.

No. I don't wish to purchase SQL Server Full Edition.

No. I don't own a tablet PC.

Just llook at the various forums and you will see the frustration level at what Microsoft has done by removing support for basic access to mobile data on the desktop.

Regards

David

|||

David,

I have been just as frustrated as you.....did you ever get a solution?

Please post!!!

|||

Sorry,

I hadn't looked at this forum for a while. I'd given up in disgust at Microsoft's attitude regarding access for Windows Apps.

Since I don't own SQL Server Full or a Tablet PC, the work around I am using is exporting to CSV in the Pocket PC and importing from CSV in the Windows App.

Regards

David

|||

Darren,

I'm quite interested in your efforts to escalate the issue of the restrictive SQL Mobile licensing, but for a different reason. My reason is at odds with your statement that you "have never had a need to work with the .sdf file from a desktop app."

I have an ASP.NET server product that runs atop SQL Server 2005 (Express and Standard). Typically, 20-30 Windows Mobile (CF2.0) devices connect to my server via web services in order to download large volumes of data and store it in SQL Mobile. The volume of data results in .sdf file sizes that are ~15-18MB in size. Sync times of over an hour are common, and this an intense thorn in my side. When this performance is analyzed, it's quickly becomes clear that the critical path is SQL Mobile's insert performance. I simply need a way to get data into SQL Mobile faster. To this end, I currently use a product from Fitiri Software that is SQL Mobile equivalent of BCP - it bypasses the database engine to load data into the .sdf quickly.

With the release of SQL Mobile, I thought that my troubles would be over; I thought I could use the server's considerable resources to create/load an .sdf file and then copy that file to the mobile device where it would be used. I am theorizing that ther server could accomplish this in a fraction of the time that it takes on the mobile side. I was disappointed to learn that licensing terms forbid me from adding that feature to my server. I understand Microsoft's motive to restrict SQL Mobile usage; they don't want it to be "abused" as a free database in desktop apps. I feel, however, that my proposed usage does not violate the intent of Microsoft's licensing terms: The .sdf file will built by the server but will be read/consumed only in a WinCE-based environment.

Thus I end up quite interested in your efforts to adjust the SQL Mobile licensing terms, though I arrived by a different road. For what it's worth, I am a Certified Partner and an ISV Success Story, and I would like to know how I can participate or at least be informed of Microsoft's responses and intentions in this area.

Kind regards,

Ken

|||

Ken,

Good timing on your note - I have some answers for you and everyone else who is strugging with this issue of working with SQL Mobile on the desktop.

First, I received clarification from Microsoft yesterday of the specific situations in which you may work with SQL Mobile on the desktop, and the good news is that there are some non-TabletPC scenarios included in this that I was not aware of. Here is the clarification from Microsoft exactly as I received it:

"Remember, there are two exceptions to the current SQL Mobile rule on Win32.

If VS is installed - the developer can use a non TabletPC platform to develop their application

If SQL is installed - the production app requires pre-building .sdf files for route delivery type systems.

The files are created on the server and each morning they are copied to each device.

You can even create a Windows Service to run in the midst of the night to pre build the files,

place them on a file share."

So let me frame this for you - I was explaining to Microsoft that there are many mobile problem domains where the model is to create a workingset of data, place it on a mobile device, work with it throughout the day, upload changes, and repeat. Delivery drivers, baggage handlers, field service inspectors, fixed asset tracking, etc all involve this model. So there are a number of you who want/need to generate a SQL Mobile database on your server/Windows PC that can later be moved to Windows Mobile devices, and then you need to move those .SDF files back to the server/PC to upload changes to a back-end database. An offline batch data collection mindset that has been around since DOS-based Telxons.

What the clarification above boils down to is this: Microsoft permits you to work with SQL Mobile databases from any sort of application (WinForms 2.0, ASP.NET, a Windows Service, a Web Service, SS2005 DTS Package, etc) on a Win32 machine AS LONG AS THERE IS A LICENSED COPY OF VISUAL STUDIO 2005 OR SQL SERVER 2005 on that machine. While this is not the end-goal we want, it certainly improves on the Tablet PC - only answer we've had in the past.

Additionally, while I cannot share much about this yet - Microsoft did listen to the feedback in this forum regarding the ability to easily synch a SQL Mobile database on a device to a database on an ActiveSync-connected machine (IIS not required). I will provide more details on this when I am permitted to.

Hope this helps diffuse some of the frustration with this issue.

-Darren