How do I connect to an .mdf (Microsoft SQL Server Database File) in a simple web project?

Question

Specifically, in VS 2008, I want to connect to a data source that you can have by right-clicking on the automatically-generated App_Data folder (an .mdf "database"). Seems easy, and it is once you know how.

I just figured out how to do this. I'll post my answer below, which will include the one thing no tutorial on MSDN included that happens to be essential, which is, what if there is no Data menu?.

Answer 1

So here's the answer from MSDN:

Choos[e] "Add New Data Source" from the Data menu.[And follow the connection wizard]

Very easy, except that I have no Data menu. If you don't have a Data menu, do the following:

• Click on Tools >> Connect to Database...
• Select "Microsoft SQL Server Database File", take the default Data provider, and click OK
• On the next screen, browse to your Database file, which will be in your VS Solution folder structure somewhere.

Test the connection. It'll be good. If you want to add the string to the web.config, click the Advanced button, and copy the Data Source line (at the bottom of the dialog box), and paste it into a connection string in the appropriate place in the web.config file. You will have to add the "AttachDbFilename" attribute and value. Example:

The raw text from the Advanced panel:

Data Source=.\SQLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=True

The actual entry in the web.config:

<add name="SomeDataBase" connectionString="Data Source=.\SQLEXPRESS; 
AttachDbFilename=C:\Development\blahBlah\App_Data\SomeDataFile.mdf;
Integrated Security=True; Connect Timeout=30; User Instance=True" />

I hope you found this useful. If any part of this is confounding you, leave a comment and I'll expand on it.

Answer 2

A great resource I always keep around is connectionstrings.com. It's really handy for finding these connection strings when you can't find an example.

Particularly this page applied to your problem

Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

Answer 3

Just one more -- i've always kept a udl file on my desktop to easily create and test connection strings. If you've never done it before - create a new text file and name it to connection.udl (the ext is the only important part). Open the file, start on the Provider tab and work your way through. Once you're happy with the connection rename the file giving it a .txt extension. Open the file and copy the string - it's relatively easy and lets you test the connection before using it.

Answer 4

<add name="Your Database" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Expanse.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient"/>