.NET - Generate data schema for Access DB

  • 首先添加数据库连接

Server Explorer -> Add Connection

Data Source: Microsoft Access Database File (OLE DB) Database file name: 选择Access的数据文件

测试连接,Ok,可以看见添加的表结构

  • 然后新建DataSet

Add New Item -> Data -> DataSet

把表从Server Explorer中拖拽至DataSet Designer,如 Customers、Employees

  • 在Main函数中使用如下代码测试:
static void Main(string[] args)
{
    var DataDirectory = new System.IO.DirectoryInfo(System.IO.Path.Combine(System.IO.Directory.GetCurrentDirectory(), "../../")).FullName;
    AppDomain.CurrentDomain.SetData("DataDirectory", DataDirectory);
    using (var adapter = new Northwind2003DataSetTableAdapters.CustomersTableAdapter()) {
        var dt = adapter.GetData();
        System.Console.WriteLine(dt[0].Address);
        dt[0].Address = dt[0].Address + "," + ((new Random()).Next() % 100).ToString();
        adapter.Update(dt);
    }
}

运行后就可以看见更新的结果了。

注意:如果程序运行Ok但是结果没有写入到数据库,需要查看是否是数据路径配置的问题,检查app.config

默认的connectionString配置为:

connectionString=”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Northwind2003.mdb”

可以如高亮行所示调用

AppDomain.CurrentDomain.SetData("DataDirectory", DataDirectory);
或者直接把 DataDirectory 改成绝对路径。
  • 关于DataDirectory的解释(MSDN)
|DataDirectory| (enclosed in pipe symbols) is a substitution string that indicates the path to the database. It eliminates the need to hard-code the full path which leads to several problems as the full path to the database could be serialized in different places. DataDirectory also makes it easy to share a project and also to deploy an application.

For example, instead of having the following connection string:

“Data Source= c:\program files\MyApp\Mydb.sdf”

Using DataDirectory, you can have the following connection string:

“Data Source = |DataDirectory|\Mydb.sdf”

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules will be applied to access the database folder:

• For applications that are put in a folder on the user’s computer, the database folder uses the application folder.
• For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.