public async Task <Poll> GetByIdAsync(int id)
{
var records = await _databaseRepository.GetCollectionAsync <PollRecord>(Functions.PollRepository.GetById,
DbParameterHelper.Create(nameof(id), id));
return(ToPoll(records).FirstOrDefault());
}
/// <summary>
/// 执行命令,将第一列的值填充到类型为T的行集合中
/// </summary>
/// <typeparam name="T">返回值类型</typeparam>
/// <param name="databaseFacade">数据库对象</param>
/// <param name="sql">执行的SQL语句</param>
/// <param name="args">sql的参数</param>
/// <returns>结果集的第一列集合</returns>
public static List <T> FillScalarList <T>(this DatabaseFacade databaseFacade, string sql, object args = null)
{
var concurrencyDetector = databaseFacade.GetService <IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var infrastructure = databaseFacade as IInfrastructure <IServiceProvider>;
var rawSqlCommand = infrastructure.GetService <IRawSqlCommandBuilder>()
.Build(sql, DbParameterHelper.DynamicParamToDBParams(databaseFacade, args));
using (var reader = rawSqlCommand
.RelationalCommand
.ExecuteReader(
infrastructure.GetService <IRelationalConnection>(),
rawSqlCommand.ParameterValues))
{
List <T> list = new List <T>();
using (reader.DbDataReader)
{
while (reader.DbDataReader.Read())
{
list.Add(ConvertScalar <T>(reader.DbDataReader[0]));
}
return(list);
}
}
}
}
public async Task <User> GetByEmailAsync(string email)
{
var userRecord = await _databaseRepository.GetAsync <UserRecord>(Functions.UserRepository.GetByEmail,
DbParameterHelper.Create(nameof(email), email));
return(_mapper.Map <User>(userRecord));
}
public async Task <User> GetByIdAsync(int id)
{
var userRecord = await _databaseRepository.GetAsync <UserRecord>(Functions.UserRepository.GetById,
DbParameterHelper.Create(nameof(id), id));
return(_mapper.Map <User>(userRecord));
}
public Task <int> CreateAsync(Poll poll)
{
return(_databaseRepository.GetAsync <int>(Functions.PollRepository.Create,
DbParameterHelper.Create(nameof(poll.Title), poll.Title),
DbParameterHelper.Create(nameof(poll.Status), poll.Status),
DbParameterHelper.Create(nameof(poll.Type), poll.Type),
DbParameterHelper.CreateJsonb(nameof(poll.Options), poll.Options)));
}
public Task <int> AddAsync(User user)
{
return(_databaseRepository.GetAsync <int>(Functions.UserRepository.Add,
DbParameterHelper.Create(nameof(user.Email), user.Email),
DbParameterHelper.Create(nameof(user.PasswordHash), user.PasswordHash),
DbParameterHelper.Create(nameof(user.FirstName), user.FirstName),
DbParameterHelper.Create(nameof(user.LastName), user.LastName),
DbParameterHelper.Create("UserRoleId", user.Role.Id)));
}
/// <summary>
/// 执行命令,返回第一行,第一列的值,并将结果转换为T类型
/// </summary>
/// <typeparam name="T">返回值类型</typeparam>
/// <param name="databaseFacade">数据库对象</param>
/// <param name="sql">执行的SQL语句</param>
/// <param name="args">sql的参数</param>
/// <returns>结果集的第一行,第一列</returns>
public static T ExecuteScalar <T>(this DatabaseFacade databaseFacade, string sql, object args = null)
{
var concurrencyDetector = databaseFacade.GetService <IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var infrastructure = databaseFacade as IInfrastructure <IServiceProvider>;
var rawSqlCommand = infrastructure.GetService <IRawSqlCommandBuilder>()
.Build(sql, DbParameterHelper.DynamicParamToDBParams(databaseFacade, args));
var result = rawSqlCommand
.RelationalCommand
.ExecuteScalar(
infrastructure.GetService <IRelationalConnection>(),
rawSqlCommand.ParameterValues);
return(ConvertScalar <T>(result));
}
}
/// <summary>
/// 执行命令,将结果集第一行转换为实体
/// </summary>
/// <typeparam name="T">数据类型</typeparam>
/// <param name="databaseFacade">数据库对象</param>
/// <param name="sql">执行的SQL语句</param>
/// <param name="args">sql的参数</param>
/// <returns></returns>
public static T ToSingle <T>(this DatabaseFacade databaseFacade, string sql, object args = null) where T : class
{
var concurrencyDetector = databaseFacade.GetService <IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var infrastructure = databaseFacade as IInfrastructure <IServiceProvider>;
var rawSqlCommand = infrastructure.GetService <IRawSqlCommandBuilder>()
.Build(sql, DbParameterHelper.DynamicParamToDBParams(databaseFacade, args));
using (var reader = rawSqlCommand
.RelationalCommand
.ExecuteReader(
infrastructure.GetService <IRelationalConnection>(),
rawSqlCommand.ParameterValues))
{
using (reader.DbDataReader)
{
return(ToSingle <T>(reader.DbDataReader));
}
}
}
}
/// <summary>
/// 执行命令,并返回影响函数
/// </summary>
/// <typeparam name="T">数据类型</typeparam>
/// <param name="databaseFacade">数据库对象</param>
/// <param name="sql">执行的SQL语句</param>
/// <param name="args">sql的参数</param>
/// <returns>影响行数</returns>
public static int ExecuteNonQuery(this DatabaseFacade databaseFacade, string sql, object args = null)
{
return(databaseFacade.ExecuteSqlCommand(sql, DbParameterHelper.DynamicParamToDBParams(databaseFacade, args)));
}
public Task <int> AddAnswerAsync(PollAnswer pollAnswer)
{
return(_databaseRepository.GetAsync <int>(Functions.PollAnswerRepository.Add,
DbParameterHelper.Create(nameof(pollAnswer.PollOptionId), pollAnswer.PollOptionId),
DbParameterHelper.Create(nameof(pollAnswer.UserId), pollAnswer.UserId)));
}
public async Task <ICollection <PollAnswer> > GetByPollIdAsync(int pollId)
{
return((await _databaseRepository.GetCollectionAsync <PollAnswer>(Functions.PollAnswerRepository.GetByPollId,
DbParameterHelper.Create(nameof(pollId), pollId)))
.ToList());
}
public Task <PollAnswer> GetByIdAsync(int id)
{
return(_databaseRepository.GetAsync <PollAnswer>(Functions.PollAnswerRepository.GetById,
DbParameterHelper.Create(nameof(id), id)));
}
