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); }