C# object creation from datatable

Question

I'm just getting my head round C#. I've been creating classes and objects so say i created a class called Member:

 public class Member
 {
     public int MemberID;
     public string FirstName;
     public string LastName;
     public string UserName;
 }

and I create a new object of that class by doing this:

    Member Billy = new Member();

    Billy.UserName = "Jonesy";
    Billy.FirstName = "Billy";
    Billy.LastName = "Jones";

That's all fine but what if I've queried a database and gotten back 5 members, can I create objects on the fly? Or what is the best way to store these members in memory?

I've used VB.Net where I would just add them into a datatable. But I've never really done any object-oriented programming before and thought since I'm learning C#, now's the best time to learn OOP.

Answer 1

This is a common problem. Fortunately there is a good answer to this: Linq To Sql! You can read about it here: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

What it basically does is that it creates a class, one per table you choose in your database. This makes it very easy to get all your objects directly from the database into object oriented programming.

Saving is as easy as calling a function "SubmitChanges()". There are more providers for this but I think Linq will suit you well as a beginner as it abstracts a lot of the annoying parts.

Answer 2

I'd recommend that you look at LINQ to SQL. Then you can write code like this to query the database to get a specific user:

Member member = db.Members.Single(member => member.UserName == "Jonesy");

or to get users matching a criterion:

IQueryable<Member> members = db.Members
    .Where(member => member.LastName == "Jones");

LINQ to SQL also takes care of writing the boilerplate code to declare the classes based on the database structure.

Answer 3

If you don't go with LINQ to SQL (or the Entity Framework) then using a regular ADO.NET DataReader you would loop through the results, instantiate a new object with the details, and add it to a list.

Roughly it would look like this:

List<Member> members = new List<Member>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Member member = new Member();
                member.UserName = reader.GetString(0);
                member.FirstName = reader.GetString(1);
                member.LastName = reader.GetString(2);
                members.Add(member);
            }
        }
    }
}

foreach(Member member in members)
{
    // do something
}

Answer 4

Linq2Sql suggested twice is not the only way, but having in mind case when all objects can be one to one mapped to tables in your database it works just fine. Personally I would go for EF instead however, because it allows you to have one more layer of abstraction.

Also I can suggest you to look at db4o and etc, there you just can save you poco and that's all.