I've written a class that synchronizes a table between two databases using Dapper. The public and private Methods in the class are generic and the generic parameter is the POCO class that is being synchronized.
var Sync = new syncClass(localConn, remoteConn); await Sync.SyncTable<myTablePoco1>(); I feel a little back story will help:
For simplicity sake, I want to wrap all of the synchronization inside a serializable transaction(pushing and pulling), so that if anything goes wrong, I can rollback.
Next, I want to synchronize multiple tables and trying to come up with an appropriate way manage the multiple tables. The consumer could write multiple lines:
await Sync.StartTransaction(); await Sync.SyncTable<myTablePoco1>(); ... ... await Sync.SyncTable<myTablePoco10>(); await Sync.Complete(); I was trying to find a way to encapulate all of the table syncing like so:
Sync.AddTablePoco(typeof(MyTablePoco1)); ... Sync.AddTablePoco(typeof(MyTablePoco1)); ... await Sync.SyncAllTables(); Public async Task SyncAllTables() { foreach (var pocoClass in TableList) { Sync.SyncTable<pocoClass>(); <-- compiler does not like this } } I have started to re-write all the generic methods to something with a signature like this:
public async Task SyncTable(Type tableEntity)
At some point down the line of converting I run into this scenario :
private async Task<Ienumerable<?>> FindRecordsToSync(Type tableEntity) <--cannot return a generic type How to handle this (This method uses Dapper's QueryAsync<T>)
Do I need to use Dynamic types? Is that a code smell?
I'm a little stuck and looking for some direction on how to accomplish this.
How can I do this? Do I stick with the generic methods and just define all the syncing at compile time? Or is there a more elegant/cleanway?
(I've looked into reflection as an option to invoke a generic method, but would prefer a non-reflection way.)
More Code Added:
public static async Task<int> UpsertAsync<T>(this IDbConnection db, IEnumerable<T> entitiestoUpsert, IDbTransaction transaction = null) where T : class { var contribType = typeof(SqlMapperExtensions); var type = typeof(T); var tableName = contribType.GetTableName(type); //GetTableName var sbColumnList = new StringBuilder(null); var allProperties = contribType.TypePropertiesCache(type); //TypePropertiesCache(type); var keyProperties = contribType.KeyPropertiesCache(type);// KeyPropertiesCache(type).ToList(); var computedProperties = contribType.ComputedPropertiesCache(type);// ComputedPropertiesCache(type); var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList(); //added need to include key column for upsert var allPropertiesExceptComputed = allProperties.Except(computedProperties).ToList(); var explicitKeyProperties = contribType.ExplicitKeyPropertiesCache(type); // ExplicitKeyPropertiesCache(type); if (keyProperties.Count == 0 && explicitKeyProperties.Count == 0) throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property"); keyProperties.AddRange(explicitKeyProperties); var columns = allPropertiesExceptComputed.Select(x => x.Name).ToList(); var dbConnectionType = db.GetType().Name; int result; switch (dbConnectionType) { case "SQLiteConnection": result = await db.ReplaceInto<T>(entitiestoUpsert, columns, tableName, transaction); break; case "MySqlConnection": result = await db.MySQLUpsert<T>(entitiestoUpsert, columns, tableName, keyProperties.First().Name, transaction); break; default: throw new Exception($"No method found for database type: {dbConnectionType}"); } return result; } Here's the ReplaceInto code:
private static async Task<int> ReplaceInto<Tentity>(this IDbConnection db, IEnumerable<Tentity> records, List<string> columns, string intoTableName, IDbTransaction transaction = null) { var intoColumns = String.Join(",", columns); var valueSb = new StringBuilder(); var inserts = new List<string>(); var dynamicParams = new DynamicParameters(); long i = 0; var type = records.First().GetType(); foreach (var r in records) { var valueList = new List<string>(); foreach (var column in columns) { var value = type.GetProperty(column)?.GetValue(r, null); var p = $"p{i}"; dynamicParams.Add(p, value); valueList.Add($"@{p}"); i++; } valueSb.Append("("); valueSb.Append(String.Join(",", valueList)); valueSb.Append(")"); inserts.Add(valueSb.ToString()); valueSb.Clear(); } var cmd = $"REPLACE INTO {intoTableName} ({intoColumns}) VALUES {String.Join(",", inserts)}"; return await db.ExecuteAsync(cmd, dynamicParams, transaction); } I'm trying to follow some of the patterns/conventions that Dapper uses, in particular Dapper.Contrib.Extensions and how the use reflection on the POCO class to build the queries.
FindRecordsToSyncand how it uses the resultset?IEnumerable<?>in your example?