how to get SELECT * FROM TABLE results into more than one variable in c#

Question

I have a table in DB with 3 columns and i have a SELECT query like this:

cmd.CommandText = ("SELECT * FROM Game WHERE gameID = " + game_id);

this table has: gameID, Player1, Player2. my purpose is to get the players names (Player1, Player2) from the table into 2 variables in C# so i can use them to display in the form. I tried to use the cmd.executeScalar() but it gets me only one result and not 2.

how can i do this please ?

Answer 1

First, a few important recommendations:

• Do not use SELECT *. You might get back something different than what you thought (e.g. if the database schema has been changed in the meantime). Instead, specify each column that you're interested in explicitly.

• Instead of glueing together a SQL query using string concatenation, use a @parameterized query. This is a good principle to follow for reasons of both performance (query caching) and safety (preventing SQL injection attacks).

So your code becomes:

cmd.CommandText = "SELECT Player1, Player2 FROM Game WHERE gameID = @gameId";
cmd.Parameters.AddWithValue("@gameId", game_id);

Now to your question: How to receive the values of Player1 and Player2 of each record? That is where IDataReaders come in. A data reader is a forward-only cursor over the resulting table that allows you to access the values of each returned record:

// SqlConnection connection = cmd.Connection;
connection.Open();
using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
    int player1Index = reader.GetOrdinal("Player1");
    int player2Index = reader.GetOrdinal("Player2");
    while (reader.Read())  // one loop iteration per record returned from SELECT
    {
        string player1 = cmd.GetString(player1Index);
        string player2 = cmd.GetString(player2Index);
        … // do something with player1, and player2
    }        
}

Answer 2

Use ExecuteReader(); here's an example. Also, take a look at my question here for how to read from reader

Answer 3

Per the ExecuteScalar documentation:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

You'll have to use ExecuteReader instead. Also your code is vulnerable to SQL injection.

Answer 4

Consider using something like this:

using (SqlConnection cnn = new SqlConnection(cnnString))
{
    cnn.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Game WHERE gameID = @gameID", cnn))
    {
        cmd.Parameters.AddWithValue("@gameID", game_id);
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            var player1 = rdr.GetString(1);
            var player2 = rdr.GetString(2);
        }
    }
}

By using a block like this you get three things:

1. Access to the fields you want.
2. Protection from SQL Injection.
3. Proper disposal of unmanaged resources when connecting to a database.

Answer 5

Use cmd.ExecuteReader(); instead of executeScalar