"how to embed a database in a visual studio solution?" Code Answer

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.