EF4 Generate Database

Question

Hi,

I am trying my hardest to find the simplest way to create a basic "model first" entity framework example. However I am struggling with the actually generation of the database, particularly the running of the SQL against the database.

Tools

• Visual Studio 2010
• SQL Server 2008 Express

Process

1. Create a new class project
2. Add a new Server-Database item (mdf) named "Database1.mdf" to the project
3. Add an empty ADO.net Entity Model
4. Create a simple entity (Person: Id, Name)
5. Generate the Script selecting the Database1 connection created for me by visual studio
6. Right click the script editor and select the "Execute SQL..." option
7. Log in to SQLEXPRESS

This is where is falls over saying it cant find a database name "Database1".

The "problem" is that the SQL server has not had Database1 attached to it. I am 100% positive that Visual Studio use to attach a database to SQLExpress when it created a new database (Step 2). This appears to not be the case any more (even the beta of VS2010 did it). Can someone confirm this? or tell me how to get this to happen?

Is there a way that I can modify the TSQL script to use an un-attached database. ie a file.

I know I can use SQL Management Studio or sqlcmd to attach the database, but I would ideally like to avoid the solutions as I would like to see the cleanest method of just using visual studio.

Ideal Solutions (in order of most prefered)

1. Get visual studio to attach the newly created database
2. Modify the generated SQL to point to file

Thanks in advance.

Answer 1

What does your connection string look like? Have you tried putting an AttachDbFilenamein the connection string?

Answer 2

Thanks HighTechRider, the connection string created for the DataConnection in the server explorer did indeed have the "AttachDBFilename" parameter.

But this idea let me to try something else.

Following the same steps listed above I did something different at the log in to sqlexpress step. This time, in the Connect to Database Engine screen, I clicked the show more options button and went to the "Additional Connection Parameter" tab.

In here i added AttachDBFilename=c:\src\mydatabasehome\Database1.mdf;database=Database1;. Note I used the full path, not AttachDbFilename=|DataDirectory|\Database1.mdf

This then executed my sql against the database without an issue.

Then I went back to the server explorer and went to expand my database connection it so I could see my new tables, however the connection failed. After a bit of farting around, I worked out that for whatever reason, when I connected to the SQLExpress instance using the "Connect to Database Engine" screen the database file I specified in the AttachDBFilename actually made a permanent connection. Thus the previous connection string (using the AttachDBFilename parameter) was no longer valid as this database is already attached.

So I deleted that connection and created a new connection directly to the SQLServer which had a database in it named "database1" for me to select.

Now I think it has all worked out ok.

This is was a weird process, and certainly unexpected for me, so if anyone would like to explain what is going on I would certainly appreciate it. For now I have a way (even if a bit weird) of doing what I want, better solutions appreciated.

Hope this helps someone.

Answer 3

Where is the "Additional Connection Parameter" tab? I only have and "Advanced" tab and it is not clear where you added the attach statement.