[.NET]RowMapper模組

[.NET]RowMapper模組

前言
很常用需要到一些RowMapper模組,這邊做個記錄,沒有優化、也沒有太多防呆,但已經可以符合我的需求了。

RowMapper模組的角色,請見下圖:

image

模組

2012/04/15補充,發現之前忘了把Entity放上來,這樣會完全看不出來,整合測試所解釋的使用方式。
 


    {
        [ColumnMapping("LogID")]
        public int Id { get; set; }

        [ColumnMapping("Title")]
        public string Title { get; set; }

        public JoeyEmployee GetMappingEntity(SqlDataReader reader, int index)
        {
            var result = new JoeyEmployee();
            result.Id = Convert.ToInt32(reader["LogID"]);
            result.Title = Convert.ToString(reader["Title"].DbNullToNull());

            return result;
        }
    }



1. 使用Delegate來做RowMapper:

==針對回傳集合的module==
整合測試程式


        ///GetEntityCollection 的測試
        ///</summary>
        public void GetEntityCollectionTest_DelegateMappingFunction<T>()
        {
            //arrange
            string sqlStatemnet = @"SELECT TOP 100 LogID, EventID, Title FROM Log(NOLOCK) where LogID BETWEEN @low and @upper";
            string connectionString = @"你的connection string";
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("low", 47097), new SqlParameter("upper", 47098) };
            Func<SqlDataReader, int, JoeyEmployee> rowMapperDelegate =
                (reader, rowIndex) =>
                {
                    var result = new JoeyEmployee
                    {
                        Id = Convert.ToInt32(reader["LogID"].DbNullToNull()),
                        Title = Convert.ToString(reader["Title"].DbNullToNull()),
                    };

                    return result;
                };

            List<JoeyEmployee> expected = new List<JoeyEmployee>
            {
                new JoeyEmployee{ Id=47097, Title="Exception Log Manager Handling"},        
                new JoeyEmployee{ Id=47098, Title="Exception Log Manager Handling"},        
            };

            //act
            IEnumerable<JoeyEmployee> actual;
            actual = Joey.RowMapper.RowMapperService.GetEntityCollection<JoeyEmployee>(sqlStatemnet, connectionString, parameters, rowMapperDelegate);

            var actualToDictionary = actual.ToDictionary(x => x.Id, x => x);

            Assert.AreEqual(expected[0].Id, actualToDictionary[47097].Id);
            Assert.AreEqual(expected[0].Title, actualToDictionary[47097].Title);

            Assert.AreEqual(expected[1].Id, actualToDictionary[47098].Id);
            Assert.AreEqual(expected[1].Title, actualToDictionary[47098].Title);
        }

        [TestMethod()]
        public void GetEntityCollectionTest_DelegateMappingFunction()
        {
            GetEntityCollectionTest_DelegateMappingFunction<GenericParameterHelper>();
        }


實際程式


        /// 供每次都是新起connection的sql statement使用
        /// 可自訂委派rowmapper function來決定O/R mapping邏輯,其中rowIndex可供Entity結合資料筆數序號
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStatemnet">The SQL statemnet.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="parameters">The parameters.</param>
        /// <param name="rowMapperDelegate">The row mapper delegate.</param>
        /// <returns>透過自訂的delegate方法,所回傳的IEnumerable T</returns>        
        public static IEnumerable<T> GetEntityCollection<T>(string sqlStatemnet, string connectionString, SqlParameter[] parameters, Func<SqlDataReader, int, T> rowMapperDelegate) where T : class, new()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlStatemnet, connection);
                sqlCommand.Parameters.AddRange(parameters);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);

                int rowIndex = 0;
                while (reader.Read())
                {
                    var result = rowMapperDelegate(reader, rowIndex);
                    rowIndex++;
                    yield return result;
                }
            }
        }


