C#, MySQL, looping.reading through rows and storing multiple values

I would avoid writing all the boilerplate ADO.NET Code and use Dapper. Dapper is a micro ORM and has no DB specific implementation details. It works across all .NET ADO providers including MySQL. It will handle mapping the results into a strongly typed class.

example

public class NextFormResult
{
    public string Field1 { get; set; }
    public string Field2 { get; set; }
}

string sql = "SELECT * FROM nextcare_form";

using (var connection = new SqlConnection("YOUR CONNECTION"))
{            
    var nextFormResult = connection.Query<NextFormResult>(sql).ToList();

    // more code
}

First, create a class which will hold all of your variables:

class Item
{
    public string Item1 {get;set;}
    public string Item2 {get;set;}
    public string Item3 {get;set;}
    .
    .
    public string Item15 {get;set;}

}

Now, create a list:

List<Item> items = new List<Item>();

Then, execute MySQL command to fetch data from database and loop through it:

long recCount = 0;
MySqlCommand mySqlCommand = new MySqlCommand();
mySqlCommand.Connection = connection;
mySqlCommand.CommandText = "SELECT COUNT(*) FROM nextcare_form";
recCount = (Int64)mySqlCommand.ExecuteScalar();

if (recCount > 0)
{
    mySqlCommand.CommandText = "SELECT * FROM nextcare_form";
    MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

    while (mySqlDataReader.Read())
    {
        if (!DBNull.Value.Equals(mySqlDataReader.GetValue(0)))
        {
            items.Item1 = (string)mySqlDataReader.GetValue(0);
        }

        if (!DBNull.Value.Equals(mySqlDataReader.GetValue(1)))
        {
            items.Item2 = (string)mySqlDataReader.GetValue(1);
        }

        if (!DBNull.Value.Equals(mySqlDataReader.GetValue(2)))
        {
            items.Item3 = (string)mySqlDataReader.GetValue(2);
        }
        .
        .
        .
        if (!DBNull.Value.Equals(mySqlDataReader.GetValue(14)))
        {
            items.Item15 = (string)mySqlDataReader.GetValue(14);
        }
    }
}

In this way, you can get and store all the values... Hopefully, it will solve your problem

I think @William Xifaras 's Dapper idea is probably the best solution but here is a solution with your existing technologies.

I used a DataTable so I could close the connection immediately after the DataTable is filled. I can manipulate the data in the DataTable after the connection is closed.

I used a List to store your data in variables.

using blocks ensure that your database objects are closed and disposed even if there is an error.

    public class NextCare
    {
        public string Field1 { get; set; }
        public string Field2 { get; set; }
        public string Field3 { get; set; }
        public NextCare(string f1, string f2, string f3)
        {
            Field1 = f1;
            Field2 = f2;
            Field3 = f3;
        }
    }

    private List<NextCare> OpCode()
    {
        DataTable dt = new DataTable();
        List<NextCare> lst = new List<NextCare>();
        using (MySqlConnection cn = new MySqlConnection("Your connection string"))
        using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM nextcare_form;", cn))
        {
            cn.Open();
            dt.Load(cmd.ExecuteReader());
        }
        foreach (DataRow row in dt.Rows)
        {
            NextCare nc = new NextCare(row[0].ToString(), row[1].ToString(), row[2].ToString());
            lst.Add(nc);
        }
        return lst;
    }