Building A Generic SQLite Repository

In a recent project, I found myself needing multiple, seemingly unrelated databases.  It was crazy to copy and paste the code for such basic operations as getting all the data, etc., so the obvious alternative was a generic repository, and a database with tables for each set of data.

The first step is creating the SQLite repo.  We begin by creating the class:

public class GenericSqliteRepository<T> where T : IEntity, new()

The best way to understand what that “new()” is doing is to take it off, at which point your build will fail with this message:

Severity Code Description Project File Line Suppression State
Error CS0310 'T' must be a non-abstract type with a public 
parameterless constructor in order to use it as parameter 'T' 
in the generic type or method 'SQLiteAsyncConnection.Table<T>()'...

Create the Connection and Tables

Now we want to create a SQLiteAsyncConnection, which we will use in all the methods, including the constructor.

private readonly SQLiteAsyncConnection database;
public GenericSqliteRepository( string dbPath )
{
  database = new SQLiteAsyncConnection( dbPath );
  database.CreateTableAsync<ImageData>().Wait();
  database.CreateTableAsync<ConfigurationData>().Wait();
  database.CreateTableAsync<LogData>().Wait();
}

The tables are unrelated, but we can put them all in the same database, by creating different instances.  We do that by placing the database construction code in App.xaml.cs:

      private static GenericSqliteRepository configurationDataBase;
      public static GenericSqliteRepository ConfigurationDataBase
      {
         get
         {
            if ( configurationDatabase == null)
            {
               configurationDataBase = new GenericSqliteRepository(
                  DependencyService.Get().GetLocalFilePath(
                        "Configuration.db3" ) );
            }
            return configurationDatabase;
         }
      }

      private static GenericSqliteRepository imageDatabase;
      public static GenericSqliteRepository ImageDatabase
      {
         get
         {
            if ( imageDatabase == null )
            {
               imageDatabase = new ImageDatabase(
                  DependencyService.Get().GetLocalFilePath( 
                         "Image.db3" ) );
            }
            return imageDatabase;

         }

      }


      private static GenericSqliteRepository logDatabase;
      public static GenericSqliteRepository LogDatabase
      {
         get
         {
            if ( logDatabase == null )
            {
               logDatabase = new LogDatabase(
                  DependencyService.Get().GetLocalFilePath(
                   "Log.db3" ) );
            }
            return logDatabase;

         }

      }

Notice that the repo thinks they are just tables in one database, but the data is actually stored in three separate files.

Repo Methods

Back in our generic repository, we can now add the basic database operations.  For example, to get all the items from a given table we have:

 public Task<List<T>> GetDataAsync()
 {
    return database.Table<T>().ToListAsync();
 }

The way that the compiler knows which table to use is by instantiating the right database, as we’ll see shortly.

If you want to get a single item based on id, you can write this:

      public async Task GetDataAsync( int id )
      {
         return await database.Table()
            .Where( i => i.ID == id )
            .FirstAsync();
      }

Which is the moral equivalent to:

     public async Task GetDataAsync( int id )
      {
         List items = await database.Table().ToListAsync();
         foreach ( T item in items )
         {
            if ( item.ID == id )
            {
               return item;
            }
         }
         return default( T );
      }

To save data you want to know whether you are updating an existing record, or you are inserting a new one.

      public async Task SaveDataAsync( T instance )
      {
         if ( instance.ID != 0 )
         {
            return await database.UpdateAsync( instance );
         }
         else
         {
            return await database.InsertAsync( instance );
         }
      }

Finally, we have deleting an instance and deleting all the data. The easiest way to do the latter is just to drop the table:

      public async Task DeleteDataAsync( T instance )
      {
         return await database.DeleteAsync( instance );
      }

      public async Task DeleteAllData()
      {
         await database.DropTableAsync();
         await database.CreateTableAsync();
      }

Using the Repo

That’s the generic repo in a nutshell.  Now, to put it to use, you have to declare the models for your tables and then call the generic methods by way of one of the databases you created in App.xaml.cs.  For example:

configurations = await App.ConfigurationDatabase.GetDataAsync();

You have signaled that you want T to be configuration based on the database you use.  There is no ambiguity, and yet you do not have to write all the access and save methods for each database.  This is not only efficient, it is easy to understand and very easy to use.

In the line shown above, all of the data is retrieved from the ConfigurationDatabase, which is represented as a table in the repo’s database, but which is stored in its own file.

Once again, easy peasy.

About Jesse Liberty

Jesse Liberty has three decades of experience writing and delivering software projects and is the author of 2 dozen books and a couple dozen online courses. His latest book, Building APIs with .NET will be released early in 2025. Liberty is a Senior SW Engineer for CNH and he was a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a Microsoft MVP.
This entry was posted in C#, Data, Essentials, Xamarin and tagged , , . Bookmark the permalink.