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