Buggers, Scripting Support in .NET CF 2.0

Scripting support in .NET CF 2.0 is just as absent as in .NET CF 1.0 :(

Hopefully someone will get around to answering this post.

Cingular 2125 Developer Locked

Solution to Windows Mobile 5.0 hardware being developer (application) locked by default can be found here.

Genius!

Primary Key Efficiency

Not sure if this is relevant to SQL Server Mobile or SQL Server 2005, but still interesting nonetheless. The key takeaway seems to be that the primary key should be single column if possible and an int datatype (link).

Also, from link, "A primary key is an attribute (or combination of attributes) that uniquely identify each instance of an entity. A primary key cannot be null and the value assigned to a primary key should not change over time. A primary key also needs to be efficient. For example, a primary key that is associated with an INTEGER datatype will be more efficient than one that is associated with a CHAR datatype. Primary keys should also be non-intelligent; that is, their values should be assigned arbitrarily without any hidden meaning. Sometimes none of the attributes of an entity are sufficient to meet the criteria of an effective primary key. In this case the database designer is best served by creating an "artificial" primary key."

Multiple Columns as Primary Key?

I was questioning whether my database design was a good one. This is my first project using SQL (or any database for that matter) and I wondered whether using multiple columns as a primary key was "good design" or, alternatively, if I should just create a dummy column in my tables and use the IDENTITY property (so that they are auto-numbered and guaranteed unique). I guess this is a hotly debated issue. I found quite a few discussions on the web about it--with people arguing adamently in both directions.

Here are a few links.

1. "Primary Key" (Google Groups).
2. "Design: multiple columns for primary key" (Google Groups)
3. "SQL Server Performance Questions & Answers" (in favor of IDENTITY columns)

Adding Foreign Keys to .SDF via VS2005

From msdn forums:

"You can't add a foreign key to a SQL Mobile database from within VS2005 - you cannot do it graphically nor by using the query designer (where it would be tempting to enter an ALTER TABLE .. ADD FOREIGN KEY statement).
In the absence of SQL Server 2005 Management Studio in your situation, you will need to run the ALTER TABLE command either from your program code or inside of Query Analyzer 3.0 on device."

(link)

SqlCeConnection Keep Connection Open?

Consensus seems to point to keeping one instance of SqlCeConnection open for the duration of executation for performance reasons.

1. SqlCeConnection guidelines (MSDN Forums)
2. Performance with SqlCeConnection (Usenet)
3. Data Architecture & Pooling (Usenet) <- this is for SqlServerCe 2003

Marcus Perry in his MSDN blog notes:

Creating and destroying SQL Mobile database connections is an expensive task and so the SqlCeConnection is designed to be a long lived, shared instance across the lifetime of the application. For a complex app, ideally the SqlCeConnection instance would be placed in a singleton wrapper class that manages access to the database.

Though this information comes from a Microsoft employee, it still seems contentious. Dave Hayden, a MS MVP, said in this MSDN forum post:

For 99% of all applications, the best practice is to open and dispose of database connections right when you need them and not to leave them open for the duration of the application. Open the database connection as late as possible and close/dispose of it as soon as possible.

Nicholas Paldino (a .NET/C# MVP) seems to agree with Dave:

You shouldn't keep a connection open and lying around. The SqlConnection class uses a connection pool in the background, and keeping your connection open is actually going to degrade your performance. You will want to create a new connection, open it, use it, and then close it for every operation that you do (or group of operations). You shouldn't be holding the connection open across operations. You will also notice MUCH better performance when you do this. The same thing holds with the command object. Don't use the same one. Use new ones. If you want to use a command object over and over again, you can, but use it for the same parameterized command. Don't keep changing the command string.

Both Dave and Nicholas are basing their advice on SQL Server's behavior (not SQL Server Mobile). SQL Server supports connection pooling, SQL Server Mobile does not. Again, from MS Employee Marcus Perry (link),

There is no connection pool used for local database access for SQL Mobile.

If only one SqlCeConnection is used, note that it is not thread safe. Thus, a multithread application must provide mechanisms to safely access the SQL database from multiple threads. There seem to be two approaches: one is to make the singleton wrapper class (mentioned above) thread safe and the other is to give each thread access to its own SqlCeConnection object. See these MSDN forum posts (here and here). Microsoft has yet to endorse a specific approach as a "best practice" (a discussion about this can be found here).