[Data Access] ORM 原理 (2) : 處理不同型別

我們首先了解了 ORM 的繫結物件屬性與欄位時使用的方式,只是還有個問題,就是我們原先用的屬性型別都是 string,很好處理沒錯,但大多數的資料結構沒這麼簡單,一定不會只有 string 型別,像 int, double, long, char, boolean 這些型別也時常出現,那麼要怎麼處理這些型別間的轉換?

我們首先了解了 ORM 的繫結物件屬性與欄位時使用的方式,只是還有個問題,就是我們原先用的屬性型別都是 string,很好處理沒錯,但大多數的資料結構沒這麼簡單,一定不會只有 string 型別,像 int, double, long, char, boolean 這些型別也時常出現,那麼要怎麼處理這些型別間的轉換?

為了要實驗這個特性,我們定義了一個叫 CustomerAmount 的類別,使用不同資料型別,這個類別的定義如下:

public class CustomerAmount
{
    public string CustomerID { get; set; }
    public int Qty { get; set; }
    public decimal Amount { get; set; }
}

 

裡面我們放了 int 和 decimal 的型別,以對於 Customer 的訂單彙總數據,SQL 指令以及資料存取邏輯為:

namespace ConsoleApplication2
{
    class ProgramStep2
    {
        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 o.CustomerID,
                         SUM(od.Quantity) AS Qty,
                         SUM(od.Quantity * od.UnitPrice * od.Discount) AS Amount
                  FROM Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
                  GROUP BY o.CustomerID", db);
            List<CustomerAmount> customerAmounts = new List<CustomerAmount>();

            db.Open();
            SqlDataReader reader = dbcmd.ExecuteReader(CommandBehavior.CloseConnection);

            while (reader.Read())
            {
                CustomerAmount customerAmount = new CustomerAmount();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    // TODO: bind data into property.

                }

                customerAmounts.Add(customerAmount);
            }

            reader.Close();
            db.Close();

            foreach (CustomerAmount customer in customerAmounts)
            {
                Console.WriteLine(
                   "id: {0}, qty: {1:###,##0}, amount: {2:$###,###,##0}",
                   customer.CustomerID, customer.Qty, customer.Amount);
            }

            Console.WriteLine("");
            Console.WriteLine("Press ENTER to exit.");
            Console.ReadLine();
        }
    }
}

 

現在的重點是在 TODO 的那一段要怎麼寫,我們一般使用的 DataReader/DataTable 多半是弱型別,也就是 object 型別,而類別屬性是強型別,我們必須要想一套方法來做這件事,但又不想要回到 hard coding 的作法,我們一樣要使用 Reflection,然而型別轉換這件事要考量的因素也不少,像是資料可能會有 NULL 值,轉型可能會出現 InvalidCastException,轉型失敗的預設值,轉型需要的效能損耗等,只是因為資料來源本身就是弱型別,不轉也不行,不過我們想要轉得簡單,不用像這樣一行一行判斷:

PropertyInfo property = customerAmount.GetType().GetProperty(reader.GetName(i));
Type propType = property.PropertyType;
object defaultValue = null;

if (propType == typeof(int) || propType == typeof(long) || propType == typeof(short))
    defaultValue = 0;
else if (propType == typeof(float) || propType == typeof(double) || propType == typeof(decimal))
    defaultValue = 0.0;
else if (propType == typeof(bool))
    defaultValue = false;
else if (propType == typeof(char))
    defaultValue = (char)0;
else
    defaultValue = string.Empty;

....

 

所以,我們定義了一個叫 ITypeConverter 的介面,並定義了 StringConverter,IntegerConverter 以及 DecimalConverter 類別:

public class StringConverter : ITypeConverter
{
    public object Convert(object ValueToConvert)
    {
        if (ValueToConvert == null || ValueToConvert == DBNull.Value)
            return string.Empty;

        return ValueToConvert.ToString();
    }
}

public class IntegerConverter : ITypeConverter
{
    public object Convert(object ValueToConvert)
    {
        if (ValueToConvert == null || ValueToConvert == DBNull.Value)
            return 0;

        return System.Convert.ToInt32(ValueToConvert);
    }
}

public class DecimalConverter : ITypeConverter
{
    public object Convert(object ValueToConvert)
    {
        if (ValueToConvert == null || ValueToConvert == DBNull.Value)
            return 0.0m;

        return System.Convert.ToDecimal(ValueToConvert);
    }
}

 

我們將型別的轉換交給特定的 TypeConverter 以後,用戶端程式要做的就變簡單了,不過因為要動態叫用正確的 Type Converter,所以我們還需要一個 Factory:

public class TypeConverterFactory
{
    public static ITypeConverter GetConvertType<T>()
    {
        if (typeof(T) == typeof(int))
            return (new IntegerConverter());
        if (typeof(T) == typeof(long))
            return (new LongConverter());
        if (typeof(T) == typeof(short))
            return (new ShortConverter());
        if (typeof(T) == typeof(float))
            return (new FloatConverter());
        if (typeof(T) == typeof(double))
            return (new DoubleConverter());
        if (typeof(T) == typeof(decimal))
            return (new DecimalConverter());
        if (typeof(T) == typeof(bool))
            return (new BooleanConverter());
        if (typeof(T) == typeof(char))
            return (new CharConverter());
        if (typeof(T) == typeof(string))
            return (new StringConverter());

        return null;
    }

    public static ITypeConverter GetConvertType(Type T)
    {
        if (T == typeof(int))
            return (new IntegerConverter());
        if (T == typeof(long))
            return (new LongConverter());
        if (T == typeof(short))
            return (new ShortConverter());
        if (T == typeof(float))
            return (new FloatConverter());
        if (T == typeof(double))
            return (new DoubleConverter());
        if (T == typeof(decimal))
            return (new DecimalConverter());
        if (T == typeof(bool))
            return (new BooleanConverter());
        if (T == typeof(char))
            return (new CharConverter());
        if (T == typeof(string))
            return (new StringConverter());

        return null;
    }
}

 

最後,再將它拿到用戶端程式來用:

while (reader.Read())
{
    CustomerAmount customerAmount = new CustomerAmount();

    for (int i = 0; i < reader.FieldCount; i++)
    {
        PropertyInfo property = customerAmount.GetType().GetProperty(reader.GetName(i));
        Type propType = property.PropertyType;
       
TypeConverters.ITypeConverter typeConverter =
            TypeConverters.TypeConverterFactory.GetConvertType(propType);

        property.SetValue(customerAmount,
            Convert.ChangeType(typeConverter.Convert(reader.GetValue(i)), propType), null);
    }

    customerAmounts.Add(customerAmount);
}

 

這樣,就可以省去自己做 Type Convert 的工了。

Source Code Download: https://dotblogsfile.blob.core.windows.net/user/regionbbs/1111/20111110143952457.rar