My Codeplex project, Database Schema Reader, has a new version.
I needed to create a SQLite database was a replica of a parent SQL Server database. The existing project could easily read the schema, and the code generation tools could give me the table DDL and the insert SQL. It was just a matter of executing the SQL and creating the database file.
So I created another simple Windows Forms UI. The SqlWriter class needed some tweaks for SQLite support (and I added integration tests).
For fun, I decided to see if it could also support Microsoft's latest version of SQL Server CE 4.0.
SQL Server CE 4.0 is, like SQLite, an in-process database which can be XCOPY deployed. And unlike previous versions, it is easily able to run ASP.Net websites (it's the default database behind WebMatrix). And 4.0 provides the standard ADO GetSchema which my Database Schema Reader uses (3.5 throws a Not Implemented exception).
It has some of the same limitations as SQLite, such as no stored procedures (good riddance), and no output parameters. But it also has additional limitations. In SQLite you can batch together multiple SQL statements in a single line and execute them in one command. You can't in SQL Server CE.
Unlike SQLite's weakly typed and very simple data types, SQL Server CE 4.0 has most of the standard data types as SQL Server Express and full versions. Unfortunately, this was the big problem. If your table is defined with an IDENTITY primary key, you can't insert the data row with the same primary key, and subsequent foreign key relationships are broken. It's a pretty critical limitation for my scenario.
SQL Server CE 4 only supports a subset of SQL Server data types. The one data type it didn't support, which my database (and later versions of Northwind) use, is NVARCHAR(MAX). You have to use the horrible old NTEXT data type. I could have written a DDL provider that translates varchar max to ntext, but I don't think it's worth the effort.
So my SQL Server CE creation program has major limitations, compared to the SQLite version. It's built into CopyToSQLite.exe, and auto-detects if you've installed SQL Server CE, but you have to have a very simple database for it to work.
SQL Server CE 4 also doesn't support the ADO Sync framework which allows you to synchronize a disconnected database to a parent SQL Server database. Even SQL Server 2008 R2 Management Studio doesn't support it (you have to use Visual Studio 2010 with SP1).
The new FETCH /OFFSET syntax for paging, that will be SQL Server 11, is very nice though. I'm looking forward to that.