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("所有数据迁移完成！");
	}
}