CODEWITHSUNDEEP
c#sql
nitinvertigo
nitinvertigo

Reputation: 1180

Retrieve and store two values from SQL Server into c#

Here is what I am trying to achieve. I want to get two values from the same row in the same table and store them into two variables. I am doing this in MVC.

Here is what I am doing : 

SqlCommand amd = new SqlCommand("SELECT [Value1] FROM [ExampleTable] where Ad_name=@adname", con);
SqlCommand bmd = new SqlCommand("SELECT [Value2] FROM [ExampleTable] where Ad_name=@adname", con);
amd.Parameters.AddWithValue("@adname", aname);
bmd.Parameters.AddWithValue("@adname", aname);
imgpath1 = amd.ExecuteScalar().ToString();
imgpath2 = bmd.ExecuteScalar().ToString();

But here is what I want:

SqlCommand amd = new SqlCommand("SELECT [Value1] AND [Value2] FROM [ExampleTable] where Ad_name=@adname", con);
amd.Parameters.AddWithValue("@adname", aname);
imgpath1 = Value1;
imgpath2 = Value2;

How can I achieve that without writing multiple queries? Thanks

Upvotes: 0

Views: 8044

Answers (4)

Steve
Steve

Reputation: 216293

You call the database just one time with the method ExecuteReader.
Notice how the single columns required are listed after the SELECT separated by a comma.
This is the common basic syntax required for a SELECT statement
This method returns a DataReader that you can use to get single values of a row.
I suppose that your query returns just one record, so, the loop is not strictly necessary. 

SqlCommand amd = new SqlCommand("SELECT [Value1], [Value2] FROM [ExampleTable] where Ad_name=@adname", con);
amd.Parameters.AddWithValue("@adname", aname);
SqlDataReader reader = amd.ExecuteReader();
while(reader.Read())
{
    imgPath1 = reader[0].ToString();
    imgPath2 = reader[1].ToString();
}

Upvotes: 1

Daniel
Daniel

Reputation: 411

Your second SQL command isn't going to work, and if you want to values you wont be able to do a scalar query...

Try:

 SqlCommand command = new SqlCommand("SELECT [Value1], [Value2] FROM [ExampleTable] where Ad_name=@adname", con);

And add the parameter.

Then you can 

var reader = command.ExecuteReader();

and get the values by 

reader["[Value1]"];
reader["[Value2]"];

Essentially, doing a scalar query is meant for queries which only return a single value.

Upvotes: 2

Danila Polevshchikov
Danila Polevshchikov

Reputation: 2278

Use comma as separator between retrieved columns, use GetOrdinal to avoid constant numbers like [1] and [2].

const string ColumnOne = "ColumnOne";
const string ColumnTwo = "ColumnTwo";
var sqlCmd = new SqlCommand("select [VALUE1] as " + ColumnOne + ", [VALUE2] as " + ColumnTwo + " from table", sqlConn);
var sqlCmdReader = sqlCmd.ExecuteReader();
if (sqlCmdReader.Read())
    {
    var resultOne= sqlCmdReader.GetString(sqlCmdReader.GetOrdinal(ColumnOne));
    var resultTwo= sqlCmdReader.GetString(sqlCmdReader.GetOrdinal(ColumnTwo ));
}

Upvotes: 1

Cyril Gandon
Cyril Gandon

Reputation: 17058

See the method of SqlCommand ExecuteReader that return a SqlDataReader:

using(var command = new SqlCommand("SELECT [Value1], [Value2] FROM [ExampleTable] where Ad_name=@adname", con))
{
    command.Parameters.AddWithValue("@adname", aname);

    using(var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            imgpath1 = reader[0];
            imgpath2 = reader[1];
        }
    }
}

Upvotes: 4

Related Questions