Move to next record using oledbdatareader C#

Question

I have created a method where I can search an access database using oledbdatareader however i can't seem to work out how i can move to the next record using a button.

Please help!

    private void button2_Click(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnection conn = new
        System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BoilerSvc_be.mdb";
        try
        {
            conn.Open();
            OleDbCommand command = new OleDbCommand("SELECT Equipment.CustID AS CustID,Equipment.Manufacturer AS Manufacturer,Equipment.Model AS Model, Equipment.LastService AS LastService,Initial,Surname,[Address 1],[Address 2],[Address 3],[Post Town],[Post Code],Telephone FROM Contacts INNER JOIN Equipment ON Equipment.CustID = Contacts.CustID WHERE Surname = '" +  textBox12.Text + "' OR Initial = '" + textBox12.Text +  "' OR[Post Town] = '" + textBox12.Text + "' OR[Post Code] = '" + textBox12 + "'", conn);
            command.Parameters.Add(new OleDbParameter("@Name", textBox12));
            OleDbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                FirstName.Text = reader["Initial"].ToString();
                LastName.Text = reader["Surname"].ToString();
                Address1.Text = reader["Address 1"].ToString();
                Address2.Text = reader["Address 2"].ToString();
                Address3.Text = reader["Address 3"].ToString();
                TownCity.Text = reader["Post Town"].ToString();
                PostCode.Text = reader["Post Code"].ToString();
                Telephone.Text = reader["Telephone"].ToString();
                LstSvcDat.Text = reader["LastService"].ToString();
                BoilerMan.Text = reader["Manufacturer"].ToString();
                BoilerMod.Text = reader["Model"].ToString();
            }
            // Insert code to process data.
        }
        finally
        {
            conn.Close();
        }

Answer 1

You are moving to the next record when you call reader.Read(), would suggest loading the data you need rather than reading the next record from the database based off of user input.

This is how you do that

Should define a DtoObject containing all of your fields first

public class DtoObject
{
    public string FirstName { get; set;}
    public string LastName  { get; set;}
    public string Address1  { get; set;}
    public string Address2  { get; set;}
    public string Address3  { get; set;}
    public string TownCity  { get; set;}
    public string PostCode  { get; set;}
    public string Telephone { get; set;}
    public string LstSvcDat { get; set;}
    public string BoilerMan { get; set;}
    public string BoilerMod { get; set;} 
}

Then in your method add a list of the Dtos, and a method to load them to the valid Form Inputs, and methods to move forward and backwards an item

//in your class
private readonly List<DtoObject> _ls = new List<DtoObject>();
private int _currentIndex = 0;


private void LoadDto(DtoObject object)
{
    FirstName.Text = reader["Initial"].ToString();
    LastName.Text = reader["Surname"].ToString();
    ...
    BoilerMod.Text = reader["Model"].ToString();
}


private void MoveToNextItem()
{
   _currentIndex++;
   LoadDtoObject(_ls[_currentIndex]);
}

private void MoveToPreviousItem()
{
   _currentIndex--;
   LoadDtoObject(_ls[_currentIndex]);
}

Then inside your method to read just read all of them into the list and initialize the value to the first index

//clear existing data
_ls.Clear();            

while (reader.Read())
{

     var current = new DtoObject();

     current.FirstName = reader["Initial"].ToString();
     current.LastName = reader["Surname"].ToString();
     current.Address1= reader["Address 1"].ToString();
     current.Address2= reader["Address 2"].ToString();
     current.Address3= reader["Address 3"].ToString();
     current.TownCity= reader["Post Town"].ToString();
     current.PostCode= reader["Post Code"].ToString();
     current.Telephone= reader["Telephone"].ToString();
     current.LstSvcDat= reader["LastService"].ToString();
     current.BoilerMan= reader["Manufacturer"].ToString();
     current.BoilerMod= reader["Model"].ToString();  

     _ls.Add(current);
}  

//if there are any set the first values to current form
if(_ls.Any())
{
    _currentIndex = 0;
    LoadDto(_ls.First());
}
else
{
    //do something to notify no records were found
}

Then create your event handler

private void nextItemButton_Click(object sender, EventArgs args)
{
    MoveToNextItem();
}

private void prevItemButton_Click(object sender, EventArgs args)
{
    MoveToPreviousItem();
}

Answer 2

You will need to move everything except reader.Read() out of the click handler. Then, the first time the button is clicked (or on startup or load), open the reader. When someone clicks the button, you Read on record.

Right now, you are opening the reader, reading all of the data, then closing the reader each time someone clicks the button.