Confused about how to work with List[]

Question

I'm learning on how to access MySQL database using C# by following this guide

In those codes, it uses List<string>[4] to store the Selected data because it has 4 columns

After some experiment, I found out that those codes store the data in a format like this:

//List[0] = Name1, Name2, Name3, ...

//List[1] = Age1, Age2, Age3, ...

//...

I want to access the data row by row and display it in ListView,

but if we use foreach, it will list the data column by column

foreach (List<string> entry in dataEntries){
    ListViewItem lvi = new ListViewItem(entry[0]); //refer to Name1 during first loop

    lvi.SubItems.Add(entry[1]); //Name2
    lvi.SubItems.Add(entry[2]); //Name3
    lvi.SubItems.Add(entry[3]); //and so on
    listView1.Items.Add(lvi);
}

Is there a way to display it row by row? Or is there any simpler workaround?

Answer 1

Yes this article is quite bad. I put the code from the article at the end. Here is how I would do it:

Note: I made everything a string as he did, which seems wrong. Also, I did not test so there might be typos.

class DataItem 
{
   public string id { get; set; }
   public string name { get; set; }
   public string age { get; set; }
}

//Select statement
public List<DataITem> Select()
{
    string query = "SELECT * FROM tableinfo";

    //Create a list to store the result
    List<DataItem> list = new List<DateItem>();

    //Open connection
    if (this.OpenConnection())
    {
        //Create Command
        using (MySqlCommand cmd = new MySqlCommand(query, connection))
        {
           //Create a data reader and Execute the command
           using (MySqlDataReader dataReader = cmd.ExecuteReader())
           {

              //Read the data and store them in the list
              while (dataReader.Read())
              {
                 list.Add(new DataItem() { id = dataReader["id"] + "",
                                           name = dataReader["name"] + "",
                                           age = dataReader["age"] + "" });
              }
           }

        }

        this.CloseConnection();
    }

    return list;
}

Code from article:

//Select statement
public List< string >[] Select()
{
    string query = "SELECT * FROM tableinfo";

    //Create a list to store the result
    List< string >[] list = new List< string >[3];
    list[0] = new List< string >();
    list[1] = new List< string >();
    list[2] = new List< string >();

    //Open connection
    if (this.OpenConnection() == true)
    {
        //Create Command
        MySqlCommand cmd = new MySqlCommand(query, connection);
        //Create a data reader and Execute the command
        MySqlDataReader dataReader = cmd.ExecuteReader();

        //Read the data and store them in the list
        while (dataReader.Read())
        {
            list[0].Add(dataReader["id"] + "");
            list[1].Add(dataReader["name"] + "");
            list[2].Add(dataReader["age"] + "");
        }

        //close Data Reader
        dataReader.Close();

        //close Connection
        this.CloseConnection();

        //return list to be displayed
        return list;
    }
    else
    {
        return list;
    }
}

Answer 2

This part of the tutorial:

public List< string >[] Select()
{
    string query = "SELECT * FROM tableinfo";

    //Create a list to store the result
    List< string >[] list = new List< string >[3];
    list[0] = new List< string >();
    list[1] = new List< string >();
    list[2] = new List< string >();

    //Open connection
    if (this.OpenConnection() == true)
    {
        //Create Command
        MySqlCommand cmd = new MySqlCommand(query, connection);
        //Create a data reader and Execute the command
        MySqlDataReader dataReader = cmd.ExecuteReader();

        //Read the data and store them in the list
        while (dataReader.Read())
        {
            list[0].Add(dataReader["id"] + "");
            list[1].Add(dataReader["name"] + "");
            list[2].Add(dataReader["age"] + "");
        }

        //close Data Reader
        dataReader.Close();

        //close Connection
        this.CloseConnection();

        //return list to be displayed
        return list;
    }
    else
    {
        return list;
    }
}

is what you're referring to, I suppose. It's not a good way to store the results, and it's rather poorly-written to boot. You could just as easily do:

public List<object[]> Select() {
    string query = "SELECT * FROM tableinfo";

    // Create a list to store the result
    var result = new List<object[]>();

    // Open connection
    if (this.OpenConnection()) {
        // Create Command
        var cmd = new MySqlCommand(query, connection);
        // Create a data reader and Execute the command
        MySqlDataReader dataReader = cmd.ExecuteReader();

        // Read the data and store them in the list
        while(dataReader.Read()) {
            object[] items = new object[dataReader.FieldCount];
            dataReader.GetValues(items);
            result.Add(items);
        }

        // Close Data Reader
        dataReader.Close();

        // Close Connection
        this.CloseConnection();

        // Return list to be displayed
        return list;
    } else {
        // This is a bad thing.
        throw new ApplicationException("Could not connect to database.");
    }
}

And if I recall database access correctly, this is how you're supposed to do it:

public DataSet Select() {
    if(!this.OpenConnection()) {
        throw new ApplicationException("Could not connect to database.");
    }

    using(var query = new MySqlDataAdapter("SELECT * FROM tableinfo", this.connection)) {
        var data = new DataSet();

        query.Fill(data);

        return data;
    }
}