Connecting to SQL CE 3.5 databases from C#

I’ve been scratching my head for a long time about how I should be connecting to my .sdf files from c#.  I’ve had a couple of data based projects on the go for some time now, and continually find myself bumping up against some problem as the project progresses.

I tried creating the data with the wizard and an Entity Data Model/Entity Framework.

This leads to pain, as your tables cannot have an server generated identity field.

I tried creating the database using EDM and code first

But for the life of me, I couldn’t work out how to bind it to tables in the database.

I tried using OLE DB

And then had to struggle with code to fetch the identity like this:

_lastIndexCmd = _sqlConn.CreateCommand();
_lastIndexCmd.CommandText = "SELECT @@IDENTITY";

and had to be very careful about inserting only one record at a time.

 

This spring, I went to TechDays in London, and Andy Wigley gave a talk http://blogs.msdn.com/b/mikeormond/archive/2011/07/12/tech-days-live-video-sql-server-compact-and-user-data-access-in-mango.aspx
on using SQL CE in mango.  I asked a bunch of questions in the talk (yes, that’s me in the orange jumper at the front) about server generated keys.

The reason I asked, was because I had misunderstood the comment earlier in the talk that mentioned EDM 4.1, and didn’t realize that we were actually talking about LINQ to SQL, not EF.  In other projects I had problems with inserting multiple server generated keys as described in the OLE DB section above.

Today, I watched the talks again, very carefully, and realized that I should have been using Linq to SQL, and that that will let me work on the phone too.

To create your objects from an already existing database file, run the following command:

sqlmetal /dbml:mydb.dbml mydb.sdf /pluralize

Afterwards, simply add the mydb.dbml file to your project.

Don’t forget the pluralize switch if you’re trying to replace your EDM implementation, or you may be in for a lot of member renaming.

The other gotchas?  AddObject in EF is replaced with InsertOnSubmit on the tables, and  SaveChanges on the DataContext becomes SubmitChanges.

After these changes, I was pleasantly surprised that my query time dropped from 95msec for 11520 records to 78, and improvement of  18%.

I hope I’ve got this right, but if not, hope ErikEJ over at http://erikej.blogspot.com/ will put me right.

Who says I don’t know anything about Culture

I’ve just had a good dose today, because I got bit doing some csv export for a customer in Germany.  As a dyed in the wool C++ programmer, the move to C#/.NET contains a few unexpected gotchas.
Today’s?  The culture used by string.Format() isn’t at all like the one used by sprintf.
It turns out that though they both use the current culture/locale for the thread, in C++, that’s set to the C_LOCALE, whereas in C#, it’s set to the current culture of the operating system.
So in France for example, 1,000.45 gets printed like this: 1 000,45
That’s really going to mess up your CSV.
Of course, you can specify a culture in every print, but really, who’s going to remember to do that, and even then I haven’t checked if it gets passed down to the ToString method when formatted as {0} with no specifier.
So, I really need to do something like this:

CultureInfo ciEntry = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new CultureInfo(String.Empty);

// do my culture invariant work here...

Thread.CurrentThread.CurrentCulture = ciEntry;

And, remember to do it on thread pool methods too, because who knows what Culture they were last looking about.

Where did my resources go

I have been trying to understand the correct way to set my DataContext.  I have been doing it in code behind, but have been looking for a better way to do it with DataBinding.

Yesterday, I tried adding it to my local resources in my App() constructor, like this:

Resources.Add("MyGlobalViewModel", mgvm);

My application also had some resources defined in a ResourceDictionary in it’s own xaml file.

Unfortunately, I chose to add my resources in code before I called InitializeComponent(), and as a result, my additions were removed.

Once I did that I was able to set my data context as

<… DataContext={Binding {StaticResource MyGlobalViewModel}} … >

Tombstoning and Trial Mode for Window Phone 7

Yesterday, I attended the Windows Phone developer camp in London, courtesy of Microsoft.  I took the opportunity while there to add trial functionality to my two existing WP7 apps.

I based my code on the information found here:

http://msdn.microsoft.com/en-us/library/hh286402(v=VS.92).aspx

I naively bound some of my UI elements to be visible when the Trial Mode flag was set.  Because I was running in the debugger, and I tried to purchase the application, my app was tombstoned.  When the app is resurrected, Trial Mode defaults to true.

However, my debug version prompts as to whether to run in TrialMode or not, and as a result when the app is resurrected, it has already bound to the TrialMode flag.  Therefore you must implement INotifyPropertyChanged to broadcast the information.

WPF/Silverlight quirks

 

I’ve been trying to share some code between some of my projects, and occasionally struggle to get things to work.

Today, I moved some data binding from my WPF project to my Silverlight project.  The WPF project was using a string indexer on the current DataContext like this:

{Binding Path=.[version]}

This page:

http://msdn.microsoft.com/en-us/library/system.windows.data.binding.path.aspx

states

Optionally, a period (.) path can be used to bind to the current source. For example, Text="{Binding}" is equivalent to Text="{Binding Path=.}".

Silverlight on the other hand does not like the leading period, but works correctly (as does WPF) when specified like this:

{Binding Path=[version]}

Moral of the Story:  Don’t use leading periods.

VS2010/WiX Porting issues

Upgrading some old VS2005/Wix 3.5 projects to the new compiler raised some issues:

WiX gave some build errors described here:

http://sourceforge.net/tracker/index.php?func=detail&aid=3293788&group_id=105970&atid=642714

this was also set incorrectly in my wixproj file:

<WixToolPath>$(ProgramFiles)\Windows Installer XML v3.5\bin\</WixToolPath>
 
 
 

Battling with PDO

I’ve been having a lot of problems with error HY093, and having the wrong number of parameters.

There are some blogs around that imply that you can pass an object converted to an array to the execute function documented below.

 http://php.net/manual/en/pdostatement.execute.php

$query = $dbh->prepare(
  "INSERT INTO `myTable` (ID, Val1, Val2) " 
 ."VALUES(:ID, :Val1, :Val2)");
$query->execute((array) $crud);

This does appear to work, and seems to occasionally work when there are more fields in the object than are in the query. 

 

Generally though it does not work.  Instead bind explicitly like this.

$query->bindParam(":ID", $crud->ID);
$query->bindParam(":Val1", $crud->Val1);
$query->bindParam(":Val2", $crud->Val2);

However, the documentation above states an FALSE will be returned if you bind more parameters than specified.