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);
}
}
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);
}
}