CODEWITHSUNDEEP
c#sql-server
Firesky
Firesky

Reputation: 17

Retrieving more than one value from database and store them

What I'm trying to do is retrieve the FullName values where the Username corresponds to the user, which does indeed work, the problem is I don't exactly know how to store the values when there is more than one value, I tried using an array but when there is for example two values, when retrieving it, characterReader[0] will be null and characterReader[1] will have only the first retrieved value, however if there is only 1 value to be retrieve characterReader[0] will no longer be null and display the correct value.

This is my code, I'm not exactly sure this is even the right way:

SqlCommand displayCharactersCMD = new SqlCommand(String.Format("SELECT FullName FROM [Characters] WHERE Username='{0}'", username), con);
displayCharactersCMD.Parameters.AddWithValue("@checkPlayerName", username);

using (SqlDataReader reader = displayCharactersCMD.ExecuteReader())
{
    int counter = 0;

    while (reader.Read())
    {
        if (counter != countCharsToVar)
        {
            characterReader = new string[countCharsToVar];
            characterReader[counter] = reader[0].ToString();
            counter++;
        }
        else
            break;
    }
}

Example when there are two values to be retrieved:

API.consoleOutput("CHAR 1: " + characterReader[0]); - This will become null.
API.consoleOutput("CHAR 2: " + characterReader[1]); - This will contain the first value.

How I intend it to work:

API.consoleOutput("CHAR 1: " + characterReader[0]); - This will display first value.
API.consoleOutput("CHAR 2: " + characterReader[1]); - This will display second value.

Upvotes: 0

Views: 420

Answers (3)

animalito maquina
animalito maquina

Reputation: 2414

You need to iterate SqlDataReader for each value, calling reader.Read() makes the reader to point to the next row, when it reaches the end and there is no more rows in the resultset it returns false.

When a "read" is done, the reader is moved to point to the next row so you can access all the columns in this way. reader[0] will be the first column, reader[1] for the second column and so on, in your example you only have one column fullname.

You can add all your results to a list in this way:

var values = new List<string>();
using (SqlDataReader reader = displayCharactersCMD.ExecuteReader())
{

    while (reader.Read())
    {
        values.Add(reader[0]);
    }
}

Note: as @steve points, parameter does not work that way, you remove the string.format call and use the name of the parameter.

new SqlCommand("SELECT FullName FROM [Characters] WHERE Username=@checkPlayerName"), con);

Upvotes: 0

Andrei U
Andrei U

Reputation: 1998

Here's a really good link from Microsoft: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

Use the GetString(columnNumber) method to get the whole value from the row. That should make it easy for you.

Hope this helps.

Upvotes: 0

OhmnioX
OhmnioX

Reputation: 483

Instead of storing values in array, you can utilize List<>. This might help you:

SqlCommand displayCharactersCMD = new SqlCommand("SELECT FullName FROM [Characters] WHERE Username=@checkPlayerName");
displayCharactersCMD.Parameters.AddWithValue("@checkPlayerName", username);
var characterReader = new List<string>();
using (SqlDataReader reader = displayCharactersCMD.ExecuteReader())
        {                
            while (reader.Read())
            {
                characterReader.Add(reader[0].ToString());                
            }
        }

Upvotes: 2

Related Questions