public string InsertOrder(clsOrder prOrder)
        {   // insert
            try
            {
                clsBook _tempBook = GetCurrentBook(prOrder.FKBookID);
                if (_tempBook.stockLevel < prOrder.qty)
                {
                    return("Sorry but there is insufficient stock for this order, please review the stock level change.");
                }

                int lcRecCount = clsDbConnection.Execute("INSERT INTO `order` " +
                                                         "(orderDate, custPhone, custName, qty, pricePerItem, FKBookID) " +
                                                         "VALUES (@orderDate, @custPhone, @custName, @qty, @pricePerItem, @FKBookID)",
                                                         prepareOrderParameters(prOrder));
                int lcRecCount2 = clsDbConnection.Execute("UPDATE book SET " +
                                                          "stockLevel = stocklevel - @qty " +
                                                          "WHERE bookID = @FKBookID",
                                                          prepareStockUpdateParameters(prOrder));
                if (lcRecCount == 1 && lcRecCount2 == 1)
                {
                    return("Order Confirmed");
                }
                else
                {
                    return("Error, contact developer " + lcRecCount);
                }
            }
            catch (Exception ex)
            {
                return(ex.GetBaseException().Message);
            }
        }
        private Dictionary <string, object> prepareBookParameters(clsBook prBook)
        {
            Dictionary <string, object> par = new Dictionary <string, object>(10)
            {
                { "bookName", prBook.bookName },
                { "author", prBook.author },
                { "bookType", prBook.bookType },
                { "price", prBook.price },
                { "lastModified", prBook.lastModified },
                { "stockLevel", prBook.stockLevel },
                { "publisher", prBook.publisher },
                { "description", prBook.description },
                { "ebookFormat", prBook.ebookFormat },
                { "hardback", prBook.hardback },
                { "genreName", prBook.genreName },
                { "bookID", prBook.bookID }
            };

            return(par);
        }
 public string UpdateBook(clsBook prBook)
 {   // update
     if (prBook.lastModified == (DateTime)(GetCurrentBook(prBook.bookID).lastModified))
     {
         prBook.lastModified = DateTime.Now;
     }
     else
     {
         return("Current book out of date, please review changes.");
     }
     try
     {
         int lcRecCount = clsDbConnection.Execute("UPDATE book SET " +
                                                  "bookName = @bookName," +
                                                  "author = @author," +
                                                  "bookType = @bookType," +
                                                  "price = @price," +
                                                  "lastModified = @lastModified," +
                                                  "stockLevel = @stockLevel," +
                                                  "publisher = @publisher," +
                                                  "description = @description, " +
                                                  "ebookFormat = @ebookFormat," +
                                                  "hardback = @hardback " +
                                                  "WHERE bookID = @bookID",
                                                  prepareBookParameters(prBook));
         if (lcRecCount == 1)
         {
             return("One book updated");
         }
         else
         {
             return("Unexpected book update count: " + lcRecCount);
         }
     }
     catch (Exception ex)
     {
         return(ex.GetBaseException().Message);
     }
 }
 public string InsertBook(clsBook prBook)
 {   // insert
     try
     {
         int lcRecCount = clsDbConnection.Execute("INSERT INTO book " +
                                                  "(bookName, author, bookType, price, lastModified, stockLevel, publisher, description, ebookFormat, hardback, FKGenreName) " +
                                                  "VALUES (@bookName, @author, @bookType, @price, @lastModified, @stockLevel, @publisher, @description, @ebookFormat, @hardback, @genreName)",
                                                  prepareBookParameters(prBook));
         if (lcRecCount == 1)
         {
             return("One book inserted");
         }
         else
         {
             return("Unexpected book insert count: " + lcRecCount);
         }
     }
     catch (Exception ex)
     {
         return(ex.GetBaseException().Message);
     }
 }