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