C# How to implement method that return list of SQL result?

Question

using (var connection = new SqlConnection("user id=xxx;" +
                   "password=xxx;server=xxx;" +
                   "Trusted_Connection=yes;" +
                   "database=xxx; " +
                   "connection timeout=30"))
{
    connection.Open();
    string sql = "SELECT * FROM testTable";
    using (var command = new SqlCommand(sql, connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                .
                .
                .
                .
                .
            } 
        }
    }
}

The code above can be used to retrive the list of rows of records and display them in my C# console application but it will be very messy to be in Main method. I'm wondering how to handle this chunk of records and store them into a list or something by calling a method. Will it be possible in C# console application?

Answer 1

Not knowing what is in your database table, I will make up an example:

Account class:

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
}

Now you can have your reader loop populate each Person, like this:

while (reader.Read())
{
    var person = new Person();
    person.FirstName = reader["FirstName"].ToString();
    person.LastName = reader["LastName"].ToString();
    person.Age = Convert.ToInt32(reader["Age"]);
}

Finally, we want to build a list of Person objects to return, like this:

var listOfPerson = new List<Person>();

while (reader.Read())
{
    var person = new Person();
    person.FirstName = reader["FirstName"].ToString();
    person.LastName = reader["LastName"].ToString();
    person.Age = Convert.ToInt32(reader["Age"]);

    listOfPerson.Add(person);
}

return listOfPerson;

Complete code:

public List<Person> LoadPeople()
{
    var listOfPerson = new List<Person>();

    using (var connection = new SqlConnection("user id=xxx;" +
               "password=xxx;server=xxx;" +
               "Trusted_Connection=yes;" +
               "database=xxx; " +
               "connection timeout=30"))
    {
        connection.Open();
        string sql = "SELECT * FROM testTable";
        using (var command = new SqlCommand(sql, connection))
        {
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var person = new Person();
                    person.FirstName = reader["FirstName"].ToString();
                    person.LastName = reader["LastName"].ToString();
                    person.Age = Convert.ToInt32(reader["Age"]);

                    listOfPerson.Add(person);
                }
            }
        }
    }

    return listOfPerson;
}