How to store sql server express in a different project from the UI?_问答_开发者

I have two projects in my solution:

MyProject.UI (ASP.NET MVC 3)
MyProject.Domain (class library)

I have added a sql server express database to my class library project as well as an entity framework edmx file. The model designer in entity framework has no problem finding and connecting to the database in that project. When I do so it generates an app.config file with the following connection string:

<add name="TestDBstuffEntities" connectionString="metadata=res://*/Test.csdl|res://*/Test.ssdl|res://*/Test.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestDBstuff.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />开发者_Go百科;

You'll notice that the connection string contains AttachDbFilename=|DataDirectory|\TestDBstuff.mdf;. When I copy this connection string to the UI web.config so that it can be used during runtime, I get an error. I assume that is because it is now referring to the UI project's data directory (App_Data). If I change the AttachDbFilename=|DataDirectory|\TestDBstuff.mdf; to the fully-qualified path where the .mdf file is stored, it works.

My question is what is the best practice to store a sql server express file in another project outside the UI project?

I know what the solution is supposed to be, since I had to look it up when I was creating an MVC solution for the first time and had the same problem. I never had time to get it working fully, so there could be something wrong here.

Basically, you should set the value of DataDirectory. In the Global.asax.cs file's Application_Start method, add

AppDomain.CurrentDomain.SetData("DataDirectory", "c:\Path\To\Your\Database\Directory");

making the obvious substitution.

Now, there are other potential issues -- the .mdf needs to be deployed somewhere, and SQL Server Express needs to have permissions to read and write the file where it gets deployed. I don't recall the username for SQL Server Express, but I do recall it being quite long; double-check the permissions on a directory you know SQL Server Express can access to find the username. Also, you may not want your database file to be overwritten each time you build the solution. This MSDN blog post was written for Visual Studio 2005, but I think it still applies even in 2010.

I'm sorry if I'm missing something. As I said, I haven't had time to get my own solution completely working, but I think this is the idea.

Quite honestly: I've never liked this "just attach a MDF file on the fly" approach.

I would

create my database on the SQL Server Express instance manually (e.g. in SQL Server Mgmt Studio Express)
have a "real" connection string in the project that connects to that Express server and uses my database.

So your connection string would then be something like:

<add name="TestDBstuffEntities" 
     connectionString="metadata=res://*/Test.csdl|res://*/Test.ssdl|res://*/Test.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Database=TestDBstuff;Integrated Security=True;MultipleActiveResultSets=True&quot;" 
     providerName="System.Data.EntityClient" />

This way, you are not dependent on any physical location of the MDF file - you just talk to the SQL Server Express server process and the location of the actual file is totally transparent to you.

I think it depends on how you want to deploy. For web based projects, I tend to use a Database projects that I deploy to SQL Server. This obviously won't work for User Instanced databases however.

I guess I'm not a fan of user instances, and would rather use SQL CE if I wasn't using a permanently attached database.

But more to the point of answering your question, the DataDirectory for Web based apps I believe is the App_Data directory. I'm going to assume that if you reference your class library, then the database and class files will end up in the Bin directory instead. If I were to make a decision about how I go about deciding where the database lives, between the class library and the MVC project, it would highly depend on the data. If the data is specific to the class library then my opinion would be to have it with the class library and your filename is bin\testDBstuff.mdf. But more often then not this data is application specific and in my opinion should be stored with the application using it, in which case would be the app_data directory of the MVC project and the filename is correct..

I reckon it is more of a deployment issue. Where you store your physical mdf file shouldn't really matter. When you deploy you could just update the relevant settings (probablt from some kind of installer).