CODEWITHSUNDEEP
c#sqlasp.netsql-server
SlothGod
SlothGod

Reputation: 376

Return SQL data to C# object

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

Upvotes: 0

Views: 7607

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82474

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 (??).

Upvotes: 3

SArmaD Abbas
SArmaD Abbas

Reputation: 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 )

Upvotes: -1

spodger
spodger

Reputation: 1679

For that exact query use ExecuteScalar instead of ExecuteNonQuery.

Upvotes: 2

Related Questions