How do I structure an OleDbCommand Query so that I can take Tables from one .MDB, and replace them in another .MDB

Question

I am trying to take tables from one Access Database File, add them to another Access Database file with the exact same structure but with different information. I need to overwrite any existing tables. I am almost done with my project this is last my brick wall.

I am using a separate class file named DatabaseHandling.cs to work with the Access Database files.

Here is my ENTIRE current DatabaseHandling.cs code. This is kept up to date for now on.

Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;

namespace LCR_ShepherdStaffupdater_1._0
{
    public class DatabaseHandling
    {
        static DataTable datatableB = new DataTable();
        static DataTable datatableA = new DataTable();
        public static DataSet datasetA = new DataSet();
        public static DataSet datasetB = new DataSet();
        static OleDbDataAdapter adapterA = new OleDbDataAdapter();
        static OleDbDataAdapter adapterB = new OleDbDataAdapter();
        static string connectionstringA = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Settings.getfilelocationA();
        static string connectionstringB = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Settings.getfilelocationB();
        static OleDbConnection dataconnectionB = new OleDbConnection(connectionstringB);
        static OleDbConnection dataconnectionA = new OleDbConnection(connectionstringA);
        static DataTable tableListA;
        static DataTable tableListB;

        static public void addTableA(string table, bool addtoDataSet)
        {
            dataconnectionA.Open();
            datatableA = new DataTable(table);
            try
            {
                OleDbCommand commandselectA = new OleDbCommand("SELECT * FROM [" + table + "]", dataconnectionA);
                adapterA.SelectCommand = commandselectA;
                adapterA.Fill(datatableA);
            }
            catch
            {
                Logging.updateLog("Error: Tried to get " + table + " from DataSetA. Table doesn't exist!", true, false, false);
            }

            if (addtoDataSet == true)
            {
                datasetA.Tables.Add(datatableA);
                Logging.updateLog("Added DataTableA: " + datatableA.TableName.ToString() + " Successfully!", false, false, false);
            }

            dataconnectionA.Close();
        }

        static public void addTableB(string table, bool addtoDataSet)
        {
            dataconnectionB.Open();
            datatableB = new DataTable(table);

            try
            {
                OleDbCommand commandselectB = new OleDbCommand("SELECT * FROM [" + table + "]", dataconnectionB);
                adapterB.SelectCommand = commandselectB;
                adapterB.Fill(datatableB);
            }
            catch
            {
                Logging.updateLog("Error: Tried to get " + table + " from DataSetB. Table doesn't exist!", true, false, false);
            }



            if (addtoDataSet == true)
            {
                datasetB.Tables.Add(datatableB);
                Logging.updateLog("Added DataTableB: " + datatableB.TableName.ToString() + " Successfully!", false, false, false);
            }

            dataconnectionB.Close();
        }

        static public string[] getTablesA(string connectionString)
        {
            dataconnectionA.Open();
            tableListA = dataconnectionA.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
            string[] stringTableListA = new string[tableListA.Rows.Count];

            for (int i = 0; i < tableListA.Rows.Count; i++)
            {
                stringTableListA[i] = tableListA.Rows[i].ItemArray[2].ToString();
            }
            dataconnectionA.Close();
            return stringTableListA;
        }

        static public string[] getTablesB(string connectionString)
        {
            dataconnectionB.Open();
            tableListB = dataconnectionB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
            string[] stringTableListB = new string[tableListB.Rows.Count];

            for (int i = 0; i < tableListB.Rows.Count; i++)
            {
                stringTableListB[i] = tableListB.Rows[i].ItemArray[2].ToString();
            }
            dataconnectionB.Close();
            return stringTableListB;
        }

        static public void createDataSet()
        {

            string[] tempA = getTablesA(connectionstringA);
            string[] tempB = getTablesB(connectionstringB);
            int percentage = 0;
            int maximum = (tempA.Length + tempB.Length);

            Logging.updateNotice("Loading Tables...");
            Logging.updateLog("Started Loading File A", false, true, false);
            for (int i = 0; i < tempA.Length ; i++)
            {
                if (!datasetA.Tables.Contains(tempA[i]))
                {
                    addTableA(tempA[i], true);
                    percentage++;
                    Logging.loadStatus(percentage, maximum);
                }
                else
                {
                    datasetA.Tables.Remove(tempA[i]);
                    addTableA(tempA[i], true);
                    percentage++;
                    Logging.loadStatus(percentage, maximum);
                }
            }
            Logging.updateLog("Finished loading File A", false, true, false);
            Logging.updateLog("Started loading File B", false, true, false);
            for (int i = 0; i < tempB.Length ; i++)
            {
                if (!datasetB.Tables.Contains(tempB[i]))
                {
                    addTableB(tempB[i], true);
                    percentage++;
                    Logging.loadStatus(percentage, maximum);
                }
                else
                {
                    datasetB.Tables.Remove(tempB[i]);
                    addTableB(tempB[i], true);
                    percentage++;
                    Logging.loadStatus(percentage, maximum);
                }
            }
            Logging.updateLog("Finished loading File B", false, true, false);


        }

