How to run SQL script in MDF file?

Question

I created a database model using the model-first method using Entity Framework 4.0. Then I created a sql script using Generate Database from Model ... I also created a SQL Server database file in the App_Data folder. How do I now run the SQL file with this MDF file?

I am using Visual Studio 2010.

Answer 1

I found a solution, but a bit hacked.

I have SQL Server Express (2008 R2). Therefore, when creating a database from a model, I connect to it and allow it to create a database there. Then I go to C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA . Part 10_50 is due to version and may be different for you. Therefore, in this folder there is a .mdf file, named the same as the -.mdf database. There is also a _log.ldf file. I copied them to the App_Data folder of my project.

This works, but it is very time consuming to create a new .mdf database for each model change. So I do it before shipping.

If you find a better answer, please share.

Answer 2

I ran into this problem and this is what worked for me.

When I selected "Generate Database from Model ...", I created a new MDF file. This process worked perfectly, and Visual Studio generated the necessary SQL Script. However, I did not know how to connect to the same MDF file to run Script.

It turned out pretty easy.

• Right click on the script and select Connection> Connect

• Server name and authentication should already be set for the local SQLEXPRESS instance.

• Click Options

• Go to the Advanced Connection Settings tab.

• Paste the path to the database file, using as a guide:

  AttachDBFilename = C: \ Path \ K \ Database \ LocalData.mdf, base = LocalData;

• Click Connect

If you still have connection problems, it may be because there is already an open connection. Check the server explorer and if the connection is open, right-click and select "Close Connection".

This process also creates a persistent connection if the SQL script remains connected. You need to close the script or choose Right Click> Connection> Disconnect.

More information can be found on this subject: EF4 Create a database

Answer 3

I ran into the same problem: Model First approach in mvc4 using EF5. What I did was create a project, add a new model, add entities and associations on the edmx diagram. Right-click on the chart and select "Generate Database from Model". This creates a sql file.

Now add the mdf file, say Database1.mdf (Add> New Item> Data> SQL Server Database) in the App_Data folder in Solution Explorer.

Right click> Open mdf in this file. Now in the properties area you have a connection string. Just copy it as it is.

Go back to the created SQL script, right click> Exeecute. A popup window asks for connection settings. Make sure you have the correct value in the server name (use the name of your PC or 127.0.0.1).

Now in the settings go to the tab "Advanced connection settings" and paste the connection string copied from the properties of the mdf file.

It worked perfectly for me and is quite logical. There is no need to interact directly with the SQL Server installation directory.

Answer 4

The excellent answers are above, but I had to take a few more steps in VS2013, but I decided to solve this problem as follows.

• I created an ADO.NET model from an existing .mdf
• Right click -> Create Database from Model
• Right-click on .mdf in Solution Explorer and open
• In the properties panel, copy the Connection String to the clipboard
• In the SQLQuery window created by the ADO Model, click the name change link
• In the new window "Connect to Server" → "Advanced Connection Settings" → paste the connection string from the clipboard
• In Server Explorer, right-click Database -> New Query
• Copy and paste the request from the created ADO script into a new request window
• Change USE [yourdatabase.mdf] to USE [longPath], where longPath is the very long path specified in the drop-down list at the top. The easiest way is to start typing and CTRL-SPACE.
• Run request
• Save longPath, because you can simply paste it into USE [] for any new request generated by ADO.

Hope this helps someone - Happy Coding!