Monday, May 11, 2009

SQL Compact and Testing SQL Scripts

I'm working with a SQL Server Compact DB today (for an embedded application)...and found out the following:
If you want to connect using Sql Server Management Studio (SSMS) you need to choose the SQL Server Compact connection type.
If you want to validate your scripts, there is no button to do so and Ctrl + F5 won't do it either. Being the creative "outside the box" thinker, I came up with a simple and elegant way to validate the scripts...use the "Display Estimated Execution Plan" option. When testing the execution plan, the SQL Server has to execute the code. Therefore, when it checks the execution plan, it is executing the sample code without applying the transaction to the database. It will even tell you where the offending code lies. If you forget to append a semi-colon to the string, it tells you the code is invalid (discovered this the hard way). Always a good thing to check syntax before executing the code.

My next "big idea" related to the SQL Server Compact (Embedded) database is to use LINQ for data interaction. What I think this should allow me to do is execute a query against the database, which means I can create quasi-stored procedures via the LINQ to SQL engine.
Why not just use SQL Server Express? Because I want to embedd the database into the application. If there are any other "Why not ..." I would tell them a simple answer. Because I want to try. I'm a hacker in the sense of trying things to see if they work. If I never fail, it's because I never tried.

No comments: