我們在原理 (1) 時，看到了看似很完美的屬性與欄位對應，因為屬性和欄位名稱一致，在處理起來算是很容易，但現實的情況是，屬性名稱未必會和欄位名稱一樣，這時我們就需要一套方法來處理欄位與屬性的對應。
我們在原理 (1) 時，看到了看似很完美的屬性與欄位對應，因為屬性和欄位名稱一致，在處理起來算是很容易，但現實的情況是，屬性名稱未必會和欄位名稱一樣，這時我們就需要一套方法來處理欄位與屬性的對應。
首先，我們要有個可標示欄位名稱的工具，在 .NET Framework，特徵項 (attribute) 最適合用來做這個工作了，之前有特別為這個寫過文章。而這次我們就應用它來做到我們想要的事。接著就是解析特徵項，工作有點多，但核心的工作其實就是如此 ...
這次我們要撈的是 Employees 表格內的資料，只撈五個欄位：EmployeeID, LastName, FirstName, Title, HomePhone 五個，但今天我不太喜歡 HomePhone 這個名稱，我要用 Phone，看起來比較直覺，所以我們定義了一個 Employee 類別：
public class Employee
{
public string EmployeeID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Title { get; set; }
public string Phone { get; set; }
}不過為了要確保 HomePhone 欄位可以對得到 Phone 屬性，所以我們需要一個特徵項：
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class DataSourceColumnAttribute : Attribute
{
public string Name { get; private set; }
public DataSourceColumnAttribute(string Name)
{
this.Name = Name;
}
}然後，把特徵項設定加到 Employee 類別中：
public class Employee
{
public string EmployeeID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Title { get; set; }
[DataSourceColumn("HomePhone")]
public string Phone { get; set; }
}接著修改主程式：
namespace ConsoleApplication2
{
class ProgramStep4
{
static void Main(string[] args)
{
// step 2. handling data type convert.
SqlConnection db = new SqlConnection("initial catalog=Northwind; integrated security=SSPI");
SqlCommand dbcmd = new SqlCommand(@"SELECT EmployeeID, LastName, FirstName, Title, HomePhone FROM Employees", db);
List<Employee> employees = new List<Employee>();
db.Open();
SqlDataReader reader = dbcmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Employee employee = new Employee();
for (int i = 0; i < reader.FieldCount; i++)
{
// TODO: add data binding with attributes.
}
employees.Add(employee);
}
reader.Close();
db.Close();
foreach (Employee employee in employees)
{
Console.WriteLine("id: {0}, name: {1}, title: {2}, phone: {3}",
employee.EmployeeID, employee.FirstName + ' ' + employee.LastName, employee.Title, employee.Phone));
}
Console.WriteLine("");
Console.WriteLine("Press ENTER to exit.");
Console.ReadLine();
}
}
}重點來了，最精華的 TODO，我們要先決定欄位，在這裡要判斷屬性是否帶有 DataSourceColumnAttribute，很簡單，只要在 PropertyInfo 中呼叫 GetCustomAttributes() 方法，它會回傳一個陣列，若沒有找到，此陣列會是空陣列，但為了保險起見，也一併判斷它是否為 null：
int ordinal = -1;
Type propType = property.PropertyType;
string dataSourceColumnName = null;
// get attribute.
DataSourceColumnAttribute[] attributes =
property.GetCustomAttributes(typeof(DataSourceColumnAttribute), true) as DataSourceColumnAttribute[];
if (attributes != null && attributes.Length > 0)
dataSourceColumnName = attributes[0].Name;
else
dataSourceColumnName = property.Name;接著，由 Data Source 中取出序號，但因為 DataReader.GetOrdinal() 如果找不到欄位會擲回 IndexOutOfRangeException，所以我們要捕捉它，以免發生錯誤，當序號 >= 0 時，為有效，此時即可取值：
// get column index, if not exist, set -1 to ignore.
try
{
ordinal = reader.GetOrdinal(dataSourceColumnName);
}
catch (Exception)
{
ordinal = -1;
}
// set value.
if (ordinal >= 0)
{
TypeConverters.ITypeConverter typeConverter = TypeConverters.TypeConverterFactory.GetConvertType(propType);
if (!propType.IsEnum)
{
property.SetValue(employee,
Convert.ChangeType(typeConverter.Convert(reader.GetValue(ordinal)), propType), null);
}
else
{
TypeConverters.EnumConverter converter = typeConverter as TypeConverters.EnumConverter;
property.SetValue(employee,
Convert.ChangeType(converter.Convert(propType, reader.GetValue(ordinal)), propType), null);
}
} 最終完整程式碼如下：
namespace ConsoleApplication2
{
class ProgramStep4
{
static void Main(string[] args)
{
// step 2. handling data type convert.
SqlConnection db = new SqlConnection("initial catalog=Northwind; integrated security=SSPI");
SqlCommand dbcmd = new SqlCommand(@"SELECT EmployeeID, LastName, FirstName, Title, HomePhone FROM Employees", db);
List<Employee> employees = new List<Employee>();
db.Open();
SqlDataReader reader = dbcmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Employee employee = new Employee();
PropertyInfo[] properties = employee.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
int ordinal = -1;
Type propType = property.PropertyType;
string dataSourceColumnName = null;
// get attribute.
DataSourceColumnAttribute[] attributes =
property.GetCustomAttributes(typeof(DataSourceColumnAttribute), true) as DataSourceColumnAttribute[];
if (attributes != null && attributes.Length > 0)
dataSourceColumnName = attributes[0].Name;
else
dataSourceColumnName = property.Name;
// get column index, if not exist, set -1 to ignore.
try
{
ordinal = reader.GetOrdinal(dataSourceColumnName);
}
catch (Exception)
{
ordinal = -1;
}
// set value.
if (ordinal >= 0)
{
TypeConverters.ITypeConverter typeConverter = TypeConverters.TypeConverterFactory.GetConvertType(propType);
if (!propType.IsEnum)
{
property.SetValue(employee,
Convert.ChangeType(typeConverter.Convert(reader.GetValue(ordinal)), propType), null);
}
else
{
TypeConverters.EnumConverter converter = typeConverter as TypeConverters.EnumConverter;
property.SetValue(employee,
Convert.ChangeType(converter.Convert(propType, reader.GetValue(ordinal)), propType), null);
}
}
}
employees.Add(employee);
}
reader.Close();
db.Close();
foreach (Employee employee in employees)
{
Console.WriteLine("id: {0}, name: {1}, title: {2}, phone: {3}",
employee.EmployeeID, employee.FirstName + ' ' + employee.LastName, employee.Title, employee.Phone);
}
Console.WriteLine("");
Console.WriteLine("Press ENTER to exit.");
Console.ReadLine();
}
}
}