Tech Tock

Time is of the essence.

A Free Database You’ll Hate (At First?)

I’m doing a small project that could use some local caching persistence, so I picked my old buddy SQL Server.  SQL Server Compact Edition (SSCE, CE) to be precise.

I’m finding it to be SQL Server with all the fun removed.  At least it supports multiple database connections.  But there’s no database diagrams, you can’t make a PK clustered, and Linq2SQL refuses to make a diagram in Visual studio among other indignities.

You can’t even generate scripts from a Compact DB schema :(.  An MVP recommended this commercial utility (what, pay for a utility?) and the guy with all the points on StackOverflow got it wrong, Management Studio won’t let you generate a script for a Compact DB.  But the answer is down in the list of responses (the first time I saw a correct answer ignored on StackOverflow).  This CodePlex tool does a good job of scripting the database.  Its a convenient Management Studio plug-in.

Strange thing that the same MVP recommends the same company for making database diagrams in SSCE. My Spidey Sense is tingling.

Since the project is so small and the database part is so trivial, I can justify Linq2SQL even though its on its way out, but try to drag a table into a Linq2SQL dbml designer and Visual Studio complains:

image

Since my project only needs a couple quick and dirty tables and SQL Server any version is acceptable to the client, I’m going to forge ahead.

Since Visual Studio UI won’t create a Linq2SQL dbml for a SQLCE database, this blog, pointed me to SQLMetal.exe, which is included with Visual Studio 2008.  Why Visual Studio should be so picky is beyond me.

On the other hand I could use a typed dataset, which I’ve found brittle in the past and hardly as user friendly as Linq2SQL.  This is created automatically when you create a CE file database in Visual Studio.  No thanks.

the SQLMetal worked on the first try, so alls well that ends well.  Its easy to keep the command window open and just delete the dbml and rerun the SQLMetal command anytime you update the schema.

Once it was all setup I was back in Linq Heaven.

So, it seems the moral of this story is that with the right collection of utilities, SQL Server Compact Edition is a workable persistence store for a small application.

Advertisements

December 3, 2009 - Posted by | Uncategorized | , , , , , , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: