SQLCE and ASP.NET

Sometimes it's difficult to decide which database to use for a particular application. I found myself in this dilemma when creating my website. Most of my website is not really data centric so I was thinking maybe using MySql since it's free, or perhaps Sql Server since I already have it installed on this machine. I decided to go with Sql Compact 3.5 since it's new and I wanted to see what was involved with getting it to work with LinQ. The first problem I ran into was creating the SQL Enitity classes with the Compact Edition. It's easy to create the entities with SQL Server 2008, which involves just going to the project and adding a LINQ to SQL Classes Template to your project and dragging over the tables, or whatever you need for you entity class. However, with SQL Compact, this doesn't work. You could create the entity class by hand, but that involves a lot of typing (no pun intended).

Here's how I solved the problem:

There's a command line tool in the Microsoft SDKs folder for creating entities for various databases called SQLMetal. It's in the folder: \Program Files\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe

The use of the program is as follows:

SqlMetal.exe MyData.sdf /code:MyCode.cs

example: SqlMetal.exe northwind.sdf /code:northwind.cs

That solved the problem and created the Sql Compact 3.5 entity.

I created a batch file to make it easier (see below) :


@echo off
Set SQLMetalPath="C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin"
Set DBPath="C:\Users\Jonathan\Documents\Visual Studio 2008\WebSites\CSharpSolutions_8_2008\App_Data"
Set AppCodePath="C:\Users\Jonathan\Documents\Visual Studio 2008\WebSites\CSharpSolutions_8_2008\App_Code"

cd %DBPath%%SQLMetalPath%\SqlMetal.exe CSharpSol.sdf /code:%AppCodePath%\CSharpSol.cs

---

Here's problem #2 I had using SQL Compact 3.5 but the resolution came from Steve's site:

Force SQLCE to run under ASP.NET
Steve Lasker posted blog entry on how to use SQLCE under ASP.NET using the pre-release version of the SQLEV. Under SQLEV you set a flag that would tell the SQL Connection, “yes I know this isn’t supported, but let me do it anyway”:
AppDomain.CurrentDomain.SetData("SQLServerEverywhereUnderWebHosting", true)
As you can see the name of the product is right there in the key. Well they changed the name of the product and so they changed the name of the key. So, if you were using the beta for development and are now switching over to the release version of SQLCE , you will need to change the key:
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true)
That should allow you to use the database under ASP.NET. Now you can revel in the fact that you are swimming in unsupported waters!

Note:
I placed the AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true) line of code in my default page's constructor in the code behind section:
Hope that helps