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

Question

I have an error below adding an .mdf file (SQL Server Database) in a Visual Studio 2010 project

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

I don't want to install SQL Server Express (2005/2008) because I have already installed SQL Server 2005 Enterprise Edition

I am using Visual Studio 2010 Ultimate

Solution 1

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?

Solution 2

I know this post is a bit old but i encountered the same problem and i actually found a solution, so i would like to share it.

1. Install sql express 2008 r2
2. In visual studio 2010 go to Tools -> Options
3. Select Database Tools -> Data Connections and update the Sql Server Instance Name (blank for default) with the instance name of your database.
4. Then go to services by pressing ⊞Win + R and services.msc
5. Select the SQL Server (<instance name of express edition>), right click and select Properties
6. Then in the properties window of the service go to Log On tab and select Local System account

After these steps i was able to add a .mdf file into visual studio 2010.

Also maybe is possible to be able to do it without installing Sql server express, just starting from the second step, but i did not try it.

Solution 3

you can use code to add that if not exist

string curFile = @"C:\Dev\Test_data.mdf";
        if (!File.Exists(curFile))
        {
            SqlConnection connection = new SqlConnection(@"server=(localdb)\v11.0");
            using (connection)
            {
                connection.Open();

                string sql = string.Format(@"
                                    CREATE DATABASE
                                        [Test]
                                    ON PRIMARY (
                                       NAME=Test_data,
                                       FILENAME = '{0}\Test_data.mdf'
                                    )
                                    LOG ON (
                                        NAME=Test_log,
                                        FILENAME = '{0}\Test_log.ldf'
                                    )",
                    @"C:\Dev"
                );

                SqlCommand command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
            } 
        }

Comments

[Ali]

First of all thanks for your reply.Do not want to take restore or attach mdf file if I run my developed application on other PC that developed on my PC.If you download sample code from website then for some people it is annoying to configure applications(means restore db or attach mdf file and configure connection string etc)

[dash]

It all depends - you could argue that those MDF files are a dependency of your project, and, like any dependency, you have to include them in some way. Good luck!