[.NET]RowMapper模組
前言
很常用需要到一些RowMapper模組，這邊做個記錄，沒有優化、也沒有太多防呆，但已經可以符合我的需求了。
RowMapper模組的角色，請見下圖：
模組
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等形式。
- 透過委派方法，讓使用者自行決定怎麼mapping到自己想要的Entity上。
- 自訂Attribute，透過Attribute來宣告，這個Entity的property要自動mapping到哪一個column Name，讓使用的人無感。只要定義好Entity的property與資料庫columnName的mapping即可。
- 自訂IRowMapper，好處是把mapping的邏輯封到介面裡面。可以重複使用，也可以有彈性的使用。例如一個Entity可以實作多種type的RowMapper。
Source code很短，但可以用的很爽。Developer學習進入門檻也低，鼓勵大家在系統中，盡量透過Entity來操作，不管是可讀性、可擴充性，都會提升許多。
有問題或有建議，麻煩再跟我說一聲，謝謝。另外，上面的絕大部分的測試是整合測試（除了最後一個），因為是在撰寫DAL與DB之間的RowMapper，要再拆IDataReader出來應該也沒啥問題，不過有需求的朋友再自己拆囉。這樣就也可以做單元測試，也可以做DB的抽換。
Sample Code： RowMapper.zip
blog 與課程更新內容，請前往新站位置：http://tdd.best/
