//получение одной цитаты
public static Citation GetCitation(int id)
{
String query = "SELECT * FROM Citation WHERE Id = @id";
SqlDataReader reader = CitationsDB.Get(query, new List <SqlParameter>()
{
new SqlParameter("id", id)
});
Citation citation = new Citation();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
citation.Id = (int)reader["Id"];
citation.Text = (String)reader["Text"];
citation.Author = (String)reader["Author"];
citation.Date = (DateTime)reader["Date"];
if (reader["IdCategory"] != DBNull.Value)
{
citation.Category = Category.GetCategory((int)reader["IdCategory"]);
}
}
}
CitationsDB.CloseConnection();
return(citation);
}
//чтение всех цитат из БД
public static List <Citation> Read()
{
String query = "SELECT * FROM Citation";
List <Citation> citations = new List <Citation>();
SqlDataReader reader = CitationsDB.Get(query);
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
Citation citation = new Citation(reader);
citations.Add(citation);
}
}
return(citations);
}
//поиск по автору
public static List <Citation> SearchAuthor(String author)
{
String query = @"SELECT * FROM Citation WHERE Author LIKE N'%" + author + "%'";
SqlDataReader reader = CitationsDB.Get(query);
List <Citation> data = new List <Citation>();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
Citation citation = new Citation(reader);
data.Add(citation);
}
}
CitationsDB.CloseConnection();
return(data);
}
//создание цитаты
public static void Create(Citation citation)
{
String query = "INSERT INTO Citation(Text, Author, IdCategory, Date) VALUES(@text, @author, @idCategory, @date)";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("text", citation.Text),
new SqlParameter("author", citation.Author),
new SqlParameter("date", citation.Date)
};
if (citation.Category.Id == 0)
{
parameters.Add(new SqlParameter("idCategory", DBNull.Value));
}
else
{
parameters.Add(new SqlParameter("idCategory", citation.Category.Id));
}
CitationsDB.Execute(query, parameters);
CitationsDB.CloseConnection();
}
//изменение цитаты
public static void Update(Citation citation)
{
String query = "UPDATE Citation SET Text = @text, Author = @author, IdCategory = @idCategory, Date = @date WHERE Id = @id";
List <SqlParameter> parameters = new List <SqlParameter>()
{
new SqlParameter("id", citation.Id),
new SqlParameter("text", citation.Text),
new SqlParameter("author", citation.Author),
new SqlParameter("date", citation.Date)
};
if (citation.Category.Id == 0)
{
parameters.Add(new SqlParameter("idCategory", DBNull.Value));
}
else
{
parameters.Add(new SqlParameter("idCategory", citation.Category.Id));
}
CitationsDB.Execute(query, parameters);
CitationsDB.CloseConnection();
}