/// <summary>
/// 获取父节点列表Sql语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fieldName">字段名</param>
/// <param name="fieldValue">字段值</param>
/// <param name="order">排序</param>
/// <param name="idOnly">是否只需要主键</param>
/// <param name="conditionBuilder">条件生成器</param>
/// <returns></returns>
public string GetParentsByCode(string tableName, string fieldName, string fieldValue, string order, bool idOnly = false,
Action <IConditionBuilder> conditionBuilder = null)
{
StringBuilder sb = new StringBuilder();
if (idOnly)
{
sb.AppendFormat("select {0} ", SqlBusinessLogicOptions.FieldId);
}
else
{
sb.Append("select * ");
}
sb.AppendFormat(" from {0} ", tableName);
ConditionBuilder builder = new ConditionBuilder();
builder.AppendRaw("(LEFT({0}, LEN({1})) = {1}) ", builder.AddParameter(fieldName, fieldValue), fieldName);
conditionBuilder?.Invoke(builder);
sb.Append(builder.ToString());
_currentParamDict = builder.GetParamDict();
sb.AppendFormat(" order by {0}", order);
return(sb.ToString());
}
public void Test_AppendRaw()
{
ConditionBuilder builder = new ConditionBuilder();
builder.AppendRaw("A.CreateTime like '%123456%'").AppendRaw("A.CreateTime like '%123456%'");
var result = builder.ToString();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
}
public void Test_CustomParam()
{
ConditionBuilder builder = new ConditionBuilder();
builder.AppendRaw("(LEFT({0}, LEN({1})) = {1}",
builder.AddParameter("ParentID", "BB2A1B4A-5E9E-33D5-697B-39E37974ACC4"), "ParentID")
.Equal("IsDeleted", 1).Equal("Status", 1);
var result = builder.ToString();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
}
public void Test_Block()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
ConditionBuilder builder = new ConditionBuilder();
builder.Append("A.CreateTime", SqlOperator.Equal, DateTime.Now);
builder.Between("B.CreateTime", DateTime.Now, DateTime.Now.AddDays(1));
//builder.Block(RelationType.And, childBuilder);
builder.Or(child =>
{
child.Equal("D.CreateTime", DateTime.Now);
child.Equal("E.CreateTime", DateTime.Now);
child.Equal("E.CreateTime", DateTime.Now);
child.And(c2 =>
{
c2.Append("HH.CreateTime", SqlOperator.Equal, 5);
});
}).Or(child =>
{
child.Append(RelationType.And, "E.CreateTime", SqlOperator.Equal, DateTime.Now);
});
builder.Append(RelationType.And, "F.CreateTime", SqlOperator.Equal, DateTime.Now);
builder.Append(RelationType.And, "A.ID", SqlOperator.In, new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 });
builder.Append(RelationType.And, "B.ID", SqlOperator.In, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
builder.AppendRaw("E.CreateTime like '%123456%'");
builder.Between("H.CreateTime", DateTime.Now, DateTime.Now.AddDays(5));
var result = builder.ToString();
stopwatch.Stop();
var param = builder.GetParamDict().ToJson();
Console.WriteLine(result);
Console.WriteLine(param);
Console.WriteLine(stopwatch.Elapsed.Milliseconds);
}