==針對回傳單筆的module==
整合測試程式


        ///GetEntity 的測試
        ///</summary>
        public void GetEntityTestHelper_透過delegateMappingFunction取得第一筆<T>()
            where T : new()
        {
            //arrange
            string sqlStatemnet = @"SELECT TOP 100 LogID, EventID, Title FROM Log(NOLOCK) where LogID BETWEEN @low and @upper";
            string connectionString = @"你的connection string";
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("low", 47097), new SqlParameter("upper", 47098) };
            Func<SqlDataReader, JoeyEmployee> rowMapperDelegate =
                (reader) =>
                {
                    var result = new JoeyEmployee
                    {
                        Id = Convert.ToInt32(reader["LogID"].DbNullToNull()),
                        Title = Convert.ToString(reader["Title"].DbNullToNull()),
                    };

                    return result;
                };

            JoeyEmployee expected = new JoeyEmployee { Id = 47097, Title = "Exception Log Manager Handling" };

            //act
            JoeyEmployee actual;
            actual = RowMapperService.GetEntity<JoeyEmployee>(sqlStatemnet, connectionString, parameters, rowMapperDelegate);

            Assert.AreEqual(expected.Id, actual.Id);
            Assert.AreEqual(expected.Title, actual.Title);

        }

        [TestMethod()]
        public void GetEntityTest_透過delegateMappingFunction取得第一筆()
        {
            GetEntityTestHelper_透過delegateMappingFunction取得第一筆<GenericParameterHelper>();
        }


實際程式


        /// 取得查詢結果,僅取一筆,若不存在資料則回傳null。若存在多筆資料,則回傳第一筆。
        /// 透過rowMapperDelegate function決定如何回傳Entity
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStatemnet">The SQL statemnet.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="parameters">The parameters.</param>
        /// <param name="rowMapperDelegate">The row mapper delegate.</param>
        /// <returns></returns>
        public static T GetEntity<T>(string sqlStatemnet, string connectionString, SqlParameter[] parameters, Func<SqlDataReader, T> rowMapperDelegate) where T : class, new()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlStatemnet, connection);
                sqlCommand.Parameters.AddRange(parameters);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);

                if (reader.Read())
                {
                    var result = rowMapperDelegate(reader);
                    reader.Close();
                    return result;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
        }


2. 使用自訂屬性來做RowMapping:

==針對回傳集合的module==
整合測試程式


        ///GetEntityCollection 的測試
        ///</summary>
        public void GetEntityCollectionTestHelper_測試ColumnMappingAttribute<T>()
            where T : new()
        {
            //arrange
            string sqlStatemnet = @"SELECT TOP 100 LogID, EventID, Title FROM Log(NOLOCK) where LogID BETWEEN @low and @upper";
            string connectionString = @"你的connection string";
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("low", 47097), new SqlParameter("upper", 47098) };

            List<JoeyEmployee> expected = new List<JoeyEmployee>
            {
                new JoeyEmployee{ Id=47097, Title="Exception Log Manager Handling"},        
                new JoeyEmployee{ Id=47098, Title="Exception Log Manager Handling"},        
            };

            //act
            IEnumerable<JoeyEmployee> actual;
            actual = Joey.RowMapper.RowMapperService.GetEntityCollection<JoeyEmployee>(sqlStatemnet, connectionString, parameters);

            var actualToDictionary = actual.ToDictionary(x => x.Id, x => x);

            Assert.AreEqual(expected[0].Id, actualToDictionary[47097].Id);
            Assert.AreEqual(expected[0].Title, actualToDictionary[47097].Title);

            Assert.AreEqual(expected[1].Id, actualToDictionary[47098].Id);
            Assert.AreEqual(expected[1].Title, actualToDictionary[47098].Title);
        }

        [TestMethod()]
        public void GetEntityCollectionTest_測試ColumnMappingAttribute()
        {
            GetEntityCollectionTestHelper_測試ColumnMappingAttribute<GenericParameterHelper>();
        }


實際程式
自訂的Attribute


    public sealed class ColumnMappingAttribute : Attribute
    {
        public string ColumnName { get; private set; }

        public ColumnMappingAttribute(string columnName)
        {
            this.ColumnName = columnName;
        }
    }


