2014年4月17日 星期四

ADO.NET DataSet 對映轉換為型別 - 使用 AutoMapper

以往我們要把使用 ADO.NET 操作所取得的資料 Mapping 到自訂的類別時,往往都會用到蠻多累人的作法,對於這樣需求的開發者通常都是直接將每一筆資料用手動的方式把一個個欄位對映到類別的屬性,而這件事情做個幾次之後就會覺得煩,所以開發者就會四處找答案,而大部分所找到的解決方案就是使用反射(Reflection),不過要使用反射做這件事情也不見得簡單,而且要靠考慮的事情也相當多,往往要碰到無數個錯誤之後才能夠讓程式穩定,但是例外總是會層出不窮地出現,所以最後總是無限循環地作修改。

其實可以使用 AutoMapper 來處理這樣的操作,而且沒有太複雜與繁瑣的步驟。

 


這篇文章裡的操作範例所使用的資料為 Northwind 的 Product,以下為設定的 Product 類別內容,

public class Product
{
    public int ProductID { get; set; }
 
    public string ProductName { get; set; }
 
    public int? SupplierID { get; set; }
 
    public int? CategoryID { get; set; }
 
    public string QuantityPerUnit { get; set; }
 
    public decimal? UnitPrice { get; set; }
 
    public short? UnitsInStock { get; set; }
 
    public short? UnitsOnOrder { get; set; }
 
    public short? ReorderLevel { get; set; }
 
    public bool Discontinued { get; set; }
 
}

 

先來看看最基本的作法,就是將資料的每個欄位逐一對映到 Product 的屬性,

public IEnumerable<Product> GetAllMethod2()
{
    List<Product> authors = new List<Product>();
 
    string sqlStatement = "SELECT [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Products] ORDER BY ProductID;";
 
    using (SqlConnection conn = new SqlConnection(this.ConnectionString))
    using (SqlCommand command = new SqlCommand(sqlStatement, conn))
    {
        command.CommandType = CommandType.Text;
        command.CommandTimeout = 180;
 
        if (conn.State != ConnectionState.Open) conn.Open();
 
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Product item = new Product();
 
                item.ProductID = int.Parse(reader["ProductID"].ToString());
                item.ProductName = reader["ProductName"].ToString();
                item.SupplierID = reader.IsDBNull(2) ? (int?)null : int.Parse(reader["SupplierID"].ToString());
                item.CategoryID = reader.IsDBNull(3) ? (int?)null : int.Parse(reader["CategoryID"].ToString());
                item.QuantityPerUnit = reader["QuantityPerUnit"].ToString();
                item.UnitPrice = reader.IsDBNull(5) ? (Decimal?)null : decimal.Parse(reader["UnitPrice"].ToString());
                item.UnitsInStock = reader.IsDBNull(6) ? (short?)null : short.Parse(reader["UnitsInStock"].ToString());
                item.UnitsOnOrder = reader.IsDBNull(7) ? (short?)null : short.Parse(reader["UnitsOnOrder"].ToString());
                item.ReorderLevel = reader.IsDBNull(8) ? (short?)null : short.Parse(reader["ReorderLevel"].ToString());
                item.Discontinued = bool.Parse(reader["Discontinued"].ToString());
 
                authors.Add(item);
            }
        }
    }
    return authors;
}

分別執行五次的時間觀察

image

 

再來就是使用反射的方式,通常不會直接在取出資料的程式裡做反射的操作,而是會另外做一個專門做反射處理的 Helper 程式,以下是我所做的 ReflectionHelper,做得並不是相當嚴謹,只是為了這個範例而做的,僅供參考,

ReflectionHelper.cs

public class ReflectionHelper
{
    #region -- SetValue --
    /// <summary>
    /// Sets the value.
    /// </summary>
    /// <param name="propertyName">Name of the property.</param>
    /// <param name="val">The val.</param>
    /// <param name="instance">The instance.</param>
    public static void SetValue(
        string propertyName,
        object val,
        object instance)
    {
        if (null == instance) return;
 
        Type type = instance.GetType();
        PropertyInfo info = GetProperty(type, propertyName);
 
        if (null == info) return;
 
