public static OperationStatus<bool> DeleteBook(Book book)
{
SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
connection.Open();
using (SQLiteTransaction mytransaction = connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(connection))
{
mycommand.CommandText = "DELETE FROM book where id=@id";
mycommand.Parameters.AddWithValue("@id", book.Id);
mycommand.ExecuteNonQuery();
}
mytransaction.Commit();
}
connection.Close();
return new OperationStatus<bool>();
}
public static OperationStatus<bool> UploadBookCover(Book book, Image image, System.Drawing.Imaging.ImageFormat format)
{
using (MemoryStream ms = new MemoryStream())
{
image.Save(ms, format);
byte[] photo = ms.ToArray();
SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
connection.Open();
using (SQLiteTransaction mytransaction = connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(connection))
{
mycommand.CommandText = "UPDATE book SET photo=@photo where id=@id";
mycommand.Parameters.Add("@photo", DbType.Binary, 20).Value = photo;
mycommand.Parameters.AddWithValue("@id", book.Id);
mycommand.ExecuteNonQuery();
}
mytransaction.Commit();
}
connection.Close();
}
return new OperationStatus<bool> { OperationMessage = "Ok³adka zosta³a dodana", Result = OperationResult.Passed, };
}
private static Book CreateBookRecord(DataRow row)
{
Book book = new Book { Id = Int64.Parse(row["Id"].ToString()) };
if (row["CategoryId"] != DBNull.Value)
book.CategoryId = Int64.Parse(row["CategoryId"].ToString());
book.CategoryName = row["CategoryName"].ToString();
book.Title = row["Title"].ToString();
book.Author = row["Author"].ToString();
book.ISBN = row["isbn"].ToString();
book.AdditionalInfoLine1 = row["additionalInfoLine1"].ToString();
book.AdditionalInfoLine2 = row["additionalInfoLine2"].ToString();
book.EntryDate = Helpers.ConvertFromUnixTimestamp(long.Parse(row["entryDate"].ToString()));
var cover=(row["cover"]) as byte[];
if (cover != null && cover.Length>0)
book.Cover = Image.FromStream(new MemoryStream((byte[])row["cover"]));
return book;
}
public static OperationStatus<Book> UpdateBook(Book book)
{
byte[] photo = new byte[0];
if (book.Cover != null)
using (MemoryStream ms = new MemoryStream())
{
book.Cover.Save(ms, book.Cover.RawFormat);
photo = ms.ToArray();
}
SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
connection.Open();
if (!CheckUniqueISBN(connection, book.ISBN, book.Id))
return new OperationStatus<Book> { OperationMessage = "Podany ISBN jest pusty lub jest ju¿ w bazie danych", Result = OperationResult.Failed, Data = book };
using (SQLiteTransaction mytransaction = connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(connection))
{
mycommand.CommandText = "UPDATE book SET category_id=@categoryId, title=@title, author=@author,isbn=@isbn, additionalInfoLine1=@additionalInfoLine1, additionalInfoLine2=@additionalInfoLine2, photo=@photo where id=@id";
mycommand.Parameters.Add("@photo", DbType.Binary, 20).Value = photo;
mycommand.Parameters.AddWithValue("@categoryId", book.CategoryId);
mycommand.Parameters.AddWithValue("@title", book.Title);
mycommand.Parameters.AddWithValue("@author", book.Author);
mycommand.Parameters.AddWithValue("@isbn", book.ISBN);
mycommand.Parameters.AddWithValue("@additionalInfoLine1", book.AdditionalInfoLine1);
mycommand.Parameters.AddWithValue("@additionalInfoLine2", book.AdditionalInfoLine2);
mycommand.Parameters.AddWithValue("@id", book.Id);
mycommand.ExecuteNonQuery();
}
mytransaction.Commit();
}
connection.Close();
return new OperationStatus<Book> { OperationMessage = "Ksi¹¿ka zosta³a zaktualizowana.", Result = OperationResult.Passed, Data = book };
}
public static OperationStatus<Book> InsertBook(Book book)
{
SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
connection.Open();
if (!CheckUniqueISBN(connection, book.ISBN, 0))
return new OperationStatus<Book> { OperationMessage = "Podany ISBN jest pusty lub jest ju¿ w bazie danych", Result = OperationResult.Failed, Data = book };
using (SQLiteTransaction mytransaction = connection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(connection))
{
mycommand.CommandText = "INSERT INTO book (category_id, title, author, isbn, additionalInfoLine1, additionalInfoLine2, entryDate) values(@categoryId, @title, @author, @isbn, @additionalInfoLine1, @additionalInfoLine2, @entryDate)";
mycommand.Parameters.AddWithValue("@categoryId", book.CategoryId);
mycommand.Parameters.AddWithValue("@title", book.Title);
mycommand.Parameters.AddWithValue("@author", book.Author);
mycommand.Parameters.AddWithValue("@isbn", book.ISBN);
mycommand.Parameters.AddWithValue("@additionalInfoLine1", book.AdditionalInfoLine1);
mycommand.Parameters.AddWithValue("@additionalInfoLine2", book.AdditionalInfoLine2);
mycommand.Parameters.AddWithValue("@entryDate", Helpers.ConvertToUnixTimestamp(DateTime.Now));
mycommand.ExecuteNonQuery();
mycommand.CommandText = @"select last_insert_rowid()";
long lastId = (long)mycommand.ExecuteScalar();
book.Id = lastId;
}
mytransaction.Commit();
}
connection.Close();
return new OperationStatus<Book> { OperationMessage = "Ksi¹¿ka zosta³a dodana.", Result = OperationResult.Passed, Data = book };
}
public static Book GetBook(long id)
{
Book book = new Book();
SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
connection.Open();
using (SQLiteCommand mycommand = new SQLiteCommand(connection))
{
mycommand.CommandText = @"SELECT c.name as CategoryName, b.id as Id, b.category_id as CategoryId, b.title as Title, b.author as Author, b.isbn as isbn, b.additionalInfoLine1 as additionalInfoLine1, b.additionalInfoLine2 as additionalInfoLine2, b.entryDate as entryDate, b.photo as cover from book b
Left outer join category c on c.id=b.category_id
where b.id=@id";
mycommand.Parameters.AddWithValue("@id", id);
IDataReader reader = mycommand.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
book = CreateBookRecord(row);
}
}
else
{
book = null;
}
reader.Close();
}
connection.Close();
return book;
}
private void OnAddBookToCategoryItemExecute(object sender, ExecutedRoutedEventArgs e)
{
Category cat = e.Parameter as Category;
if (cat != null)
{
Book book = new Book() { Category = cat };
OperationStatus<Book> result1 = BookDetails.ShowWindow(this, book);
if (result1.Result == OperationResult.Passed)
{
var result = BooksManager.BooksManager.InsertBook(book);
if (result.Result == OperationResult.Passed)
{
RefreshBooksList(new BookFilter() { RootCategoryId = selectedCategoryId });
uxBooksList.CurrentItem = book;
}
else
MessageBox.Show(result.OperationMessage);
}
}
}
private void OnAddBookItemExecute(object sender, ExecutedRoutedEventArgs e)
{
Book book = new Book();
book.CategoryId = selectedCategoryId;
book.Title = uxAddBookTitle.Text;
book.Author = uxAddBookAuthor.Text;
book.ISBN = uxAddBookISBN.Text;
var result = BooksManager.BooksManager.InsertBook(book);
if (result.Result == OperationResult.Passed)
{
uxAddBookTitle.Text = String.Empty;
uxAddBookAuthor.Text = String.Empty;
uxAddBookISBN.Text = String.Empty;
RefreshBooksList(new BookFilter { RootCategoryId = selectedCategoryId });
var x = (uxBooksList.ItemsSource as IList<Book>).FirstOrDefault(b => b.Id == book.Id);
uxBooksList.CurrentItem = x;
uxBooksList.SelectedItem = x;
uxBooksList.UpdateLayout();
uxBooksList.ScrollIntoView(x);
}
else
MessageBox.Show(result.OperationMessage);
}