使用 Reflection构建 Dapper SQL Builder 查询参数
本文关键字:Builder 查询 参数 SQL Dapper Reflection 构建 使用 | 更新日期: 2023-09-27 17:55:19
我正在研究使用 Dapper 作为我们的 DAL 层的项目,因为我们仍在使用内联查询。为了构造我们的查询,我们使用dapper sqlBuilder模板。
下面是我们的方法代码之一:
SqlBuilder builder = new SqlBuilder();
var update = builder.AddTemplate("DECLARE @transactionID bigint; " +
"EXEC @transactionID = dbo.sp_getAndIncrementTransactionCounter @accountID; " +
"UPDATE TimeEntryIndex /**set**/ OUTPUT inserted.id /**where**/");
builder.Set("userID = @userID", new { field.UserId });
builder.Set("deviceID = @deviceID", new { field.DeviceId });
builder.Set("deviceName = @deviceName", new { field.DeviceName });
builder.Set("transactionID = @transactionID");
builder.Set("state = @state", new { ttContent.state });
builder.Set("lastUpdated = GETUTCDATE()");
// Determine which fields to update
if (!string.IsNullOrEmpty(ttContent.title))
{
builder.Set("title = @title", new { ttContent.title });
}
if (!string.IsNullOrEmpty(ttContent.description))
{
builder.Set("description = @description", new { ttContent.description });
}
if (tEntryIndex.finish.HasValue)
{
builder.Set("finish = @finish", new { tEntryIndex.finish });
}
if (ttContent.created.HasValue)
{
builder.Set("created = @created", new { ttContent.created });
}
if (tEntryIndex.duration.HasValue)
{
builder.Set("duration = @duration", new { tEntryIndex.duration });
}
builder.Where("accountid = @accountid", new { accountid = field.AccountId });
builder.Where("id = @id", new { id = timeEntryId });
var result = new Result<long?>(sqlConn.ExecuteScalar<long?>(update.RawSql, update.Parameters));
在上面的代码中,它看起来适合数据库中的少量字段。但是,当我们在表中有很多字段时,假设 25 列,仅构建该构建器就需要做很多工作。
我想使用反射将其包装在一个函数中。我首先尝试这样的东西:
foreach (PropertyInfo prop in typeof(Account).GetProperties())
{
// Will do checking on every type possible
if (typeof(String).IsAssignableFrom(prop.PropertyType) && prop.GetValue(aContent) != null && !string.IsNullOrEmpty(prop.GetValue(aContent).ToString()))
{
builder.Set(string.Format("{0} = @{0}", prop.Name.ToLowerInvariant()), new { <what should I put in here> });
}
}
问题是我找不到一种方法来使其与 Dapper 在其构建器类上需要的动态参数一起工作。
每一个建议和帮助将不胜感激。
您可以使用
DynamicParamters
类添加动态名称-值对:
var prms = new DynamicParameters();
foreach (PropertyInfo prop in typeof(Account).GetProperties())
{
// Will do checking on every type possible
if (typeof(String).IsAssignableFrom(prop.PropertyType) && prop.GetValue(aContent) != null && !string.IsNullOrEmpty(prop.GetValue(aContent).ToString()))
{
builder.Set(string.Format("{0} = @{0}", prop.Name.ToLowerInvariant())ף
prms.Add(prop.Name.ToLowerInvariant(), prop.GetValue(aContent));
}
}
prms.AddDynamicParams(update.Parameters);
var result = new Result<long?>(sqlConn.ExecuteScalar<long?>(update.RawSql, prms));