public ActionResult <IEnumerable <Book> > GetListOfBooks()
{
#region XML Converter
//XmlSerializer xsSubmit = new XmlSerializer(typeof(Person));
//var xml = "";
//using (var sww = new StringWriter())
//{
// using (XmlWriter writer = XmlWriter.Create(sww))
// {
// xsSubmit.Serialize(writer, person);
// xml = sww.ToString(); // Your XML
// }
//}
//return xml;
#endregion
//By manual
//var books = new List<Book>();
var books = new List <Book>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
//SqlCommand command = new SqlCommand("select * from Books", connection);
SqlCommand command = new SqlCommand(@"select Books.*, Authors.FullName as AuthorName from Books
join BookAuthors on Books.ID = BookAuthors.BooksID
join Authors on Authors.ID = BookAuthors.AuthorsID", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
#region By manual
//Book book = new Book();
//book.ID = Guid.Parse(reader["ID"].ToString());
//book.BookName = reader["BookName"].ToString();
//book.Genre = reader["Genre"].ToString();
//book.ReleaseDate = Convert.ToDateTime(reader["ReleaseDate"]);
//books.Add(book);
#endregion
books.Add(Helper.ADONetToClass <Book>(reader));
}
}
return(Ok(books));
}
public ActionResult <IEnumerable <Book> > GetBookByAuthorName(string authorName)
{
Author author = new Author();
string authorId = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand("select * from Authors where FullName = @FullName", connection);
command.Parameters.AddWithValue("@FullName", authorName);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
author = Helper.ADONetToClass <Author>(reader);
authorId = author.ID.ToString();
}
}
List <Book> books = new List <Book>();
Book book = null;
if (authorId != null)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand(@"select Books.*, Authors.FullName as AuthorName from Books
join BookAuthors on Books.ID = BookAuthors.BooksID
join Authors on Authors.ID = BookAuthors.AuthorsID
where BookAuthors.AuthorsID = @AuthorId", connection);
command.Parameters.AddWithValue("@AuthorId", authorId);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
book = Helper.ADONetToClass <Book>(reader);
books.Add(book);
}
}
}
else
{
return(NotFound());
}
return(Ok(books));
}
public ActionResult <IEnumerable <Author> > GetListOfAuthors()
{
var authors = new List <Author>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand("select * from Authors", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
authors.Add(Helper.ADONetToClass <Author>(reader));
}
}
return(Ok(authors));
}
public ActionResult <Author> GetAuthor(string id)
{
Author author = new Author();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand("select * from Authors where ID = @id", connection);
command.Parameters.AddWithValue("@id", id);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
author = Helper.ADONetToClass <Author>(reader);
}
}
return(Ok(author));
}
public ActionResult <IEnumerable <Book> > GetBook(string id, string authorName)
{
string authorsId = "";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand(@"select * from Authors where FullName = @FullName", conn);
command.Parameters.AddWithValue("@FullName", authorName);
conn.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
authorsId = reader["ID"].ToString();
}
}
//List<Book> books = new List<Book>();
Book book = new Book();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
//SqlCommand command = new SqlCommand("select * from Books where ID = @id", connection);
SqlCommand command = new SqlCommand(@"select Books.*, Authors.FullName as AuthorName from Books
join BookAuthors on Books.ID = BookAuthors.BooksID
join Authors on Authors.ID = BookAuthors.AuthorsID
where BookAuthors.BooksID = @BooksId and BookAuthors.AuthorsID = @AuthorsId", connection);
//command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@BooksId", id);
command.Parameters.AddWithValue("@AuthorsId", authorsId);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
book = Helper.ADONetToClass <Book>(reader);
//books.Add(book);
}
}
return(Ok(book));
}