c# - versión - vinculacion de datos definicion

¿Cómo estructurar una consulta OleDbCommand para poder tomar tablas de un.MDB y reemplazarlas en otro.MDB?

Question

Estoy tratando de tomar tablas de un archivo de base de datos de acceso, agregarlas a otro archivo de base de datos de acceso con la misma estructura pero con información diferente. Necesito sobreescribir cualquier tabla existente. Ya casi termino con mi proyecto, esta es mi última pared de ladrillo.

Estoy usando un archivo de clase separado denominado DatabaseHandling.cs para trabajar con los archivos de la base de datos de Access.

Aquí está mi TODO el código actual DatabaseHandling.cs. Esto se mantiene actualizado por ahora.

Código:

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



        }          
    }

Simplemente quiero tomar una tabla de datos que está en la memoria y escribirla en un archivo .MDB. He intentado hacer esto por más de 30 horas.

ÚLTIMA EDICIÓN:

De acuerdo, agregó un nuevo código. Obtengo un nuevo error en tiempo de ejecución: error de sintaxis en la cláusula FROM.

Código:

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. !!!
        }
    }
}

Una vez más, no funciona. Avíseme si necesita información adicional.

Answer 1

@Mark Brackett tenía muy cerca el motivo por el que obtienes el no DeleteCommand porque OleDbCommandBuilder se está deshaciendo, así que mueve ese soporte y deberías estar bien.

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

Actualizar

Pruebe este código de excepción

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

actualizar

intente cambiar

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

a

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

Estoy tentado de saber si es solo una fila que arroja el error o si es la consulta. Mi pensamiento es que una de las filas tiene un valor funky

Answer 2

Intenta reemplazar

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

con

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` = ?)");

Donde las consultas son válidas para la estructura de su tabla.

Answer 3

Me da la sensación de que realmente no estás asimilando todo lo de DataTable / DataRow. Verá, en una base de datos, realmente no trabaja con tablas , sino con filas. Si desea "sobrescribir" TableB con las filas de TableA, primero debe eliminar todas las filas en TableB y luego insertar copias de todas las filas de TableA.

Suponiendo que la tabla de destino ya existe, puede hacer la inserción llenando desde 1 fuente y luego estableciendo las filas en Agregado. El DataAdapter luego ejecutará un comando de inserción de SQL para cada fila agregada.

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

EDITAR: divida la tabla de sobrescritura en un método diferente para acomodar su tabla de datos de memoria. También se agregaron citas alrededor de las declaraciones Sql generadas para sus nombres de columna de Año y Mes reservados. Los objetos movidos se deshacen de CommandBuilder como lo establece bendewey .