/// <summary>
/// Создать новую тему
/// </summary>
/// <param name="topic"></param>
public bool CreateTopic(Topics topic)
{
using (var connection = new SqlConnection(ConnectionString()))
{
var command = new SqlCommand(
"INSERT INTO dbo.Topics (SectionID, TopicName, TopicText," +
"UserID, CreateDate) VALUES (@SectionID, @TopicName, " +
"@TopicText, @UserID, @Date)", connection);
command.Parameters.AddWithValue("@SectionID", topic.SectionID);
command.Parameters.AddWithValue("@TopicName", topic.TopicName);
command.Parameters.AddWithValue("@TopicText", topic.TopicText);
command.Parameters.AddWithValue("@UserID", topic.UserID);
command.Parameters.AddWithValue("@Date", topic.CreateDate);
connection.Open();
return command.ExecuteNonQuery() == 1;
}
}
/// <summary>
/// Получение тем в разделе, для администратора
/// </summary>
/// <param name="SectionID"></param>
public IEnumerable<Topics> GetTopicsForAdmin(int SectionID)
{
if (CheckSectionID(SectionID) == false)
throw new ArgumentException("Такого раздела не существует.");
var topics = new List<Topics>();
using (var connection = new SqlConnection(ConnectionString()))
{
var command = new SqlCommand(
"SELECT t.SectionID, t.TopicID, t.TopicName, " +
"t.CreateDate, u.Name, (SELECT COUNT(MessageID)" +
" FROM dbo.Messages WHERE TopicID = t.TopicID) " +
"as MessageCount FROM dbo.Topics as t " +
"INNER JOIN dbo.Users as u ON u.UserID = t.UserID" +
" WHERE t.SectionID = @SectionID", connection);
command.Parameters.AddWithValue("@SectionID", SectionID);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var topic = new Topics();
topic.SectionID = Convert.ToInt32(reader.GetValue(0));
topic.TopicID = Convert.ToInt32(reader.GetValue(1));
topic.TopicName = Convert.ToString(reader.GetValue(2));
topic.CreateDate = Convert.ToDateTime(reader.GetValue(3));
topic.Name = Convert.ToString(reader.GetValue(4));
topic.MessageCount = Convert.ToInt32(reader.GetValue(5));
topics.Add(topic);
}
return topics;
}
}
/// <summary>
/// Получение темы по идентификатору
/// </summary>
/// <param name="TopicID"></param>
public Topics GetTopicByID(int TopicID)
{
if (CheckTopicID(TopicID) == false)
throw new ArgumentException("Такой темы не существует.");
using (var connection = new SqlConnection(ConnectionString()))
{
var command = new SqlCommand(
"SELECT t.SectionID, t.TopicID, t.TopicName, t.TopicText, " +
"t.CreateDate, u.Name, u.Avatar FROM dbo.Topics as t " +
"INNER JOIN dbo.Users as u ON u.UserID = t.UserID" +
" WHERE t.TopicID = @TopicID", connection);
command.Parameters.AddWithValue("@TopicID", TopicID);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
var topic = new Topics();
while (reader.Read())
{
topic.SectionID = Convert.ToInt32(reader.GetValue(0));
topic.TopicID = Convert.ToInt32(reader.GetValue(1));
topic.TopicName = Convert.ToString(reader.GetValue(2));
topic.TopicText = Convert.ToString(reader.GetValue(3));
topic.CreateDate = Convert.ToDateTime(reader.GetValue(4));
topic.Name = Convert.ToString(reader.GetValue(5));
topic.Avatar = Convert.ToString(reader.GetValue(6));
}
return topic;
}
}
/// <summary>
/// Создать тему
/// </summary>
/// <param name="topic"></param>
public bool CreateTopic(TopicsDTO topic)
{
try
{
Topics TopicDAL = new Topics();
TopicDAL.SectionID = topic.SectionID;
TopicDAL.TopicName = topic.TopicName;
TopicDAL.TopicText = topic.TopicText;
TopicDAL.UserID = topic.UserID;
TopicDAL.CreateDate = DateTime.Now;
if (Data.CreateTopic(TopicDAL) == true)
return true;
else throw new ValidationException("Ваш запрос не был обработан", "");
}
catch (ArgumentException ex)
{
throw new ValidationException(ex.Message, ex.ParamName);
}
}