由資料庫取出資料後轉為物件的方法 / How do I transferring data from a database to an object?

首先建立一個 Class

public class Customer
{
    public string Sn { get; set; }
    public string Name { get; set; }
    public string Title { get; set; }
}

有一串 SQL 語法長這樣，取得這三個欄位

select  ROW_NUMBER() over(order by id) as Sn
       ,Name
       ,Title 
from [dbo].[Data_User]

要如何自動轉成 Customer 的 Class呢？

public static List LoadCustomers()
{
    List customers = new List();
    SqlConnection conn = new SqlConnection(strConn);
    SqlCommand cmd = new SqlCommand("select  ROW_NUMBER() over(order by id) as Sn,Name,Title from [dbo].[Data_User]", conn);

    conn.Open();
    SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

    while (reader.Read())
    {
        Customer customer = new Customer();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            PropertyInfo property = customer.GetType().GetProperty(reader.GetName(i));
            property.SetValue(customer, (reader.IsDBNull(i)) ? "" : reader.GetValue(i).ToString(), null);
        }
        customers.Add(customer);
    }
    reader.Close();
    return customers;
}

這樣就可以啦！寫彈性一點可以用到<T>。