        try
        {
            if (info.PropertyType == typeof(string))
            {
                info.SetValue(instance, val, new object[0]);
            }
            else if (info.PropertyType == typeof(Boolean))
            {
                var value = false;
                value = val.ToString().Equals("true", StringComparison.OrdinalIgnoreCase);
                info.SetValue(instance, value, new object[0]);
            }
            else if (info.PropertyType == typeof(int))
            {
                var value = (val == null)
                    ? 0
                    : int.Parse(val.ToString(), new CultureInfo("en-US"));
                info.SetValue(instance, value, new object[0]);
            }
            else if (info.PropertyType == typeof(int?))
            {
                var value = (val == null)
                    ? (int?)null
                    : int.Parse(val.ToString(), new CultureInfo("en-US"));
                info.SetValue(instance, value, new object[0]);
            }
            else if (info.PropertyType == typeof(decimal?))
            {
                var value = (val == null)
                    ? (decimal?)null
                    : decimal.Parse(val.ToString(), new CultureInfo("en-US"));
                info.SetValue(instance, value, new object[0]);
            }
            else if (info.PropertyType == typeof(short?))
            {
                var value = (val == null)
                    ? (short?)null
                    : short.Parse(val.ToString(), new CultureInfo("en-US"));
                info.SetValue(instance, value, new object[0]);
            }
            else if (info.PropertyType == typeof(double))
            {
                var value = 0.0d;
                if (val != null)
                {
                    value = Convert.ToDouble(val);
                }
                info.SetValue(instance, value, new object[0]);
            }
            else if (info.PropertyType == typeof(DateTime))
            {
                var value = (val == null)
                    ? new DateTime(1753, 1, 1, 0, 0, 0, 0)
                    : DateTime.Parse(val.ToString());
                info.SetValue(instance, value, new object[0]);
            }
            else if (info.PropertyType == typeof(DateTime?))
            {
                var value = (val == null)
                    ? null
                    : DateTime.Parse(val.ToString()) as DateTime?;
                info.SetValue(instance, value, new object[0]);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    #endregion
 
    #region -- GetProperty --
    /// <summary>
    /// Gets the property.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="propName">Name of the prop.</param>
    /// <returns></returns>
    public static PropertyInfo GetProperty<T>(string propName)
    {
        return GetProperty(typeof(T), propName);
    }
 
    /// <summary>
    /// Gets the property.
    /// </summary>
    /// <param name="type">The type.</param>
    /// <param name="propName">Name of the prop.</param>
    /// <returns></returns>
    public static PropertyInfo GetProperty(Type type, string propName)
    {
        try
        {
            PropertyInfo[] infos = type.GetProperties();
            if (infos == null || infos.Length == 0)
            {
                return null;
            }
            foreach (PropertyInfo info in infos)
            {
                if (propName.ToLower().Equals(info.Name.ToLower()))
                {
                    return info;
                }
            }
        }
        catch (Exception ex)
        {
            return null;
            throw ex;
        }
        return null;
    }
    #endregion
 
    /// <summary>
    /// Gets the type.
    /// </summary>
    /// <param name="pathOrAssemblyName">Name of the path or assembly.</param>
    /// <param name="classFullName">Full name of the class.</param>
    /// <returns></returns>
    public static Type GetType(string pathOrAssemblyName, string classFullName)
    {
        try
        {
            if (!pathOrAssemblyName.Contains(Path.DirectorySeparatorChar.ToString()))
            {
                string assemblyName = AbstractAssemblyName(pathOrAssemblyName);
                if (!classFullName.Contains(assemblyName))
                {
                    classFullName = String.Concat(assemblyName, ".", classFullName);
                }
                Assembly assembly = Assembly.Load(assemblyName);
                return assembly.GetType(classFullName);
            }
 
            Assembly asm = Assembly.LoadFrom(pathOrAssemblyName);
            if (null == asm) return null;
 
            Type type = asm.GetType(classFullName);
 
            if (null == type)
            {
                foreach (Type one in asm.GetTypes())
                {
                    if (one.Name == classFullName)
                    {
                        type = one;
                        break;
                    }
                }
            }
            return type;
        }
        catch (Exception)
        {
            return null;
        }
    }
 
    /// <summary>
    /// Abstracts the name of the assembly.
    /// </summary>
    /// <param name="assemblyName">Name of the assembly.</param>
    /// <returns></returns>
    private static string AbstractAssemblyName(string assemblyName)
    {
        string prefix = ".\\";
        string suffix = ".dll";
 
        if (assemblyName.StartsWith(prefix))
        {
            assemblyName = assemblyName.Substring(prefix.Length);
        }
        if (assemblyName.EndsWith(suffix, StringComparison.OrdinalIgnoreCase))
        {
            assemblyName = assemblyName.Substring(0, assemblyName.Length - suffix.Length);
        }
        return assemblyName;
    }
}

而在程式裡使用 ReflectionHelper 來做資料對映轉換的內容如下,

public IEnumerable<Product> GetAllMethod3()
{
    List<Product> authors = new List<Product>();
 
    string sqlStatement = "SELECT [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Products] ORDER BY ProductID;";
 
    using (SqlConnection conn = new SqlConnection(this.ConnectionString))
    using (SqlCommand command = new SqlCommand(sqlStatement, conn))
    {
        command.CommandType = CommandType.Text;
        command.CommandTimeout = 180;
 
        if (conn.State != ConnectionState.Open) conn.Open();
 
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Product item = new Product();
 
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    PropertyInfo property = item.GetType().GetProperty(reader.GetName(i));
 
                    if (property != null && !reader.GetValue(i).Equals(DBNull.Value))
                    {
                        ReflectionHelper.SetValue(property.Name, reader.GetValue(i), item);
                    }
                }
                authors.Add(item);
            }
        }
    }
    return authors;
}

