public DbQueryResponse <VW_BOOK> GetBooksByAuthorName(string authorName)
{
DbQueryResponse <VW_BOOK> output = new DbQueryResponse <VW_BOOK> {
};
string methodName = MethodBase.GetCurrentMethod().GetName();
var sw = Stopwatch.StartNew();
try
{
string commandText = $"SELECT ID, TITLE, AUTHORS " +
$"FROM VW_BOOKS bks " +
$"WHERE AUTHORS LIKE '%{authorName}%'";
output = this._dbContext.ExecuteQuery(commandText, null, this.ReadBook, $"{methodName}");
return(output);
}
catch (Exception ex)
{
LogEngine.BookLogger.WriteToLog(LogLevels.Error, $"DAL.Exception: {JsonConvert.SerializeObject(ex)}");
return(output = new DbQueryResponse <VW_BOOK> {
});
}
finally
{
sw.Stop();
LogEngine.BookLogger.WriteToLog(LogLevels.Debug, $"DAL.{methodName} => OUTLEN={output.Items.Count} in {sw.ElapsedMilliseconds}ms");
}
}
public DbQueryResponse <VW_BOOK> GetBooksByMemberId(int memberId, List <LikedRating> likedRatings = null)
{
DbQueryResponse <VW_BOOK> output = new DbQueryResponse <VW_BOOK> {
};
string methodName = MethodBase.GetCurrentMethod().GetName();
var sw = Stopwatch.StartNew();
try
{
string commandText = $"SELECT bks.ID as ID, bks.TITLE as TITLE, bks.AUTHORS " +
$"FROM VW_BOOKS bks " +
$"INNER JOIN READINGS rds ON bks.ID = rds.BOOK_ID " +
$"WHERE rds.READER_ID = {memberId}";
if (likedRatings != null && likedRatings.Any())
{
commandText = $"{commandText} AND rds.liked_rating IN ({string.Join(",", likedRatings.Select(c => $"{(int) c}"))})";
}
output = this._dbContext.ExecuteQuery(commandText, null, this.ReadBook, $"{methodName}");
return(output);
}
catch (Exception ex)
{
LogEngine.BookLogger.WriteToLog(LogLevels.Error, $"DAL.Exception: {JsonConvert.SerializeObject(ex)}");
return(output = new DbQueryResponse <VW_BOOK> {
});
}
finally
{
sw.Stop();
LogEngine.BookLogger.WriteToLog(LogLevels.Debug, $"DAL.{methodName} => OUTLEN={output.Items.Count} in {sw.ElapsedMilliseconds}ms");
}
}
public DbQueryResponse <MEMBER> GetFriendsByMemberId(int memberId)
{
DbQueryResponse <MEMBER> output = new DbQueryResponse <MEMBER> {
};
string methodName = MethodBase.GetCurrentMethod().GetName();
var sw = Stopwatch.StartNew();
try
{
string commandText = $"SELECT mbs.ID as ID, mbs.NAME as NAME " +
$"FROM MEMBERS mbs " +
$"INNER JOIN ARE_FRIENDS fds ON mbs.ID = fds.requester_id " +
$"WHERE fds.addressed_id = {memberId}";
output = this._dbContext.ExecuteQuery(commandText, null, this.ReadMember, $"{methodName}");
return(output);
}
catch (Exception ex)
{
LogEngine.BookLogger.WriteToLog(LogLevels.Error, $"DAL.Exception: {JsonConvert.SerializeObject(ex)}");
return(output = new DbQueryResponse <MEMBER> {
});
}
finally
{
sw.Stop();
LogEngine.BookLogger.WriteToLog(LogLevels.Debug, $"DAL.{methodName} => OUTLEN={output.Items.Count} in {sw.ElapsedMilliseconds}ms");
}
}
public DbQueryResponse <MEMBER> GetAllMembers()
{
DbQueryResponse <MEMBER> output = new DbQueryResponse <MEMBER> {
};
string methodName = MethodBase.GetCurrentMethod().GetName();
var sw = Stopwatch.StartNew();
try
{
string commandText = $"SELECT ID, NAME FROM MEMBERS";
output = this._dbContext.ExecuteQuery(commandText, null, this.ReadMember, $"{methodName}");
return(output);
}
catch (Exception ex)
{
LogEngine.MemberLogger.WriteToLog(LogLevels.Error, $"DAL.Exception: {JsonConvert.SerializeObject(ex)}");
return(output = new DbQueryResponse <MEMBER> {
});
}
finally
{
sw.Stop();
LogEngine.MemberLogger.WriteToLog(LogLevels.Debug, $"DAL.{methodName} => OUTLEN={output.Items.Count} in {sw.ElapsedMilliseconds}ms");
}
}
public DbQueryResponse <T> ExecuteQuery <T>(string commandText, List <NpgsqlParameter> parameters, Func <DbDataReader, T> readRowFunc, string inputLogMessage)
{
DbQueryResponse <T> output = new DbQueryResponse <T>();
var sw = Stopwatch.StartNew();
try
{
if (string.IsNullOrEmpty(commandText))
{
return(output);
}
this.Init();
using (NpgsqlCommand command = new NpgsqlCommand(commandText, this._connection))
{
command.CommandType = CommandType.Text;
if (parameters != null && parameters.Any())
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
sw.Stop();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
var dbEntry = readRowFunc(dataReader);
if (dbEntry != null)
{
output.Items.Add(dbEntry);
}
}
}
dataReader.Close();
}
}
output.Success = true;
return(output);
}
catch (Exception ex)
{
LogEngine.Logger.WriteToLog(LogLevels.Error, $"DAL.Exception: {JsonConvert.SerializeObject(ex)}");
return(new DbQueryResponse <T> {
Success = false
});
}
finally
{
var inputLogMessageSplitParts = inputLogMessage.Split(new char[] { '(' }, StringSplitOptions.RemoveEmptyEntries);
if (inputLogMessageSplitParts.Length > 1)
{
LogHelper.WriteMetric($"{inputLogMessageSplitParts[0]}", sw.Elapsed, output.Success);
}
else
{
LogHelper.WriteMetric($"{inputLogMessage}", sw.Elapsed, output.Success);
}
}
}
