如何在Visual Studio解决方案中嵌入数据库? [英] How to embed a database in a visual studio solution?
问题描述
我一直在阅读有关Visual Studio 2013的新Sql Server数据工具的出色之处，以及有关新的localdb数据库服务器等的信息，所以我一直在尝试做我认为似乎要点的事情.整个过程-将本地测试/开发数据库嵌入VS解决方案中，这样，当我将项目检出到干净目录中的新计算机上时，我只需运行应用程序即可连接到解决方案中的数据库.
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?
添加一些说明
在MSDN上，有一个关于如何在项目中包括localdb数据库的教程，
There's a tutorial on how to include a localdb database in a project, on MSDN, here:
但是，不幸的是，它不起作用.完全按照我的指示进行，并且似乎一切正常，直到将解决方案文件夹移动到新位置，这时它就失去了对数据库的跟踪.
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:
在配置文件中使连接字符串的AttachDbFilename相对
但是这个问题没有答案.
But that question has no answers.
推荐答案
所以，我找到了窍门.
ADO允许连接字符串以| DataDirectory |开头-这是一个替换字符串，由当前AppDomain的"DataDirectory"设置替换.
ADO allows connection strings to start with |DataDirectory| - which is a substitution string that is replaced by the "DataDirectory" setting of the current AppDomain.
这通常默认为.EXE的位置，尽管它随网站，单击一次安装等的不同而不同.
This usually defaults to the location of the .EXE, though it varies with websites, click-once installs, etc.
并且因为EntityFramework基于ADO构建，所以它也可以在EF中使用.
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.
我正在做的是在每个项目的App.config中放置一个与.EXE位置相对的路径的appSetting，并使用它在程序启动时进行设置:
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>
然后在代码中:
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="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>
并且:
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.)
并且由于您的生产安装将使用指向真实数据库服务器的连接字符串，而不是指向本地文件，因此与DataDirectory的任何混乱都不会产生任何影响.
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.
这篇关于如何在Visual Studio解决方案中嵌入数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
