How to embed a database into a visual studio solution?
I was reading about how wonderful Visual Studio 2013 is the new Sql Server Data Tools, and about the new localdb database server and so on, so I tried to do what would seem to me to be the point of the whole thing - embed a local test / development database into a VS solution so that when checking out a project to a clean directory on a new computer, I can just run the application connected to the database in the solution.
But I couldn't figure out how to do it.
Can anyone give me any hints? Or directions for a tutorial?
Adding some clarifications
There's a tutorial on how to include the localdb database in a project, on MSDN, here:
But unfortunately it doesn't work. I followed the instructions exactly and it all seemed to work until I moved the solution folder to a new location, after which I lost the database.
The problem is the connection string that contains the absolute path to where the database was when it was created. It's useless. I need to be able to test a project anywhere, on any computer, and build and run it 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 connectionString AttachDbFilename relative in config file
But this question has no answers.
source to share
So I found the trick.
ADO lets you start connection strings with | DataDirectory | - which is a substitution string that is replaced by the "DataDirectory" parameter of the current AppDomain.
This usually depends on the location of the .EXE, although it depends on websites, one-time installation, and so on.
And since EntityFramework is based on ADO, it works in EF as well.
What makes it work is that you can change it when you start the program, anywhere you want.
What I am doing is to set appSetting with the path to the .EXE location in each App.config project and use that to install at the beginning of the program:
<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 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="data source=(LocalDB)\v11.0;attachdbfilename=|DataDirectory|EmbeddedDatabase.mdf;integrated security=True;connect timeout=30;MultipleActiveResultSets=True;App=EntityFramework""
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 are not actually unit tests. (Strictly speaking, if a test hits the database, it is an integration test, not a unit test, but such tests can be very useful even if they violate doctrine.)
And since your production installations are going to use connection strings that point to real database servers, not local files, none of this DataDirectory mess will have any effect.
source to share
Take a look at "binary resources". In addition to resources such as icons, cursors, and string tables, arbitrary binary blobs can be added to the executable. One example would be to embed the hlsl precompiled shaders. Another example is sysinternals explorer. A 64-bit executable file is a resource inside an executable 32-bit executable file.
If your api database expects a file to mount, you may need to copy that resource to disk. Otherwise use directly. Examples can be found on the sdk platform.
source to share