Iterate trougth database table rows C#

Question

I have in my database a table called students that have the number and name, address....
I have a form where I load all information for one student at a a time , and I have a next button and a back button.
How can I iterate to the next row (or previous row) in mysql (to be able to see the info of the next student) ?
I tried to use the primary key (auto increment) to iterate and when I want to see the next record I add 1 to the id or subtract 1 to see the previous record.
But if one record is deleted it will show an empty record. Can you point me in the rigth direction?
I´m using WinForms
Sorry about my english..

            string config = "server=localhost; userid = root; database = databaseName";
            MySqlConnection con = new MySqlConnection(config);

            MySqlDataReader reader = null;
            string query = "SELECT * FROM students WHERE id = " + id; //id is the primary Key (auto increment)

            MySqlCommand command = new MySqlCommand(query, con);
            con.Open();
            reader = command.ExecuteReader();

            while (reader.Read())
            {

                string studentName = (string)reader["studentName"];

                string studentNum = (string)reader["studentNum"];

                tbstudentName.Text = Convert.ToString(studentName);
                tbstudentNum.Text = Convert.ToString(studentNum);                   

                .....
            }
            con.Close();

score 2 · Accepted Answer · answered Jan 29 '14 at 13:33

You should not be calling the database each time you want to view the next record. Try reading all the data into a List.

I am not sure what you are using.. WinForms? WPF?

If WinForms you will need to do something like this.

    public class Student
    {//First create a class to hold your data in
      public string Name { get; set; }
      public string Num { get; set; }
    }

public class MyForm : Form
{
    int Index = 0;
    List<Student> FormData { get; set; }

    void GetData()
    {
    //This will hold all your data in memory so you do not have to make a database call each and every "iteration"
    List<Student> dbData = new List<Student>();

    string config = "server=localhost; userid = root; database = databaseName";
        MySqlConnection con = new MySqlConnection(config);

        MySqlDataReader reader = null;
        string query = "SELECT * FROM students";

        MySqlCommand command = new MySqlCommand(query, con);
        con.Open();
        reader = command.ExecuteReader();

        while (reader.Read())
        {
            Student newStudent = new Student();

            newStudent.Name = (string)reader["studentName"];

            newStudent.Num = (string)reader["studentNum"];
            //Add data to the list you created
            dbData.Add(newStudent);          

            .....
        }
        con.Close();

        //set the Form's list equal to the one you just populated
        this.FormData = dbData;
    }

    private void BindData()
    {
        //If winforms
        tbstudentName.Text = FormData[Index].Name;
        tbstudentNum.Text = FormData[Index].Num;

        //If wpf you will have to use view models and bind your data in your XAML but I am assuming you are using
        //winforms here.
    }

    private void NextRecord()
    {    //If you reached the end of the records then this will prevent IndexOutOfRange Exception
        if (Index < FormData.Count - 1)
        {
            Index++;
            BindData();
        }
    }

    private void PreviousRecord()
    {
        if (Index != 0)
        {
            Index--;
            BindData();
        }
    }
}

Now the above scenario will get it working quickly; however, there are better ways in doing this that would help you when you need to alter that data. I would recommend WinForms Binding. You can check it out here http://msdn.microsoft.com/en-us/library/c8aebh9k(v=vs.110).aspx

I tried your suggestion but i get an error in this line if (Index < FormData.Count - 1) saying NullReferenceException was unhandled — hi itsme, Jan 29 '14 at 14:33
Can you update your answer with all of the code you are using? That probably means your `FormData` is null and was never set. I'll check it out for you. — Adrian, Jan 29 '14 at 15:50

score 1 · Answer 2 · answered Jan 29 '14 at 13:19

1

To get the next you can write:

select * from students where id > @id
order by id asc
limit 1

And to get previous

select * from students where id < @id
order by id desc
limit 1

answered Jan 29 '14 at 13:19

Magnus

45,362
8
80
118

score 0 · Answer 3 · answered Jan 29 '14 at 13:13

0

DataReader Designed to quick one-time read.

If you want to hold the data, you need to fill memory arrays. the DataTable implements it very well.

answered Jan 29 '14 at 13:13

dovid

6,354
3
33
73

score 0 · Answer 4 · edited May 23 '17 at 11:56

You will need to think a little different.

Getting id+1 you are being very careless.. Even identity, the Id can be another value and you will get an Exception.. I suppose that you don't want it.

You will need to Adjust your logic to return lines with top or, in mysql, limit statement..

This will be easy using lambda to use .Take() and Skip() methods... You also can use the limit parameter to pass throug this sample.. you can understand..

MySQL skip first 10 results

Hope it helps.

Iterate trougth database table rows C#

4 Answers4