        static public DataTable getDataTableA()
        {
            datatableA = datasetA.Tables[Settings.textA];

            return datatableA;
        }
        static public DataTable getDataTableB()
        {
            datatableB = datasetB.Tables[Settings.textB];
            return datatableB;
        }

        static public DataSet getDataSetA()
        {
            return datasetA;
        }

        static public DataSet getDataSetB()
        {
            return datasetB;
        }

        static public void InitiateCopyProcessA()
        {
            DataSet tablesA;
            tablesA = DatabaseHandling.getDataSetA();

                foreach (DataTable table in tablesA.Tables)
                {
                    OverwriteTable(table, table.TableName);
                    Logging.updateLog("Copied " + table.TableName + " successfully.", false, true, false);
                }

        }

        static void OverwriteTable(DataTable sourceTable, string tableName)
        {
            using (var destConn = new OleDbConnection(connectionstringA))
            using (var destCmd = new OleDbCommand(tableName, destConn) { CommandType = CommandType.TableDirect })
            using (var destDA = new OleDbDataAdapter(destCmd))
            {
                // Since we're using a single table, we can have the CommandBuilder
                // generate the appropriate INSERT and DELETE SQL statements
                using (var destCmdB = new OleDbCommandBuilder(destDA))
                {
                    destCmdB.QuotePrefix = "["; // quote reserved column names
                    destCmdB.QuotePrefix = "]";
                    destDA.DeleteCommand = destCmdB.GetDeleteCommand();
                    destDA.InsertCommand = destCmdB.GetInsertCommand();

                    // Get rows from destination, and delete them
                    var destTable = new DataTable();
                    destDA.Fill(destTable);
                    foreach (DataRow dr in destTable.Rows)
                    {
                        dr.Delete();
                    }
                    destDA.Update(destTable);

                    // Set rows from source as Added, so the DataAdapter will insert them
                    foreach (DataRow dr in sourceTable.Rows)
                    {
                        dr.SetAdded();
                    }
                    destDA.Update(sourceTable);
                }
            }
        }



        }          
    }

I simply want to take a Datatable that is in memory and write it to a .MDB file. I have been attempting to do this for over 30 hours.

LATEST EDIT:

Okay, added new code. I get a new run-time error: Syntax error in FROM clause.

Code:

static public void InitiateCopyProcessA()
{
    DataSet tablesA;
    tablesA = DatabaseHandling.getDataSetA();

        foreach (DataTable table in tablesA.Tables)
        {
            OverwriteTable(table, table.TableName);
            Logging.updateLog("Copied " + table.TableName + " successfully.", false, true, false);
        }

}

static void OverwriteTable(DataTable sourceTable, string tableName)
{
    using (var destConn = new OleDbConnection(connectionstringA))
    using (var destCmd = new OleDbCommand(tableName, destConn) { CommandType = CommandType.TableDirect })
    using (var destDA = new OleDbDataAdapter(destCmd))
    {
        // Since we're using a single table, we can have the CommandBuilder
        // generate the appropriate INSERT and DELETE SQL statements
        using (var destCmdB = new OleDbCommandBuilder(destDA))
        {
            destCmdB.QuotePrefix = "["; // quote reserved column names
            destCmdB.QuotePrefix = "]";
            destDA.DeleteCommand = destCmdB.GetDeleteCommand();
            destDA.InsertCommand = destCmdB.GetInsertCommand();

            // Get rows from destination, and delete them
            var destTable = new DataTable();
            destDA.Fill(destTable);
            foreach (DataRow dr in destTable.Rows)
            {
                dr.Delete();
            }
            destDA.Update(destTable);

            // Set rows from source as Added, so the DataAdapter will insert them
            foreach (DataRow dr in sourceTable.Rows)
            {
                dr.SetAdded();
            }
            destDA.Update(sourceTable); // !!! Run-time error: Syntax error in FROM clause. !!!
        }
    }
}

Once again, it does not work. Let me know if you need additional information.

Answer 1

I get the feeling you're not really grokking the whole DataTable/DataRow thing. You see, in a database, you don't really work with tables - but with rows. If you want to "overwrite" TableB with TableA's rows, you'd first delete all of the rows in TableB and then insert copies of all the rows from TableA.

Assuming the destination table already exists, you can do the insert by filling from 1 source, and then setting the rows to Added. The DataAdapter will then run a SQL insert command for each added row.

static void CopyTable(string sourceConnectionString, string destinationConnectionString, string tableName) {
    // Get rows from source
    var sourceTable = new DataTable();
    using (var sourceConn = new OleDbConnection(sourceConnectionString))
    using (var sourceCmd = new OleDbCommand(tableName, sourceConn) { CommandType = CommandType.TableDirect })
    using (var sourceDA = new OleDbDataAdapter(sourceCmd)) {
        sourceDA.Fill(sourceTable);
    }

    OverwriteTable(sourceTable, destinationConnectionString, tableName);
}

