Efficient way to retrieve multiple values from the same column

Question

I want to set some buttons text (content) with values I retrieve from a mysql.

till now I always did it like this:

if (rdr.Read())
{
    Item1.Visibility = Visibility.Visible;
    Item1txt.Text = rdr.GetString("item_name");
}
if (rdr.Read())
{
    Item2.Visibility = Visibility.Visible;
    Item2txt.Text = rdr.GetString("item_name");
}
if (rdr.Read())
{
    Item3.Visibility = Visibility.Visible;
    Item3txt.Text = rdr.GetString("item_name");
}

This way works fine because I retrieve the right values in each button, but it makes the readability horrible..

When I started this project I had zero knowledge of C# so I tried some things like:

while (rdr.Read())
{
    Item4.Visibility = Visibility.Visible;
    Item4txt.Text = rdr.GetString("item_name");
    Item5.Visibility = Visibility.Visible;
    Item5txt.Text = rdr.GetString("item_name");
}

But this gave me the same value retrieved from my database in my buttons..

example:

button 1: test1 | button 2: test1 | button 3: test1.. etc..

what i need:

button 1: test1 | button2: test2 | button 3: test3.. etc..

Now my knowledge of C# is getting better every day so I want to learn some new things.

Right now I'm trying to use the foreach loop but I have a feeling I'm missing something:

using (MySqlConnection conn = new MySqlConnection(_CS))
{
    conn.Open();
    string cmd = "SELECT * FROM ordertopos LIMIT 10";
    MySqlCommand custid = new MySqlCommand(cmd, conn);
    using (MySqlDataReader rdr = custid.ExecuteReader())
    {
        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Load(rdr);

        foreach (System.Data.DataRow row in dt.Rows)
        {
            Orderl1.Text = row["customerid"].ToString();
        }
    }
}

Essentially I want to know how I can set the content of my buttons, retrieved from mysql, in a more efficient en easier to maintain code..

I'm fairly new with foreach, so please be specific.

`Orderl1.Text = row["customerid"].ToString();` will always overwrite the existing text. you need to perhaps do something like this `Orderl1.Text += (string)row["customerid"];` where are you actually declaring and or initially using `Orderl1.Text`? `Wrap the SQL code around a try{}catch{} as well also step thru the code and make sure you are getting values as well — MethodMan, Mar 12 '13 at 15:16
Does the row itself hold a value (e.g. the customer id) that would allow you to derive the name of the button you wanted to set the text on? For example, if the customer id were 2, you want to set the text on `Button2`. — Mike Perrenoud, Mar 12 '13 at 15:16
As for me, your code is fine. You could refactor it to make it look better, but the core idea is correct. Especially first approach with if(rdr.Read()) - is the most efficient way possible (not taking into account accessing the values of reader by index instead of column name). — Sasha, Mar 12 '13 at 15:17
The default value of a button is empty, the value retrieved from my database will fill the button's text. the `customerid` is irrelevant regarding this question — DeMama, Mar 12 '13 at 15:29
@OlivierJacot-Descombes Yes, i know this; but since all the answers didn't fit my needs i can't tag any of them as an answer. I think next time i'll have to be a bit more explicit in my questions. I also did some tests and my conclusion was that i have to keep using my old way of loading mysql data **in my buttons content (text of buttons)** this is my first project & more will definitely come in the future, where i can learn new things, but after all, thanks ;-) — DeMama, Mar 13 '13 at 22:06

score 2 · Answer 1 · edited May 23 '17 at 12:04

i would recommend to do it in several step's for a better reusability

Step 1

List<string> myItems;

using (MySqlConnection conn = new MySqlConnection(_CS))
{
    conn.Open();
    string cmd = "SELECT * FROM ordertopos LIMIT 10";
    MySqlCommand custid = new MySqlCommand(cmd, conn);
    using (MySqlDataReader rdr = custid.ExecuteReader())
    {
        myItems= new List<string>();

        while (rdr.Read())
       {
            myItems.Add(rdr.GetString("item_name"));
       }
    }
}

Step 2

modified Olivier Jacot-Descombe version

for(int i =0; i< myItems.count; i++) {
    Button btn =   FindChild<Button>(this, "Item" + i); //"this" will be the control which contains your button's
    TextBlock tb = FindChild<TextBlock>(btn, "Item" + i  + "txt");
    btn.Visibility = Visibility.Visible;
    tb.Text =myItems[i];
    i++;
}

score 0 · Answer 2 · answered Mar 12 '13 at 15:19

If you want to scale your problem in order to use loops than you need to have a List or an Array that contains objects for which you want to set values of properties. In your particular case, put your Orders in a List<Order> and then you could use something like this:

int count = 0;
foreach (System.Data.DataRow row in dt.Rows)
{
     if(count<orders.Count)
         orders[count++].Text = row["customerid"].ToString();
}

score 0 · Answer 3 · answered Mar 12 '13 at 15:22

You need to traverse through your Items to set respective values. As DJ Kraze suggested, you are just overwriting the same control. And it will have the last accessed value (as it won't be overwritten once loop has ended).

You you just need to have somehow reference to your target controls, or if you are creating controls on the fly, than you can simply pass reference of newly created control every time you access a row from database.

score 0 · Answer 4 · edited May 23 '17 at 12:04

0

You can access controls by their name instead of their member variable by using the FindChild method found here: WPF ways to find controls.

int i = 1;
while (rdr.Read()) {
    Button btn =   FindChild<Button>(this, "Item" + i);
    TextBlock tb = FindChild<TextBlock>(btn, "Item" + i  + "txt");
    btn.Visibility = Visibility.Visible;
    tb.Text = rdr.GetString("item_name");
    i++;
}

This enables you to loop through them by using a counter (i).

edited May 23 '17 at 12:04

Community

1
1

answered Mar 12 '13 at 15:31

Olivier Jacot-Descombes

104,806
13
138
188

I'm using regular `buttons`, but for text wrapping reasons i place a `textblock` in my buttons, of course a normal button is `.Content` and textblock is `.Text` – DeMama Mar 12 '13 at 15:46
I assumed WinForms, but apparantly you are using WPF. (I added a corresponding tag.) – Olivier Jacot-Descombes Mar 12 '13 at 15:53
Yes indeed, i use wpf, i assumed it was irrelevant regarding this question... thx! – DeMama Mar 12 '13 at 16:06
I changed my answer to use WPF. I hope it will work (I'm not using WPF myself). – Olivier Jacot-Descombes Mar 12 '13 at 16:09

Efficient way to retrieve multiple values from the same column

4 Answers4

Step 1

Step 2