Mapping的程式


        /// 取得查詢結果,透過ColumnMappingAttribute轉換成Entity,回傳IEnumberable泛型結果
        /// </summary>
        /// <typeparam name="T">回傳Entity型別</typeparam>
        /// <param name="sqlStatemnet">The SQL statemnet.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>SQL指令回傳的查詢結果</returns>
        public static IEnumerable<T> GetEntityCollection<T>(string sqlStatemnet, string connectionString, SqlParameter[] parameters) where T : class, new()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlStatemnet, connection);
                sqlCommand.Parameters.AddRange(parameters);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);

                int rowIndex = 0;
                while (reader.Read())
                {
                    var result = GetMappingEntity<T>(reader);
                    rowIndex++;
                    yield return result;
                }
                reader.Close();
            }
        }

        /// <summary>
        /// 透過Entity上的ColumnMappingAttribute,取得從SqlReader對應的Entity
        /// </summary>
        /// <typeparam name="T">Entity型別</typeparam>
        /// <param name="reader">The reader.</param>
        /// <returns>SqlReader對應的Entity</returns>
        private static T GetMappingEntity<T>(SqlDataReader reader) where T : new()
        {
            var result = new T();
            var columns = GetAllColumns(reader);
            PropertyInfo[] properties = typeof(T).GetProperties();

            foreach (var p in properties)
            {
                var mappingAttribute = p.GetCustomAttributes(typeof(ColumnMappingAttribute), false).FirstOrDefault() as ColumnMappingAttribute;
                var columnName = mappingAttribute == null ? p.Name : mappingAttribute.ColumnName;

                if (columns.Contains(columnName))
                {
                    if (!p.PropertyType.IsEnum)
                    {
                        p.SetValue(result, Convert.ChangeType(reader[columnName], p.PropertyType), null);
                    }
                    else
                    {
                        p.SetValue(result, Enum.ToObject(p.PropertyType, reader[columnName]), null);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 取得SqlReader上所有欄位名稱
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <returns>SqlReader上所有欄位名稱</returns>
        private static IEnumerable<string> GetAllColumns(SqlDataReader reader)
        {
            if (reader.FieldCount == 0)
            {
                yield return null;
            }

            for (int i = 0; i < reader.FieldCount; i++)
            {
                var result = reader.GetName(i);
                yield return result;
            }
        }


==針對回傳單筆的module==
整合測試程式


        ///GetEntity 的測試
        ///</summary>string connectionString = @"你的connection string";
        public void GetEntityTestHelper_透過AttributeMapping取得第一筆<T>()
            where T : new()
        {
            //arrange
            string sqlStatemnet = @"SELECT TOP 100 LogID, EventID, Title FROM Log(NOLOCK) where LogID BETWEEN @low and @upper";
            string connectionString = @"你的connection string";
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("low", 47097), new SqlParameter("upper", 47098) };

            JoeyEmployee expected = new JoeyEmployee { Id = 47097, Title = "Exception Log Manager Handling" };

            //act
            JoeyEmployee actual;
            actual = RowMapperService.GetEntity<JoeyEmployee>(sqlStatemnet, connectionString, parameters);

            Assert.AreEqual(expected.Id, actual.Id);
            Assert.AreEqual(expected.Title, actual.Title);

        }

        [TestMethod()]
        public void GetEntityTest_取得第一筆()
        {
            GetEntityTestHelper_透過AttributeMapping取得第一筆<GenericParameterHelper>();
        }


實際程式


        /// 取得查詢結果,僅取一筆,若不存在資料則回傳null。若存在多筆資料,則回傳第一筆。
        /// 透過ColumnMappingAttribute來決定回傳的Entity
        /// </summary>
        /// <typeparam name="T">回傳Entity型別</typeparam>
        /// <param name="sqlStatemnet">The SQL statemnet.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>查詢單筆結果</returns>
        public static T GetEntity<T>(string sqlStatemnet, string connectionString, SqlParameter[] parameters) where T : class, new()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlStatemnet, connection);
                sqlCommand.Parameters.AddRange(parameters);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);

                if (reader.Read())
                {
                    var result = GetMappingEntity<T>(reader);
                    reader.Close();
                    return result;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
        }


3.使用RowMapper介面來定義RowMapper的方式

==針對回傳集合的module==
整合測試程式


        ///GetEntityCollection 的測試
        ///</summary>
        public void GetEntityCollectionTest_使用IRowMapper<T>()
            where T : new()
        {
            //arrange
            string sqlStatemnet = @"SELECT TOP 100 LogID, EventID, Title FROM Log(NOLOCK) where LogID BETWEEN @low and @upper";
            string connectionString = @"你的connection string";
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("low", 47097), new SqlParameter("upper", 47098) };


            List<JoeyEmployee> expected = new List<JoeyEmployee>
            {
                new JoeyEmployee{ Id=47097, Title="Exception Log Manager Handling"},        
                new JoeyEmployee{ Id=47098, Title="Exception Log Manager Handling"},        
            };

            IRowMapper<JoeyEmployee> rowmapper = new JoeyEmployee();

            //act
            IEnumerable<JoeyEmployee> actual;
            actual = RowMapperService.GetEntityCollection<JoeyEmployee>(sqlStatemnet, connectionString, parameters, rowmapper);

            var actualToDictionary = actual.ToDictionary(x => x.Id, x => x);

            Assert.AreEqual(expected[0].Id, actualToDictionary[47097].Id);
            Assert.AreEqual(expected[0].Title, actualToDictionary[47097].Title);

            Assert.AreEqual(expected[1].Id, actualToDictionary[47098].Id);
            Assert.AreEqual(expected[1].Title, actualToDictionary[47098].Title);
        }

        [TestMethod()]
        public void GetEntityCollectionTest_使用IRowMapper()
        {
            GetEntityCollectionTest_使用IRowMapper<GenericParameterHelper>();
        }


