How to Connect to an .Mdf (Microsoft SQL Server Database File) in a Simple Web Project

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

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" />

How do I connect to an MDF database file?

Add space between Data Source

 con.ConnectionString = @"Data Source=.\SQLEXPRESS;
                          AttachDbFilename=c:\folder\SampleDatabase.mdf;
                          Integrated Security=True;
                          Connect Timeout=30;
                          User Instance=True";

How to add SQL Server database file (.mdf) in Visual Studio without installing SQL Server Express Edition?

This is a really annoying one. Basically, in Machine.config for the version of the framework you are developing against, there is an entry for LocalSqlServer.

On my machine, for version 4:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\Machine.config

<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

I found that if I changed the data source part of the connection string to point at my Sql 2005 full server instance, then the error you mentioned went away.

(Similar for other versions of the framework, which I also changed)

I can't remember if I needed to restart just visual studio or the whole machine before I saw the changes work.

Remember to back up your machine.config files before editing them!

With that being said, there's also no reason why you can't add the database into Sql Server itself (if you have the mdf) then connect to it from Visual Studio via the View -> Server Explorer -> Data Connections (Right Click -> Add Connection) - have you tried that?

Connections to SQL Server database files (.mdf) require LocalDB or SQL Server Express to be installed and running on the local computer

The error message is quite clear. You need to have either LocalDB or SQL Server Express installed and running on the local computer, and you apparently don't.

Do as the error message instructs, go to the link provided in that error message, select Microsoft SQL Server Data Tools, and install the latest version.

Add *.mdf file to C# Project

To start with, and MDF file can be read only by an instance of SQL Server. If you deploy MDFs, then your application must either connect to a SQL Server provided by your end-user during setup, or it must deploy its own instance, in the later case a SQL Server Express Edition instance. See How to: Install SQL Server Express. With Visual Studio 2008 you can add a prerequisite to your own application setup MSI, see "Installing" the SQL Server 2008 Express ClickOnce Bootstrapper for Visual Studio 2008 SP1.

A second issue is that, despite the wide belief of the contrary, distributing the MDF alone without the LDF can land you into a world of pain. You can end up distributing an inconsistent MDF that needs the LDF to finish recovery and get into a consistent state.

But a more serious issue is your plan to deploy binaries (MDFs) instead of scripts for database deployment. This is doomed to fail. As soon as you'll plan to release v. 1.1 of your application you'll face the non-trivial problem of how to replace the user MDF (which now contains data added by the user) with your new MDF. This is why is much much better to deploy upgrade scripts always, and forget about the MDF in your project.

Connection String to Connect to .MDF

If you're using the *.mdf file in the App_Data folder of an ASP.NET app you can use this.

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;

If it's not an ASP.NET application don't use the DataDirectory syntax and just use the full c:\... path.

Cannot attach the file *.mdf as database

Take a look at this: Entity Framework don't create database

I would try giving the database a
different name. Sometimes you can run into problems with SQL Express
when trying to create a database with the same name a second time.
There is a way to fix this using SQL Server Management Studio but it's
generally easier to just use a different database name.

Edit
This answer was accepted because it confirms the bug and the workaround used by OP (renaming database could help). I totally agree that renaming the database is not really an acceptable way, and does not totally solve the issue. Unfortunatly I didn't check the other ways to really solve it in SSMS.

Run SQL script on MDF file in App_Data

In my opinion, the whole AttachDbFileName= approach is flawed. Since that .mdf file is not attached to your SQL Server instance that's already installed, there's many things you cannot do:

• you cannot use BACKUP to back up your database
• you cannot easily execute .sql scripts against your database
• and quite a few more things

The only really viable solution in my opinion would be to

1. install SQL Server Express (and you've already done that anyway)
2. install SQL Server Management Studio Express
3. create your database in SSMS Express, give it a logical name (e.g. YourDatabase)

4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

   Data Source=.\\SQLEXPRESS;Database=YourDatabase;Integrated Security=True
   

   and everything else is exactly the same as before...

---

Related Topics