//TODO Либо генерировать у пользователя GUID и передавать его, либо генерировать его здесь и возвращать как результат операции
//TODO Соответственно, MessageInChat либо генерируется по запросам клиента, либо "вшить" генерацию внутрь контроллера
//TODO (когда посылаем и создаем сообщениеб тогда же и создаем MsgInChat)
public CMessageInfo CreateMessage(CMessageInfo message)
{
s_log.LogInfo($@"Data provider's method '{nameof(CreateMessage)}({message})' is called");
#region SQL
var sql = @"
INSERT INTO messages (Id, DispatchDate, MessageText, Type, ContentUri)
VALUES (
@Id, @DispatchDate, @MessageText, @Type, @ContentUri
);
SELECT USN
FROM messages
WHERE Id = @Id;
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var outputId = Guid.NewGuid();
var result = executor.ExecuteScalar <Int64>(sql,
SSqlParameterCreator.Create(
"@Id", outputId, SqlDbType.UniqueIdentifier, false, ParameterDirection.Input
),
SSqlParameterCreator.Create(
"@DispatchDate", message.DispatchDate, SqlDbType.DateTimeOffset, false, ParameterDirection.Input
),
SSqlParameterCreator.Create(
"@MessageText", message.MessageText, SqlDbType.NVarChar, true, ParameterDirection.Input, 2038
),
SSqlParameterCreator.Create(
"@Type", message.Type, SqlDbType.TinyInt, false
),
SSqlParameterCreator.Create(
"@ContentUri", message.ContentUri, SqlDbType.NVarChar, true, ParameterDirection.Input, 2038
)
);
executor.Commit();
return(new CMessageInfo(outputId, message.DispatchDate, message.MessageText, message.Type,
message.ContentUri, message.FromUserId, message.IsRead, message.Login, result));
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(CreateMessage)}({message}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(CreateMessage)}({message}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(CreateMessage)}({message}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(null);
}
}
}
}
public Int32 GetUnreadMessagesCount(Guid userId, Guid chatId)
{
s_log.LogInfo($@"Data provider's method '{nameof(GetUnreadMessagesCount)}({userId}, {chatId})' is called");
#region SQL Query
var sqlQuery = @"
SELECT DISTINCT
COUNT(*)
FROM messagesInChats
WHERE ChatId = @ChatId
AND ToUserId = @UserId
AND IsRead = 0
";
#endregion
using (IDbConnection connection = new SqlConnection(_dbSettings.DbConnectionString))
{
using (CDbTransactionQueryExecutor executor = new CDbTransactionQueryExecutor(connection))
{
try
{
var result = executor.ExecuteScalar <Int32>(sqlQuery,
SSqlParameterCreator.Create("@UserId", userId, System.Data.SqlDbType.UniqueIdentifier, false),
SSqlParameterCreator.Create("@ChatId", chatId, System.Data.SqlDbType.UniqueIdentifier, false)
);
executor.Commit();
return(result);
}
catch (SqlException e)
{
s_log.LogError($@"{nameof(GetUnreadMessagesCount)}({userId}, {chatId}): Error occured during SQL query execution", e);
s_log.LogInfo($@"{nameof(GetUnreadMessagesCount)}({userId}, {chatId}): Operation was rolled back because of error");
Console.WriteLine($@"{nameof(GetUnreadMessagesCount)}({userId}, {chatId}): Error occured during SQL query execution");
Console.WriteLine("Operation was rolled back because of error");
return(0);
}
}
}
}