Easy and convenient way to serialize SQLite tables with xamarin
Hi Community,
As a follow up to my previous post on Xamarin development, Today is about a common task which is serialization of SQLite tables as a CSV file (It could be any file actually, but in this specific case is a CSV file). At the same time, I’d like to also share with you a convenient way to pass across relevant information about Android.Content.Context to a PCL library that does not have nor support it.
Let’s get started by describing the way we serialize our SQLite tables as CSV files. Firstly, we define an interface that will be implemented by a helper class in the Xamarin Android project. Please note that its only method “SerializeTableAsCsv” is a generic method, therefore we will pass the model class (Table).
public interface ITableSerializer {
/// <summary>
/// Serializes the table as CSV.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conn">The connection.</param>
/// <param name="outputFilePath">The output file path.</param>
/// <returns></returns>
ExecutionResult SerializeTableAsCsv<T>(SQLite.Net.SQLiteConnection conn, string outputFilePath) where T: class, new();
}
public class TableSerializer : ITableSerializer {
/// <summary>
/// The reflection binding flags
/// </summary>
private const BindingFlags ReflectionBindingFlags = BindingFlags.DeclaredOnly | BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.Public;
/// <summary>
/// Serializes the table as CSV.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conn">The connection.</param>
/// <param name="outputFilePath">The output file path.</param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public ExecutionResult SerializeTableAsCsv<T>(SQLiteConnection conn, string outputFilePath) where T : class, new() {
List<TableMapping> mappings = null;
var retval = ExecutionResult.Empty;
var context = TinyIoC.TinyIoCContainer.Current.Resolve<ILightAndroidContext>();
if (conn != null && (mappings = conn.TableMappings?.ToList()).Count > 0 && context != null) {
var table = conn.TableMappings.FirstOrDefault(_ => typeof(T).ToString().Contains(_.TableName));
if (table != null) {
var workFolder = $"{context.Cache.Parent}/files/DB/Export";
var targetFile = outputFilePath ?? $"{workFolder}/{table.TableName}.csv";
try {
// If file exists, we'll delete it
if (File.Exists(targetFile))
File.Delete(targetFile);
// If Export directory doesn't exist, we'll create it
if (!Directory.Exists(workFolder))
Directory.CreateDirectory(workFolder);
SerializeHelper<T>(conn, targetFile);
retval = new ExecutionResult() { Succeeded = true, Tag = targetFile };
} catch (Exception ex) {
retval = new ExecutionResult() { Tag = targetFile, CaughtException = ex, Succeeded = false };
}
}
} else
throw new ArgumentException(Constants.UnableToSerializeTableWithMissingInformation);
return retval;
}
/// <summary>
/// Lines the builder.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t">The t.</param>
/// <param name="tv">The tv.</param>
/// <returns></returns>
private static string LineBuilder<T>(Type t, T tv) {
var retval = string.Empty;
var buffer = new StringBuilder();
var formatter = new Func<object, string>(p => p?.ToString() ?? string.Empty);
var props = t.GetProperties(ReflectionBindingFlags).ToList();
props?.ForEach(x => buffer.Append($"\"{formatter(x.GetValue(tv))}\","));
retval = buffer.ToString();
return retval.Substring(0, retval.Length - 1);
}
/// <summary>
/// Serializes the helper.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conn">The connection.</param>
/// <param name="targetFile">The target file.</param>
private static void SerializeHelper<T>(SQLiteConnection conn, string targetFile) where T : class, new() {
var newLine = string.Empty;
var rows = conn.Table<T>().ToList();
if (rows?.Count > 0) {
using (var csvFile = File.CreateText(targetFile)) {
rows.ForEach(x => {
if (!string.IsNullOrEmpty(newLine = LineBuilder(x.GetType(), x)))
csvFile.WriteLine(newLine);
});
csvFile.Flush();
csvFile.Close();
}
}
}
}
The only question now is how to invoke this method from the PCL library project? The answer is simple, we use a delegate (or Func) that points to the above implementation.
/*****************************/
/* Interface */
/*****************************/
public interface IDataService {
/// <summary>
/// Serializes the table as CSV.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="exportFunc">The export function.</param>
/// <returns></returns>
ExecutionResult SerializeTableAsCsv<T>(Func<SQLite.Net.SQLiteConnection, string, ExecutionResult> exportFunc);
}
/*****************************/
/* Implementation */
/*****************************/
public class DataService: IDataService {
/// <summary>
/// Serializes the table as CSV.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="exportFunc">The export function.</param>
/// <returns></returns>
public ExecutionResult SerializeTableAsCsv<T>(Func<SQLite.Net.SQLiteConnection, string, ExecutionResult> exportFunc) {
return exportFunc?.Invoke(Connection, null);
}
}
/*****************************/
/* Usage */
/*****************************/
private void ExportTablesForDebugging() {
//For debugging purposes only
#if DEBUG
var dataSvc = TinyIoC.TinyIoCContainer.Current.Resolve<IDataService>();
var debugUtil = TinyIoC.TinyIoCContainer.Current.Resolve<IDebugUtil>();
var csvExporter = TinyIoC.TinyIoCContainer.Current.Resolve<ITableSerializer>();
dataSvc?.SerializeTableAsCsv<Log>(csvExporter.SerializeTableAsCsv<Log>);
#endif
}
Up until now, we are able to serialize our SQLite table, but in case you didn’t notice I’m able to pull information about the Android Context which is non-existent in PCL, in order to make it available I have come up with my ILightAndroidContext implementation that allows me to get information about the application.
#region "Required structs"
/// <summary>
///
/// </summary>
public struct CacheDir {
/// <summary>
/// Gets or sets the absolute path.
/// </summary>
/// <value>
/// The absolute path.
/// </value>
public string AbsolutePath {
get; set;
}
/// <summary>
/// Gets or sets the canonical path.
/// </summary>
/// <value>
/// The canonical path.
/// </value>
public string CanonicalPath {
get; set;
}
/// <summary>
/// Gets or sets the parent.
/// </summary>
/// <value>
/// The parent.
/// </value>
public string Parent {
get; set;
}
/// <summary>
/// Gets or sets the path.
/// </summary>
/// <value>
/// The path.
/// </value>
public string Path {
get; set;
}
/// <summary>
/// Gets or sets the total space.
/// </summary>
/// <value>
/// The total space.
/// </value>
public long TotalSpace {
get; set;
}
/// <summary>
/// Gets or sets the usable space.
/// </summary>
/// <value>
/// The usable space.
/// </value>
public long UsableSpace {
get; set;
}
}
/// <summary>
///
/// </summary>
public struct AppContext {
/// <summary>
/// The package code path
/// </summary>
public string PackageCodePath {
get; set;
}
/// <summary>
/// The package name
/// </summary>
public string PackageName {
get; set;
}
}
#endregion
/**********************************/
/* Interface */
/**********************************/
/// <summary>
///
/// </summary>
public interface ILightAndroidContext {
/// <summary>
/// Gets or sets the raw context.
/// </summary>
/// <value>
/// The raw context.
/// </value>
object RawContext {
get;
}
/// <summary>
/// Gets or sets the application context.
/// </summary>
/// <value>
/// The application context.
/// </value>
AppContext ApplicationContext {
get; set;
}
/// <summary>
/// Gets or sets the cache.
/// </summary>
/// <value>
/// The cache.
/// </value>
CacheDir Cache {
get; set;
}
/// <summary>
/// Gets or sets the name of the context.
/// </summary>
/// <value>
/// The name of the context.
/// </value>
string ContextName {
get; set;
}
}
/**********************************/
/* Implementation */
/**********************************/
public class LightAndroidContext : ILightAndroidContext {
/// <summary>
/// The raw context
/// </summary>
private static object _rawContext;
/// <summary>
/// The has been initialized
/// </summary>
private bool _hasBeenInitialized = false;
/// <summary>
/// The context name
/// </summary>
private string _contextName;
/// <summary>
/// The application context
/// </summary>
private AppContext _applicationContext = new AppContext();
/// <summary>
/// The cache
/// </summary>
private CacheDir _cache = new CacheDir();
/// <summary>
/// Gets or sets the application context.
/// </summary>
/// <value>
/// The application context.
/// </value>
public AppContext ApplicationContext {
get {
return _applicationContext;
}
set {
throw new Exception(Constants.UnableToWriteReadOnlyPropertyException);
}
}
/// <summary>
/// Gets or sets the cache.
/// </summary>
/// <value>
/// The cache.
/// </value>
public CacheDir Cache {
get {
return _cache;
}
set {
throw new Exception(Constants.UnableToWriteReadOnlyPropertyException);
}
}
/// <summary>
/// Gets or sets the name of the context.
/// </summary>
/// <value>
/// The name of the context.
/// </value>
public string ContextName {
get {
return _contextName;
}
set {
throw new Exception(Constants.UnableToWriteReadOnlyPropertyException);
}
}
/// <summary>
/// Gets or sets the raw context.
/// </summary>
/// <value>
/// The raw context.
/// </value>
public object RawContext {
get {
return _rawContext;
}
private set {
lock (new object()) {
if (_rawContext == null)
_rawContext = value;
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="LightAndroidContext"/> class.
/// </summary>
/// <param name="context">The context.</param>
/// <exception cref="ArgumentException"></exception>
public LightAndroidContext(object context) {
if (!_hasBeenInitialized)
if (!IsAvalidAndroidContext(context))
throw new ArgumentException(Constants.UnableToConstructLightContextWithoutProperContext);
}
/// <summary>
/// Determines whether [is avalid android context] [the specified context].
/// </summary>
/// <param name="context">The context.</param>
/// <returns>
/// <c>true</c> if [is avalid android context] [the specified context]; otherwise, <c>false</c>.
/// </returns>
private bool IsAvalidAndroidContext(object context) {
var retval = false;
Type targetType = null;
PropertyInfo appInfo, cacheDir;
if (context != null && (targetType = context.GetType()) != null) {
cacheDir = targetType.GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("CacheDir"));
appInfo = targetType.GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("ApplicationContext"));
if (appInfo != null && cacheDir != null) {
try {
var appInfoValue = appInfo.GetValue(context);
var packageName = appInfoValue.GetType().GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("PackageName"));
_applicationContext.PackageName = packageName.GetValue(context).ToString();
var packageCodePath = appInfoValue.GetType().GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("PackageCodePath"));
_applicationContext.PackageCodePath = packageCodePath.GetValue(context).ToString();
var cache = cacheDir.GetValue(context);
var absolutePath = cache.GetType().GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("AbsolutePath"));
_cache.AbsolutePath = absolutePath.GetValue(cache).ToString();
var canonicalPath = cache.GetType().GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("CanonicalPath"));
_cache.CanonicalPath = canonicalPath.GetValue(cache).ToString();
var parent = cache.GetType().GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("Parent"));
_cache.Parent = parent.GetValue(cache).ToString();
var path = cache.GetType().GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("Path"));
_cache.Path = path.GetValue(cache).ToString();
var totalSpace = cache.GetType().GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("TotalSpace"));
_cache.TotalSpace = long.Parse(totalSpace.GetValue(cache).ToString());
var usableSpace = cache.GetType().GetRuntimeProperties().FirstOrDefault(_ => _.Name.Equals("UsableSpace"));
_cache.UsableSpace = long.Parse(usableSpace.GetValue(cache).ToString());
RawContext = context;
_contextName = context.ToString();
retval = true;
} catch {
// Safe to ignore exception. Reflection related
}
}
}
return retval;
}
}
Regards,
Angel
Share this:
Related
Filed under: Microsoft - @ September 12, 2016 11:26 am
Tags: .NET, PCL, Visual C++, Xamarin