Пример #1
0
        //получение одной цитаты
        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);
        }
Пример #2
0
        //удаление
        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();
        }
Пример #3
0
        //чтение всех цитат из БД
        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);
        }
Пример #4
0
        //поиск по автору
        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);
        }
Пример #5
0
        //получение все категорий
        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);
        }
Пример #6
0
        //получение 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);
        }
Пример #7
0
        //создание цитаты
        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();
        }
Пример #8
0
        //получение одной кактегории
        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);
        }
Пример #9
0
        //изменение цитаты
        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();
        }