//получение одной цитаты
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();
}