實際程式
IRowMapper介面定義


    {
        T GetMappingEntity(SqlDataReader reader, int index);
    }


使用IRowMapper的方法


        /// 取得查詢結果,透過IRowMapper取得對應的Entity,回傳IEnumberable泛型結果
        /// </summary>
        /// <typeparam name="T">回傳Entity型別</typeparam>
        /// <param name="sqlStatemnet">The SQL statemnet.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="parameters">The parameters.</param>
        /// <param name="rowmapper">RowMapper介面</param>
        /// <returns>
        /// SQL指令回傳的查詢結果
        /// </returns>
        public static IEnumerable<T> GetEntityCollection<T>(string sqlStatemnet, string connectionString, SqlParameter[] parameters, IRowMapper<T> rowmapper) where T : class, new()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlStatemnet, connection);
                sqlCommand.Parameters.AddRange(parameters);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);

                int rowIndex = 0;
                while (reader.Read())
                {
                    var result = rowmapper.GetMappingEntity(reader, rowIndex);
                    rowIndex++;
                    yield return result;
                }
                reader.Close();
            }
        }


==針對回傳單筆的module==
整合測試程式


        ///GetEntity 的測試
        ///</summary>
        public void GetEntityTestHelper_透過IRowMapper取得第一筆<T>()
            where T : class , new()
        {
            //arrange
            string sqlStatemnet = @"SELECT TOP 100 LogID, EventID, Title FROM Log(NOLOCK) where LogID BETWEEN @low and @upper";
            string connectionString = @"你的connection string";
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("low", 47097), new SqlParameter("upper", 47098) };

            JoeyEmployee expected = new JoeyEmployee { Id = 47097, Title = "Exception Log Manager Handling" };

            IRowMapper<JoeyEmployee> rowmapper = new JoeyEmployee();

            JoeyEmployee actual;
            actual = RowMapperService.GetEntity<JoeyEmployee>(sqlStatemnet, connectionString, parameters, rowmapper);

            Assert.AreEqual(expected.Id, actual.Id);
            Assert.AreEqual(expected.Title, actual.Title);
        }

        [TestMethod()]
        public void GetEntityTest_透過IRowMapper取得第一筆()
        {
            GetEntityTestHelper_透過IRowMapper取得第一筆<GenericParameterHelper>();
        }


實際程式


        /// 取得查詢結果,僅取一筆,若不存在資料則回傳null。若存在多筆資料,則回傳第一筆。
        /// 透過IRowMapper的GetMappingEntity,來決定如何回傳Entity
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStatemnet">The SQL statemnet.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="parameters">The parameters.</param>
        /// <param name="rowmapper">The rowmapper.</param>
        /// <returns></returns>
        public static T GetEntity<T>(string sqlStatemnet, string connectionString, SqlParameter[] parameters, IRowMapper<T> rowmapper) where T : class, new()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlStatemnet, connection);
                sqlCommand.Parameters.AddRange(parameters);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);

                if (reader.Read())
                {
                    var result = rowmapper.GetMappingEntity(reader, 0);
                    reader.Close();
                    return result;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
        }

