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();
}
}
