示例#1
0
        private PublicationDateCondition GetPublicationDateCondition()
        {
            if (comboBoxPublicationDate.SelectedItem == null)
            {
                return(null);
            }
            PublicationDateCondition pdc = new PublicationDateCondition
            {
                Condition = comboBoxPublicationDate.SelectedItem.ToString(),
                Month     = comboBoxMonth.SelectedIndex + 1,
                Year      = Convert.ToInt32(numericUpDownYear.Value.ToString())
            };

            return(pdc);
        }
示例#2
0
        private void UpdateDataGridViewSearchResult()
        {
            dataGridViewSearchResult.DataSource = null;
            dataGridViewSearchResult.Rows.Clear();
            dataGridViewSearchResult.Columns.Clear();
            dataGridViewSearchResult.AutoGenerateColumns = false;
            AddColumnsToDataGridViewSearchResult();
            string     title             = textBoxTitle.Text.Trim();
            int        authorID          = GetSelectedAuthorID();
            int        publisherID       = GetSelectedPublisherID();
            List <int> categoriesID      = GetSelectedCategoriesID();
            PublicationDateCondition pdc = GetPublicationDateCondition();
            List <Book> books            = BookDAO.SearchBooks(title, authorID, publisherID, categoriesID, pdc);

            dataGridViewSearchResult.DataSource = books;
        }
示例#3
0
        private static string AddPublicationDateConditionToSearchBookQuery(string sql, PublicationDateCondition pdc)
        {
            if (pdc == null)
            {
                return(sql);
            }
            string condition       = pdc.Condition;
            string firstDayOfMonth = "'" + pdc.Year + "-" + pdc.Month + "-01'";

            if (condition.Equals("During"))
            {
                string lastDayOfMonth = "EOMONTH(" + firstDayOfMonth + ")";
                sql += "and [PublicationDate] between " + firstDayOfMonth + " and " + lastDayOfMonth;
            }
            else if (condition.Equals("Before"))
            {
                sql += "and [PublicationDate] < " + firstDayOfMonth;
            }
            else if (condition.Equals("After"))
            {
                sql += "and [PublicationDate] >= " + firstDayOfMonth;
            }
            return(sql += "\n");
        }
示例#4
0
        public static List <Book> SearchBooks(string title, int authorID, int publisherID, List <int> categoriesID, PublicationDateCondition pdc)
        {
            List <Book> books = new List <Book>(0);
            string      sql   = "select [BookID],[Title],\n" +
                                "([FirstName] + ' ' + [LastName]) AS [AuthorName],\n" +
                                "[PublisherName],[PublicationDate],[UnitPrice],[UnitsInStock] from [Books]\n" +
                                "inner join [Authors] on [Books].[AuthorID]=[Authors].[AuthorID]\n" +
                                "inner join [Publishers] on [Books].[PublisherID]=[Publishers].[PublisherID]\n" +
                                "where [Books].[Title] like '%' + @title + '%'\n";

            sql = AddAuthorConditionToSearchBookQuery(sql, authorID);
            sql = AddPublisherConditionToSearchBookQuery(sql, publisherID);
            sql = AddCategoryConditionToSearchBookQuery(sql, categoriesID);
            sql = AddPublicationDateConditionToSearchBookQuery(sql, pdc);
            SqlParameter param1 = new SqlParameter("@title", SqlDbType.NVarChar)
            {
                Value = title
            };
            SqlParameter param2 = new SqlParameter("@authorID", SqlDbType.Int)
            {
                Value = authorID
            };
            SqlParameter param3 = new SqlParameter("@publisherID", SqlDbType.Int)
            {
                Value = publisherID
            };
            SqlParameter param4 = new SqlParameter("@numCategory", SqlDbType.Int)
            {
                Value = categoriesID.Count
            };
            DataTable dataTable = ExecuteReader(sql, param1, param2, param3, param4);

            foreach (DataRow dataRow in dataTable.Rows)
            {
                Book book = new Book()
                {
                    BookID          = Convert.ToInt32(dataRow["BookID"].ToString()),
                    Title           = dataRow["Title"].ToString(),
                    AuthorName      = dataRow["AuthorName"].ToString(),
                    PublisherName   = dataRow["PublisherName"].ToString(),
                    PublicationDate = Convert.ToDateTime(dataRow["PublicationDate"].ToString()),
                    UnitPrice       = Convert.ToDecimal(dataRow["UnitPrice"].ToString()),
                    UnitsInStock    = Convert.ToInt32(dataRow["UnitsInStock"].ToString())
                };
                books.Add(book);
            }
            return(books);
        }