Database to Object

You have a lot of questions in one question.

Linq2SQL is just an ORM kind of, if you are going that route I'd look at the entity framework (microsoft's orm).

Let's talk about layered applications a bit to help you understand how to populate objects. Your typical database app is composed of 3 layers (some say 4 and refer to the database itself as a layer, it really doesn't matter). You have the following:

-UI

-BLL

-DAL

So your communication is the UI talks to the BLL and the BLL talks to the DAL. The DAL returns some data to the BLL which in turn present it back to the UI. I don't know who told you datasets / tables are bad...sure a reader is faster but it doesn't mean using a datatable is bad.

Let me give you an example. Stop thinking of your DAL as one simple class. Start thinking of the DAL layer as an entire folder of different classes. One of those classes is a static DB class. It's static because you are dealing with one database (in most cases), so no need to instantiate the class. So it may look like this:

public static class DB {
private static readonly string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);

public static int Update(string sql)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;

                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }

public static DataTable GetDataTable(string sql)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sql;

                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = command;

                        DataTable dt = new DataTable();
                        adapter.Fill(dt);

                        return dt;
                    }
                }
            }
}

Some of this was taken from the dofactory website. Great resource to learn how to use design patterns. Anyhow that is just one .class file. Now you need another one for say a CustomerDAO (a customer data access object).

Ok so how can you use that DB class you created (well I would use a combination of sprocs but to make this a simple post lets avoid stored procedures for now). If I need to Get Customers I could define this:

public IList<Customer> GetCustomers()
{
    StringBuilder sql = new StringBuilder();
    sql.Append(" SELECT CustomerId, CompanyName, City, Country ");
    sql.Append("   FROM Customer ");

    DataTable dt = Db.GetDataTable(sql.ToString());

    return MakeCustomers(dt);
}

Remember this is in an entirely different .class file. Ok so how does make customers look:

private IList<Customer> MakeCustomers(DataTable dt)
        {
            IList<Customer> list = new List<Customer>();
            foreach (DataRow row in dt.Rows)
                list.Add(MakeCustomer(row));

            return list;
        }

So what I am doing here is I had a datatable full of customers. I need to loop through each row of the datatable and Make the customer:

private Customer MakeCustomer(DataRow row)
        {
            int customerId = int.Parse(row["CustomerId"].ToString());
            string company = row["CompanyName"].ToString();
            string city = row["City"].ToString();
            string country = row["Country"].ToString();

            return new Customer(customerId, company, city, country);
        }

So this customer is new'd and stored in a customer list.

This is just a small example of what your Data Access Layer does. The database class simply stores the connection string and functions to get a data set or get a data table or even in your case get a data reader (which you could do too). The CustomerDAO class is simply a class that deals with customer objects and may implement say an ICustomer interface.

So where is the Customer class itself? It can be in another folder as the business layer, because it simply is a business object. Here you can set validations, and required fields inside of the customer class.

Your UI doesnt have anything related to datareaders, datasets, or SQL at all. Your Business layer has nothing to do with it either (it defines some rules behind your business objects). Your dal can be very flexible (can work with SQL, Oracle, etc) or can be limited to say SQL Server if that is what you plan on doing. Do not overkill your app. If you are a MS guy and are certain to only use SQL Server don't make your job difficult by trying to roll out the ultimate DAL that works with any vendor. it is ok to use SQLCommand, SQLConnection, etc.