static void OverwriteTable(DataTable sourceTable, string destinationConnectionString, string tableName) {
    using (var destConn = new OleDbConnection(destinationConnectionString))
    using (var destCmd = new OleDbCommand(tableName, destConn) { CommandType = CommandType.TableDirect })
    using (var destDA = new OleDbDataAdapter(destCmd)) {
        // Since we're using a single table, we can have the CommandBuilder
        // generate the appropriate INSERT and DELETE SQL statements
        using (var destCmdB = new OleDbCommandBuilder(destDA)) {
            destCmdB.QuotePrefix = "["; // quote reserved column names
            destCmdB.QuoteSuffix = "]";
            destDA.DeleteCommand = destCmdB.GetDeleteCommand();
            destDA.InsertCommand = destCmdB.GetInsertCommand();

            // Get rows from destination, and delete them
            var destTable = new DataTable();
            destDA.Fill(destTable);
            foreach (DataRow dr in destTable.Rows) {
                dr.Delete();
            }
            destDA.Update(destTable);

            // Set rows from source as Added, so the DataAdapter will insert them
            foreach (DataRow dr in sourceTable.Rows) {
               dr.SetAdded(); 
            }
            destDA.Update(sourceTable);
        }
    }    
}

EDIT: Split the OverwriteTable to a different method to accomodate your in memory data table. Also added quotes around generated Sql statements for your reserved Year and Month column names. Moved dispose of CommandBuilder as fixed by bendewey.

Answer 2

Try replacing

using (var destCmdB = new OleDbCommandBuilder(destDA)) 
{            
    destDA.DeleteCommand = destCmdB.GetDeleteCommand();            
    destDA.InsertCommand = destCmdB.GetInsertCommand();        
}

with

destDA.InsertCommand = new OleDbCommand("INSERT INTO `AdminUsers` (`UserName`, `Password`) VALUES (?, ?)");
destDA.DeleteCommand = new OleDbCommand("DELETE FROM `AdminUsers` WHERE (`ID` = ?)");
destDA.UpdateCommand = new OldDbCommand("UPDATE `AdminUsers` SET `UserName` = ?, `Password` = ? WHERE (`ID` = ?)");

Where the queries are valid to your table structure.

Answer 3

@Mark Brackett had it really close the reason your getting the no DeleteCommand is because the OleDbCommandBuilder is disposing so move that bracket and you should be good.

static void CopyTable(string sourceConnectionString, string destinationConnectionString, string tableName)
{
// Get rows from source    
var sourceTable = new DataTable();
using (var sourceConn = new OleDbConnection(sourceConnectionString))
using (var sourceCmd = new OleDbCommand(tableName, sourceConn) {CommandType = CommandType.TableDirect})
using (var sourceDA = new OleDbDataAdapter(sourceCmd))
{
    sourceDA.Fill(sourceTable);
}
using (var destConn = new OleDbConnection(destinationConnectionString))
using (var destCmd = new OleDbCommand(tableName, destConn) {CommandType = CommandType.TableDirect})
using (var destDA = new OleDbDataAdapter(destCmd))
{
    // Since we're using a single table, we can have the CommandBuilder        
    // generate the appropriate INSERT and DELETE SQL statements        
    using (var destCmdB = new OleDbCommandBuilder(destDA))
    {
        destDA.DeleteCommand = destCmdB.GetDeleteCommand();
        destDA.InsertCommand = destCmdB.GetInsertCommand();

        // Get rows from destination, and delete them        
        var destTable = new DataTable();
        destDA.Fill(destTable);
        foreach (DataRow dr in destTable.Rows)
        {
            dr.Delete();
        }
        destDA.Update(destTable);
        // Set rows from source as Added, so the DataAdapter will insert them        
        foreach (DataRow dr in sourceTable.Rows)
        {
            dr.SetAdded();
        }
        destDA.Update(sourceTable);
    }
}

Update

Try this exception code

static public void InitiateCopyProcessA()
{
    DataSet tablesA;
    tablesA = DatabaseHandling.getDataSetA();
    int i = 0;
    string tableName = "";
    try
    {
        foreach (DataTable table in tablesA.Tables)
        {
            tableName = table.TableName;  // for debugging the exception
            CopyTable(connectionstringA, connectionstringB, table.TableName);
        }
    }
    catch(Exception ex)
    {
        throw new Exception("Error updating " + tableName, ex);
    }
}

update

try changing

// Set rows from source as Added, so the DataAdapter will insert them                
foreach (DataRow dr in sourceTable.Rows)        
{            
    dr.SetAdded();        
}

to

// only add the first row.
sourceTable.Rows[0].SetAdded()

I'm tempted to know if its just one row that;s throwing the bug or if its the query. My thought is that one of the rows has a funky value