C#如何读取YAML文件存储的多条查询语句并执行

问题背景 #

现有代码仅支持硬编码单条SQL查询，需要改造为从独立YAML文件读取多条查询统一管理执行。目前数据库连接配置已通过db-properties.yaml存储，查询配置将参照该模式实现。

现有数据库配置（db-properties.yaml） #

database:
   host: "myhost"
   port: 6380
   dbName: "mydb"
   userName: "userabc"
   password: "password"

原有业务代码 #

namespace NunitTestCase
{
    public class Database
    {
        public string Host { get; set; }
        public int Port { get; set; }
        public string DbName { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
    }
    public class Configuration
    {
        public Database Database { get; set; }
    }

    [TestFixture]
    public class Test
    {   
        string query = "SELECT * FROM EMPLOYEE"; // 硬编码单条查询，需替换为YAML读取的多查询

        [Test]
        public void Test()
        {
            var yamlString = File.ReadAllText(@"C:\Users\admin\db-properties.yaml");
            var deserializer = new DeserializerBuilder().WithNamingConvention(new CamelCaseNamingConvention()).Build();
            var config = deserializer.Deserialize<Configuration>(yamlString);
            var builder = new ConnectionStringBuilder();
            builder.UserName = config.Database.UserName;
            builder.Password = config.Database.Password;
            builder.Port = config.Database.Port;
            builder.Host = config.Database.Host;
            builder.Database = config.Database.DbName;

            using (var con = new Connection(builder.ConnectionString))
            {
                con.Open();
                using (var cmd = new Command(query, con)) // 需改造为支持多查询执行
                {
                    var rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            object o = null;
                            try
                            {
                                o = rdr.GetValue(i);
                            }
                            catch (Exception ex)
                            {
                                o = ex.Message;
                            }
                            Console.WriteLine(o);
                        }
                    }
                }
                con.Close();
            }
        }
    }
}

目标查询YAML结构 #

Queries:   
   query1: "Select * from Table1"
   query2: "Select * from Table2"
   queryN: "Select * from TableN"

注：原示例中query1的form为拼写错误，实际使用需修正为from

实现步骤 #

1. 新增查询配置映射类 #

和现有数据库配置的映射逻辑一致，新增类对应YAML的查询节点，由于Queries下是动态键值对结构，直接用Dictionary<string, string>接收即可：

public class QueryConfiguration
{
    // Key对应YAML中query1/query2这类查询标识，Value对应具体SQL语句
    public Dictionary<string, string> Queries { get; set; }
}

2. 新建查询配置YAML文件 #

创建独立的查询配置文件（比如命名为queries.yaml），和数据库配置文件放在统一目录，内容参考：

Queries:
  query1: "SELECT * FROM Table1"
  query2: "SELECT * FROM Table2"
  queryEmployee: "SELECT * FROM EMPLOYEE"
  # 后续新增查询直接在下方追加键值对即可

3. 改造执行逻辑 #

核心改动点：

• 移除硬编码的单条query变量
• 读取并反序列化查询配置YAML
• 遍历所有查询条目逐个执行，执行时打印查询标识方便区分结果
• 每个查询执行完成后主动释放读取器资源，避免影响后续查询
  改造后的完整代码：

using YamlDotNet.Serialization;
using YamlDotNet.Serialization.NamingConventions;
// 保留原有其他using引用

namespace NunitTestCase
{
    public class Database
    {
        public string Host { get; set; }
        public int Port { get; set; }
        public string DbName { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
    }
    public class Configuration
    {
        public Database Database { get; set; }
    }

    public class QueryConfiguration
    {
        public Dictionary<string, string> Queries { get; set; }
    }

    [TestFixture]
    public class Test
    {   
        // 配置路径可抽为常量统一维护，生产环境建议放到配置项中，不要硬编码绝对路径
        private const string DbConfigPath = @"C:\Users\admin\db-properties.yaml";
        private const string QueryConfigPath = @"C:\Users\admin\queries.yaml";

        [Test]
        public void Test()
        {
            var deserializer = new DeserializerBuilder()
                .WithNamingConvention(new CamelCaseNamingConvention())
                .Build();

            // 加载数据库配置
            var dbYamlContent = File.ReadAllText(DbConfigPath);
            var dbConfig = deserializer.Deserialize<Configuration>(dbYamlContent);
            
            // 加载查询配置
            var queryYamlContent = File.ReadAllText(QueryConfigPath);
            var queryConfig = deserializer.Deserialize<QueryConfiguration>(queryYamlContent);

            // 构造数据库连接
            var builder = new ConnectionStringBuilder();
            builder.UserName = dbConfig.Database.UserName;
            builder.Password = dbConfig.Database.Password;
            builder.Port = dbConfig.Database.Port;
            builder.Host = dbConfig.Database.Host;
            builder.Database = dbConfig.Database.DbName;

            using (var con = new Connection(builder.ConnectionString))
            {
                con.Open();
                
                // 遍历所有查询逐个执行
                foreach (var queryItem in queryConfig.Queries)
                {
                    string queryName = queryItem.Key;
                    string sql = queryItem.Value;

                    Console.WriteLine($"===== 执行查询：{queryName} | SQL：{sql} =====");
                    using (var cmd = new Command(sql, con))
                    {
                        var rdr = cmd.ExecuteReader();
                        while (rdr.Read())
                        {
                            for (int i = 0; i < rdr.FieldCount; i++)
                            {
                                object result = null;
                                try
                                {
                                    result = rdr.GetValue(i);
                                }
                                catch (Exception ex)
                                {
                                    result = ex.Message;
                                }
                                Console.WriteLine(result);
                            }
                        }
                        rdr.Close();
                    }
                    Console.WriteLine($"===== 查询 {queryName} 执行完成 =====\n");
                }
                con.Close();
            }
        }
    }
}

补充说明 #

• 现有代码已经引入YamlDotNet依赖，无需额外安装Nuget包
• 如果需要单条查询失败不中断后续执行，可以在遍历执行的cmd逻辑外层增加try-catch捕获异常
• 如果需要对查询做分类，可以调整YAML结构和对应映射类，比如按业务模块分组存储SQL

内容的提问来源于stack exchange，提问作者kr_devops