/// <summary>
/// Utility function for inserting book records
/// </summary>
/// <param name="book"></param>
/// <param name="mode"></param>
/// <returns></returns>
private bool insertBook(Book book, char mode)
{
//attempt to insert subject data
foreach (Subject s in book.Subjects)
{
object[] subjectData = { s.Id, s.Name };
this.ExecuteNonQuery(SQL.Subject.INSERT_NONCUSTOM, subjectData);
object[] subjectRelation = { book.Id, s.Id };
this.ExecuteNonQuery(SQL.Subject.INSERT_RELATION, subjectRelation);
}
//attempt to insert publisher data
if (!book.PublisherId.Equals(""))
{
object[] publisherData = { book.PublisherId, book.PublisherInfo };
this.ExecuteNonQuery(SQL.Publisher.INSERT, publisherData);
}
//attempt to insert author data
foreach (Person p in book.Authors)
{
object[] authorData = { p.Id, p.FirstName, p.LastName };
bool success = this.ExecuteNonQuery(SQL.Person.INSERT_PERSON, authorData);
object[] authorRelation = { book.Id, p.Id };
this.ExecuteNonQuery(SQL.Person.INSERT_AUTHOR_RELATION, authorRelation);
}
//attempt to insert editor data
foreach (Person p in book.Editors)
{
object[] authorData = { p.Id, p.FirstName, p.LastName };
bool success = this.ExecuteNonQuery(SQL.Person.INSERT_PERSON, authorData);
object[] editorRelation = { book.Id, p.Id };
this.ExecuteNonQuery(SQL.Person.INSERT_EDITOR_RELATION, editorRelation);
}
//attempt to insert book data
object[] data = { book.Id, book.ISBN, book.ShortTitle, book.LongTitle, book.PhysicalDescription, book.Edition, book.Dewey, book.DeweyNormalized, book.LibraryOfCongress, book.PublisherId, book.Summary, book.Notes, book.Awards, book.NewPrice, book.UsedPrice, book.PricePaid, DateTime.Now.ToShortDateString(), book.Language, book.Urls };
if (mode == 'O')
return this.ExecuteNonQuery(SQL.Book.INSERT_LIBRARY, data);
else if (mode == 'W')
return this.ExecuteNonQuery(SQL.Book.INSERT_WISHLIST, data);
else
return false;
}
/// <summary>
/// Inserts a book record to O as type (meaning own)
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public bool InsertIntoLibrary(Book book)
{
return this.insertBook(book, 'O');
}
/// <summary>
/// Inserts a book with the type field set to "w" for wish list
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public bool InsertIntoWishList(Book book)
{
return this.insertBook(book, 'W');
}
/// <summary>
/// Receives a DataSet, and fills the domain objects Properties from it
/// </summary>
/// <param name="ds"></param>
/// <param name="book"></param>
/// <returns></returns>
public bool FillBookFromDataSet(DataSet ds, Book book)
{
if (ds == null || ds.Tables[0].Rows.Count < 1)
return false;
DataRow dr = ds.Tables[0].Rows[0];
book.Id = dr["id"].ToString();
book.ISBN = dr["isbn"].ToString();
book.Language = dr["language"].ToString();
book.LibraryOfCongress = dr["lcc_number"].ToString();
book.LongTitle = dr["long_title"].ToString();
book.Notes = dr["notes"].ToString();
book.PhysicalDescription = dr["physical_desc"].ToString();
book.PublisherId = dr["publisher_id"].ToString();
book.ShortTitle = dr["short_title"].ToString();
book.Summary = dr["summary"].ToString();
book.Urls = dr["urls"].ToString();
book.NewPrice = dr["new_price"].ToString();
book.UsedPrice = dr["used_price"].ToString();
book.PricePaid = dr["price_paid"].ToString();
//get book_subject data
DataSet subjectDS = this.ExecuteQuery(SQL.Subject.SELECT_BY_BOOK_ID, new object[] { book.Id });
if (subjectDS != null)
foreach (DataRow dr1 in subjectDS.Tables[0].Rows)
{
Subject s = new Subject();
s.Id = dr1["id"].ToString();
s.Name = dr1["subject"].ToString();
book.Subjects.Add(s);
}
//get publisher data
DataSet publisherDS = this.ExecuteQuery(SQL.Publisher.SELECT_PUBLISHER_BY_ID, new object[] { book.PublisherId });
if (publisherDS != null)
foreach (DataRow dr2 in publisherDS.Tables[0].Rows)
{
book.PublisherInfo = dr2["publisher_info"].ToString();
}
//get author data
DataSet authorDS = this.ExecuteQuery(SQL.Person.SELECT_AUTHORS_BY_BOOK_ID, new object[] { book.Id });
if (authorDS != null)
foreach (DataRow dr3 in authorDS.Tables[0].Rows)
{
Person p = new Person();
p.Id = dr3["id"].ToString();
p.FirstName = dr3["first_name"].ToString();
p.LastName = dr3["last_name"].ToString();
book.Authors.Add(p);
}
//get editor data
DataSet editorDS = this.ExecuteQuery(SQL.Person.SELECT_EDITORS_BY_BOOK_ID, new object[] { book.Id });
if (editorDS != null)
foreach (DataRow dr4 in editorDS.Tables[0].Rows)
{
Person p = new Person();
p.Id = dr4["id"].ToString();
p.FirstName = dr4["first_name"].ToString();
p.LastName = dr4["last_name"].ToString();
book.Editors.Add(p);
}
return true;
}
/// <summary>
/// Retrieves a single book record by its id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Book GetBookById(string id)
{
object[] data = { id };
DataSet ds = this.ExecuteQuery(SQL.Book.SELECT_BY_ID_LIBRARY, data);
Book b = new Book();
if (this.FillBookFromDataSet(ds, b))
return b;
else
return null;
}
/// <summary>
/// Checks to see if the book is already in the wish list
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public bool ExistsInWishList(Book book)
{
object[] data = { book.Id };
DataSet ds = this.ExecuteQuery(SQL.Book.SELECT_SIMPLE_WISHLIST_BY_ID, data);
#if DEBUG
Console.Out.WriteLine(this.getDataSetInfo(ds));
#endif
if (ds == null || ds.Tables[0].Rows.Count < 1)
return false;
else
return true;
}
public bool Updatebook(Book book)
{
//attempt to insert subject data
this.ExecuteNonQuery(SQL.Subject.DELETE_BY_ID, new object[] {book.Id});
foreach (Subject s in book.Subjects)
{
object[] subjectData = { s.Id, s.Name };
this.ExecuteNonQuery(SQL.Subject.INSERT_NONCUSTOM, subjectData);
object[] subjectRelation = { book.Id, s.Id };
this.ExecuteNonQuery(SQL.Subject.INSERT_RELATION, subjectRelation);
}
//attempt to insert publisher data
if (!book.PublisherId.Equals(""))
{
object[] publisherData = { book.PublisherId, book.PublisherInfo };
this.ExecuteNonQuery(SQL.Publisher.INSERT, publisherData);
}
//attempt to insert author data
this.ExecuteNonQuery(SQL.Person.DELETE_AUTHOR_RELATION_BY_BOOK_ID,new object[] {book.Id});
foreach (Person p in book.Authors)
{
object[] authorData = { p.Id, p.FirstName, p.LastName };
bool success = this.ExecuteNonQuery(SQL.Person.INSERT_PERSON, authorData);
object[] authorRelation = { book.Id, p.Id };
this.ExecuteNonQuery(SQL.Person.INSERT_AUTHOR_RELATION, authorRelation);
}
//attempt to insert editor data
this.ExecuteNonQuery(SQL.Person.DELETE_EDITOR_RELATION_BY_BOOK_ID,new object[] {book.Id});
foreach (Person p in book.Editors)
{
object[] authorData = { p.Id, p.FirstName, p.LastName };
bool success = this.ExecuteNonQuery(SQL.Person.INSERT_PERSON, authorData);
object[] editorRelation = { book.Id, p.Id };
this.ExecuteNonQuery(SQL.Person.INSERT_EDITOR_RELATION, editorRelation);
}
object[] param = { book.Id, book.ISBN, book.ShortTitle, book.LongTitle, book.PhysicalDescription, book.Edition, book.Dewey, book.DeweyNormalized, book.LibraryOfCongress, book.PublisherId, book.Summary, book.Notes, book.Awards, book.NewPrice, book.UsedPrice, book.PricePaid, book.Language, book.Urls };
return this.ExecuteNonQuery(SQL.Book.UPDATE_BOOK, param);
}
private void btnSave_Click(object sender, EventArgs e)
{
MainForm form = (MainForm)this.MdiParent;
BookDAO dao = new BookDAO(form.CurrentDatabase.FullName);
this.clearErrors();
for (int i = 0; i < _textboxes.Length; i++)
{
string isbn = _textboxes[i].Text;
string uri = Constants.GetWebServiceURI("isbn", isbn);
if (isbn == null || isbn.Equals(""))
continue;
TextBox txtbox = _textboxes[i];
Label lbl = _resultLabels[i];
this.setTextBox(txtbox, lbl, "Processing...", Color.Blue, true, true);
WebRequest request = null;
WebResponse response = null;
try
{
request = WebRequest.Create(uri);
response = request.GetResponse();
}
catch(WebException e2)
{
MessageBox.Show(ErrorMessages.Common.WEB_SERVICE_ERROR, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
this.setTextBox(txtbox,lbl,"No Connection",Color.Red,true,false);
return;
}
catch (Exception e1)
{
ExceptionHandler.HandleException(e1);
this.setTextBox(txtbox, lbl, "Failure", Color.Red, true, false);
continue;
}
Element element = XMLParser.ParseXML(response.GetResponseStream());
#if DEBUG
string tree = "";
int level = 0;
XMLParser.GetParseTreeInfo(element, ref tree, ref level);
Console.Out.WriteLine(tree);
#endif
Book book = new Book();
Constants.XMLResultReturnValue result = book.FillFromXMLResults(element);
if (result == Constants.XMLResultReturnValue.NOT_FOUND)
this.setTextBox(txtbox, lbl, "Not Found", Color.Red, true, false);
else if (result == Constants.XMLResultReturnValue.UNKNOWN)
this.setTextBox(txtbox, lbl, "Failure", Color.Red, true, false);
else if (book != null)
{
if (_mode == Constants.LibraryMode.LIBRARY && dao.ExistsInLibrary(book))
this.setTextBox(txtbox, lbl, "Already Exists", Color.Red, true, false);
else if (_mode == Constants.LibraryMode.WISHLIST && dao.ExistsInWishList(book))
this.setTextBox(txtbox, lbl, "Already Exists", Color.Red, true, false);
else if (_mode == Constants.LibraryMode.LIBRARY && !dao.InsertIntoLibrary(book))
this.setTextBox(txtbox, lbl, "Failure", Color.Red, true, false);
else if (_mode == Constants.LibraryMode.WISHLIST && !dao.InsertIntoWishList(book))
this.setTextBox(txtbox, lbl, "Failure", Color.Red, true, false);
else
{
this.setTextBox(txtbox, lbl, "Success", Color.Green, true, false);
foreach (Form f in this.MdiParent.MdiChildren)
{
if (f is ViewBooksForm)
(f as ViewBooksForm).Refresh();
}
}
}
}
}
/// <summary>
/// We override refresh to refresh the text boxees from the data
/// </summary>
public override void Refresh()
{
if(_bookId != null)
{
MainForm frm = this.MdiParent as MainForm;
BookDAO dao = new BookDAO(frm.CurrentDatabase.Name);
this._book = dao.GetBookById(_bookId);
}
Book b = _book;
base.Refresh();
txtISBN.Text = b.ISBN;
txtShortTitle.Text = b.ShortTitle;
txtLongTitle.Text = b.LongTitle;
string authors = "";
foreach (Person p in b.Authors)
authors += p.FullName + Environment.NewLine;
txtAuthors.Text = authors;
string editors = "";
foreach (Person p in b.Editors)
editors += p.FullName + Environment.NewLine;
txtEditors.Text = editors;
txtPublisher.Text = b.PublisherInfo;
txtDewey.Text = b.Dewey;
txtDeweyNorm.Text = b.DeweyNormalized;
txtLCC.Text = b.LibraryOfCongress;
txtPhysDesc.Text = b.PhysicalDescription;
txtEdition.Text = b.Edition;
txtLanguage.Text = b.Language;
txtSummary.Text = b.Summary;
txtNotes.Text = b.Notes;
txtAwards.Text = b.Awards;
txtURLs.Text = b.Urls;
txtNewPrice.Text = b.NewPrice;
txtUsedPrice.Text = b.UsedPrice;
string subjects = "";
foreach (Subject s in b.Subjects)
{
subjects += s.Name + "\r\n";
}
txtSubjects.Text = subjects;
}
/// <summary>
/// resets the data and form
/// </summary>
private void clear()
{
this._book = new Book();
this.Refresh();
this.errorProvider1.SetError(this.txtISBN, "");
this.txtLookup.Focus();
}
/// <summary>
/// Called when Look Up button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLookUp_Click(object sender, EventArgs e)
{
if (this.txtLookup.Text.Equals(""))
this.errorProvider1.SetError(this.txtLookup, "Fill in data to lookup");
else
this.errorProvider1.SetError(this.txtLookup, "");
this.errorProvider1.SetError(this.txtISBN, "");
this.lblResult.Text = "Processing...";
this.lblResult.ForeColor = Color.Blue;
this.lblResult.Visible = true;
Application.DoEvents();
string uri = Constants.GetWebServiceURI((this.cboLookupBy.SelectedItem as DDItem).Value, this.txtLookup.Text);
WebRequest request = null;
WebResponse response = null;
try
{
request = WebRequest.Create(uri);
response = request.GetResponse();
}
catch(WebException e2)
{
MessageBox.Show(ErrorMessages.Common.WEB_SERVICE_ERROR, "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
this.lblResult.Text = "No Connection";
this.lblResult.ForeColor = Color.Red;
this.lblResult.Visible = true;
return;
}
catch (Exception e1)
{
ExceptionHandler.HandleException(e1);
this.lblResult.Text = "Failed";
this.lblResult.ForeColor = Color.Red;
this.lblResult.Visible = true;
return;
}
Element element = XMLParser.ParseXML(response.GetResponseStream());
#if DEBUG
string tree = "";
int level = 0;
XMLParser.GetParseTreeInfo(element,ref tree,ref level);
Console.Out.WriteLine(tree);
#endif
this.lblResult.Text = "";
this.lblResult.Visible = false;
_book = new Book();
Constants.XMLResultReturnValue result = _book.FillFromXMLResults(element);
if (result == Constants.XMLResultReturnValue.BOOK_ALREADY_EXISTS)
{
this.lblResult.Text = "Failed";
this.lblResult.ForeColor = Color.Red;
this.lblResult.Visible = true;
this.errorProvider1.SetError(this.txtLookup, "This book already exists in your library");
}
else if (result == Constants.XMLResultReturnValue.NOT_FOUND)
{
this.lblResult.Text = "Not Found";
this.lblResult.ForeColor = Color.Red;
this.lblResult.Visible = true;
}
else
{
this.errorProvider1.SetError(this.txtLookup, "");
this.Refresh();
}
if (this.chkAutoAdd.Checked == true && result == Constants.XMLResultReturnValue.SUCCESS)
{
this.save();
this.clear();
}
}
