Creating and implementing the book database wrapper

In the previous section, we successfully created and implemented our BookItem database model that will be used by our BookLibrary application. Our next step is to begin implementing the code required for our BookItemDatabase class model, by performing the following steps:

  1. Create a new folder within the BookLibrary project solution, called Database as shown in the following screenshot:

    Creating and implementing the book database wrapper

  2. Next, create an empty class within the Database folder, by choosing Add | New File..., as you did when creating the model in the previous section entitled Creating and implementing the book library database model, located within this chapter.
  3. Then, enter in BookDatabase for the name of the new class that you want to create, and click on the New button to allow the wizard to proceed and create the new file.
  4. Next, ensure that the BookDatabase.cs file is displayed within the code editor window, and then enter in the following code snippet:
            // 
        //  BookDatabase.cs 
        //  BookLibrary Database to handle performing database  
        //  Creation, Retrieval, Updating and Deletion of Book Items. 
        // 
        //  Created by Steven F. Daniel on 20/10/2016. 
        //  Copyright © 2016 GENIESOFT STUDIOS. All rights reserved. 
        // 
        using System.Collections.Generic; 
        using System.Linq; 
        using SQLite; 
 
        namespace BookLibrary 
        { 
            public class BookDatabase 
            {
  5. Next, we need to create a locker variable that will be used to create a mutually-exclusive lock on the database while we are either creating, updating, retrieving, or deleting book items. We do this so that no other operation can interfere while we are processing, and this will prevent issues from arising. We also need to declare a database variable, that points to an instance of our SQLiteConnection located within our SQLite library, so that we can perform database operations:
            static object locker = new object(); 
        SQLiteConnection database;
  6. Then, we need to create the BookDatabase(SQLiteConnection conn) method that accepts a conn object, which is an instance of our SQLiteConnection class, and this instance method will be used to create the necessary database table structure, based on our BookItem model:
            /// <summary> 
        /// Initializes a new instance of the BookLibrary  
        /// Database class. 
        /// </summary> 
        /// <param name="conn">Conn.</param> 
        public BookDatabase(SQLiteConnection conn) 
        { 
            database = conn; 
 
            // Create the tables within our Book Library Database 
            database.CreateTable<BookItem>(); 
        }
  7. Next, we need to create the GetItems() method that will be used to extract all of the existing book entries that have been saved to the database. We use the LINQ language syntax to iterate and retrieve all items from our BookItem table, and convert this collection to a list instance, as determined by the .ToList() method:
            /// <summary> 
        /// Gets all of the book library items from our database. 
        /// </summary> 
        /// <returns>The items.</returns> 
        public IEnumerable<BookItem> GetItems() 
        { 
            // Set a mutual-exclusive lock on our database, while  
            // retrieving items. 
            lock (locker) 
            { 
                return (from i in database.Table<BookItem>()  
                        select i).ToList(); 
            } 
        }
  8. Then, we need to create the GetItem() method that will extract the selected book entry from the BookItem database table, using their id as the key. Again, we use the LINQ language syntax to retrieve the first item from the BookItem table that matches the id of the book item:
            /// <summary> 
        /// Gets a specific book item from the database. 
        /// </summary> 
        /// <returns>The item.</returns> 
        /// <param name="id">Identifier.</param> 
        public BookItem GetItem(int id) 
        { 
            // Set a mutual-exclusive lock on our database, while  
            // retrieving the book item. 
            lock (locker) 
            { 
                return database.Table<BookItem>().FirstOrDefault(x => 
                       x.Id == id); 
            } 
        }
  9. Next, we need to create the SaveItem() method that will save the book item to the BookItem database table. In this instance method, we are handling two case scenarios: one if the item we are saving is an existing item, we check the id of the book item, and if it is a non-zero value, we proceed to update the book item using the Update method on the database object, and return the book item id back.
  10. However, if the item is a new book record, that is all new books that get created will have an id of 0, this will be directly inserted into the BookItem table, using the Insert method on the database object:
            /// <summary> 
        /// Saves the book item currently being edited. 
        /// </summary> 
        /// <returns>The item.</returns> 
        /// <param name="item">Item.</param> 
        public int SaveItem(BookItem item) 
        { 
            // Set a mutual-exclusive lock on our database, while  
            // saving/updating our book item. 
            lock (locker) 
            { 
                if (item.Id != 0) 
                { 
                    database.Update(item); 
                    return item.Id; 
                } 
                else { 
                    return database.Insert(item); 
                } 
            } 
        }
  11. Finally, we create the DeleteItem() method that will, as you might have guessed, delete a book item from the BookItem database table using the book's item id and then calling the Delete method on the database object:
            /// <summary> 
        /// Deletes a specific book item from the database. 
        /// </summary> 
        /// <returns>The item.</returns> 
        /// <param name="id">Identifier.</param> 
          public int DeleteItem(int id) 
          { 
               // Set a mutual-exclusive lock on our database, while  
               // deleting our book item. 
               lock (locker) 
              { 
                return database.Delete<BookItem>(id); 
              } 
           } 
          } 
         }

Now that we have created our BookDatabase class, we can proceed and create our BookLibraryDB database wrapper that will use our BookDatabase class to handle all the operations for creating, retrieving, updating, and deletion of book items from our database. That will be used by our WebViewController.cs class to interact with our Razor template Views.

Note

The Android version of the BookDatabase class is available in the companion source code for this book.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.189.170.134