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.
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:
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.
No comments yet.
- Internal Product Owner September 29, 2015 Mark Plant
- Agile Manager September 29, 2015 Mark Plant
- User Experience Architect September 29, 2015 Mark Plant
- User Experience Designer September 29, 2015 Mark Plant
- Senior Software Engineer (Java) September 29, 2015 Mark Plant
- Software Engineer (Web UI) September 29, 2015 Mark Plant