My First Experience With SqlServer CE 4.0

I have heard all the hype about how great Sql Server CE 4.0 and that it is now standard with Visual Studio 2010 SP1.

I’ve got a small project (1 table) that I’d like to include in an asp.net website project so I decided to give it a try. For the most part, things have gone smoothly, however I did have a couple hiccups I’d like to mention. One cost me $9 because I was doing this implementation on an airplane and immediately, I had a failed connection with a confusing error that I needed Bing to lookup (more details below).

Method Names Changed

I have to wonder why Microsoft decided to change the names of the methods in ADO.NET. Seems to me that all the names should be the same whether I’m using Sql Server, Sql Server Express or Sql Server CE. My expectation was that the statement:

using (var connection = new SqlConnection(testDataContext.ConnectionString)) {..

would work correctly, however, I get the very misleading (or at least unhelpful) error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

It turns out, you need to open the connection like this:

using (var connection = new SqlCeConnection(connectionString)) {...

And things now work (my connection string is actually what I’ve pasted below).

<connectionStrings>
  <add name="ConnectionStringMxMessage" connectionString="Data Source=|DataDirectory|\MxMessageDb.sdf"
       providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

Bottomline, all ado.net commands need the Ce in them.

Getting Identity Column After Insert Fails

My expectation is that what has worked for a very long time in Sql Server for me should work in Sql Server CE. The most simple pattern of inserting a row and getting back the value of the identity column does not work the same. My original code looks like this (which fails):

using (var connection = new SqlCeConnection(testDataContext.ConnectionString))
    {
        connection.Open();
        using (var sqlCeCommand =
            new SqlCeCommand(
                "INSERT INTO message (Name, Subject, Header, Body, Footer) Values 
                    (@Name);SELECT @@IDENTITY",
                connection))
        {
            sqlCeCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Name;
            // problem, need to do separate select to get this to work
            sqlCeCommand.Parameters.Add
                (new SqlCeParameter("@IDENTITY", SqlDbType.Int)
                                            {
                                                Direction =
                                                    ParameterDirection.Output
                                            });

            using (var reader = sqlCeCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    message.Id = reader.IsDBNull(0) ? 0 : 
                      Convert.ToInt32(reader.GetDecimal(0));
                }
            }
        }
    }
}

To fix it, it seems that you can not send two sql commands on the same execution (separated by semicolon). That is, after the first command (without the SELECT @@IDENTITY) you need to do another SqlCeCommand as follows:

using (var sqlCeCommand2 =
    new SqlCeCommand(
        "SELECT @@IDENTITY",
        connection))
{
    message.Id = Convert.ToInt32(sqlCeCommand2.ExecuteScalar());
}

Conclusions

Well, things are working, but I have not done much and I’ve had a couple of stumbles. Hopefully I’m done with stumbling for now.