I have been struggling today with some SQL Express related data issues. I have been trying to use a combination of Web Application Projects and ATLAS, as I am convinced that Microsoft have completely stuffed up with their "Web Site" style projects, I really don't want to use them. So to do this you simply create a new Web Application Project, and then copy all the necessary settings from the sample ATLAS web.config file into your own web.config file, and ALL is fine... that is until you want to do something with SQL Express. Regardless of ATLAS, the standard approach is to add a new item to the project and select a SQL Database. This uses SQL Express in a mode called "User Instances". This is designed to allow you to treat the databases primary file (mydatabase.mdf) as though it is just that, a file (impressive if you know what's really going on under the hood and understand how SQL Server normally works), and this is how all the Microsoft demos go, except that they use Web Sites instead of Web Application Projects. The theory is then to add tables, and create a DataSet off these tables. So I thought it would just be a simple matter of creating a Web Application Project, and then following exactly the same logic. WRONG!!!!!!
My first efforts yielded the following error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: CS0122: MyWebApplicationProject.Properties.Settings' is inaccessible due to its protection level
This is because the default way of storing the connection string is to use the Properties folder and create an entry in the Setteings.Settings file. In this case there is a class called MyWebApplication.Properties.Settings. This has a protection level of "internal" meaning that only code from within the same assembly can refer to it. The code attempting to access it is NOT in the same assembly so it fails compilation.... DOH.
Next step was to attempt to rework it so that it got the connection string from the web.config file. After correcting all the places where I found a reference to the connection string, I received the following error.
Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file C:\Projects\AtlasApp\AtlasApp\App_Data\App_Data\Tasks.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Better, at least this time it's compiling. After spending entirely too much time attempting to figure out why SQL Server User Instances were failing, I eventually re-read the error message, and noticed the path it was attempting to access. C:\Projects\AtlasApp\AtlasApp\App_Data\App_Data\Tasks.mdf
Notice the two App_Data directories. Yep, so simply exclude the App_Data part of the path in the connection string and away it goes, everything now works.
The reason I write this article is because I found very little documentation on the problems. Problems which I thought would be occurring quite frequently if people are using Web Application Projects, but alas the only mentions I have found are a recognition by Microsoft that there are problems, and a vague promises of a white paper on the issue from Microsoft.
In conclusion, I've had quite a frustrating day, and I think Microsoft still have some work to do on Web Application Projects.