After I wrote "Choose between SQL Server Express Edition and Compact Edition", some friends in the garden asked whether SQL Server Compact Edition (sqlce) supports ASP.NET? I wasn't sure at the time. I tested it myself and it definitely didn't work, but I thought it was weird and there was no reason not to support it!
I read Steve Lasker's blog today and finally figured it out!
Since sqlce does not support ASP.NET by default, many users have made requests to MS, constantly complained, and even threatened (if you don't let it support it, I will use XX products). Steve Lasker finally couldn't sit still and came out to clarify all this.
In fact, the market positioning of sqlce is very clear, mainly to solve the offline data storage and synchronization problems of smart clients. This is also clarified in "Choosing Between SQL Server Express Edition and Compact Edition". Therefore, Microsoft has not optimized the usage scenario of sqlce as a website database. In the environment of multi-user access to the Web site and data concurrency, the performance of sqlce is not ideal and cannot be compared with SQL Server Express. Therefore, it is recommended that the Web site still uses SQL Server Express or SQL Server database. You must know that sqlce is an embedded database engine, and SQL Server Express is a database service. Now the Hosting Toolkit SQL Server recently released by the SQL Server development team can assist you in deploying SQL Server Express on your Web server.
So, can ASP.NET use sqlce as data storage? Can!
By default, sqlce will be blocked when opening a sqlce database connection in the ASP.NET worker process. You can remove this restriction through the following code. This line of code will change in the RTM version because sqlce has been renamed and is no longer called SQL Server Everywhere:
AppDomain.CurrentDomain.SetData("SQLServerEverywhereUnderWebHosting", true)
should be noted that the data synchronization function of sqlce cannot be used in ASP.NET.
In short, no matter how big the shoes are, how big the feet are. You should not use sqlce in ASP.NET. If you insist on using it, please pay attention to the consequences!
Reference:
SQL Server Compact Edition under ASP.net and IIS
SqlCeConnection Class (System.Data.SqlServerCe)