How to retrieve more than one column using ExecuteScalar?

Question

I am getting one column using ExecuteScalar:

 cmd.commandtext = "select rodeuser from customer_db_map";
    string rodecustomer = cmd.executescalar;

But I need to get more than one column, e.g.:

 cmd.commandtext = "select rodeuser,username,password from customer_db_map";

I need each column in a string:

 string rodecustomer = cmd.ExecuteScalar();
 string username= cmd.ExecuteScalar();
 string password = cmd.ExecuteScalar();

But that is not possible. How is this achieved?

Answer 1

ExecuteScalar 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.

To achecive this you need to use SqlCommand.ExecuteReader Method

Answer 2

ExecuteScalar returns first columns of first row ,so you can use a trick like this

var m = cmd.commandtext =    select str(rodeuser)+','+username+','+password  from 
    (select rodeuser,username,password from customer_db_map)

string[] result=m.ToString().Split(new char[] { ',' });
string rodeuser=result[0];

string username=result[1];

string password=result[2];