问题描述

我正在尝试从一个 Access 数据库文件中获取表，将它们添加到另一个具有完全相同结构但具有不同信息的 Access 数据库文件中.我需要覆盖任何现有的表.我的项目快完成了，这是我的最后一堵砖墙.

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.

我正在使用一个名为 DatabaseHandling.cs 的单独类文件来处理 Access 数据库文件.

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

这是我当前的全部 DatabaseHandling.cs 代码.暂时保持最新状态.

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

代码:

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);
                }
            }
        }



        }          
    }

我只想获取内存中的数据表并将其写入 .MDB 文件.我已经尝试这样做了 30 多个小时.

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.

最新

好的，添加了新代码.我收到一个新的运行时错误:FROM 子句中的语法错误.

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

代码:

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.

推荐答案

尝试替换

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

与

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.

这篇关于如何构建 OleDbCommand 查询，以便我可以从一个 .MDB 中获取表，并在另一个 .MDB 中替换它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！