Click here to Skip to main content
15,867,488 members
Articles / Desktop Programming / WPF

Using the PetaPoco ORM to Manage SQLServer, SQLIte, MySQL and ProgreSQL Databases

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
10 Dec 2020CPOL6 min read 12.5K   340   8  
Simple WPF app that shows how to use PetaPoco library to manage an SQL database
This article presents a simple WPF application which demonstrates how to use the PetaPoco library to manage an SQL database. The application supports the SQLServer, SQLIte, MySQL and ProgreSQL database providers.

Introduction

This article presents a simple application which demonstrates the following:

  1. Creation of a simple WPF application
  2. Use of the PetaPoco ORM to create and maintain a database
  3. Use of multiple database providers
  4. Use of dependency injection to create a clean and maintainable architecture

The application supports SQLServer, SQLite, MySQL and ProgreSQL database providers.

Traditionally, a C#/WPF application would access an SQL database using the ADO Framework. However, adding the code to create and manage the tables can be rather tedious and repetitive, and many object-relational mapper (ORM) frameworks have been developed to simplify the task. These map each object in a database to a Plain Old CLR Object (POCO). The best known ORM is arguably Entity Framework, which is powerful but very inefficient. Lightweight alternatives to Entity Framework have been developed which, whilst less powerful, are almost as efficient as native ADO code. These include Dapper and PetaPoco, both of which are free to use without charge.

In this article, I present a WPF PetaPoco application which allows the user to edit fungal collection records. (The nature of the records is neither here nor there since this is a demonstration application.) The application uses the PetaPoco ORM to manage the creation and editing of database objects.

The application uses a number of design principles to make the architecture flexible and maintainable. These are as follows:

  1. The code is written using the WPF framework with the MVVM (Model View View model) pattern. This separates the presentation (view) from the data displayed in the view (view model) and source data (model).
  2. The code is extremely modular with the database code moved into a separate package. This avoids having database logic spread throughout the application.
  3. The code uses inversion of Control (IoC) implemented using dependency injection to reduce coupling between components, and makes it easy to modify the code, for example, use a different ORM library. Thus the view models access the database objects by means of an ISpeciesManager interface, and know nothing about the nature of the database. The ISpeciesManager interface is implemented by the SpeciesManager class, which knows nothing about the ORM. The SpeciesManager class accesses each database table using ISpecies, IImage, IImagePath and IConfiguration interfaces. These interfaces are implemented by classes in the PetaPocoAdapter namespace. Thus modifying the code to use the Dapper ORM, for example, would be relatively straightforward, as the new code would simply implement the ISpecies, IImage, IImagePath and IConfiguration interfaces, and the existing views and view models would not need to know anything about the new component.

The application supports the following SQL providers:

  1. SQLServer
  2. SQLite
  3. MySQL
  4. ProgreSQL

These are either free or, in the case of SQLServer, have a free version with restricted functionality. SQLite supports local databases. The other providers support local and remote databases.

The MainWindow code is responsible for creating the ISpeciesManager, IDatabase, ISpecies, IImage, IImagePath and IConfiguration instances, thus isolating the views and view models from any knowledge of the implementation.

Requirements

You will need to install at least one of the database providers. SQLite has the smallest footprint if disk space is in short supply. The installation downloads are easy to find using a search engine.

Background

This article assumes that you have the following:

  1. A sound knowledge of C#
  2. An understanding of the basic concepts of SQL

A basic knowledge of WPF will help but it is not essential.

You do not need to know to know anything about the SQL providers, i.e., SQLServer, SQLIte, MySQL and ProgreSQL.

Architecture

The architecture is shown in the following UML class diagram:

Image 1

There are three views, with associated view models, as follows:

  • SpeciesListView: a list of the species in the database
  • SpeciesView: the data associated with a given species
  • ConfigurationView: the list of root folders containing images. Images may be stored in a folder path in each root folder

Each view model accesses the database objects by means of an instance of the ISpeciesManager interface. The database instance is created using the appropriate factory, e.g., SQLServerDatbaseFactory.

The database objects are represented by classes in the DBObjects namespace as follows:

  • Species
  • Image
  • ImagePath
  • Configuration

Note the clean separation between the database abstractions and the PetaPoco implementation. The benefit of such a clean separation is that it is very easy to replace the PetaPoco code with an alternative implementation. It also makes it easy to reuse the PetaPoco code in another application if desired.

Using the Code

The PetaPoco ORM provides a very simple and convenient interface.

The ImagePathsStore class manages the table that stores the image folders. The Update command is as follows:

C#
public void Update(DBObject.ImagePath imagePath)
{
    _iDatabase.Update("tblImagesDatabase", "id", imagePath);
}

The _iDatabase property is an instance of the PetaPoco IDatabase interface. "tblImagesDatabase" is the name of the database table, "id" is the primary key, and imagePath is an instance of the ImagePath class which represents an image folder.

The insert command is as follows:

C#
public void Insert(DBObject.ImagePath imagePath)
{
    imagePath.id = System.Convert.ToInt64
                   (_iDatabase.Insert("tblImagesDatabase", "id", imagePath));
}

The PetaPoco insert command returns the value of the primary key for the new table row.

Note how the ORM hides the details of the SQL insert and update commands.

The ImagePath class is defined as follows:

C#
internal class ImagePath
{
    public ImagePath()
    {
    }

    public Int64 id { get; set; }
    public string path { get; set; }
}

The two properties map to the columns in the database table, with the id property being the primary key.
The database factory classes, such as SQLServerDatabaseFactory, provide static methods to create a new database, and open an existing database. The code creates the tables as part of the database creation. The Create database and create table commands are specific to each SQL provider.

Running the Application

The demonstration application allows you to create a database containing fungal records. Each record has multiple descriptive fields and you may add one of more images to each record. The images are not stored in the database. The database stores the file paths to the images and not the images. Note that you must configure the image folders before you can add images. An image folder is a file path, such as as "C:\Images\". You can then add any image which is stored under that folder, either directly or in a decendent folder, e.g., "C:\Images\D500\MyImage.jpg". (I have done it this way so that you can move your entire image database to a new location, for example "D:\Images\" without having to update every single fungal record, which would be a nightmare. Instead, you simply move the images, and then edit the image folder, changing it from "C:\Images\" to "D:\Images\". You can have multiple image folders, and these are edited using the Configuration view.

Screenshots

The main window on start up:

Image 2

Opening a local SQLServer database using windows authentication:

Image 3

An example list of fungal records:

Image 4

The Configuration view:

Image 5

A fungal record with one image:

Image 6

Points of Interest

It turned out to be easy to support these four database providers. The only small issue occurred for the PostgreSQL ADO provider, whereby the name of the database table in SQL commands must be enclosed in parentheses in order to correctly preserve the case.

I tried adapting the code to use the Dapper ORM, and discovered that it is quite complicated to use the Dapper ORM with multiple SQL providers. This is definitely an advantage of the PetaPoco library.

GitHub

The code is provided in the attached ZIP file and it is also available from the following GitHub repository:

History

  • 10th December, 2020: Version 1: Initial release
  • 14th December, 2020: Version 2: Fixed a bug when creating a new record

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United Kingdom United Kingdom
C#/WPF/C++ Windows developer

Comments and Discussions

 
-- There are no messages in this forum --