Only get single value from list executereader

Question

I am trying to read values form my database. But why am I getting only values with no column name? this is my controller. that returns the values in JSON

            SqlCommand cmd = con.CreateCommand();

            cmd.CommandText = "SELECT DISTINCT State FROM MyDBtable";

            con.Open();
            List<string> StateList = new List<string>();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                StateList.Add(reader[0].ToString());
            }

            return Json(new
            {
                myTable = StateList
            }, JsonRequestBehavior.AllowGet);

and this is my JSON

{"myTable":["VA","CA"]}

Where as, it's suppose to give me

{"myTable":[{"State":"VA"},{"State":"CA"}]}

Why is it not reading and printing State

Answer 1

Because you are selecting state. This will create a new object where the State property is assigned the state, such that you get what you want:

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "SELECT DISTINCT State FROM MyDBtable";

con.Open();
List<string> StateList = new List<string>();
SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    StateList.Add(reader[0].ToString());
}

return Json(new
{
    myTable = StateList.Select(i => new { State = i })
}, JsonRequestBehavior.AllowGet);

For additional columns, see lazyberezovsky's answer who has changed StateList to solve this.