数据表到mdb [英] Data Table to mdb

问题描述

我在C＃中有一个内存DataTable。我想将本地的.mdb副本保存在我的文件系统上，并将其复制。这个C＃datatable从excel公式捕获结果并保存它。当同一个Excel文件再次打开时，我想要加载datatable。这个表就像我要重用的本地缓存。

如何将C＃Datatable文件写入.mdb文件并加载特定的表，当具有相同名称的excel文件再次加载？

解决方案

使用ADO.NET

ADO.NET架构（概述，示例）

许多样本（如下图）在互联网上可以使用ADO.NET的Google搜索关键字（其中许多在此答案中）。

一般流程是根据与MSAccess兼容的数据提供者决定的。在这种情况下，我们使用System.Data.OleDb;

在名称空间中选择OleDb一般的ADO.NET流是

• 制作一个连接对象 - 登录到db的位置和安全参数


• 创建一个命令对象表示数据库操作（例如插入，更新，删除，选择）这也是SQL进入的地方。


• 发出命令（执行它）通过连接影响数据库（或查询数据


• 必须在数据库上重复使用命令


• 关闭您的连接。

为Access创建适当的连接字符串，使用GUI工具（例如，视觉工作室）或从 ConnectionString.com（访问页面）等网站查找

以下示例非常一般，您需要根据需要进行自定义。有如何做到这一点的许多变化。
为了简单起见，这个答案选择一个简短的手段。

  using System; 
使用System.Data; // for DataTable，DataSet 
 using System.Data.OleDb; // for ADO.NET OLEDb provider 
  

---

  void SaveMyDataTable（DataTable datTable）{
 
 string strConnection =您的连接字符串访问; 
 
 //建立连接。 
 OleDbConnection conn = new OleDbConnection（strConnection）; 
 conn.Open（）; 
 
 try {
 OleDbCommand cmd = conn.CreateCommand（）; 
 
 //在Access中创建表。 
 cmd.CommandText =CREATE TABLE SomeTable（
 +Field1 int，
 +ThisField varchar（255）
 +ThatField varchar（255）
 +）; 
 cmd.ExecuteNonQuery（）; 
 
 / *从datatable插入数据。 
 *（您必须将数据从数据表行中提取
 *并在此处使用）
 * / 
 
 cmd.CommandText =INSERT INTO SomeTable VALUES（1，'a'，'b'）; 
 cmd.ExecuteNonQuery（）; 
 cmd.CommandText =INSERT INTO SomeTable VALUES（3，'d'，'e'）; 
 cmd.ExecuteNonQuery（）; 
 // etc ...（可能在DataTable中的行循环）
 
 conn.Close（）; 
} 
 finally {
 conn.Close（）; 
} 
 
} 
  

另一个ADO.NET方式< h2>

ADO.NET中的一个替代方案

OleDbDataAdapter 类 - ADO.NET架构的一部分用作<一个href =http://msdn.microsoft.com/en-us/library/system.data.dataset(VS.80).aspx =nofollow noreferrer> DataSet ，其中包含DataTables和兼容Ole db数据源（如Access）。

这有点难以设置，你需要研究它。

I have an in memory DataTable in C#. I want to save a local .mdb copy of that on to my file system and retrive it . This C# datatable captures results from an excel formula and saves it. When the same excel file is opened again, I want to have the datatable loaded. This table is like a local cache that I want to reuse.

How do i write C# Datatable files to .mdb files and load the particular table when the excel file with the same name loads again?

解决方案

Using ADO.NET

ADO.NET architecture (overview, sample)

Many samples (like the one below) are available on the Internet by Googling keywords of ADO.NET(many of which are in this answer).

The general flow is to decided upon a data provider compatible with MSAccess. In this case we choose OleDb in the namespace using System.Data.OleDb;

The general ADO.NET flow is

• Make an Connection object - the location of, and security parameters to login to the db
• Create a Command object to represent a database action (e.g. Insert, Update, Delete, Select) This is also where the SQL comes in.
• Issue the command (Execute it) through the connection to affect the database (or query data from it)
• repeat commands on the database is necessary
• Close your connection.

Make the appropriate connection string for Access, either build it using GUI tools (e.g. visual studio) or find it from a site like ConnectionString.com (Access page)

The following sample is very general and you will need to customize it for your needs. There are many variations on how to do this. For simplicity purposes this answer chooses a short means.

using System; 
using System.Data;   // for DataTable, DataSet
using System.Data.OleDb;  // for ADO.NET OLEDb provider

---

void SaveMyDataTable(DataTable datTable) {

    string strConnection = "your connection string to Access";

    // Make connection.
    OleDbConnection conn = new OleDbConnection(strConnection);
    conn.Open();

    try {
        OleDbCommand cmd = conn.CreateCommand();

        // Create table in Access.
        cmd.CommandText = "CREATE TABLE SomeTable("
                + "Field1 int,"
                + "ThisField varchar(255)"
                + "ThatField varchar(255)"
                + ")";
        cmd.ExecuteNonQuery();

        /* Insert data from datatable.
         * (You'll have to pull data out of your DataTable row
         *  and use it here.)
         */

        cmd.CommandText = "INSERT INTO SomeTable VALUES (1, 'a','b')";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "INSERT INTO SomeTable VALUES (3, 'd','e')";
        cmd.ExecuteNonQuery();
        //etc... (maybe make a loop over the rows in your DataTable)

        conn.Close();
    }
    finally {
        conn.Close();
    }

}

Another ADO.NET Way

An alternative within ADO.NET

The OleDbDataAdapter class -- part of the ADO.NET architecture serves as an adapter between a DataSet which contains DataTables and compatible Ole db data sources (like Access).

This is a bit harder to set up and you would need to research it.

这篇关于数据表到mdb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！