Code Monkey’s Cubicle

Normally I write my data access layer using an ORM like nHibernate as I thoroughly enjoy the speed, flexibility and power that comes from using such tools. Recently at work, however, I have been doing a lot of Windows Mobile development and I although there are compact framework ORMs I didn’t have the experience enough to want to use one in a very important project.

As a result the following code is something I had to look up and was actually an annoying thing to find. I wanted to return the identity of a newly inserted object. Normally nHibernate would set this for me but as I’m not using nHibernate I have to do this myself.

using (connection = new SqlCeConnection(CONNECTION_STRING))
{
     connection.Open();
 
     var command = new SqlCeCommand(@"INSERT INTO photos (path, format) VALUE (?, ?) SELECT SCOPE_IDENTITY();");
     command.Parameters.Add(New SqlCeParameter());
     command.Parameters.Add(New SqlCeParameter());
     command.Parameters(0).Value = path;
     command.Parameters(1).Value = format;
 
     var id = command.ExecuteScalar();
 
     Debug.WriteLine(String.Format("New Identifier = {0}", id));
}

This is insert the photo into the database and return the new id for the photo rather than the number of rows effected. The key is the ExecuteScalar function.

How cool is it that log4net works for Compact Framework apps. Has been a huge lifesaver.