asp.net Database return only one value instead of whole set

I'm trying to grab information about a particular user from my db:

 public string GetUserData()
    {
        string conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString))
        {

            SqlCommand com = new SqlCommand("SELECT lastName , phoneNo , creditCardNo , dateOfBirth  FROM UserExtendedDataSet WHERE UserId = @UserId", con);

            com.Parameters.Add("@UserId", SqlDbType.NVarChar).Value = getUserId();

            con.Open();

            string result = Convert.ToString(com.ExecuteScalar());
            StatusLabel.Text = result;
            return result;
        }
    }

The problem is that it returns only the first table data (in this case lastName), i know that I can write separate queries for each field but I assume this would not be really efficient.

Is there anyway to get this data with one query ?

Answers


You need to call ExecuteReader on the command and use the instance of the SqlDataReader returned by the command. ExecuteScalar returns just the first column of the first row.

 using(SqlDataReader reader = com.ExecuteReader())
 {
     if(reader.Read()
     {
       StatusLabel.Text = reader[0].ToString() + " " + reader[1].ToString();
       .... other labels for other fields 
     }
 }

Instead the ExecuteReader moves on the first row (if there is one) after you call the Read method and then every column of the row is available from the reader as an indexed array. (Pay attention to null values before applying any conversion like ToString())

By the way, your tags says MySql but you use Sql Server classes in the namespace SqlClient?


Need Your Help

Small "embeddable" database that can also be synced over the network?

python database sqlite couchdb

I am looking for a small database that can be "embedded" into my Python application without running a separate server, as one can do with SQLite or Metakit. I don't need an SQL database, in fact st...