ASP.NET MVC,MSSQL,DB Mapping, Model, Attribute,IQueryable, T model, IDataReader, IDbCommand,Activator,PropertyInfo,SqlConnection,List
public static class SQLTool
{
private static IDbConnection _dbCon;
private static string ConnectionString =
WebConfigurationManager.ConnectionStrings["dbserver"].ConnectionString;
public static IQueryable<T> Query<T>(string sql, SqlParameterCollection spc = null)
{
List<T> list = new List<T>();
T obj = default(T);
using (_dbCon = new SqlConnection(SQLTool.ConnectionString )
{
IDataReader dr;
IDbCommand cmd = new SqlCommand(sql);
cmd.CommandText = sql;
cmd.Connection = _dbCon;
_dbCon.Open();
if(spc != null)
cmd.Parameters.Add(spc);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection|CommandBehavior.SingleResult);
{
obj = Activator.CreateInstance<T>();
foreach (PropertyInfo pi in obj.GetType().GetProperties())
{
if (!object.Equals(dr[pi.Name], DBNull.Value))
pi.SetValue(obj,dr[pi.Name],null);
}
list.Add(obj);
}
_dbCon.Close();
}
return list.AsQueryable();
}
}
心得：本範例重要之處在於使用Activator類別每次將T model做實體重置，使用PropertyInfo類別將T model物件中填入 DataReader指標之資料(逐一寫入)
注意：Activator.CreateInstance<T>(); 不可放置於迴圈之外，否則將造成所有寫入的list的參考皆為最後一筆寫入資料
參考來源：https://stackoverflow.com/questions/1464883/how-can-i-easily-convert-datareader-to-listt
