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