Return SQL data to C# object

Question

I am developing a website using ASP.NET C#. I have an SQL-Server database. I want to be able to retrieve data from the table with my data already in it.

For example, Here is my Details Table. I want to be able to do something like SELECT SteamName FROM Details WHERE SteamID = @SteamID and return the value that I get from the query to a C# object.

Here is the C# I have tried:

private void ReadSteamDetails()
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT SteamID FROM Details WHERE SteamID = @SteamID";
            command.Parameters.Add("@SteamID", SqlDbType.NVarChar);
            command.Parameters["@SteamID"].Value = SteamID;

            connection.Open();
            DisplaySQLID = command.ExecuteNonQuery().ToString();
            connection.Close();
        }
    }
}

Running this code simply returns -1

Show the c# code that you are trying

sujith karivelil
– sujith karivelil

2017-10-23 11:59:31 +00:00
Commented Oct 23, 2017 at 11:59 — sujith karivelil
– sujith karivelil, Commented Oct 23, 2017 at 11:59
Don't use ExecuteNonQuery(), use ExecuteScalar().

Zohar Peled
– Zohar Peled

2017-10-23 12:03:14 +00:00
Commented Oct 23, 2017 at 12:03 — Zohar Peled
– Zohar Peled, Commented Oct 23, 2017 at 12:03
use DataAdapter instead of SqlCommand

Hitesh Thakor
– Hitesh Thakor

2017-10-23 12:03:28 +00:00
Commented Oct 23, 2017 at 12:03 — Hitesh Thakor
– Hitesh Thakor, Commented Oct 23, 2017 at 12:03
@Amit What do you mean Issue Statement? Error message?

SlothGod
– SlothGod

2017-10-23 12:03:57 +00:00
Commented Oct 23, 2017 at 12:03 — SlothGod
– SlothGod, Commented Oct 23, 2017 at 12:03
use any ORM instead of sql commands.

Kirill Bestemyanov
– Kirill Bestemyanov

2017-10-23 12:04:08 +00:00
Commented Oct 23, 2017 at 12:04 — Kirill Bestemyanov
– Kirill Bestemyanov, Commented Oct 23, 2017 at 12:04

Zohar Peled · Accepted Answer · 2017-10-24 06:35:12Z

You are using ExecuteNonQuery(), that returns an int value indicating the number of rows effected by the SQL statement. It should be used with insert, update or delete, but not with select statement.

When executing a select statement, you should use either ExecuteReader() if you want to iterate the query result using a DataReader, or use a DataAdapter to fill a DataSet or a DataTable, or use ExecuteScalar() if your query should return 0 or 1 results (and that's the case here).
Also, your code can and should be shorter - for instance, you can specify the select statement and connection object directly in the SqlCommand constructor.

Here is how I would write it:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand("SELECT SteamID FROM Details WHERE SteamID = @SteamID", connection))
    {
        command.Parameters.Add("@SteamID", SqlDbType.NVarChar).Value = SteamID;
        connection.Open();
        DisplaySQLID = command.ExecuteSalar()?.ToString() ?? "";
    }
}

Note that your query might not return any result so the ExecuteScalar() will return null, hence the use if the null conditional operator (?.) and the null coalescing operator (??).

spodger · Accepted Answer · 2017-10-23 12:03:28Z

2

For that exact query use ExecuteScalar instead of ExecuteNonQuery.

answered Oct 23, 2017 at 12:03

spodger

1,6791 gold badge13 silver badges16 bronze badges

1 Comment

SlothGod Over a year ago

I could have sworn that I tried this... So simple... Thanks for making me feel stupid :P But seriously, Thank You! Just gotta wait for the answer timer

SArmaD Abbas · Accepted Answer · 2017-10-23 12:22:23Z

-1

you can use the Entity Framework for that purpose. . .

with the combination of Entity Framework and link u can get your desire result set .

Entities entities = new Entities(Connection-String);

var result = entities.Details.Where(a=>a.SteamID = @SteamID )

answered Oct 23, 2017 at 12:22

SArmaD Abbas

12 bronze badges

Collectives™ on Stack Overflow

Return SQL data to C# object

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related