/// <summary>
/// Создать новое сообщение
/// </summary>
/// <param name="message"></param>
public bool CreateMessage(Messages message)
{
using(var connection = new SqlConnection(ConnectionString()))
{
var command = new SqlCommand();
if (message.StatusID == 0)
{
command = new SqlCommand(
"INSERT INTO dbo.Messages (TopicID, Text, SendDate,UserID) " +
"VALUES (@TopicID, @Text, @SendDate, @UserID)", connection);
command.Parameters.AddWithValue("@TopicID", message.TopicID);
command.Parameters.AddWithValue("@Text", message.Text);
command.Parameters.AddWithValue("@SendDate", message.SendDate);
command.Parameters.AddWithValue("@UserID", message.UserID);
}
else
{
command = new SqlCommand(
"INSERT INTO dbo.Messages (TopicID, Text, SendDate,UserID,StatusID) " +
"VALUES (@TopicID, @Text, @SendDate, @UserID, @StatusID)", connection);
command.Parameters.AddWithValue("@TopicID", message.TopicID);
command.Parameters.AddWithValue("@Text", message.Text);
command.Parameters.AddWithValue("@SendDate", message.SendDate);
command.Parameters.AddWithValue("@UserID", message.UserID);
command.Parameters.AddWithValue("@StatusID", message.StatusID);
}
connection.Open();
return command.ExecuteNonQuery() == 1;
}
}
/// <summary>
/// Получение сообщений в теме
/// </summary>
/// <param name="TopicID"></param>
public IEnumerable<Messages> GetMessages(int TopicID)
{
if (CheckTopicID(TopicID) == false)
throw new ArgumentException("Такой темы не существует.");
var messages = new List<Messages>();
using (var connection = new SqlConnection(ConnectionString()))
{
var command = new SqlCommand(
"SELECT m.MessageID, m.TopicID, m.SendDate, m.Text, " +
"m.StatusID, u.Name, u.Avatar FROM dbo.Messages as m " +
" INNER JOIN dbo.Users as u ON m.UserID = u.UserID " +
"WHERE m.TopicID = @TopicID ORDER BY m.SendDate", connection);
command.Parameters.AddWithValue("@TopicID", TopicID);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var message = new Messages();
message.MessageID = Convert.ToInt32(reader.GetValue(0));
message.TopicID = Convert.ToInt32(reader.GetValue(1));
message.SendDate = Convert.ToDateTime(reader.GetValue(2));
message.Text = Convert.ToString(reader.GetValue(3));
message.StatusID = Convert.ToInt32(reader.GetValue(4));
message.Name = Convert.ToString(reader.GetValue(5));
message.Avatar = Convert.ToString(reader.GetValue(6));
messages.Add(message);
}
return messages;
}
}