其他補充
Extension Method


    {
        /// <summary>
        /// 若original為DBNull,則轉為null
        /// </summary>
        /// <param name="original">The original.</param>
        /// <returns>轉換結果</returns>
        public static object DbNullToNull(this object original)
        {
            return original == DBNull.Value ? null : original;
        }

        /// <summary>
        /// 將DataTable資料轉換成對應的Entity集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt">The dt.</param>
        /// <returns></returns>
        public static IEnumerable<T> GetEntityCollection<T>(this DataTable dt) where T : class, new()
        {
            foreach (DataRow row in dt.Rows)
            {
                T result = new T();
                foreach (DataColumn column in dt.Columns)
                {
                    typeof(T).GetProperty(column.ColumnName).SetValue(result, row[column.ColumnName].DbNullToNull(), null);
                }
                yield return result;
            }
        }
    }


對應單元測試


        ///GetEntityCollection 的測試
        ///</summary>
        public void GetEntityCollectionTestHelper_DataTable轉Entity集合<T>()
        {
            //arrange
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("Name", typeof(string)));
            dt.Columns.Add(new DataColumn("Age", typeof(int)));
            dt.Columns.Add(new DataColumn("Birthday", typeof(DateTime)));

            dt.Rows.Add("91", 31, new DateTime(2011, 11, 11));
            dt.Rows.Add("Ruffy");
            dt.Rows.Add("Bill", 40);
            dt.Rows.Add("林志玲", null, new DateTime(1977, 11, 29));

            IList<Person> expected = new List<Person>
            {
                new Person{ Name="91", Age=31, Birthday=new DateTime(2011,11,11)},
                new Person{ Name="Ruffy"},
                new Person{ Name="Bill", Age=40},
                new Person{ Name="林志玲", Birthday=new DateTime(1977,11,29)}
            };

            //act
            IEnumerable<Person> actual;
            actual = dt.GetEntityCollection<Person>();

            var result = actual.ToList<Person>();

            //assert
            Assert.AreEqual(expected.Count, result.Count);

            for (int i = 0; i < expected.Count; i++)
            {
                Assert.AreEqual(expected[i].Age, result[i].Age);
                Assert.AreEqual(expected[i].Birthday, result[i].Birthday);
                Assert.AreEqual(expected[i].Name, result[i].Name);
            }
        }

        [TestMethod()]
        public void GetEntityCollectionTest_DataTable轉Entity集合()
        {
            GetEntityCollectionTestHelper_DataTable轉Entity集合<GenericParameterHelper>();
        }

//測試用的Entity class
    public class Person
    {
        public string Name { get; set; }
        public int? Age { get; set; }
        public DateTime? Birthday { get; set; }
    }


結論
主要就是三種方式來實作DA層如何以Entity回傳,而非DataSet, DataTable等形式。

  1. 透過委派方法,讓使用者自行決定怎麼mapping到自己想要的Entity上。
  2. 自訂Attribute,透過Attribute來宣告,這個Entity的property要自動mapping到哪一個column Name,讓使用的人無感。只要定義好Entity的property與資料庫columnName的mapping即可。
  3. 自訂IRowMapper,好處是把mapping的邏輯封到介面裡面。可以重複使用,也可以有彈性的使用。例如一個Entity可以實作多種type的RowMapper。


Source code很短,但可以用的很爽。Developer學習進入門檻也低,鼓勵大家在系統中,盡量透過Entity來操作,不管是可讀性、可擴充性,都會提升許多。

有問題或有建議,麻煩再跟我說一聲,謝謝。另外,上面的絕大部分的測試是整合測試(除了最後一個),因為是在撰寫DAL與DB之間的RowMapper,要再拆IDataReader出來應該也沒啥問題,不過有需求的朋友再自己拆囉。這樣就也可以做單元測試,也可以做DB的抽換。

Sample Code: RowMapper.zip

想收到第一手公開培訓課程資訊,或想詢問企業內訓、顧問、教練、諮詢服務的,請加 Odd-e Line 帳號