As kamilacbe said you need to use execute reader, your code should be something like this, try to understand it and change it to suit:
public List<string> getName()
//SQL statement to select Name from MyTable
string sql = "SELECT Name FROM Mytable.Name";
//SQL command establishes database connection and executes SQL command
SqlCommand cmd = new SqlCommand(sql, db.MyDBConnectMethod());
//Creates list of strings
List<string> NameList = new List<string>();
//Reads the result of cmd
rdr = cmd.ExecuteReader();
//Executes contents while there is data to available to read
//Adds each name read to a list of strings
//Executed if try cannot
//Returns string list
How you choose to store the data should really depend on what you're going to do with it when you get it out of the database.
My code example would be pretty-much like ChrisHunter's example.
I recommend custom classes for the data where you fill a collection of those classes by passing a DataReader to the constructor of the new object and then add that to the collection. This is of best use to me when I need to use the result in many different places and don't want to rely on human memory as to what the values were that came back.
Some folks really believe in DataSets, DataAdapters and DataTables.
I've never studied the use of the DataTable and DataAdapter enough to determine if it is better than doing it the other way.
I do see a lot of new programmers completely confused by it though especially when changing from the select to the update.
My background brought me through MFC (with C++), so using the Recordset was the preferred method but required the programmer to be EXTREMELY familiar with the framework before expecting success. MFC did, however, bring back the columns in individually named member variables inside the class (because the wizard generated them from the DB schema, if you wanted). You could modify the structure, but you MUST be familiar with what it's going to do under the hood. When I look back at my legacy code, I have to "remember" what pieces did certain functions.
With the DataTable construction, it feels kind-of like MFC did.
Right now, I prefer to spell-it-out more explicitly.
I think in the end, it will be just as much code either way (to do what I do with classes).