/// <summary> /// Get all changes fro SQLite Database /// </summary> /// <param name="schema">All Tables</param> /// <param name="lastModifiedDate">Changes since this date</param> internal IEnumerable <SQLiteOfflineEntity> GetChanges(OfflineSchema schema, DateTime lastModifiedDate) { List <SQLiteOfflineEntity> lstChanges = new List <SQLiteOfflineEntity>(); using (SQLiteConnection connection = new SQLiteConnection(localFilePath)) { try { foreach (var ty in schema.Collections) { // Get mapping from my type var map = manager.GetMapping(ty); // Create query to select changes var querySelect = SQLiteConstants.SelectChanges; var columnsDcl = new List <String>(); var columnsPK = new List <String>(); // Foreach columns, create the tsql command to execute foreach (var c in map.Columns) { if (!c.IsPK) { columnsDcl.Add("[s].[" + c.Name + "]"); } // If it's the PK, add it from Tracking (because of deleted items not in real table if (c.IsPK) { columnsDcl.Add("[t].[" + c.Name + "]"); columnsPK.Add("[s].[" + c.Name + "] = [t].[" + c.Name + "]"); } } var decl = string.Join(",\n", columnsDcl.ToArray()); var pk = string.Join(" \nAND ", columnsPK.ToArray()); querySelect = String.Format(querySelect, map.TableName, pk, decl); // Prepare command using (var stmt = connection.Prepare(querySelect)) { try { // Set Values BindParameter(stmt, 1, lastModifiedDate); // Get mapping form the statement var cols = new TableMapping.Column[map.Columns.Length]; // Foreach column, get the property in my object for (int i = 0; i < cols.Length; i++) { var name = stmt.ColumnName(i); var c = map.FindColumn(name); if (c != null) { cols[i] = map.FindColumn(name); } } // While row is available //while (await stmt.StepAsync().AsTask().ConfigureAwait(false)) while (stmt.Step() == SQLiteResult.ROW) { // Create the object SQLiteOfflineEntity obj = (SQLiteOfflineEntity)Activator.CreateInstance(map.MappedType); for (int i = 0; i < cols.Length; i++) { if (cols[i] == null) { continue; } // Read the column var val = ReadCol(stmt, i, cols[i].ColumnType); // Set the value cols[i].SetValue(obj, val); } // Read the Oem Properties var newIndex = map.Columns.Count(); obj.ServiceMetadata = new OfflineEntityMetadata(); obj.ServiceMetadata.IsTombstone = (Boolean)ReadCol(stmt, newIndex, typeof(Boolean)); obj.ServiceMetadata.Id = (String)ReadCol(stmt, newIndex + 1, typeof(String)); obj.ServiceMetadata.ETag = (String)ReadCol(stmt, newIndex + 2, typeof(String)); String absoluteUri = (String)ReadCol(stmt, newIndex + 3, typeof(String)); obj.ServiceMetadata.EditUri = String.IsNullOrEmpty(absoluteUri) ? null : new Uri(absoluteUri); lstChanges.Add(obj); } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } finally { stmt.Reset(); stmt.ClearBindings(); } } } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } } return(lstChanges); }
/// <summary> /// Get all changes fro SQLite Database /// </summary> /// <param name="schema">All Tables</param> /// <param name="lastModifiedDate">Changes since this date</param> /// <param name="uploadBatchSize">Maximum number of rows to upload</param> internal IEnumerable <SQLiteOfflineEntity> GetChanges(OfflineSchema schema, DateTime lastModifiedDate, int uploadBatchSize) { List <SQLiteOfflineEntity> lstChanges = new List <SQLiteOfflineEntity>(); using (SQLiteDatabaseConnection connection = SQLitePCL.pretty.SQLite3.Open(localFilePath)) { try { foreach (var ty in schema.Collections) { // Get mapping from my type var map = manager.GetMapping(ty); // Create query to select changes var querySelect = SQLiteConstants.SelectChanges; var columnsDcl = new List <String>(); var columnsPK = new List <String>(); // Foreach columns, create the tsql command to execute foreach (var c in map.Columns) { if (!c.IsPK) { columnsDcl.Add("[s].[" + c.Name + "]"); } // If it's the PK, add it from Tracking (because of deleted items not in real table if (c.IsPK) { columnsDcl.Add("[t].[" + c.Name + "]"); columnsPK.Add("[s].[" + c.Name + "] = [t].[" + c.Name + "]"); } } var decl = string.Join(",\n", columnsDcl.ToArray()); var pk = string.Join(" \nAND ", columnsPK.ToArray()); querySelect = String.Format(querySelect, map.TableName, pk, decl); // add limit if specified if (uploadBatchSize > 0) { querySelect += $" LIMIT {uploadBatchSize}"; } try { // Get mapping form the statement var cols = new TableMapping.Column[map.Columns.Length]; bool firstRow = true; // While row is available foreach (var row in connection.Query(querySelect, P(lastModifiedDate))) { if (firstRow) { // Foreach column, get the property in my object for (int i = 0; i < cols.Length; i++) { var name = row[i].ColumnInfo.Name; var c = map.FindColumn(name); if (c != null) { cols[i] = map.FindColumn(name); } } firstRow = false; } // Create the object SQLiteOfflineEntity obj = (SQLiteOfflineEntity)Activator.CreateInstance(map.MappedType); for (int i = 0; i < cols.Length; i++) { if (cols[i] == null) { continue; } // Read the column var val = ReadCol(row, i, cols[i].ColumnType); // Set the value cols[i].SetValue(obj, val); } // Read the Oem Properties var newIndex = map.Columns.Count(); obj.ServiceMetadata = new OfflineEntityMetadata(); obj.ServiceMetadata.IsTombstone = (bool)ReadCol(row, newIndex, typeof(Boolean)); obj.ServiceMetadata.Id = (String)ReadCol(row, newIndex + 1, typeof(String)); obj.ServiceMetadata.ETag = (String)ReadCol(row, newIndex + 2, typeof(String)); String absoluteUri = (String)ReadCol(row, newIndex + 3, typeof(String)); obj.ServiceMetadata.EditUri = String.IsNullOrEmpty(absoluteUri) ? null : new Uri(absoluteUri); lstChanges.Add(obj); } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } // if we are batching uploads and the upload rowcount has been reached, skip if (uploadBatchSize > 0 && lstChanges.Count >= uploadBatchSize) { break; } } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } } // if we are batching uploads, limit the in-memory result set as well if (uploadBatchSize > 0) { return(lstChanges.Take(uploadBatchSize)); } return(lstChanges); }