MySQL and C# retrieving records

Question

I am wanting to have the values from the two select statements returned via stored procedure. I can have one statement returned when its in a stored procedure on its own, but it wont work when two '.read' are present.

private void viewLeaderboard(int pgamenumber)
{
    MySqlCommand cmd = new MySqlCommand("viewLeaderboard", coon); // Select stored proecdure name
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("pGameNumber", pgamenumber); // Add a parameter
    coon.Open();
    MySqlDataReader myReader;
    myReader = cmd.ExecuteReader();
    try
    {
        while (myReader.Read())
        {
           characterList.Add(myReader.GetString("CharacterName"));
           characterScoreList.Add(myReader.GetString("CharacterScore"));
        }
    }
    finally
    {
        myReader.Close();
        coon.Close();
    }
}

DELIMITER; ;
CREATE DEFINER =`root`@`localhost` PROCEDURE `viewLeaderBoard`(pGameNumber INT(50))
BEGIN
    SELECT CharacterScore FROM tbl_character WHERE GameID = pGameNumber;
    SELECT CharacterName FROM tbl_character WHERE GameID = pGameNumber;
END; ;
DELIMITER;

PaoloTa · Accepted Answer · 2021-05-18 22:53:49Z

1

Merge the two sql statements inside the stored procedure into a single query (in SQL you can select multiple fields in a SELECT statement):

SELECT CharacterScore, CharacterName FROM tbl_character WHERE GameID = pGameNumber;

Then you should be able to read it properly without code change.

To further improve the code, you can replace your try finally block with multiple using statements for MySqlDataReader, MySqlConnection. Also MySqlCommand implements IDisposable and should be disposed.

answered May 18, 2021 at 22:53

PaoloTa

4785 silver badges7 bronze badges

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

1 Comment

Testnominiee Over a year ago

Are you able to show what you mean using the code?

Collectives™ on Stack Overflow

MySQL and C# retrieving records

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related