C# Create buttons from database with missing row

Question

First of all, sorry for my English :)

I would like to make buttons automatically from database. In the database, every button own an ID, and i call this ID.

My problem is simple, If one of the IDs missing (like 1,2,4,5), the program stop after the 2nd read. Here is the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace e_res
{
public partial class Layout : Form
{
    public Layout()
    {
        InitializeComponent();
    }

    private void Layout_Load(object sender, EventArgs e)
    {
        SQLFunctions Lgn = new SQLFunctions();
        Lgn.ConnectionToday();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = SQLFunctions.conn;

        int NumOfButtons = 40;
        int i = 1;
        cmd.CommandText = "SELECT id FROM Buttons where id='" + i + "'";
        int _bId = Int32.Parse(cmd.ExecuteScalar().ToString());

        //            int counter = 0;

        while ( _bId <= NumOfButtons)
        {
            if (_bId != null)
            {
                Button btn = new Button();
                {
                    btn.Tag = _bId;
                    btn.Dock = DockStyle.Fill;
                    btn.Margin = new Padding(10, 10, 10, 10);

                    cmd.CommandText = "SELECT bName FROM Buttons where id='" + btn.Tag + "'";
                    btn.Text = cmd.ExecuteScalar().ToString();
                    string btn_name = cmd.ExecuteScalar().ToString();
                    btn.Name = btn_name.ToString();

                    /*                    btn.Click += delegate
                                        {
                                            pass_txt.Clear();
                                            username_txt.Text = btn_name;
                                            username_lbl.Text = btn_name;
                                            username_lbl.Visible = true;
                                            pass_txt.ReadOnly = false;
                                        };*/

                }
                cmd.CommandText = "SELECT col FROM Buttons where id='" + btn.Tag + "'";
                int btn_col = Int32.Parse(cmd.ExecuteScalar().ToString());
                //                MessageBox.Show(btn_col.ToString());
                cmd.CommandText = "SELECT row FROM Buttons where id='" + btn.Tag + "'";
                int btn_row = Int32.Parse(cmd.ExecuteScalar().ToString());
                //                MessageBox.Show(btn_row.ToString());
                tableLayoutPanel4.Controls.Add(btn, btn_col, btn_row);
                _bId++;
            }
            else
            {
                _bId++;
            }
        }
        SQLFunctions.conn.Close();

    }

    private void button2_Click(object sender, EventArgs e)
    {
        NewButton nw = new NewButton();
        nw.Show();
    }
}




}

Thanks

Answer 1

Do you know what the sequence will be i.e. is is contiguous that is to say it will start at a number and is expected to increment by 1 each time? If that is the case then I can give you a bit of pseudo code that should solve your problem. I don't have visual studio to hand to check all the code.

Then write a linq statement that finds the lowest ID in the sequence. Then write a linq statement that finds the highest ID in the sequence.

Then use a

int last = lowest;
for (int i = lowest; i <= highest; i++)
{
int curr = (from int btn in collection/datatable where btn.ID == i select btn.ID).first;

if (curr == (last + 1))
{
curr = i;
}
else
{
++last
curr = last;
}

  //make the button based on curr or not as needed

++last;
}
}

Answer 2

If we're skipping the part "your approach is not really efficient" but just trying to make this code working, then you should modify it to look like this:

SQLFunctions Lgn = new SQLFunctions();
Lgn.ConnectionToday();
SqlCommand cmd = new SqlCommand();
cmd.Connection = SQLFunctions.conn;

int NumOfButtons = 40;
int i = 1;

//            int counter = 0;

while ( i <= NumOfButtons)
{
    cmd.CommandText = "SELECT id FROM Buttons where id='" + i + "'";

    int _bId = Int32.Parse(cmd.ExecuteScalar().ToString());

    Button btn = new Button();
    {
        btn.Tag = _bId;
        btn.Dock = DockStyle.Fill;
        btn.Margin = new Padding(10, 10, 10, 10);

        cmd.CommandText = "SELECT bName FROM Buttons where id='" + btn.Tag + "'";
        btn.Text = cmd.ExecuteScalar().ToString();
        string btn_name = cmd.ExecuteScalar().ToString();
        btn.Name = btn_name.ToString();

        /*                    btn.Click += delegate
         {
         pass_txt.Clear();
         username_txt.Text = btn_name;
         username_lbl.Text = btn_name;
         username_lbl.Visible = true;
         pass_txt.ReadOnly = false;
         };*/

    }
    cmd.CommandText = "SELECT col FROM Buttons where id='" + btn.Tag + "'";
    int btn_col = Int32.Parse(cmd.ExecuteScalar().ToString());
    //                MessageBox.Show(btn_col.ToString());
    cmd.CommandText = "SELECT row FROM Buttons where id='" + btn.Tag + "'";
    int btn_row = Int32.Parse(cmd.ExecuteScalar().ToString());
    //                MessageBox.Show(btn_row.ToString());
    tableLayoutPanel4.Controls.Add(btn, btn_col, btn_row);

    i++;

}
SQLFunctions.conn.Close();

But if we're talking about code I would really use to solve your case, I would change column type of Id to int and with 1 simple SQL command took first NumOfButtons from table ordered by Id. Here is simple SQL request which would work:

SELECT id, col, row ORDER BY id FETCH NEXT (@NumOfButtons) ROWS ONLY

And I would use this structure to run it:

using (var reader = cmd.ExecuteReader($"SELECT id, col, row ORDER BY id FETCH NEXT ({NumOfButtons}) ROWS ONLY"))
{
    while (reader.Read())
    {
        var id = reader.GetInt32(0);
        var col = reader.GetString(1);
        var row = reader.GetString(2);

        // ToDo: Your stuff here
    }
}

Answer 3

Here some things you really need to be looking at, i take it from your code your still new to programming since your way off best bractices.

1. int _bId = Int32.Parse(cmd.ExecuteScalar().ToString()); This is dangerous
2. In your Page Layout, you have an entire Method implementation, you need to move the code to its own respected method and call it from page layout.
3. Why do you need to use the database auto generated id? you can auto generate your own id after you retrieve all the data from one single request instead of 3 as @Alex K. mentioned

4. int NumOfButtons = 40; int i = 1; cmd.CommandText = "SELECT id FROM Buttons where id='" + i + "'";

You don't know how many buttons you have in your DB, you cannot hardcode the number. instead do something like this "SELECT id, bName, col, row FROM Buttons;"

Answer 4

Use ExecuteReader() with a query which selects all buttons. I assume MS Sql Sever supporting TOP()

  cmd.CommandText = "SELECT TOP(" + NumOfButtons + ") id, bname,col,row FROM Buttons ORDER BY id";
  SqlDataReader reader = cmd.ExecuteReader();
  while (reader.Read())
  {
        // process the button
  }

Answer 5

if (_bId != null)
            {
           //   Here.. You need to check if _bId (For ex: 3) exists in database(by making a query), and then proceed with creating button which is rest of your code.


            }

Note: You do not need to query again and again in for loop, you can retrieve all data at once by using below.

SELECT bName, col, row FROM Buttons where id='"+bId+"'

If this query retruns a row, // you proceed with rest of your code, create Button else bId++;