public Entities.PostText GetPostById(Guid Id)
{
StringBuilder s = new StringBuilder();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("SELECT Post_Id, Post_Title, Post_Text, User_Name, Time FROM dbo.Posts WHERE Post_Id = @Id", con);
command.Parameters.Add(new SqlParameter("@Id", Id));
con.Open();
Entities.PostText post = new Entities.PostText()
{
Id = Id
};
int count = 0;
var reader = command.ExecuteReader();
string post_id = "";
while (reader.Read())
{
post_id = (string)reader["Post_Id"];
post.Title = (string)reader["Post_Title"];
post.Text = (string)reader["Post_Text"];
post.Author = (string)reader["User_Name"];
post.Time = (DateTime)reader["Time"];
count++;
}
if (count < 0)
{
return(post = null);
}
else
{
reader.Close();
using (SqlConnection con2 = new SqlConnection(ConnectionString))
{
SqlCommand command2 = new SqlCommand("SELECT Tag FROM dbo.Tags WHERE Post_Id = @Id", con);
command2.Parameters.Add(new SqlParameter("@Id", post_id));
con2.Open();
var reader2 = command2.ExecuteReader();
while (reader2.Read())
{
s.Append((string)reader2["Tag"]);
s.Append(" ");
}
}
post.Tags = s.ToString();
return(post);
}
}
}
public bool EditPostById(Entities.PostText post)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("UPDATE dbo.Posts SET Post_Title = @Title, Post_Text = @Text, Time = @Time WHERE Post_Id = @Id", con);
command.Parameters.Add(new SqlParameter("@Id", post.Id));
command.Parameters.Add(new SqlParameter("@Text", post.Text));
command.Parameters.Add(new SqlParameter("@Title", post.Title));
command.Parameters.Add(new SqlParameter("@Time", post.Time));
con.Open();
int count = command.ExecuteNonQuery();
if (count > 0)
{
using (SqlConnection con2 = new SqlConnection(ConnectionString))
{
SqlCommand command2 = new SqlCommand("DELETE FROM dbo.Tags WHERE Post_Id = @ID", con);
command2.Parameters.Add(new SqlParameter("@ID", post.Id));
con2.Open();
command2.ExecuteNonQuery();
con2.Close();
}
string[] split = post.Tags.Trim().Split(' ');
for (int i = 0; i < split.Length; i++)
{
using (SqlConnection con2 = new SqlConnection(ConnectionString))
{
SqlCommand command2 = new SqlCommand("INSERT INTO dbo.Tags (Post_Id, Tag) VALUES (CAST(@ID AS NVARCHAR(36)), @Tag)", con);
command2.Parameters.Add(new SqlParameter("@ID", post.Id));
command2.Parameters.Add(new SqlParameter("@Tag", split[i]));
con2.Open();
command2.ExecuteNonQuery();
con2.Close();
}
}
logger.Info("DB: Изменен пост: " + post.Id + " пользователя: " + post.Author);
return(true);
}
else
{
logger.Error("DB: Ошибка изменения поста: " + post.Id + " пользователя: " + post.Author);
return(false);
}
}
}
public bool AddPost(Entities.PostText post, string login)
{
string[] split = post.Tags.Trim().Split(' ');
for (int i = 0; i < split.Length; i++)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("INSERT INTO dbo.Tags (Post_Id, Tag) VALUES (CAST(@ID AS NVARCHAR(36)), @Tag)", con);
command.Parameters.Add(new SqlParameter("@ID", post.Id));
command.Parameters.Add(new SqlParameter("@Tag", split[i]));
con.Open();
command.ExecuteNonQuery();
con.Close();
}
}
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("INSERT INTO dbo.Posts (User_Name, Post_Id, Post_Title, Post_Text, Time) VALUES (@Login,CAST(@ID AS NVARCHAR(36)), @Title, @Text, @Time)", con);
command.Parameters.Add(new SqlParameter("@Login", login));
command.Parameters.Add(new SqlParameter("@ID", post.Id));
command.Parameters.Add(new SqlParameter("@Title", post.Title));
command.Parameters.Add(new SqlParameter("@Text", post.Text));
command.Parameters.Add(new SqlParameter("@Time", post.Time));
con.Open();
int num = command.ExecuteNonQuery();
if (num == 1)
{
logger.Info("DB: Добавлен пост: " + post.Id + " пользователя: " + post.Author);
return(true);
}
else
{
logger.Error("DB: Ошибка добавления поста: " + post.Id + " пользователя: " + post.Author);
return(false);
}
}
}
