//получение одной цитаты 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 void Delete(int id) { String query = "DELETE FROM Citation WHERE Id = @id"; List <SqlParameter> parameters = new List <SqlParameter>() { new SqlParameter("Id", id) }; CitationsDB.Execute(query, parameters); CitationsDB.CloseConnection(); }
//чтение всех цитат из БД 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 List <Category> Read() { String query = "SELECT * FROM Category"; List <Category> categories = new List <Category>(); SqlDataReader reader = CitationsDB.Get(query); if (reader != null && reader.HasRows) { //чтение категорий из БД по записям while (reader.Read()) { Category category = new Category(reader); categories.Add(category); } } CitationsDB.CloseConnection(); return(categories); }
//получение id категории по названию public static int GetIdByName(string name) { String query = "SELECT Id FROM Category WHERE Name = @name"; SqlDataReader reader = CitationsDB.Get(query, new List <SqlParameter>() { new SqlParameter("name", name) }); int id = 0; if (reader != null && reader.HasRows) { while (reader.Read()) { id = (int)reader["Id"]; } } CitationsDB.CloseConnection(); return(id); }
//создание цитаты 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 Category GetCategory(int id) { String query = "SELECT Name FROM Category WHERE Id = @id"; SqlDataReader reader = CitationsDB.Get(query, new List <SqlParameter>() { new SqlParameter("id", id) }); Category category = new Category(); if (reader != null && reader.HasRows) { while (reader.Read()) { category.Id = id; category.Name = (String)reader["Name"]; } } CitationsDB.CloseConnection(); return(category); }
//изменение цитаты 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(); }