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