分別執行五次的時間觀察

image

 

最後就是使用 AutoMapper 來做 DataSet 資料的對映轉換,這邊我們建立一個 MapperHelper 專門來處理資料對映轉換(請記得在專案裡先加入 AutoMapper 的參考),

MapperHelper.cs

public class MapperHelper
{
    public static IList<T> GetDataFromDataTable<T>(DataSet dataSet, int dataTableIndex)
    {
        var table = dataSet.Tables[dataTableIndex];
        using (var reader = dataSet.CreateDataReader(table))
        {
            return Mapper.Map<IList<T>>(reader).ToList();
        }
    }
 
    public static IList<T> GetDataFromDataTable<T>(DataSet dataSet, string tableName)
    {
        var table = dataSet.Tables[tableName];
        using (var reader = dataSet.CreateDataReader(table))
        {
            return Mapper.Map<IList<T>>(reader).ToList();
        }
    }
 
}

程式的使用方法

public IEnumerable<Product> GetAllMethod4()
{
    string sqlStatement = "SELECT [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Products] ORDER BY ProductID;";
 
    using (SqlConnection conn = new SqlConnection(this.ConnectionString))
    using (SqlCommand command = new SqlCommand(sqlStatement, conn))
    {
        command.CommandType = CommandType.Text;
        command.CommandTimeout = 180;
 
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(command);
        da.Fill(ds);
 
        AutoMapper.Mapper.CreateMap<IDataReader, Product>();
        var products = MapperHelper.GetDataFromDataTable<Product>(ds, 0);
        return products;
    }
}

跟前面兩種作法做個比較,可以發現到程式少了很多,而且也簡潔許多。

分別執行五次的時間觀察

image

 

就效能來說,直接做欄位的逐一對映轉換會比較快,但是在設計開發期間卻是最耗費開發者的時間,而使用 AutoMapper 來處理則是最為簡單的,如果需要處理比較複雜的資料轉換時,我會建議先建立一個基本型別,用來做第一步的資料轉換,也就是 ADO.NET DataSet 轉換到自訂型別,然後再以這個基本型別使用 AutoMapper 做更進階的資料轉換,而相關 AutoMapper 的使用方式在這個部落格裡也寫了多篇文章做介紹,

http://kevintsengtw.blogspot.tw/search/label/AutoMapper

這一篇就說到這裡。

 

以上

沒有留言:

張貼留言

提醒

千萬不要使用 Google Talk (Hangouts) 或 Facebook 及時通訊與我聯繫、提問,因為會掉訊息甚至我是過了好幾天之後才發現到你曾經傳給我訊息過,請多多使用「詢問與建議」(在左邊,就在左邊),另外比較深入的問題討論,或是有牽涉到你實作程式碼的內容,不適合在留言板裡留言討論,請務必使用「詢問與建議」功能(可以夾帶檔案),謝謝。

最近的留言