※2024/08/24 ちょっと変更。
※2024/10/01 さらに変更。
プロダクトの大事な基幹であるDBアクセス処理。データをシームレスに扱うためにはORマッパーは欠かせない存在となってきています。
今回、ORマッパーにDapperを使おうと検討したところ、思わぬ壁に当たったため自作してみました。
要件
1.RubyOnRailsやLaravelのようにリッチなものは必要ない。
2.Modelとテーブルのカラム名が全く違っても使える（！）
3.とりあえずシンプルなCRUDが達成できればいい。
この(2)が曲者で、 Dapperでは直感的に実装することができなくなってしまいます。スネークケースをキャメルケースに直すくらいならできるみたいですが。実案件ではどうしてもここが障壁になることがあると思います。
個人的にはこれくらい薄いラッパーが使いやすくて好きです。特に帳票やデータ解析が絡むとSQLを書く前提の方が管理しやすかったり。シンプルなCRUDだけで事足りるならSQLを書くことはむしろ保守性を下げる要因とみなされるでしょうけど。
nuGetの追加
必要なパッケージは「Microsoft.Data.SqlClient」と「System.Reflection」です。それぞれインストールしてください。
マッパークラスの作成
早速コード。
public static class Mapper
{
public static List<Model> Query<Model>(this SqlConnection connection, SqlTransaction? transaction, string sql, params (string paramName, object value)[] parameters) where Model : class, new()
{
List<Model> models = [];
var modelType = typeof(Model);
var propertyMaps = GetPropertyAndMapColumnNames(modelType);
using (SqlCommand command = new SqlCommand(sql, connection))
{
if (transaction != null)
command.Transaction = transaction;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(new SqlParameter(parameter.paramName,parameter.value));
}
}
using (SqlDataReader reader = command.ExecuteReader())
{
SetModels<Model>(ref models, reader, propertyMaps);
}
}
return models;
}
private static void SetModels<Model>(ref List<Model> models, SqlDataReader reader, Dictionary<PropertyInfo, MapColumn> propertyMaps)
where Model : class, new()
{
while (reader.Read())
{
var model = new Model();
foreach (var pair in propertyMaps)
{
pair.Key.SetValue(model, reader[pair.Value.Name]);
}
models.Add(model);
}
}
public static int Execute(this SqlConnection connection, SqlTransaction? transaction, string sql, params (string paramName, object value)[] parameters)
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
if (transaction != null)
command.Transaction = transaction;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(new SqlParameter(parameter.paramName, parameter.value));
}
}
return command.ExecuteNonQuery();
}
}
public static Model? SearchFromKeys<Model>(this SqlConnection connection, SqlTransaction? transaction, params object[] parameters) where Model : class, new()
{
List<Model> models = [];
var modelType = typeof(Model);
var propertyMaps = GetPropertyAndMapColumnNames(modelType);
string sqlWhere = "";
var sqlParameters = new List<SqlParameter>();
int keyCount = 0;
foreach (var pair in propertyMaps)
{
if (pair.Value.IsPrimaryKey)
{
keyCount++;
var value = parameters[keyCount - 1];
if (!string.IsNullOrEmpty(sqlWhere))
sqlWhere += " and ";
sqlWhere += pair.Value.Name + " = @" + pair.Value.Name;
sqlParameters.Add(new SqlParameter("@" + pair.Value.Name, value));
}
}
if (string.IsNullOrEmpty(sqlWhere)) throw new InvalidOperationException("not set Primarykey at Model class.");
using (SqlCommand command = new SqlCommand("select * from " + GetTableName(modelType) + " where " + sqlWhere, connection))
{
if (transaction != null)
command.Transaction = transaction;
if (sqlParameters != null)
{
foreach (var parameter in sqlParameters)
{
command.Parameters.Add(parameter);
}
}
using (SqlDataReader reader = command.ExecuteReader())
{
SetModels<Model>(ref models, reader, propertyMaps);
}
}
if (models.Count > 0)
return models[0];
else
return null;
}
private static Dictionary<PropertyInfo, MapColumn> GetPropertyAndMapColumnNames(Type type)
{
Dictionary<PropertyInfo, MapColumn> propertyNameAndColumnNames = new Dictionary<PropertyInfo, MapColumn>();
foreach (var property in type.GetProperties())
{
if (property.IsDefined(typeof(MapColumnAttribute), true))
{
bool isPrimaryKey = false;
MapColumnAttribute attribute = (MapColumnAttribute)property.GetCustomAttribute(typeof(MapColumnAttribute), true)!;
if (property.IsDefined(typeof(MapPrimaryKeyAttribute), true))
{
isPrimaryKey = true;
}
propertyNameAndColumnNames.Add(property, new MapColumn(attribute.ColumnName, isPrimaryKey));
}
}
return propertyNameAndColumnNames;
}
private static string GetTableName(Type type)
{
if (type.IsDefined(typeof(MapTableAttribute), true))
{
MapTableAttribute attribute = (MapTableAttribute)type.GetCustomAttribute(typeof(MapTableAttribute), true)!;
return attribute.TableName;
}
else
throw new ArgumentException("not set MapTableAttribute");
}
public static void Insert<Model>(this SqlConnection connection, SqlTransaction? transaction, Model model) where Model : class, new()
{
var modelType = typeof(Model);
var propertyMaps = GetPropertyAndMapColumnNames(modelType);
var tableName = GetTableName(modelType);
var parameters = new List<SqlParameter>();
string sql = "";
sql += "insert into " + tableName + " values (";
string sqlValues = "";
foreach (var pair in propertyMaps)
{
if (!string.IsNullOrEmpty(sqlValues))
sqlValues += ", ";
var value = pair.Key.GetValue(model);
sqlValues += "@" + pair.Value.Name;
parameters.Add(new SqlParameter("@" + pair.Value.Name, value));
}
sql += sqlValues + ")";
int resultCount = 0;
using (SqlCommand command = new SqlCommand(sql, connection))
{
if (transaction != null)
command.Transaction = transaction;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
resultCount = command.ExecuteNonQuery();
if (resultCount != 1)
throw new AlreadyAddedException("既に登録されています。");
}
}
public static int Update<Model>(this SqlConnection connection, SqlTransaction? transaction, Model model) where Model : class, new()
{
var modelType = typeof(Model);
var propertyMaps = GetPropertyAndMapColumnNames(modelType);
var tableName = GetTableName(modelType);
var parameters = new List<SqlParameter>();
string sql = "";
var sqlWhere = "";
sql += "update " + tableName + " set ";
string sqlValues = "";
foreach (var pair in propertyMaps)
{
var value = pair.Key.GetValue(model);
if (pair.Value.IsPrimaryKey)
{
if (!string.IsNullOrEmpty(sqlWhere))
sqlWhere += " and ";
sqlWhere += pair.Value.Name + " = @" + pair.Value.Name;
}
else
{
if (!string.IsNullOrEmpty(sqlValues))
sqlValues += ", ";
sqlValues += pair.Value.Name + " = @" + pair.Value.Name;
}
parameters.Add(new SqlParameter("@" + pair.Value.Name, value));
}
if (string.IsNullOrEmpty(sqlWhere)) throw new InvalidOperationException("not set Primarykey at Model class.");
sql += sqlValues + " where " + sqlWhere;
int resultCount = 0;
using (SqlCommand command = new SqlCommand(sql, connection))
{
if (transaction != null)
command.Transaction = transaction;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
resultCount = command.ExecuteNonQuery();
if (resultCount <= 0)
throw new AlreadyDeletedException("更新対象がありませんでした。");
}
return resultCount;
}
public static int DeleteFromParams<Model>(this SqlConnection connection, SqlTransaction? transaction, string whereBlock, params (string paramName, object value)[] parameters) where Model : class, new()
{
var modelType = typeof(Model);
var tableName = GetTableName(modelType);
if (string.IsNullOrEmpty(whereBlock))
throw new InvalidOperationException("not set [where]block.");
if (parameters.Length < 1)
throw new InvalidOperationException("not set params");
string sql = "delete from " + tableName + " where " + whereBlock;
int resultCount = 0;
using (SqlCommand command = new SqlCommand(sql, connection))
{
if (transaction != null)
command.Transaction = transaction;
foreach (var parameter in parameters)
{
command.Parameters.Add(new SqlParameter(parameter.paramName,parameter.value));
}
resultCount = command.ExecuteNonQuery();
}
return resultCount;
}
public static int DeleteFromKeys<Model>(this SqlConnection connection, SqlTransaction? transaction, Model model) where Model : class, new()
{
var modelType = typeof(Model);
var propertyMaps = GetPropertyAndMapColumnNames(modelType);
var tableName = GetTableName(modelType);
var parameters = new List<SqlParameter>();
string sql = "";
var sqlWhere = "";
sql += "delete from " + tableName;
foreach (var pair in propertyMaps)
{
if (pair.Value.IsPrimaryKey)
{
var value = pair.Key.GetValue(model);
if (!string.IsNullOrEmpty(sqlWhere))
sqlWhere += " and ";
sqlWhere += pair.Value.Name + " = @" + pair.Value.Name;
parameters.Add(new SqlParameter("@" + pair.Value.Name, value));
}
}
if (string.IsNullOrEmpty(sqlWhere)) throw new InvalidOperationException("not set Primarykey at Model class.");
sql += " where " + sqlWhere;
int resultCount = 0;
using (SqlCommand command = new SqlCommand(sql, connection))
{
if (transaction != null)
command.Transaction = transaction;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
resultCount = command.ExecuteNonQuery();
}
return resultCount;
}
}
public class MapColumn
{
public string Name { get; set; } = "";
public bool IsPrimaryKey { get; set; } = false;
public MapColumn(string name, bool isPrimaryKey = false)
{
Name = name;
IsPrimaryKey = isPrimaryKey;
}
}
public class MapTableAttribute : Attribute
{
public string TableName { get; } = "";
public MapTableAttribute(string name)
{
TableName = name;
}
}
public class MapColumnAttribute : Attribute
{
public string ColumnName { get; } = "";
public MapColumnAttribute(string name)
{
ColumnName = name;
}
}
public class MapPrimaryKeyAttribute : Attribute
{
public bool IsSet { get; } = true;
public MapPrimaryKeyAttribute()
{
}
}使い方
モデルには属性でテーブル名とカラム名を指定。
[MapTable("test")]
public class Test
{
[MapPrimaryKey]
[MapColumn("id")]
public int TestId { get; set; }
[MapColumn("name")]
public string TestName { get; set; }
public int NoColumn { get; set; } = 111;
}テーブルと結びつけたい場合はMapColumnで指定。結びつけなくて良いものは未指定。
MapTableでテーブル名を指定できるようにしています。
各拡張メソッドのテストコードはこんな感じ。n層アーキテクチャな考え方で書いてます。
public class TestAccess
{
public static Test? Select(SqlConnection connection, SqlTransaction? transaction, string id)
{
return connection.SearchFromKeys<Test>(transaction, id);
}
public static List<Test> SelectLikeName(SqlConnection connection, SqlTransaction? transaction, string likeName)
{
return connection.Query<Test>(transaction, "select * from test where name like @like",
("@like","%" + likeName + "%"));
}
public static void Insert(SqlConnection connection, SqlTransaction? transaction, Test data)
{
connection.Insert(transaction, data);
}
public static int Update(SqlConnection connection, SqlTransaction? transaction, Test data)
{
return connection.Update(transaction, data);
}
public static int ExecuteUpdate(SqlConnection connection, SqlTransaction? transaction, Test data, string addText)
{
return connection.Execute(transaction,"update test set name = @name where id = @id",
("@name",data.Name + addText),("@id",data.Id));
}
public static int DeleteLikeName(SqlConnection connection, SqlTransaction? transaction, string likeName)
{
return connection.DeleteFromParams<Test>(transaction, "name like @like", ("@like", "%" + likeName + "%"));
}
public static int DeleteFromKeys(SqlConnection connection, SqlTransaction? transaction, Test data)
{
return connection.DeleteFromKeys<Test>(transaction, data);
}
}上位の層でトランザクション管理すれば良い感じにロジックとアクセスを分離して実装できます。
速度
今回5万件のデータをDataGridViewに表示するまででテストしてみましたが、自作は自由度がある分Dapperの方が早かったです。しっかりと平均値を出したわけではありませんが、大体Dapperだと70%くらいになります。私の関わるプロダクトであれば、これくらいの差なら柔軟に列名とマッピングできるメリットが上回ります。
