Data Table to mdb

Question

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?

Answer 1

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.