Implementation of OOP for retrieving list of objects from database

Very few things in life are free. C# default constructors are one of those, the compiler generates one automatically for you if your class doesn't explicitly have one! So unless you have another constructor (with parameters) and you also need a parameterless one, putting it in is just clutter.

Encapsulating database operations in a dedicated object (like you're doing with your People class), vs doing it all in a static method, is a step forward in the right direction.. but I don't see what the peoples property is trying to encapsulate.

Think of classes as abstractions - you have a Person abstraction which represents a single record in your database. Then you have a People abstraction which represents an object that can manipulate Person records. That's all nice and tidy and high-level - you don't want to mix this niceness with boilerplate-level SQL connections and queries. Doing that is writing code at the wrong level of abstraction.

So the next step forward would be to create another class that's responsible for dealing with these things - a type that would deal with setting up a connection, running a query and then disposing the connection and every other IDiposable along the way.

Once you've done that, you will (hopefully) realize you've just reinvented the wheel - and that your wheel is more than likely square. At this stage it's all good, the worst thing you could do is start with an Entity Data Model that does it all for you: you want to understand what's going on, if it feels automagic then you've skipped something.

So first (well, after you've written your square wheel!) take a look at Linq to SQL and see what a DataContext can do for you.

Personally, I think the better approach would be this:

public class Person
{
    public Person(){}
    public int personID {get;set;}
    public string name {get;set;}
    public string address {get;set;}

    public static List<Person> GetPeople() { }
}

Now to get a list of Person objects, you just do this:

var people = Person.GetPeople();

And just get rid of the People class. It isn't really doing anything. In fact, you're just building a new data structure when the List<T> is perfect already.

Alright, in light of the comments on this post, let me dig a little deeper for you on this one.

I believe the approach I'd take goes much further than the aforementioned simplified solution. So, let's get started. The first thing I'd do is include Dapper. This is available via NuGet.

The next thing I'd do is build a static method to wrap the work that needs to be done with Dapper to ensure proper use of resources. That might look like this:

using Dapper;

public static class DatabaseAccess
{
    private static string _connString = ConfigurationManager.ConnectionStrings["Default"];

    public static IEnumerable<T> Query<T>(string cmd,
        string filter = null,
        object parameters = null)
    {
        using (SqlConnection c = new SqlConnection(_connString))
        {
            return c.Query<T>(string.Format("{0} {1}", cmd, filter),
                parameters);
        }
    }

    public static T Single<T>(string cmd,
        string filter = null,
        object parameters = null)
    {
        return Query<T>(cmd, filter, parameters).Single();
    }
}

Alright, now that we've got a way to get to the database, let's fix up that POCO a little:

public class Person
{
    private const string _selectCmd = "SELECT personID, name, address FROM Person";

    public int personID {get;set;}
    public string name {get;set;}
    public string address {get;set;}

    public static List<Person> GetPeople()
    {
        return DatabaseAccess.Query<Person>(_selectCmd).ToList();
    }

    public static Person GetPerson(int id)
    {
        return DatabaseAccess.Single<Person>(_selectCmd,
            "WHERE personID = @ID",
            new { ID = id });
    }
}

and so now to use this, you just do this still:

var list = Person.GetPeople();

or to get one:

var p = Person.GetPerson(1);

Now, I could take this even further by using attributes, reflection, and dynamic SQL generation; but I just feel that's probably outside the scope of this post.