/// <summary>
/// 生成 like 语句从句
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="region"></param>
/// <param name="fieldName"></param>
/// <param name="likevalue"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static string GenLikeClause(this DapperSqlGenerator sqlgen, LikeRegion region, string fieldName, string likevalue, DynamicParameters parameters)
{
string value = likevalue;
var underLineFieldName = DapperContextExtentions.ConvertNameFromPascalToUnderline(null, fieldName);
switch (region)
{
case LikeRegion.Both:
value = $"%{likevalue}%";
break;
case LikeRegion.Left:
value = $"%{likevalue}";
break;
case LikeRegion.Right:
value = $"{likevalue}%";
break;
default:
throw new SherlockException($"{nameof(SqlGeneratorExtention)}-->{nameof(GenLikeClause)}:不支持的region");
}
parameters.Add(fieldName, value);
return($" {underLineFieldName} like @{fieldName} ");
}
public async Task <IDbConnection> SetUp()
{
var connection = new SQLiteConnection(ConnectionString);
ProductOrderInit.Init(ConnectionString);
DapperSqlGenerator.Configure(ConnectionString).Entity <TestProduct>(a =>
{
// Ignoring non-trivial mapping for testing
a.Property(x => x.MaybeGuid).Ignore();
a.Property(x => x.Guid).Ignore();
a.Property(x => x.Duration).Ignore();
});
connection.Open();
var namespaceName = Assembly.GetExecutingAssembly().GetName().Name + ".TestMigrations";
var applied = await connection.InitDatabase(Assembly.GetExecutingAssembly(), namespaceName, new SimpleMigrationOptions { ForceApplyMissing = true });
Assert.AreEqual(1, applied);
var p1 = new TestProduct
{
Kind = 5,
Name = "Product 1",
Content = "Empty box",
Value = -5,
Enum = TestEnum.All,
Date = DateTime.UtcNow
};
var inserted1 = await connection.InsertReturnAsync(p1);
Id1 = inserted1.Id;
Assert.AreNotEqual(0, Id1);
var p2 = new TestProduct
{
Kind = 7,
Name = "Product 2",
Content = "Full box",
Value = 987,
Enum = TestEnum.None,
Date = DateTime.UtcNow
};
var inserted2 = await connection.InsertReturnAsync(p2);
Id2 = inserted2.Id;
Assert.AreNotEqual(0, Id2);
Assert.AreNotEqual(Id1, Id2);
return(connection);
}
private void SingleFilterTest()
{
DapperSqlGenerator generator = this.MockGenerator();
SingleQueryFilter filter = new SingleQueryFilter();
filter.AddEqual("name", "evalue");
filter.AddGreaterOrEqual("gte", 1);
filter.AddGreater("gt", 0);
filter.AddLess("lt", 10000);
filter.AddLessOrEqual("lte", 999);
filter.AddNotEqual("null", null);
DynamicParameters parameters = new DynamicParameters();
generator.GenerateFilter <DapperEntityWithNoBool>(filter, parameters);
Assert.Equal(5, parameters.ParameterNames.Count()); // null 不作为参数。
}
private void CombinedFilterTest()
{
DapperSqlGenerator generator = this.MockGenerator();
SingleQueryFilter filter = new SingleQueryFilter();
filter.AddEqual("name", "evalue");
filter.AddGreaterOrEqual("gte", 1);
filter.AddGreater("gt", 0);
filter.AddLess("lt", 10000);
filter.AddLessOrEqual("lte", 999);
filter.AddNotEqual("null", null);
SingleQueryFilter filter2 = new SingleQueryFilter();
filter2.AddGreaterOrEqual("gte2", 1);
filter2.AddEqual("name2", "evalue");
filter2.AddGreater("gt2", 0);
filter2.AddLess("lt2", 10000);
filter2.AddLessOrEqual("lte2", 999);
filter2.AddNotEqual("null2", null);
SingleQueryFilter filter3 = new SingleQueryFilter();
filter3.AddEqual("name23", "evalue");
filter3.AddGreaterOrEqual("gte3", 1);
filter3.AddGreater("gt3", 0);
filter3.AddLess("lt3", 10000);
filter3.AddLessOrEqual("lte3", 999);
filter3.AddEqual("null3", null);
CombinedQueryFilter cf1 = new CombinedQueryFilter(filter2, filter3, BooleanClause.And);
CombinedQueryFilter cf2 = new CombinedQueryFilter(filter, cf1, BooleanClause.Or);
DynamicParameters parameters = new DynamicParameters();
generator.GenerateFilter <DapperEntityWithNoBool>(cf2, parameters);
Assert.Equal(15, parameters.ParameterNames.Count()); // null 不作为参数。
}
/// <summary>
/// 生成以 AND 连接的 不带 WHERE 关键字的 WHERE 语句
/// </summary>
/// <param name="sqlgen"></param>
/// <param name="filter">查询条件</param>
/// <param name="fieldsInClause">in条件集合</param>
/// <param name="likeFilds">like条件集合</param>
/// <returns></returns>
public static string GenAndWhereSql <T>(this DapperSqlGenerator sqlgen, QueryFilter filter, Dictionary <string, List <object> > fieldsInClause, Dictionary <string, string> likeFilds, DynamicParameters parameters)
{
List <string> where = new List <string>();
if (filter != null)
{
var whilefilter = sqlgen.GenerateFilter <T>(filter, parameters);
if (!string.IsNullOrWhiteSpace(whilefilter))
{
where.Add(whilefilter);
}
}
if (!fieldsInClause.IsNullOrEmpty())
{
foreach (var item in fieldsInClause)
{
var inclause = sqlgen.GenerateInClause <T>(item.Key, item.Value, parameters);
if (!string.IsNullOrWhiteSpace(inclause))
{
where.Add(inclause);
}
}
}
if (!likeFilds.IsNullOrEmpty())
{
foreach (var item in likeFilds)
{
var likeclause = sqlgen.GenLikeClause(LikeRegion.Both, item.Key, item.Value, parameters);
if (!string.IsNullOrWhiteSpace(likeclause))
{
where.Add(likeclause);
}
}
}
return(where.ToArrayString(" AND "));
}
public void TestExtensionsCanConfigure()
{
OnModelCreating(DapperSqlGenerator.Configure());
}
public static void Init(string connectionString = null)
{
OnModelCreating(DapperSqlGenerator.Configure(connectionString));
}
public static void Reset(string connectionString = null)
{
DapperSqlGenerator.Reset(connectionString);
}