public void RemoveUserMessage(UserMessage userMessage)
{
try
{
_currentUserMessages.Remove(_currentUserMessages.Find(s => s.ID.Equals(userMessage.ID)));
}
catch (Exception ex)
{
throw new FaultException(ex.Message);
}
}
public void SubmitUserMessage(UserMessage post)
{
try
{
MessageCounter++;
post.ID = MessageCounter;
_currentUserMessages.Add(post);
_MessagesBeingSavedToDatabase.Add(post);
}
catch (Exception ex)
{
throw new FaultException(ex.Message);
}
}
public List<UserMessage> GetChatFromDatabase(int roomID)
{
List<UserMessage> _databaseUserMessages = new List<UserMessage>();
if (_currentUserMessages.Count < 20)
{
var messageCountToRetrieve = 20 - _currentUserMessages.Count;
var date = new DateTime();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ChatDatabase"].ConnectionString))
{
try
{
connection.Open();
#region query
SqlCommand cmd = new SqlCommand(@"SELECT TOP (@amount) [MessageID]
,[Message]
,[Posted]
,[Room_ID]
,[User_ID]
,[Username]
FROM [dbo].[UserMessages]
INNER JOIN [dbo].[Users]
ON [dbo].[UserMessages].[User_ID] = [dbo].[Users].[UserID]
WHERE [dbo].[UserMessages].Room_ID = @ID
ORDER by MessageID desc", connection);
cmd.Parameters.Add(new SqlParameter("@ID", roomID));
cmd.Parameters.Add(new SqlParameter("@amount", messageCountToRetrieve));
#endregion
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var chat = new UserMessage();
DateTime.TryParse((string)reader["Posted"], out date);
chat.ID = (int)reader["MessageID"];
chat.Submitter = (string)reader["Username"];
chat.Message = (string)reader["Message"];
chat.TimeStamp = date;
_databaseUserMessages.Add(chat);
}
_databaseUserMessages.Reverse();
}
#region CounterQuery
SqlCommand msgCounterCmd = new SqlCommand("SELECT * FROM [ChatDatabase].[dbo].[UserMessages]", connection);
using (SqlDataReader reader = msgCounterCmd.ExecuteReader())
{
while (reader.Read())
{
MessageCounter++;
}
}
#endregion
}
catch (SqlException ex)
{
throw new FaultException($"SQL error: {ex.Message}");
}
catch (Exception ex)
{
throw new FaultException(ex.Message);
}
}
}
else
{
//Limits the amount of messages a new client receives, if the current session includes more then 20 messages there is no need to query database.
return _currentUserMessages.Skip(_currentUserMessages.Count - 20).Take(20).ToList();
}
_currentUserMessages.Reverse();
foreach (var message in _databaseUserMessages)
{
_currentUserMessages.Add(message);
}
_currentUserMessages.Reverse();
return _currentUserMessages;
}
