How to populate a generic list of objects in C# from SQL database

I am just learning ASP.NET c# and trying to incorporate best practices into my applications. Everything that I read says to layer my applications into DAL, BLL, UI, etc based on separation of concerns. Instead of passing datatables around, I am thinking about using custom objects so that I am loosely coupled to my data layer and c开发者_StackOverflow社区an take advantage of intellisense in VS. I assume these objects would be considered DTOs?

First, where do these objects reside in my layers? BLL, DAL, other?

Second, when populating from SQL, should I loop through a data reader to populate the list or first fill a data table, then loop through the table to populate the list? I know you should close the database connection as soon as possible, but it seems like even more overhead to populate the data table and then loop through that for the list.

Third, everything I see these days says use Linq2SQL. I am planning to learn Linq2SQL, but at this time I am working with a legacy database that doesn't have foreign keys setup and I do not have the ability to fix it atm. Also, I want to learn more about c# before I start getting into ORM solutions like nHibernate. At the same time I don't want to type out all the connection and SQL plumbing for every query. Is it ok to use the Enterprise DAAB for now?

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.

Go check out Telerik's OpenAccess ORM. You don't have to use it as an "ORM" but it will give you the ability to quickly generate classes for your tables without having to type it all out. Then you can use these strong-typed classes in your DAL for whatever you want whether it's custom-written or ORM-based with something else. In this case, you're just using it for code generation to get you started quickly (and these objects are very basic and simple - i.e. exactly where you'd start if you were to hand-write them).

As for abstracting your DAL objects from your other objects, go check out WCF. You can put it in between each layer (UI/Biz/DAL) and it will generate proxy objects that will take care of your separation of concerns.

If you are using Visual Studio, it is easy to link these projects. All you have to do is to link the projects through reference. Go to the reference, and browse the projects. For example, to link the DAL project to the BL project, go to the BL reference and search for the project DAL.

For the creation of a method to select columns from the SQL table, you might go to https://www.csharpporgramming.com/sqltocsen.aspx