コード例 #1
0
ファイル: Controller.cs プロジェクト: anderson-81/webbook
        public List <AuthorBook> GetBookByTitle()
        {
            try
            {
                this.cmd            = new SqlCommand();
                this.cmd.Connection = this.conn;
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT * FROM BOOK INNER JOIN AUTHOR ON BOOK.AUTHOR_ID = AUTHOR.ID ");
                sql.Append("INNER JOIN PERSON ON AUTHOR.PERSON_ID = PERSON.ID  ");
                sql.Append("WHERE BOOK.TITLE LIKE @title + '%'; ");
                this.cmd.CommandText = sql.ToString();
                this.cmd.Parameters.Add("title", System.Data.SqlDbType.NVarChar);
                this.cmd.Parameters["title"].Value = book.Title;
                SqlDataReader sqr = this.cmd.ExecuteReader();
                if (sqr.HasRows)
                {
                    List <AuthorBook> authorBooks = new List <AuthorBook>();
                    while (sqr.Read())
                    {
                        Author _author = new Author();
                        _author.Id           = sqr.GetInt32(6);
                        _author.ArtisticName = sqr.GetString(8);
                        _author.Biography    = sqr.GetString(9);
                        _author.Name         = sqr.GetString(11);
                        _author.Email        = sqr.GetString(12);
                        _author.Birthday     = sqr.GetDateTime(13);
                        _author.Gender       = sqr.GetString(14)[0];

                        Book _book = new Book();
                        _book.Id    = sqr.GetInt32(0);
                        _book.Title = sqr.GetString(2);
                        _book.Isbn  = sqr.GetString(3);
                        _book.Page  = sqr.GetInt32(4);
                        _book.Price = sqr.GetDecimal(5);

                        AuthorBook _authorBook = new AuthorBook();
                        _authorBook.Author = _author;
                        _authorBook.Book   = _book;

                        authorBooks.Add(_authorBook);
                        _author     = null;
                        _book       = null;
                        _authorBook = null;
                    }
                    return(authorBooks);
                }
                return(null);
            }
            catch (Exception)
            {
                return(null);
            }
        }
コード例 #2
0
ファイル: Controller.cs プロジェクト: anderson-81/webbook
        public AuthorBook GetBookByISBN()
        {
            try
            {
                this.cmd            = new SqlCommand();
                this.cmd.Connection = this.conn;
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT * FROM BOOK INNER JOIN AUTHOR ON BOOK.AUTHOR_ID = AUTHOR.ID ");
                sql.Append("INNER JOIN PERSON ON AUTHOR.PERSON_ID = PERSON.ID  ");
                sql.Append("WHERE ISBN = @isbn  ");
                this.cmd.CommandText = sql.ToString();
                this.cmd.Parameters.Add("isbn", System.Data.SqlDbType.NVarChar);
                this.cmd.Parameters["isbn"].Value = book.Isbn;
                SqlDataReader sqr = this.cmd.ExecuteReader();
                if (sqr.HasRows)
                {
                    AuthorBook authorBook = new AuthorBook();
                    sqr.Read();
                    Book _book = new Book();
                    _book.Id      = sqr.GetInt32(0);
                    _book.Title   = sqr.GetString(2);
                    _book.Isbn    = sqr.GetString(3);
                    _book.Page    = sqr.GetInt32(4);
                    _book.Price   = sqr.GetDecimal(5);
                    _book.Picture = (byte[])sqr[6];

                    Author _author = new Author();
                    _author.Id           = sqr.GetInt32(7);
                    _author.ArtisticName = sqr.GetString(9);
                    _author.Biography    = sqr.GetString(10);
                    _author.Name         = sqr.GetString(12);
                    _author.Email        = sqr.GetString(13);
                    _author.Birthday     = sqr.GetDateTime(14);
                    _author.Gender       = sqr.GetString(15)[0];
                    _author.Picture      = (byte[])sqr[16];

                    AuthorBook _authorBook = new AuthorBook();
                    _authorBook.Author = _author;
                    _authorBook.Book   = _book;

                    return(_authorBook);
                }
                return(null);
            }
            catch (Exception)
            {
                return(null);
            }
        }