public void Start() { if (_db == null) { throw new Exception("Database not initialized."); } //create the SF client and connect to the instance _client.CreateClientAsync().Wait(); var existingObjects = ProcessObjectMetadata(); var tablesToSkip = ObjectsToSkip(); var namespacesToSkip = new List <string>() { "ffirule", "ffpd_dm", "ffvat", "ffbf", "fferpcore", "ffps", "pi", "PowerLoader" }; //if you want to delete any tables not realted to existing salesforce objects RemoveTables(existingObjects); foreach (var o in existingObjects.OrderBy(O => O.QualifiedApiName)) { Boolean isHistoryTable = o.QualifiedApiName != "LoginHistory" && o.QualifiedApiName.EndsWith("History"); //filter the type of objects to synchronize if (o.IsQueryable && !o.IsDeprecatedAndHidden && !o.IsCustomSetting) { //do not sync Feed, Share and Tag tables for the objects. if (o.QualifiedApiName.EndsWith("Feed") || o.QualifiedApiName.EndsWith("Share") || o.QualifiedApiName.EndsWith("Tag")) { continue; } string fixedName = o.QualifiedApiName; if (isHistoryTable) { fixedName = o.QualifiedApiName.Substring(0, o.QualifiedApiName.Length - 7); if (fixedName.EndsWith("__")) { fixedName += "__c"; } } if (tablesToSkip.Contains(o.QualifiedApiName) || tablesToSkip.Contains(fixedName)) { //delete the tbable in case it exists if (_db.ExistTable(_schemaObjects, o.QualifiedApiName)) { _db.ExecuteSQL("DROP TABLE [" + _schemaObjects + "].[" + o.QualifiedApiName + "]"); } continue; } //skip objects from a namespace bool skipNamespace = false; foreach (var name in namespacesToSkip) { if (o.QualifiedApiName.StartsWith(name + "__")) { skipNamespace = true; break; } } if (skipNamespace) { continue; } //some objects need filter to be queried, so we skip them List <string> nonQueriableObjects = new List <string>() { "ContentDocumentLink", "ContentFolderItem" }; if (nonQueriableObjects.Contains(o.QualifiedApiName)) { continue; } Console.WriteLine(o.Label + " | " + o.QualifiedApiName + " | " + o.IsQueryable); string dateFieldName; var oDefinition = UpdateObjectDefinition(o, out dateFieldName); //cannot find a date field, skip the object if (oDefinition == null) { continue; } //remove fields that we don't want to download. --> this will exclude BLOB and long text fields oDefinition.fields = oDefinition.fields.Where(F => F.type != "base64" && (F.type != "textarea" || (F.type == "textarea" && F.length <= 32768))).ToList(); bool isNewTable = false; if (!_db.ExistTable(_schemaObjects, o.QualifiedApiName)) { isNewTable = true; } else { //if there are any new fields, recreate the table var existingColumns = _db.GetDataTable("select * from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] where 1 = 2").Columns; foreach (var field in oDefinition.fields) { Boolean found = false; foreach (DataColumn c in existingColumns) { if (c.ColumnName.ToLower() == field.name.ToLower()) { found = true; break; } } if (!found) { isNewTable = true; break; } } if (isNewTable) { _db.ExecuteSQL("drop table [" + _schemaObjects + "].[" + o.QualifiedApiName + "]"); } } if (isNewTable) { CreateTable(o.QualifiedApiName, oDefinition); } //find the last modified/moodstamp date for this object object lastSync = null; if (!string.IsNullOrEmpty(dateFieldName)) { lastSync = _db.ExecuteScalar("select top 1 [" + dateFieldName + "] from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] order by [" + dateFieldName + "] desc"); } bool queryAll = !oDefinition.fields.Any(F => F.name == "IsDeleted"); //use QueryAll only if there is no IsDeleted Field //if there is no lastsync might need to load records a bit at a time long totalCount = 0; long currentCount = -1; if (lastSync == null && (!o.IsLayoutable || o.QualifiedApiName == "ContentVersion" || o.QualifiedApiName == "ContentDocument" || o.QualifiedApiName == "ContentDocumentLink")) // ensure content documents go through this piece { while (true) { try { var totalCountQuery = _client.Client.QueryAsync <dynamic>("SELECT COUNT() FROM " + o.QualifiedApiName + (queryAll ? "" : " WHERE IsDeleted = False")).Result; totalCount = totalCountQuery.TotalSize; break; } catch { System.Threading.Thread.Sleep(1000); } } Console.WriteLine(" | Total Count: " + totalCount); if (totalCount < 5000) { totalCount = 0; } } else { Console.WriteLine(); } string lastId = null; string query; string nextPageUrl = null; List <dynamic> records = null; int pageCount = 0; List <string> idList = null; //if no timestamp check the latest inserted id if (string.IsNullOrEmpty(dateFieldName)) { var lastFoundId = _db.ExecuteScalar("select top 1 [Id] from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] order by [Id] desc"); if (lastFoundId != null) { lastId = lastFoundId.ToString(); } } Boolean useLimit = (o.QualifiedApiName == "ContentVersion" || o.QualifiedApiName == "ContentDocument"); /* Find New / Updated Records*/ /*===========================*/ while (currentCount < totalCount) { query = "SELECT " + string.Join(",", oDefinition.fields.Select(D => D.name)) + " FROM " + o.QualifiedApiName; if (totalCount > 0) { if (!string.IsNullOrEmpty(lastId)) { if (!string.IsNullOrEmpty(dateFieldName)) { lastSync = _db.ExecuteScalar("select top 1 [" + dateFieldName + "] from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] order by [" + dateFieldName + "] desc"); //need to refresh the latest record inserted query += " WHERE " + dateFieldName + " >= " + ((DateTime)lastSync).ToString("yyyy-MM-ddTHH:mm:ssZ") + " AND ID > '" + lastId + "' "; } else { lastSync = _db.ExecuteScalar("select top 1 ID from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] order by ID desc"); //need to refresh the latest record inserted query += " WHERE ID > '" + lastId + "' "; } } if (!string.IsNullOrEmpty(dateFieldName)) { query += " ORDER BY " + dateFieldName + ", ID ASC " + (useLimit ? "LIMIT 5000" : ""); } else { query += " ORDER BY ID ASC " + (useLimit ? "LIMIT 5000" : "");; } } else { if (lastSync != null) { if (!string.IsNullOrEmpty(dateFieldName)) { query += " WHERE " + dateFieldName + " > " + ((DateTime)lastSync).ToString("yyyy-MM-ddTHH:mm:ssZ") + ""; } else { query += " WHERE Id > '" + lastSync.ToString() + "'"; } } if (!string.IsNullOrEmpty(dateFieldName)) { query += " ORDER BY " + dateFieldName + " ASC"; } else { query += " ORDER BY ID ASC"; } } nextPageUrl = null; records = null; pageCount = 0; idList = null; while (!string.IsNullOrEmpty(nextPageUrl) || pageCount == 0) { pageCount++; Console.SetCursorPosition(1, Console.CursorTop - 1); Console.WriteLine("| Page: " + pageCount + " - Records: " + currentCount.ToString("#,##0")); Tuple <List <dynamic>, string> recordsQuery; while (true) { try { recordsQuery = _client.GetRecordsPaged(query, nextPageUrl, queryAll).Result; break; } catch (Exception ex) { //ignore the exception and keep trying //too many times SF times out } } records = recordsQuery.Item1; nextPageUrl = recordsQuery.Item2; //get an empty data table to populate from the records retrieved DataTable dt = _db.GetDataTable("select * from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] where 1 = 2"); foreach (dynamic entry in records) { var row = dt.NewRow(); foreach (var field in oDefinition.fields) { if (entry[field.name] is Newtonsoft.Json.Linq.JObject) { continue; } if (entry[field.name].Value is DateTime) { row[field.name] = ((DateTime)entry[field.name].Value).ToUniversalTime(); } else { try { row[field.name] = entry[field.name].Value ?? DBNull.Value; } catch { row[field.name] = DBNull.Value; } } if (DataType(field).StartsWith("varchar")) { if (row[field.name] != DBNull.Value && field.length > 0 && row[field.name].ToString().Length > field.length) { row[field.name] = row[field.name].ToString().Substring(0, field.length); } } } dt.Rows.Add(row); lastId = row["Id"].ToString(); } records.Clear(); idList = new List <string>(); if (dt.Rows.Count > 0) { //delete any existing matches if ((lastSync != null || lastId != null) && !isNewTable) { foreach (DataRow r in dt.Rows) { idList.Add(r["id"].ToString()); if (idList.Count > 50) { _db.ExecuteSQL("delete from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] where ID in ('" + string.Join("','", idList) + "')"); idList.Clear(); } } if (idList.Count > 0) { _db.ExecuteSQL("delete from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] where ID in ('" + string.Join("','", idList) + "')"); } } dt.TableName = o.QualifiedApiName; _db.InsertDataTable(dt); } currentCount += dt.Rows.Count; if (dt.Rows.Count == 0 || currentCount == totalCount - 1) { currentCount = totalCount; } } GC.Collect(); Console.WriteLine("| Total Count: " + currentCount); } /* Find Deleted Records*/ /*=====================*/ if (!oDefinition.fields.Any(F => F.name.ToLower() == "IsDeleted".ToLower())) { continue; //if not isdeleted field then continue } if (isNewTable) { continue; } query = "SELECT Id FROM " + o.QualifiedApiName + " WHERE IsDeleted = TRUE "; if (lastSync != null) { query = query + " AND " + dateFieldName + " > " + ((DateTime)lastSync).ToString("yyyy-MM-ddTHH:mm:ssZ") + ""; } nextPageUrl = null; records = null; pageCount = 0; idList = null; while (!string.IsNullOrEmpty(nextPageUrl) || pageCount == 0) { pageCount++; var recordsQuery = _client.GetRecordsPaged(query, nextPageUrl, true).Result; records = recordsQuery.Item1; nextPageUrl = recordsQuery.Item2; idList = new List <string>(); foreach (dynamic entry in records) { idList.Add(entry["Id"].ToString()); if (idList.Count > 100) { _db.ExecuteSQL("delete from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] where [Id] in ('" + string.Join("','", idList) + "')"); idList.Clear(); } } if (idList.Count > 0) { _db.ExecuteSQL("delete from [" + _schemaObjects + "].[" + o.QualifiedApiName + "] where [Id] in ('" + string.Join("','", idList) + "')"); } } } GC.Collect(); } }