如何使用DataReader取值，並以物件陣列LIST傳回資料

目前公司有些專案上仍採用傳統的模式，資料庫甚至使用foxpro，所以之前以DataTable 方式取回OleDbDataReader資料，但c#大多以物件為主軸，所以需要另外再轉換DataTable成物件，如此程式無法更簡潔，所以作者嘗試直接以dataReader依欄位先後順序逐一注入物件屬性，雖然這方式也有缺點，但至少免去轉換型別的痛苦。

CommonDB

public  static List<T> selectQueryWithListT<T>(string strCommand, bool isShowDeleted) where T:new()         {                           List<T> listData = new List<T>();                 foxProConn.ConnectionString = _connstring();                                                if (foxProConn.State == ConnectionState.Open)                 {                     foxProConn.Close();                 }                 foxProConn.Open();                 if (isShowDeleted == true)                 {                     OleDbCommand oCmdMarkDeleted = new OleDbCommand("SET DELETED OFF", foxProConn);                     oCmdMarkDeleted.ExecuteNonQuery();                 }                 else                 {                     OleDbCommand oCmdMarkDeleted = new OleDbCommand("SET DELETED ON", foxProConn);                     oCmdMarkDeleted.ExecuteNonQuery();                 }                 List<OleDbParameter> liParameters = new List<OleDbParameter>();                 OleDbCommand ocmd = foxProConn.CreateCommand();                 ocmd.CommandText = strCommand;                 using (OleDbDataReader oledbReaderObj = ocmd.ExecuteReader())                 {                     T valueObj = new T(); //產生新物件                     Type myType = valueObj.GetType(); //取得該物件的型別 //取得該物件的型別有哪些屬性                     IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());                     int i = 0;                     while (oledbReaderObj.Read())                     {                         T newData = new T(); //重新產生新物件                         foreach (PropertyInfo prop in props)                         {                                 prop.SetValue(newData, oledbReaderObj[i]); //逐一填入OledbReader欄位值                                 i++;                         }                         listData.Add(newData);                         i = 0;                     }                 }                 foxProConn.Close();                 return listData;               }

entryDoctor

public class entryDoctor:commonDB     {         public string strDoctorID;  //醫師代號         public string strDoctorName; //醫師姓名         public string Gmail; //gmail         public string Gpass; //gpass }

呼叫函式:

public List<entryDoctor> listAllEmployeeWithT()         {                     int intToday = Convert.ToInt32(DateTime.Today.ToString("yyyyMMdd"));           intToday -= 19110000;           string strDoctorCommand = "SELECT 醫師代號,醫師姓名,gmail,gpass FROM DOCTOR WHERE 職務別 LIKE '%醫師%' AND (離職日期 > '" + intToday.ToString() + "' OR 離職日期 =='') ORDER BY 醫師代號";           List<entryDoctor> liDoctors = commonDB.selectQueryWithListT<entryDoctor>(strDoctorCommand, false);           return liDoctors;        }

    如果需傳回其他型別的物件，只需更換型別名稱，非常有彈性的作法，但需要注意SQL欄位的先後順序和物件屬性是否一致，否則就對應到錯誤的欄位, 以上作法僅供參考~~~~ 另外foxpro 也有提供entity framework，也可以請有心人嘗試看看。

c#參考資料:
http://blog.goyello.com/2013/01/30/6-more-things-c-developers-should-not-do/