CODEWITHSUNDEEP
sql-servervisual-studioentity-framework
Bob.at.Indigo.Health
Bob.at.Indigo.Health

Reputation: 11905

Newbie: How to execute MyData.edmx.sql

I've created a simple Entity Framework model-first app in VS 2012 by creating an MVC 4 project and adding a single data entity using the designer.

The first time I right-click the designer surface and select Generate Database it prompts me for the particulars of my database connection. I tell it to use a local database file in my App_data folder. When it's done, it has created my (empty) database file, and it presents me with a shiny new MyData.edmx.sql file.

Ok, so I assume that I need to execute this sql file against my new database file. But I can't figure out the magic to do so. When I right-click the sql file and select Execute, it tells me that I'm using a server type of Database Engine, and it asks me for a Server Name. The only available option seems to be MyPC\SQLEXPRESS. But when I select that and click on the Connect button, it complains that the the [MyData] database doesn't exist (because I've told it to use SQL Express and not LocalDb with my database file).

HELP. How do I wire up VS to my new database file?

Bob

Upvotes: 4

Views: 3097

Answers (2)

fabidi
fabidi

Reputation: 51

I encountered a similar issue in trying to execute the edmx.sql under VS 2013, EF6. There is no mention of this solution anywhere else, so I'll add it here for anyone else who might encounter the error: "Database mynewdatabase does not exist". Below is my solution:

  1. Follow the steps to compete the Generate Database from Model.
  2. Open the file *.edmx.sql (it should have opened automatically). Right click anywhere inside the text editor, and select Execute.
  3. If this is your first time connecting to this db, a login prompt will popup. If using LocalDB, enter server exactly as "(localdb)\v11.0" and use Windows Authentication.

Now the issue. If you get an error: "Database mynewdatabase does not exist", here is the fix: Near the top of *.edmx.sql is a line like so:

USE [mynewdatabase];

You must modify this to include the full path of your database filename e.g. so:

USE [D:\Projects\MyNewProject\MyNewProject\App_Data\mynewdatabase.mdf];

Now repeat step 2 and you should get the message that the database was created successfully.

Note1: The .mdf extension is required!

Note2: You must perform this manual edit again every time you Generate Database from Model as that process overwrites the edmx.sql.

Upvotes: 5

Bob.at.Indigo.Health
Bob.at.Indigo.Health

Reputation: 11905

Got a good answer to my question from Allen Li (Microsoft Tech support) at the MSDN forum [here]. Turns out that Visual Studio apparently can't execute SQL scripts directly against a database file. You need to download and install SQL Server Management Studio and use it to "attach" the file to the existing SQLEXPRESS instance.

Allen pointed me at a blog entry that explains the process in detail:

http://www.headcrash.us/blog/2010/11/model-first-entity-framework-with-mdf-database-files-in-app_data/

Upvotes: 0

Related Questions