C# access迁移数据

[landv]

class Program
{
    static void Main()
    {
        // 源表
        string sourceConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\备份0728\DATA\fix\YCMISG.mdb;Jet OLEDB:Database Password=pzg576385";
        // 目标表
        string targetConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\备份0728\DATA\YCMISG.mdb;Jet OLEDB:Database Password=pzg576385";

        // 表名列表
        List<string> tables = new List<string>
        {
            "FPDemo",
            "LINEARE",
            "PRICE",
            "USERS",
            "OPERATOR",
            "LOGIN",
            "METER",
            "METERX",
            "SETUPTAB",
            "USERP",
            "USERSX",
            "POWER",
            //"UserEvent", // 可能会出现问题
        };

        using (OleDbConnection sourceConnection = new OleDbConnection(sourceConnectionString))
        using (OleDbConnection targetConnection = new OleDbConnection(targetConnectionString))
        {
            sourceConnection.Open();
            targetConnection.Open();

            using (OleDbTransaction transaction = targetConnection.BeginTransaction())
            {
                foreach (var tableName in tables)
                {
                    try
                    {
                        // 查询数据
                        string selectQuery = $"SELECT * FROM {tableName}"; 
                        using (OleDbCommand selectCommand = new OleDbCommand(selectQuery, sourceConnection))
                        using (OleDbDataReader reader = selectCommand.ExecuteReader())
                        {
                            // 获取所有字段名
                            var columnNames = new List<string>();
                            var valuePlaceholders = new List<string>();

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string sourceField = reader.GetName(i);
                                string targetField = sourceField.Replace("电", "水");

                                // 确保包含"电话"的字段不被替换
                                if (sourceField.Contains("电话"))
                                {
                                    targetField = sourceField;
                                }

                                columnNames.Add(targetField);
                                valuePlaceholders.Add("?");
                            }

                            // 构建插入语句（不指定字段名）
                            string insertQuery = $"INSERT INTO {tableName} VALUES ({string.Join(", ", valuePlaceholders)})";

//                            Console.WriteLine($"生成的插入语句: {insertQuery}");  // 输出生成的 SQL 语句

                            using (OleDbCommand insertCommand = new OleDbCommand(insertQuery, targetConnection, transaction))
                            {
                                while (reader.Read())
                                {
                                    // 构建值列表
                                    var values = new List<object>();
                                    for (int i = 0; i < reader.FieldCount; i++)
                                    {
                                        values.Add(reader[i]);
                                    }

                                    // 检查值数量是否与字段数量匹配
                                    if (values.Count != columnNames.Count)
                                    {
                                        throw new Exception("查询值的数目与目标字段中的数目不同。");
                                    }

                                    // 为命令添加参数
                                    insertCommand.Parameters.Clear();
                                    foreach (var value in values)
                                    {
                                        // 检查 value 是否为字符串，并且判断是否包含 "电"
                                        //var parameterValue = (value is string strValue && strValue.Contains("售电")) ? strValue.Replace("售电", "售水") : value;
                                        // 检查 value 是否为字符串
                                        var parameterValue = value is string strValue
                                            ? strValue
                                                .Replace("售电", "售水")  // 替换"售电"为"售水"
                                                .Replace("购电", "购水")  // 替换"购电"为"购水"
                                            : value;

                                        // 添加参数到命令中
                                        insertCommand.Parameters.AddWithValue("?", parameterValue ?? DBNull.Value);
                                        //insertCommand.Parameters.AddWithValue("?", value ?? DBNull.Value);
                                    }

                                    int rowsAffected = insertCommand.ExecuteNonQuery();  // 获取执行结果
                                    //Console.WriteLine($"插入 {tableName} 表影响的行数: {rowsAffected}");  // 输出执行结果
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        // 如果发生异常，回滚事务
                        Console.WriteLine($"Error processing table {tableName}: {ex.Message}");
                        if (ex.InnerException != null)
                        {
                            Console.WriteLine($"Inner Exception: {ex.InnerException.Message}");
                        }
                        transaction.Rollback();
                        return;
                    }
                }

                // 如果所有表都成功处理，提交事务
                transaction.Commit();
            }
        }

        Console.WriteLine("所有数据迁移完成！");
    }
}