How to embed a database in a visual studio solution?

Question

I've been reading about how wonderful Visual Studio 2013's new Sql Server Data Tools are, and about the new localdb database server, etc., so I've been trying to do what would seem to me to be the point of the whole thing - to embed a local test/development database in a VS solution, so that when I check out a project into a clean directory, on a new machine, I can just run my application, connected to the database in the solution.

But I've not been able to figure out how to do it.

Can anyone give me any hints? Or directions to a tutorial?

Adding some clarification

There's a tutorial on how to include a localdb database in a project, on MSDN, here:

Local Data Overview

But, unfortunately, it doesn't work. I followed through the instructions, exactly, and it all seemed to work, until I moved the solution folder to a new location, at which point it lost track of the database.

The problem is the connection string, which contains an absolute path to where the database was when it was created. Which is useless. I need to be able to check out a project to any location, on any machine, and have it build and run there.

<connectionStrings>
    <add name="ConnectLocalData.Properties.Settings.SampleDatabaseConnectionString"
        connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=E:\dev\Experiments\LocalDbWalkthrough\SampleDatabaseWalkthrough\SampleDatabase.mdf;Integrated Security=True;Connect Timeout=30"
        providerName="System.Data.SqlClient"
    />
</connectionStrings>

Apparently, this question has been asked before:

Make the connectionstring's AttachDbFilename relative in config file

But that question has no answers.

Answer 1

So, I found the trick.

ADO allows connection strings to start with |DataDirectory| - which is a substitution string that is replaced by the "DataDirectory" setting of the current AppDomain.

This usually defaults to the location of the .EXE, though it varies with websites, click-once installs, etc.

And because EntityFramework builds on ADO, it works in EF, too.

What makes it work is that you can change it, on program start, to point anywhere you like.

What I am doing is putting an appSetting with a path relative to the location of the .EXE, in each project's App.config, and using it to set it on program start:

<appSettings>
    <!-- path to the directory containing the database, relative to the location of the .exe -->
    <add
        key="dataDir"
        value="..\..\..\DataBase"
        />
</appSettings>

<connectionStrings>
    <add
        name="EmbeddedDatabaseConnectionString"
        connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|EmbeddedDatabase.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient"
        />
</connectionStrings>

And then in code:

public class ReadWithADO
{
    static ReadWithADO()
    {
        var appSetting = ConfigurationManager.AppSettings["dataDir"];
        var baseDir = AppDomain.CurrentDomain.BaseDirectory;
        var path = Path.Combine(baseDir, appSetting);
        var fullPath = Path.GetFullPath(path);
        AppDomain.CurrentDomain.SetData("DataDirectory", fullPath);
    }

    static void Main(string[] args)
    {
        var connectionString = ConfigurationManager.ConnectionStrings["EmbeddedDatabaseConnectionString"].ConnectionString;

        using (var con = new SqlConnection(connectionString))
        {
            con.Open();
            var cmd = new SqlCommand("SELECT * FROM Customer", con);
            var rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]);
            }
        }

        Console.Write("<Press any key>");
        Console.ReadKey();
    }
}

This works just the same in Entity Framework:

<connectionStrings>
    <add
        name="EmbeddedDatabaseEntities"
        connectionString="metadata=res://*/EmbeddedDatabase.csdl|res://*/EmbeddedDatabase.ssdl|res://*/EmbeddedDatabase.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\v11.0;attachdbfilename=|DataDirectory|EmbeddedDatabase.mdf;integrated security=True;connect timeout=30;MultipleActiveResultSets=True;App=EntityFramework&quot;"
        providerName="System.Data.EntityClient"
        />
</connectionStrings>

<appSettings>
    <!-- path to the directory containing the database, relative to the location of the .exe -->
    <add
        key="dataDir"
        value="..\..\..\DataBase"
        />
</appSettings>

And:

public class ReadWithEF
{
    static ReadWithEF()
    {
        var appSetting = ConfigurationManager.AppSettings["dataDir"];
        var baseDir = AppDomain.CurrentDomain.BaseDirectory;
        var path = Path.Combine(baseDir, appSetting);
        var fullPath = Path.GetFullPath(path);
        AppDomain.CurrentDomain.SetData("DataDirectory", fullPath);
    }

    private static void Main(string[] args)
    {
        using (var db = new EmbeddedDatabaseEntities())
        {
            foreach (var customer in db.Customers)
            {
                Console.WriteLine(customer.CustomerId);
            }
        }

        Console.Write("<Press any key>");
        Console.ReadKey();
    }
}

With this you can have a local database that you use in development, or when running unit tests that aren't really unit tests. (Strictly speaking, if a test is hitting a database, it's an integration test, not a unit test, but such tests can be very useful, even if they do violate doctrinal purity.)

And since your production installations are going to use connection strings that point to real database servers, instead of to local files, none of this messing about with DataDirectory will have any effect.