[Data Access] ORM 原理 (4) : 處理自訂屬性與欄位對應

我們在原理 (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();
        }
    }
}