Sunday, August 23, 2015

We created our models and the tables has been created and now we will create two classes which will be the repository for our tables. The classes will help us to easily collect and modify data in the tables. First we have the repository for the authors table.

We create a class called Authors and we save the file at the location "~/App_Code/ComputerMagic/PetaPoco/Repository/Authors.cs".

using ComputerMagic.PetaPoco.Models;
using System.Collections.Generic;
using Umbraco.Core.Persistence;

namespace ComputerMagic.PetaPoco.Repository
{
    public static class Authors
    {
        public static IList<Author> GetAll()
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            return db.Fetch<Author>("SELECT * FROM CMAuthors ORDER BY Surname, Name");
        }

        public static Page<Author> GetAllPaged(int Page, int RecordsPerPage)
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            return db.Page<Author>(Page, RecordsPerPage, "SELECT * FROM CMAuthors ORDER BY Surname, Name");
        }

        public static Author GetByAuthorID(int AuthorID)
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            List<Author> Records =db.Fetch<Author>("SELECT * FROM CMAuthors WHERE AuthorID = @0", AuthorID);

            if (Records.Count > 0)
                return Records[0];
            else
                return null;
        }

        public static Author Save(Author item)
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            db.Save(item);
            return item;
        }

        public static int DeleteByAuthorID(int AuthorID)
        {
            UmbracoDatabase _database = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            return _database.Execute("DELETE FROM CMAuthors WHERE AuthorID = @0", AuthorID);
        }
    }
}

Now we create the repository for the Books table. We create a class called Books and we save the file at the location "~/App_Code/ComputerMagic/PetaPoco/Repository/Books.cs".

using ComputerMagic.PetaPoco.Models;
using System.Collections.Generic;
using Umbraco.Core.Persistence;

namespace ComputerMagic.PetaPoco.Repository
{
    public static class Books
    {
        public static IList<Book> GetAll()
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            return db.Fetch<Book>("SELECT * FROM CMBooks ORDER BY Title");
        }

        public static Page<Book> GetAllPaged(int Page, int RecordsPerPage)
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            return db.Page<Book>(Page, RecordsPerPage, "SELECT * FROM CMBooks ORDER BY Title");
        }

        public static IList<Book> GetAllByAuthorID(int AuthorID)
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            return db.Fetch<Book>("SELECT * FROM CMBooks WHERE AuthorID = @0 ORDER BY Title", AuthorID);
        }

        public static Page<Book> GetAllPagedByAuthorID(int Page, int RecordsPerPage, int AuthorID)
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            return db.Page<Book>(Page, RecordsPerPage, "SELECT * FROM CMBooks WHERE AuthorID = @0 ORDER BY Title", AuthorID);
        }

        public static Book GetByBookID(int BookID)
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            List<Book> Records =db.Fetch<Book>("SELECT * FROM CMBooks WHERE BookID = @0", BookID);

            if (Records.Count > 0)
                return Records[0];
            else
                return null;
        }

        public static Book Save(Book item)
        {
            UmbracoDatabase db = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            db.Save(item);
            return item;
        }

        public static int DeleteByBookID(int BookID)
        {
            UmbracoDatabase _database = Umbraco.Core.ApplicationContext.Current.DatabaseContext.Database;
            return _database.Execute("DELETE FROM CMBooks WHERE BookID = @0", BookID);
        }
    }
}

In both classes we have one static member function which gives us all the records of the table.

We also have a different member function for getting all the records in paged format. We just define the page number and the number of records that we want in each page and PetaPoco will do the rest.

There is one more member function which gets one record depending on the id of the table. For the Authors we use the AuthorID and for the Books we use the BookID. In case that a record with the specified id does not exists, the repository will return a null value.

For the insertion and the update of a record we use one member function called Save which will insert a new record or update an existing record.

We also have the DeleteByAuthorID and DeleteByBookID member functions which deletes a record depending on the id of the record.

Finally for the Books repository we also have the member functions GetAllByAuthorID and GetAllPagedByAuthorID which allow us to get all the books for a specific AuthorID.

Of course we can add more member functions in order to cover special needs that our project may require but we created the most frequently functions that we usually need from a repository.