Pass multiple queries in c#

Question

How to pass multiple queries from a yaml file and execute in c#. In my code I am passing a single query, but want to execute multiple queries stored in a yaml file. I already have a yaml file with db connection details, similarly want to have another yaml file for queries

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"; //Instead want to pass multiple queries from yaml file

        [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)) //Multiple Query
                {
                    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();
            }
        }
    }
}

I want to process multiple queries as stored in the yaml file

Ex Query yaml file

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

Answer 1

You might create a record for that and add it to the configuration as a list and then just iterate using foreach

record TestQueryEntry ( string Name, string QueryStr, string? someotheroption );

public class Configuration
{
    public Database Database { get; set; }
    public IList<TestQueryEntry> QueriesToTest {get;set;}
    // or if you just want the strings withouth anything extra
    // public IList<string> QueriesToTest {get;set;}
}

database:
   host: "myhost"
   port: 6380
   dbName: "mydb"
   userName: "userabc"
   password: "password"
queriesToTest:
   - name: "mickey"
     queryStr: "SELECT bla bla bla"
   - name: "goofy"
     queryStr: "SELECT bla bla bla"
     someotheroption: "a nullable option"
stringOnlyQueriesToTest:
   - "SELECT bla bla bla"
   - "SELECT bla bla bla"
   - "SELECT bla bla bla"

BUT, looking at the code i understand you want to automate testing multiple queries in a Unit test. And what you're doing is not the correct way.

Here are some resources:

• https://docs.nunit.org/articles/nunit/technical-notes/usage/Parameterized-Tests.html
• https://docs.nunit.org/articles/nunit/writing-tests/attributes/testcasesource.html
• https://docs.nunit.org/articles/nunit/technical-notes/usage/SetUp-and-TearDown.html?q=setup

What you might want to do is (inside the test class)

Connection DatabaseConn {get;set;} // or whatever your connection is

[Setup]
void Setup ()
{
  /* here you initialize once the connection string */
}

static IEnumerable<string> QueriesTestCaseSource ()
{
  yield return "SELECT * FROM someTAble WHERE yadda";
  yield return "SELECT * FROM someTAble WHERE yadda yadda";
  yield return "SELECT * FROM someTAble WHERE yadda yadda yadda";
}

[TestCaseSource(nameof(QueriesTestCaseSource))]
void TestSomeQueries ( string sqlString ){
  /* here you put the fun stuff */
  // 1. open the connection
  // 2. run the query on this.DatabaseConn, do not use try catch,
  //      the test will fail if there's a problem. 
  //      that's what a test is for
  // 3. close the connection
}