0

I am creating a library database application. I created a custom Book class to hold data from the database. My library class lets me access the data and some methods return Book objects.

The problem is when I need to return more then one Book. I want the method to return a List<book> object. When I do however the return comes out with the entire list being populated by the last Book loaded. I am sure the problem is some where in my LoadBookList() method.

Any help is welcomed. Also any advice on the application is welcomed. Thank you.

// Library.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace SharpLibrary
{
    class Library
    {
        #region Fields
        private Book _book;
        private List<Book> _bookList;
        private string _connectionString;
        private MySqlConnection _connection;
        private MySqlCommand _command;
        #endregion

        #region Propertys
        public MySqlDataReader Reader { get; set; }

        private string _sqlQuery;
        public string SqlQuery
        {
            get 
            { 
                return _sqlQuery; 
            }
            set 
            {
                _command.CommandText = value;
                _sqlQuery = value;
            }
        }

        public bool ConnectionStatus { get; set; }
        #endregion

        #region Methods
        #region Initialization
        public Library()
        {
            _book = new Book();
            _bookList = new List<Book>();
            _connectionString = "SERVER=localhost;DATABASE=library;UID=root;PASSWORD=pass;";
            _connection = new MySqlConnection(_connectionString);
            _command = _connection.CreateCommand();
            SqlQuery = "";
            ConnectionStatus = false;
        }

        public Library(string connectionString)
        {
            _book = new Book();
            _bookList = new List<Book>();
            _connectionString = connectionString;
            _connection = new MySqlConnection(_connectionString);
            _command = _connection.CreateCommand();
            SqlQuery = "";
            ConnectionStatus = false;
        }
        #endregion

        #region Connections
        public void OpenConnection()
        {
            try
            {
                _connection.Open();
                ConnectionStatus = true;
            }
            catch
            {
                ConnectionStatus = false;
                Exception OpenConnectionError = new Exception("Could not open connection to library");
                throw OpenConnectionError;
            }
        }

        public void CloseConnection()
        {
            try
            {
                _connection.Close();
                ConnectionStatus = false;
            }
            catch 
            {
                ConnectionStatus = false;
                Exception CloseConnectionError = new Exception("Could not close connection to library");
                throw CloseConnectionError;
            }
        }
        #endregion

        #region Book Query Methods
        public Book SearchBookById(int id)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE id={0}", id);
            ExecuteQuery(sqlQuery);
            LoadBook();
            return _book;
        }

        public Book SearchBookByIsbn(string isbn)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE isbn={0}", isbn);
            ExecuteQuery(sqlQuery);
            LoadBook();
            return _book;
        }

        public Book SearchBookByTitle(string title)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE title={0}", title);
            ExecuteQuery(sqlQuery);
            LoadBook();
            return _book;
        }

        public List<Book> SearchBookByAuthor(string author)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE author=\"{0}\"", author);
            ExecuteQuery(sqlQuery);
            LoadBookList();
            return _bookList;
        }
        #endregion

        #region Helper Methods 
        private void ExecuteQuery()
        {
            Reader = _command.ExecuteReader();
        }

        private void ExecuteQuery(string sqlQuery)
        {
            SqlQuery = sqlQuery;
            Reader = _command.ExecuteReader();
        }

        private void LoadBook()
        {
            while (Reader.Read())
            {
                _book.Id = (int)Reader.GetValue(0);
                _book.Isbn = Reader.GetValue(1).ToString();
                _book.Title = Reader.GetValue(2).ToString();
                _book.Author = Reader.GetValue(3).ToString();
                _book.Publisher = Reader.GetValue(4).ToString();
                _book.Genre = Reader.GetValue(5).ToString();
                _book.Type = Reader.GetValue(6).ToString();
                _book.Copyright = (int)Reader.GetValue(7);
                _book.Rating = (int)Reader.GetValue(8);
                _book.Description = Reader.GetValue(9).ToString();
                _book.Image_uri = Reader.GetValue(10).ToString();
            }
        }

        private void LoadBookList()
        {
            if (Reader.HasRows)
            {
                while (Reader.Read())
                {
                    _book.Id = Reader.GetInt32(Reader.GetOrdinal("id"));
                    _book.Isbn = Reader.GetString(Reader.GetOrdinal("isbn"));
                    _book.Title = Reader.GetString(Reader.GetOrdinal("title"));
                    _book.Author = Reader.GetString(Reader.GetOrdinal("author"));
                    _book.Publisher = Reader.GetString(Reader.GetOrdinal("publisher"));
                    _book.Genre = Reader.GetString(Reader.GetOrdinal("genre"));
                    _book.Type = Reader.GetString(Reader.GetOrdinal("type"));
                    _book.Copyright = Reader.GetInt32(Reader.GetOrdinal("copyright"));
                    _book.Rating = Reader.GetInt32(Reader.GetOrdinal("rating"));
                    _book.Description = Reader.GetString(Reader.GetOrdinal("description"));
                    _book.Image_uri = Reader.GetString(Reader.GetOrdinal("image_uri"));

                    _bookList.Add(_book);
                }
            }
        }
        #endregion
        #endregion
    }
}
// Book.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SharpLibrary
{
    public class Book
    {
        #region Propertys
        private int _id;
        public int Id
        {
            get { return _id; }
            set { _id = value; }
        }

        private string _isbn;
        public string Isbn
        {
            get { return _isbn; }
            set { _isbn = value; }
        }

        private string _title;
        public string Title
        {
            get { return _title; }
            set { _title = value; }
        }

        private string _author;
        public string Author
        {
            get { return _author; }
            set { _author = value; }
        }

        private string _publisher;
        public string Publisher
        {
            get { return _publisher; }
            set { _publisher = value; }
        }

        private string _genre;
        public string Genre
        {
            get { return _genre; }
            set { _genre = value; }
        }

        private string _type;
        public string Type
        {
            get { return _type; }
            set { _type = value; }
        }

        private int _copyright;
        public int Copyright
        {
            get { return _copyright; }
            set { _copyright = value; }
        }

        private int _rating;
        public int Rating
        {
            get 
            {
                return _rating;
            }
            set 
            {
                if (value >= 1 && value <= 5)
                {
                    _rating = value; 
                }
                else
                {
                    Exception ValueOutOfRange = new Exception("Value of rating is out of range. Must be between one and five.");
                    throw ValueOutOfRange;
                }
            }
        }

        private string _description;
        public string Description
        {
            get { return _description; }
            set { _description = value; }
        }

        private string _image_uri;
        public string Image_uri
        {
            get { return _image_uri; }
            set { _image_uri = value; }
        }
        #endregion

        #region Methods
        public Book()
        {
            Id = 0;
            Isbn = "";
            Title = "";
            Author = "";
            Publisher = "";
            Genre = "";
            Type = "";
            Copyright = 0000;
            Rating = 1;
            Description = "";
            Image_uri = "";
        }

        public Book(int id, string isbn, string title, string author, string publisher, string genre, string type, int copyright, int rating, string description, string image_uri)
        {
            Id = id;
            Isbn = isbn;
            Title = title;
            Author = author;
            Publisher = publisher;
            Genre = genre;
            Type = type;
            Copyright = copyright;
            Rating = rating;
            Description = description;
            Image_uri = image_uri;
        }
        #endregion
    }
}
// Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace SharpLibrary
{
    class Program
    {
        static void Main()
        {
            try
            {

                Library library = new Library();
                Book myBook;
                List<Book> bookList;

                library.OpenConnection();
                bookList = library.SearchBookByAuthor("j.k.rowling");
                library.CloseConnection();               

                foreach (Book book in bookList)
                {
                    Console.WriteLine(book.Title);
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                Console.WriteLine("End Of Program.");
            } 
        }
    }
}
1
Contributor
1
Reply
2
Views
5 Years
Discussion Span
Last Post by ShadyTyrant
0

Problem solved

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace SharpLibrary
{
    class Library
    {
        #region Fields
        private Book _book;
        private List<Book> _bookList;
        private string _connectionString;
        private MySqlConnection _connection;
        private MySqlCommand _command;
        #endregion

        #region Propertys
        public MySqlDataReader Reader { get; set; }

        private string _sqlQuery;
        public string SqlQuery
        {
            get 
            { 
                return _sqlQuery; 
            }
            set 
            {
                _command.CommandText = value;
                _sqlQuery = value;
            }
        }

        public bool ConnectionStatus { get; set; }
        #endregion

        #region Methods
        #region Initialization
        public Library()
        {
            _book = new Book();
            _bookList = new List<Book>();
            _connectionString = "SERVER=localhost;DATABASE=library;UID=root;PASSWORD=pass;";
            _connection = new MySqlConnection(_connectionString);
            _command = _connection.CreateCommand();
            SqlQuery = "";
            ConnectionStatus = false;
        }

        public Library(string connectionString)
        {
            _book = new Book();
            _bookList = new List<Book>();
            _connectionString = connectionString;
            _connection = new MySqlConnection(_connectionString);
            _command = _connection.CreateCommand();
            SqlQuery = "";
            ConnectionStatus = false;
        }
        #endregion

        #region Connections
        public void OpenConnection()
        {
            try
            {
                _connection.Open();
                ConnectionStatus = true;
            }
            catch
            {
                ConnectionStatus = false;
                Exception OpenConnectionError = new Exception("Could not open connection to library");
                throw OpenConnectionError;
            }
        }

        public void CloseConnection()
        {
            try
            {
                _connection.Close();
                ConnectionStatus = false;
            }
            catch 
            {
                ConnectionStatus = false;
                Exception CloseConnectionError = new Exception("Could not close connection to library");
                throw CloseConnectionError;
            }
        }
        #endregion

        #region Book Query Methods
        public Book SearchBookById(int id)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE id={0}", id);
            ExecuteQuery(sqlQuery);
            Book myBook = GetBook();
            Reader.Close();
            return myBook;
        }

        public Book SearchBookByIsbn(string isbn)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE isbn={0}", isbn);
            ExecuteQuery(sqlQuery);
            Book myBook = GetBook();
            Reader.Close();
            return myBook;
        }

        public Book SearchBookByTitle(string title)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE title={0}", title);
            ExecuteQuery(sqlQuery);
            Book myBook = GetBook();
            Reader.Close();
            return myBook;
        }

        public List<Book> SearchBookByAuthor(string author)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE author=\"{0}\"", author);
            ExecuteQuery(sqlQuery);
            List<Book> myList = GetBookList();
            Reader.Close();
            return myList;
        }

        public List<Book> SearchBookByPublisher(string publisher)
        {
            string sqlQuery = string.Format("SELECT * FROM books WHERE publisher=\"{0}\"", publisher);
            ExecuteQuery(sqlQuery);
            List<Book> myList = GetBookList();
            Reader.Close();
            return myList;
        }
        #endregion

        #region Helper Methods 
        private void ExecuteQuery()
        {
            Reader = _command.ExecuteReader();
        }

        private void ExecuteQuery(string sqlQuery)
        {
            SqlQuery = sqlQuery;
            Reader = _command.ExecuteReader();
        }

        private Book GetBook()
        {
            if (Reader.HasRows)
            {
                while (Reader.Read())
                {
                    Book book = new Book(Reader.GetInt32(Reader.GetOrdinal("id")),
                                    Reader.GetString(Reader.GetOrdinal("isbn")),
                                    Reader.GetString(Reader.GetOrdinal("title")),
                                    Reader.GetString(Reader.GetOrdinal("author")),
                                    Reader.GetString(Reader.GetOrdinal("publisher")),
                                    Reader.GetString(Reader.GetOrdinal("genre")),
                                    Reader.GetString(Reader.GetOrdinal("type")),
                                    Reader.GetInt32(Reader.GetOrdinal("copyright")),
                                    Reader.GetInt32(Reader.GetOrdinal("rating")),
                                    Reader.GetString(Reader.GetOrdinal("description")),
                                    Reader.GetString(Reader.GetOrdinal("image_uri")));
                    return book;
                }
            }
            return new Book();
        }

        private List<Book> GetBookList()
        {
            List<Book> bookList = new List<Book>();
            if (Reader.HasRows)
            {
                while (Reader.Read())
                {
                    Book myBook = new Book(Reader.GetInt32(Reader.GetOrdinal("id")),
                                           Reader.GetString(Reader.GetOrdinal("isbn")),
                                           Reader.GetString(Reader.GetOrdinal("title")),
                                           Reader.GetString(Reader.GetOrdinal("author")),
                                           Reader.GetString(Reader.GetOrdinal("publisher")),
                                           Reader.GetString(Reader.GetOrdinal("genre")),
                                           Reader.GetString(Reader.GetOrdinal("type")),
                                           Reader.GetInt32(Reader.GetOrdinal("copyright")),
                                           Reader.GetInt32(Reader.GetOrdinal("rating")),
                                           Reader.GetString(Reader.GetOrdinal("description")),
                                           Reader.GetString(Reader.GetOrdinal("image_uri")));
                    bookList.Add(myBook);
                }
            }
            return bookList;
        }
        #endregion
        #endregion
    }
}
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.