MySqlDataReader object not receiving any data from function

I have a post action method which takes in a username and password from a form. In this action method I am using a function to execute a mysql command to a database

This is the function to read from the database

public static MySqlDataReader GetDataFromDB(string command)
{
    using (MySqlConnection con = new MySqlConnection(ConStr))
    {

        com = new MySqlCommand(command, con);

        con.Open();

        MySqlDataReader rdr1 = com.ExecuteReader();

        while (rdr1.Read())
        {
                    
        }

        return rdr1;
    }
}

This is how i tried to use this function in my action method

MySqlDataReader rdr2 = Helper.GetDataFromDB("select * from library.Accounts where username = '" + username + "' and password = '" + password + "'");

I put a breakpoint in the helper method and could see that it was reading from the database fine with it storing all the expected row data in the internal ‘rdr1’ object however it isnt returning anything at the end as the ‘rdr2’ object is empty once it’s finished. I don’t know if something is wrong or if im just being an idiot and so i would appretiate if anyone can tell me where im going wrong.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

A Data Reader such as MySqlDataReader is Forward Only. This means that once the data has been read and moved on to the next record, or the end of the data, it cannot go back to the beginning.

If you remove this block of code:

while (rdr1.Read())
{

}

You should find it will return the Data Reader at the start, and therefore be able to read the data you loaded from the database.

In addition, you should know that when the using () {…} block finishes the connection will be closed, along with the Data Readers ability to read the data.

If you do want to return the Data Reader, remove the using() {…} block to be a simple statement like:

MySqlConnection con = new MySqlConnection(ConStr);

If you do want to navigate backwards and forwards, filter or sort the data after it has been loaded, you should LOAD the data in to a DataTable from the Data Reader.

Example:

DataTable dt = new DataTable();
dt.Load(rdr1);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0