Please see my updated post for a correction regarding the problem discussed here.
Last week I encountered what appears to be a rather significant bug in the Visual Studio 2005 VB.NET generated data classes when accessing a SQL Mobile database. Although, I do the majority of my Windows Mobile managed programming using C#, on occasion a situation comes up where someone asks me to use VB.NET. Last week, I had one of those occasions. As a result, I was creating in a VB.NET project that takes advantage of SQL Mobile and the Visual Studio 2005 generated data classes.
The situation that encountered the bug was very simple.
- I used the Add New Datasource option to add a SQL Mobile database file to my project.
- An important point is that the database file being added to the project is located on my desktop computer.
- I then dragged one of the database tables from the Data Source pane onto my project form.
- And ran the program.
The program immediately blows up reporting that the database file cannot be found.
This was quite a surprise because I've done these exact same steps in C# more times then I can count.
The problem stems from an error in the Visual Studio 2005 generated database classes but only occurs in VB.NET projects. As you know, in addition to generating a DataSet for the referenced database, Visual Studio 2005 also generates a table adapter class for each table. The problem is with a generated method within the table adapter class named InitConnection.
The generated InitConnection function is responsible for creating a SqlCeConnection instance, associating the connection with the table adapter and setting the connection's connection string to point to the database file on the device. For VB.NET projects, the connection string is where things go wrong.
In C# (which works correctly), the InitConnection function sets the connection string like this:
this._connection.ConnectionString = ("Data Source =" +
(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
+ "\\RealEstate.sdf;"));
The above code basically retrieves the name of the folder on the device containing the program assembly (technically it's the folder from which the executing program assembly was loaded) and then sets the connection string to look for the database file in this same folder. This works just fine because, by default, the project copies the program assembly and the database file to the same folder on the device.
In VB (which does not work), the InitConnection function sets the connection string like this:
Me._connection.ConnectionString = "Data Source ="".\RealEstate.sdf"""
The path ".\RealEstate.sdf" attempts to open the database from the program's current directory. The problem (as many of you already know) is that Windows Mobile has no concept of a program's current directory; all paths must be fully qualified.
The good news is that the work-around is quite simple. Basically you need to create a SqlCeConnection with the appropriate connection string and associate it with the table adapter prior to the table adapters first use. In most cases, you can do this in the Load event handler of the Form or Forms on which you've dropped the control(s) that databind to the data source.
Here's an example:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If RealEstateDataSetUtil.DesignerUtil.IsRunTime Then
' *** BEGIN WORK-AROUND ***
Dim runtimeFolder As String = _
System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
Me.PropertyTableAdapter.Connection = New System.Data.SqlServerCe.SqlCeConnection( _
"Data Source = " & runtimeFolder & "\RealEstate.sdf;")
' *** END WORK-AROUND ***
Me.PropertyTableAdapter.Fill(Me.RealEstateDataSet._property)
End If
End Sub
Basically the above code duplicates what is done in the C# InitConnection function. It gets the name of the folder from which the program assembly was loaded and then builds the connection string by concatenating the program assembly's folder to the database file name. It then creates a SqlCeConnection instance with the passing the connection string and associates the SqlCeConnection with the table adapter class.
If you'd like to see an example of a VB.NET program that uses the work-around and a simple C# class that works correctly, you can download example programs from here.
I have to say that it absolutely blows my mind that such a bug made it into the Visual Studio 2005 release but at least the work-around is not too painful.
Posted
Mar 21 2006, 03:39 PM
by
jim-wilson