Syncing a Local SQLite DB With a Network DB

Post Views: 749

Table of Contents

Syncing a Local SQLite DB With a Network DB

There are many occasions where we don’t want to constantly talk to a database across a network due to latency or chattiness A better approach maybe to have a local database that the application uses and is synchronized to the network database when required. This post will show a simple example of this using a Maria Database as the network Database and a SQLite Database as the local client database.

This builds on the last post;

http://www.catiawidgets.net/2022/02/17/wpf-dapper-and-mysql-a-simple-example/

NuGet Package

We will need to add System.Data.SQLite.Core to the previous project, so we can interact with a SQLite database.

SQLite Database

I downloaded SQLite Studio from the following link. Then using this application I created a new SQLite Database and set its property’s to Copy If Newer and the Build Action to Content.

https://sqlitestudio.pl/

Within the SQLite Database add a new table called testtable, the same as the table in the MariaDB Database with the same data structure.

app.config

Within the app.config we will add a new connection string for the SQLite database called localdb, using a relative path to the SQLite folder and database, relative to the exe.

//app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="testtable" connectionString="Server=localhost;Database=testdb;uid=CatiaWidget;pwd=Savannah!2"/>
    <add name="localdb" connectionString="Data Source=.\SQLite\LocalDB.db;Version=3;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Helper

The Helper class allows us to retrieve the connection string from the app.config file.

//Helper.cs

using System.Configuration;

namespace WPF_Dapper
{
    public static class Helper
    {
        public static string Connection(string name)
        {
            return ConfigurationManager.ConnectionStrings[name].ConnectionString;
        }
    }
}

Querys

Within the Query’s class we will add two new Query’s one to Delete all data from an SQLite table, and another to insert new data into an SQLite table.

//Querys.cs

using System.Text;

namespace WPF_Dapper
{
    public static class Querys
    {
        public static string GetAllNames()
        {
            StringBuilder stringBuilder = new();
            stringBuilder.Append("Select * from testtable");

            return stringBuilder.ToString();
        }

        public static string dboGetAllNames()
        {
            return "ProcGetAllNames";
        }

        public static string dboGetAllNamesLike()
        {
            return "ProcGetAllNamesLike";
        }
      
        public static string insertSQLiteTable(string iTableName,string iValues)
        {
            StringBuilder stringBuilder = new();
            stringBuilder.Append($"INSERT INTO {iTableName} VALUES {iValues}");

            return stringBuilder.ToString();
        }

        public static string deleteSQLiteTable(string iTableName)
        {
            StringBuilder stringBuilder = new();
            stringBuilder.Append($"Delete from {iTableName}");

            return stringBuilder.ToString();
        }
    }
}

Data Access

Within the Data Access class, we will create new method that will perform two steps; the first to delete the table data and the second to insert the new data.

//DataAccess.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using Dapper;
using MySql.Data.MySqlClient;

namespace WPF_Dapper
{
    public interface IDataAccess
    {
        List<DataModel> GetDataModel(string connectionName);
        void WriteLocalDataModel(string connectionName, List<DataModel> iDataModel);
    }
    public class DataAccess : IDataAccess
    {
        public List<DataModel> GetDataModel(string connectionName)
        {
            using (IDbConnection connection = new MySqlConnection(Helper.Connection(connectionName)))
            {
                var values = new { Param1="Gary" };
                return connection.Query<DataModel>(Querys.dboGetAllNames(), values, commandType: CommandType.StoredProcedure).ToList();
            }
        }

        public void WriteLocalDataModel(string connectionName,List<DataModel> iDataModel)
        {
            using (IDbConnection connection = new SQLiteConnection(Helper.Connection(connectionName)))
            {
                connection.Execute(Querys.deleteSQLiteTable("testtable"));
                connection.Execute(Querys.insertSQLiteTable("testtable","(@ID,@Name)"), iDataModel);
            }
        }
    }
}

MainWindow.xaml.cs

Within the MainWindow.xaml.cs we can now call the Write Local Database method passing in the List of class data, and the connection name from the app.config file.

//MainWindow.xaml.cs

using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Windows;

namespace WPF_Dapper
{
    public partial class MainWindow : Window
    {
        private ObservableCollection<DataModel> _dataModels;
        public ObservableCollection<DataModel> DataModels
        {
            get { return _dataModels; }
            set { _dataModels = value; }
        }
        public MainWindow()
        {
            InitializeComponent();
            DataContext = this;

            TestConnection("testtable","localdb");
        }

        private void TestConnection(string connectionName, string connectionNameLocal)
        {
            IDataAccess db = new DataAccess();

            List<DataModel> datamodels = db.GetDataModel(connectionName);
            DataModels = new ObservableCollection<DataModel>(datamodels);

            db.WriteLocalDataModel(connectionNameLocal, datamodels);
        }
    }
}