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
– MethodMan, Commented Mar 12, 2013 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
– Mike Perrenoud, Commented Mar 12, 2013 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
– Sasha, Commented Mar 12, 2013 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
– DeMama, Commented Mar 12, 2013 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
– DeMama, Commented Mar 13, 2013 at 22:06

Community · Accepted Answer · 2017-05-23 12:04:54Z

2

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++;
}

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Mar 15, 2013 at 10:14

WiiMaxx

5,4508 gold badges59 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikola Davidovic · Accepted Answer · 2013-03-12 15:19:02Z

0

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();
}

answered Mar 12, 2013 at 15:19

Nikola Davidovic

8,6761 gold badge31 silver badges34 bronze badges

Comments

Yahya · Accepted Answer · 2013-03-12 15:22:37Z

0

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.

answered Mar 12, 2013 at 15:22

Yahya

3,4743 gold badges25 silver badges42 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:04:54Z

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, 2017 at 12:04

CommunityBot

11 silver badge

answered Mar 12, 2013 at 15:31

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

4 Comments

DeMama Over a year ago

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

Olivier Jacot-Descombes Over a year ago

I assumed WinForms, but apparantly you are using WPF. (I added a corresponding tag.)

DeMama Over a year ago

Yes indeed, i use wpf, i assumed it was irrelevant regarding this question... thx!

Olivier Jacot-Descombes Over a year ago

I changed my answer to use WPF. I hope it will work (I'm not using WPF myself).

Collectives™ on Stack Overflow

Efficient way to retrieve multiple values from the same column

4 Answers 4

Step 1

Step 2

Comments

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Step 1

Step 2

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related