public async Task <PaginatedResponse <OrderModel> > Query(GetOrdersRequest request)
{
var parameters = new Dictionary <string, object>();
var where = new SqlWhereBuilder(request, _columns).Build();
var orderBy = new SqlOrderByBuilder($@"{nameof(OrderModel.Id)} ASC", _columns, request).Build();
var pagination = new SqlPaginationBuilder(request).Build();
var select = $@"
SELECT DISTINCT
{nameof(OrderModel.Id)},
{nameof(OrderModel.CarFrame)},
{nameof(OrderModel.Model)},
{nameof(OrderModel.LicensePlate)},
{nameof(OrderModel.DeliveryDate)}
FROM {nameof(CarsContext.Orders)} {where.Sql} {orderBy} {pagination.Sql}";
var count = $@"SELECT COUNT(*) FROM {nameof(CarsContext.Orders)} O {where.Sql}";
parameters.AddRange(where.Parameters);
parameters.AddRange(pagination.Parameters);
var result = await _db.QueryMultipleAsync($"{select};{count}", parameters);
return(new PaginatedResponse <OrderModel>(result.Read <OrderModel>(), result.ReadFirst <long>()));
}
public void CustomComparisonOperator()
{
// Creating node hierarchy.
var node = new Node();
var where = new Node("where");
var and1 = new Node("and");
and1.Add(new Node("foo1.qwerty", "howdy"));
where.Add(and1);
node.Add(where);
node.Add(new Node("table", "foo"));
// Adding our custom operator.
SqlWhereBuilder.AddComparisonOperator("qwerty", (builder, args, colNode, escapeChar) => {
builder.Append(" <> ");
SqlWhereBuilder.AppendArgs(args, colNode, builder, escapeChar);
});
var builder = new SqlReadBuilder(node, "'");
// Extracting SQL + params, and asserting correctness.
var result = builder.Build();
var sql = result.Get <string>();
Assert.Equal("select * from 'foo' where 'foo1' <> @0 limit 25", sql);
var arg1 = result.Children.First();
Assert.Equal("@0", arg1.Name);
Assert.Equal("howdy", arg1.Value);
}
/// <summary>
/// 获取邮件发送记录列表
/// </summary>
/// <param name="queryWhere">查询条件</param>
/// <returns></returns>
public Task <IPageResult <EmailSendRecordListDto> > GetEmailSendRecordListAsync(EmailSendRecordPageQueryWhere queryWhere)
{
SqlWhereBuilder whereBuilder = new SqlWhereBuilder("FIsDeleted=0", ReaderDataType);
const string selectTable = TableConstant.TABLE_NAME_EMAILSENDEDRECORD + SqlConstant.SQLSERVER_WITHNOLOCK;
const string selectColumn = "FID,FReceiveEmail,FTheme,FContent,FSendEmail,FSendState,FStateRemark,ISNULL(FLastModifyTime,FCreateTime) FLastModifyTime";
const string order = "ISNULL(FLastModifyTime,FCreateTime) DESC";
return(QueryPageListAsync <EmailSendRecordListDto>(selectColumn, selectTable, whereBuilder.ToString(), order, queryWhere.PageIndex, queryWhere.PageSize, cmdParms: queryWhere));
}
/// <summary>
/// 分页获取项目列表
/// </summary>
/// <param name="queryWhere">查询条件</param>
/// <returns>项目列表</returns>
public Task <IPageResult <ProjectListDto> > GetProjectListAsync(ProjectPageQueryWhere queryWhere)
{
SqlWhereBuilder whereBuilder = new SqlWhereBuilder("FIsDeleted=0", ReaderDataType);
whereBuilder.AppendLike("FName", queryWhere.Name, paramKey: nameof(queryWhere.Name));
const string selectTable = TABLE_NAME_PROJECT + SQLSERVER_WITHNOLOCK;
const string selectColumn = "FID,FName,FComment,ISNULL(FLastModifyTime,FCreateTime) AS FLastModifyTime";
const string order = "ISNULL(FLastModifyTime,FCreateTime) DESC";
return(QueryPageListAsync <ProjectListDto>(selectColumn, selectTable, whereBuilder.ToString(), order, queryWhere.PageIndex, queryWhere.PageSize, cmdParms: queryWhere));
}
/// <summary>
/// 分页获取服务器列表
/// </summary>
/// <param name="queryWhere">查询条件</param>
/// <returns>服务器列表</returns>
public Task <IPageResult <ServicerListDto> > GetServiceListAsync(ServicePageQueryWhere queryWhere)
{
SqlWhereBuilder whereBuilder = new SqlWhereBuilder("FIsDeleted=0", ReaderDataType);
whereBuilder.AppendEqual("FMacAddress", queryWhere.MacAddress, paramKey: nameof(queryWhere.MacAddress))
.AppendLike("FIP", queryWhere.IP, paramKey: nameof(queryWhere.IP))
.AppendLike("FName", queryWhere.Name, paramKey: nameof(queryWhere.Name))
;
const string selectTable = TABLE_NAME_SERVCER + SQLSERVER_WITHNOLOCK;
const string selectColumn = "FID,FMacAddress,FIP,FName,FComment,ISNULL(FLastModifyTime,FCreateTime) AS FLastModifyTime";
const string order = "ISNULL(FLastModifyTime,FCreateTime) DESC";
return(QueryPageListAsync <ServicerListDto>(selectColumn, selectTable, whereBuilder.ToString(), order, queryWhere.PageIndex, queryWhere.PageSize, cmdParms: queryWhere));
}
/// <summary>
/// 异步查找警告日志列表
/// </summary>
/// <param name="queryWhere">查询条件</param>
/// <returns>警告日志列表</returns>
public Task <IPageResult <WarningLogListDto> > GetWarningLogListAsync(WarningLogPageQueryWhere queryWhere)
{
SqlWhereBuilder whereBuilder = new SqlWhereBuilder("A.FIsDeleted=0", ReaderDataType);
whereBuilder.AppendEqual("B.FProjectID", queryWhere.ProjectID, nameof(queryWhere.ProjectID))
.AppendEqual("B.FServicerID", queryWhere.ServicerID, nameof(queryWhere.ServicerID))
.AppendLike("B.FCallMemberName", queryWhere.CallMethodName, nameof(queryWhere.CallMethodName))
.AppendEqual("B.FSource", queryWhere.Source, nameof(queryWhere.Source))
.AppendEqual("B.FRequestGuid", queryWhere.RequestGuid, nameof(queryWhere.RequestGuid))
.AppendEqual("A.FNoticeState", queryWhere.NoticeState, nameof(queryWhere.NoticeState))
.AppendEqual("A.FDealState", queryWhere.DealState, nameof(queryWhere.DealState))
;
string selectTable = $"{TABLE_NAME_WARNINGLOG} A {SQLSERVER_WITHNOLOCK} {LeftJoin} {TABLE_NAME_RUNTIMELOG} B {SQLSERVER_WITHNOLOCK} {On} A.FRunTimeLogID=B.FID {LeftJoin} {TABLE_NAME_SERVCER} C {SQLSERVER_WITHNOLOCK} {On} C.FID=B.FServicerID AND C.FIsDeleted=0";
const string selectColumn = "A.FID,A.FOperateAdvice,a.FTreatmentScheme,A.FNoticeState,A.FDealState,ISNULL(A.FLastModifyTime,A.FCreateTime) FLastModifyTime,B.FLogLevel,B.FCallMemberName,B.FContent,B.FSource,B.FExecuteTime,B.FRequestGuid,B.FProjectName,C.FName AS FServicerName ";
const string order = "A.FDealState,ISNULL(A.FLastModifyTime,A.FCreateTime)";
return(QueryPageListAsync <WarningLogListDto>(selectColumn, selectTable, whereBuilder.ToString(), order, queryWhere.PageIndex, queryWhere.PageSize, cmdParms: queryWhere));
}
/// <summary>
/// 加载运行时日志信息
/// </summary>
/// <param name="queryWhere">查询条件</param>
/// <returns></returns>
public Task <IPageResult <RuntimeLogListDto> > GetRuntimeLogListAsync(RuntimeLogPageQueryWhere queryWhere)
{
SqlWhereBuilder whereBuilder = new SqlWhereBuilder("A.FIsDeleted=0", ReaderDataType);
whereBuilder.AppendEqual("A.FLogLevel", queryWhere.LogLevel, nameof(queryWhere.LogLevel))
.AppendEqual("A.FProjectID", queryWhere.ProjectID, nameof(queryWhere.ProjectID))
.AppendEqual("A.FServicerID", queryWhere.ServicerID, nameof(queryWhere.ServicerID))
.AppendEqual("A.FSource", queryWhere.Source, nameof(queryWhere.Source))
.AppendEqual("A.FRequestGuid", queryWhere.RequestGuid, nameof(queryWhere.RequestGuid))
.AppendMoreThanOrEqual("A.FExecuteTime", queryWhere.ExecuteTimeStart, nameof(queryWhere.ExecuteTimeStart))
.AppendLessThan("A.FExecuteTime", queryWhere.ExecuteTimeEndValue, nameof(queryWhere.ExecuteTimeEndValue))
;
string selectTable = $"{TABLE_NAME_RUNTIMELOG} A {SQLSERVER_WITHNOLOCK} LEFT JOIN {TABLE_NAME_SERVCER} B {SQLSERVER_WITHNOLOCK} ON A.FServicerID=B.FID AND B.FIsDeleted=0";
const string selectColumn = "A.FID,A.FLogLevel,A.FProjectName,B.FName AS FServicerName,A.FCallMemberName,A.FContent,A.FSource,A.FRequestGuid,A.FExecuteTime,A.FCreateTime";
const string order = "A.FExecuteTime DESC";
return(QueryPageListAsync <RuntimeLogListDto>(selectColumn, selectTable, whereBuilder.ToString(), order, queryWhere.PageIndex, queryWhere.PageSize, cmdParms: queryWhere));
}
private List <FakeUser> QueryMethod(Guid?id, string username, string name, string mobile, int minPoints, int maxPoints, DateTime maxCreateDate)
{
var sqlWhere = SqlWhereBuilder.Create()
.And("[Id] = {0}", "@id", id)
.AndSub(e => e.And("[Username] = {0}", "@username", username).Or("[Name] = {0}", "@name", name))
.And("[Mobile] = {0}", "@mobile", mobile)
.AndBetween("[Points] NOT BETWEEN {0} AND {1}", "@minPoints", minPoints, "@maxPoints", maxPoints)
.AndSub(e => e.And("[Points] >= {0}", "@minPoints", minPoints).And("[Points] <= {0}", "@maxPoints", maxPoints))
.And("[CreateDate] <= @maxCreateDate", "@maxCreateDate", maxCreateDate);
var sql = "SELECT * FROM [FakeUser] " + sqlWhere.ToWhereString();
Trace.WriteLine(sql);
return(null);
//using (var context = new FakeEcDbContext())
//{
// var users = context.Database.SqlQuery<FakeUser>(sql, sqlWhere.ToSqlParameters()).ToList();
// return users;
//}
}
public SqlBuilder(DatabaseType databaseType = DatabaseType.MSSQLServer)
{
_databaseType = databaseType;
_sqlWhereBuilder = new SqlWhereBuilder(dbType: databaseType);
}