1、首先写基本查询和查询参数语句如下:
using Dapper;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion.Internal;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tzkj.Dapper;
using Tzkj.DigitalSuite.Commodity.Query;
using Tzkj.Superhard.SupplyDemand.EntityFrameworkCore;
using Tzkj.Superhard.SupplyDemand.Query.Supplies;
using Tzkj.Superhard.SupplyDemand.SupplyDemands;
using Volo.Abp.DependencyInjection;
using Volo.Abp.EntityFrameworkCore;
namespace Tzkj.Superhard.SupplyDemand.Query.Demands
{
/// <summary>
/// 需求dapper仓储
/// </summary>
public class DemandDapperRepository : SupdServiceDapperRepositoryBase, ITransientDependency
{
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbContextProvider"></param>
public DemandDapperRepository(IDbContextProvider<ISupplyDemandDbContext> dbContextProvider) : base(dbContextProvider)
{
}
/// <summary>
/// 获取需求服务分页列表
/// </summary>
/// <param name="name"></param>
/// <param name="status"></param>
/// <returns></returns>
public async Task<PagedResultQto<DemandListQto>> GetPagedResultAsync(Guid userId, string? name, string? status,
int pageIndex, int pageSize)
{
const string tableJoinStr = "supd_demand_info";
const string columnsStr = " id,category,title,contact_person,contact_number,publish_time,registration_deadline,status";
const string orderByStr = "publish_time desc";
var whereStr = "1=1 and del_flag=0 and create_by=@userId";
var sqlPredicate = new List<string>();
var queryParams = new DynamicParameters();
queryParams.Add(nameof(userId), userId, DbType.Guid);
if (!name.IsNullOrWhiteSpace())
{
sqlPredicate.Add("title like @name");
queryParams.Add(nameof(name), $"%{name}%", DbType.String);
}
if (!status.IsNullOrWhiteSpace())
{
sqlPredicate.Add("status = @status");
queryParams.Add(nameof(status), status, DbType.String);
}
return await GetPagedResultAsync<DemandListQto>(tableJoinStr, columnsStr, whereStr, orderByStr, pageIndex, pageSize, queryParams);
}
}
}
2、创建SupdServiceDapperRepositoryBase基类,在上方代码中会被继承
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.Tracing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tzkj.Dapper;
using Tzkj.Superhard.SupplyDemand.EntityFrameworkCore;
using Volo.Abp;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Repositories.Dapper;
using Volo.Abp.EntityFrameworkCore;
namespace Tzkj.DigitalSuite.Commodity.Query
{
public class SupdServiceDapperRepositoryBase : DapperRepository<ISupplyDemandDbContext>
{
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="tablesJoinStr"></param>
/// <param name="columnsStr"></param>
/// <param name="whereStr"></param>
/// <param name="orderByStr"></param>
/// <param name="queryParams"></param>
/// <returns></returns>
/// <exception cref="ArgumentException"></exception>
protected async Task<PagedResultQto<T>> GetPagedResultAsync<T>(
string tablesJoinStr,
string columnsStr,
string whereStr,
string orderByStr,
int pageIndex,
int pageSize,
DynamicParameters queryParams = null)
{
if (pageIndex <= 0 || pageSize <= 0)
{
//throw new ArgumentException("pageIndex与pageSize 不可以小于或等于0");
throw new UserFriendlyException("pageIndex与pageSize 不可以小于或等于0");
}
var skip = (pageIndex - 1) * pageSize;
var sb = new StringBuilder();
sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tablesJoinStr, whereStr);
sb.AppendFormat(@"SELECT {0}
FROM {1}
WHERE {2}
ORDER BY {3}
LIMIT @skip,@take", columnsStr, tablesJoinStr, whereStr, orderByStr);
queryParams ??= new DynamicParameters();
queryParams.Add("skip", skip, DbType.Int32);
queryParams.Add("take", pageSize, DbType.Int32);
var connection = await GetDbConnectionAsync();
var dbTransaction = await GetDbTransactionAsync();
using var reader = await connection.QueryMultipleAsync(sb.ToString(), queryParams, transaction: dbTransaction);
//using var reader = await connection.QueryMultipleAsync(sb.ToString(), queryParams);
var total = await reader.ReadFirstAsync<long>();
var list = await reader.ReadAsync<T>();
return new PagedResultQto<T>(total, list.ToList());
}
protected string GetQuerySql(string noWhereSql, List<string> sqlPredicates)
{
if (!sqlPredicates.Any()) return noWhereSql;
var sqlPredicatesStr = sqlPredicates.Aggregate((p, n) => $"{p} and {n}");
var sql = @$"{noWhereSql}
where {sqlPredicatesStr}";
return sql;
}
}