/// <summary>
/// Synchronizes registered cache data into the current connected server.
/// </summary>
/// <param name="connection"></param>
public static void Refresh(IDbConnection connection)
{
if (_cacheddataset == null)
{
_cacheddataset = SCMS.XmlToDataSet(CachePath);
}
if (_cacheddataset == null)
{
if (connection != null)
{
_cacheddataset = MySql.GetDataSet(connection);
}
}
else
{
List <string> _tables = MySql.GetTables(connection);
if (_tables != null)
{
foreach (string _tablename in _tables)
{
SyncTable(connection, _tablename);
}
}
}
Save();
}
/// <summary>
/// Synchronizes the specified database table using the supplied database connection.
/// </summary>
/// <param name="connection"></param>
/// <param name="tablename"></param>
/// <param name="primarykey"></param>
public static void SyncTable(IDbConnection connection, string tablename, string primarykey)
{
if (_cacheddataset == null)
{
_cacheddataset = SCMS.XmlToDataSet(CachePath);
}
if (_cacheddataset == null)
{
if (connection != null)
{
_cacheddataset = new DataSet();
_cacheddataset.DataSetName = connection.ConnectionString.ConnectionStringValue(ConnectionStringSection.Database);
}
}
if (_cacheddataset != null)
{
DataTable _table = null;
if (_cacheddataset.Tables.Contains(tablename))
{
_table = _cacheddataset.Tables[tablename];
DataTable _updated = null;
if (_table.Columns.Contains("LastModified") ||
_table.Columns.Contains("DateAndTime"))
{
DateTime _lastupdate = VisualBasic.CDate("1/1/1900");
string _datetimefield = "DateAndTime";
if (_table.Columns.Contains("LastModified"))
{
_datetimefield = "LastModified";
}
object _maxdate = _table.Compute("MAX([" + _datetimefield + "])", "");
if (VisualBasic.IsDate(_maxdate))
{
_lastupdate = VisualBasic.CDate(_maxdate);
}
_updated = _updated.LoadData(SCMS.Connection, "SELECT `OldValue`, `NewValue` FROM `updateditems` AS `u` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "') AND (`LastModified` >= '" + _lastupdate.ToSqlValidString(true) + "')");
}
else
{
_updated = _updated.LoadData(SCMS.Connection, "SELECT `OldValue`, `NewValue` FROM `updateditems` AS `u` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "')");
}
if (_updated != null)
{
string _pk = primarykey;
if (string.IsNullOrEmpty(_pk.RLTrim()))
{
for (int i = 0; i <= (_table.Columns.Count - 1); i++)
{
if (_table.Columns[i].Unique)
{
_pk = _table.Columns[i].ColumnName; break;
}
}
}
if (!string.IsNullOrEmpty(_pk.RLTrim()))
{
for (int i = 0; i <= (_updated.Rows.Count - 1); i++)
{
DataRow _row = _updated.Rows[i];
DataRow[] _rows = _table.Select("CONVERT([" + _pk + "], System.String) = '" + _row["OldValue"].ToString().ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow[] _exists = _table.Select("CONVERT([" + _pk + "], System.String) = '" + _row["NewValue"].ToString().ToSqlValidString(true) + "'");
if (_exists.Length <= 0)
{
_rows[0][_pk] = _row["NewValue"];
}
}
}
_table.AcceptChanges();
}
_updated.Dispose(); _updated = null;
Materia.RefreshAndManageCurrentProcess();
}
if (_table.Columns.Contains("LastModified") ||
_table.Columns.Contains("DateAndTime"))
{
DateTime _lastupdate = VisualBasic.CDate("1/1/1900");
string _datetimefield = "DateAndTime";
if (_table.Columns.Contains("LastModified"))
{
_datetimefield = "LastModified";
}
object _maxdate = _table.Compute("MAX([" + _datetimefield + "])", "");
if (VisualBasic.IsDate(_maxdate))
{
_lastupdate = VisualBasic.CDate(_maxdate);
}
DataTable _updates = null;
_updates = _updates.LoadData(connection, "SELECT * FROM `" + tablename + "` WHERE (`" + _datetimefield + "` >= '" + _lastupdate.ToSqlValidString(true) + "')");
if (_updates != null)
{
if (_updates.Rows.Count > 0)
{
try { _table.Merge(_updates, false, MissingSchemaAction.Add); }
catch (Exception ex) { SCMS.LogError("Cache", ex); }
if (_table.Columns.Contains("Voided"))
{
DataRow[] _rows = _table.Select("[Voided] = 1");
if (_rows.Length > 0)
{
System.Collections.IEnumerator _enumerators = _rows.GetEnumerator();
while (_enumerators.MoveNext())
{
((DataRow)_enumerators.Current).Delete();
}
}
}
_table.AcceptChanges();
}
try { _updates.Dispose(); }
catch { }
finally { _updates = null; Materia.RefreshAndManageCurrentProcess(); }
}
DataTable _deleted = null;
if (_table.Columns.Contains("LastModified") ||
_table.Columns.Contains("DateAndTime"))
{
_deleted = _deleted.LoadData(connection, "SELECT * FROM `deleteditems` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "') AND (`LastModified` >= '" + _lastupdate.ToSqlValidString(true) + "');");
}
else
{
_deleted = _deleted.LoadData(connection, "SELECT * FROM `deleteditems` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "');");
}
if (_deleted != null)
{
if (_deleted.Rows.Count > 0)
{
for (int i = 0; i <= (_deleted.Rows.Count - 1); i++)
{
DataRow _delrow = _deleted.Rows[i];
string _pk = primarykey;
if (string.IsNullOrEmpty(_pk.RLTrim()))
{
foreach (DataColumn _col in _table.Columns)
{
if (_col.Unique)
{
_pk = _col.ColumnName; break;
}
}
}
if (!String.IsNullOrEmpty(_pk.RLTrim()))
{
DataRow[] _delrows = _table.Select("CONVERT([" + _pk + "], System.String) LIKE '" + _delrow["Value"].ToString().ToSqlValidString(true) + "'");
if (_delrows.Length > 0)
{
System.Collections.IEnumerator _selrows = _delrows.GetEnumerator();
while (_selrows.MoveNext())
{
((DataRow)_selrows.Current).Delete();
}
_table.AcceptChanges(); Materia.RefreshAndManageCurrentProcess();
}
}
}
}
_deleted.Dispose(); _deleted = null;
Materia.RefreshAndManageCurrentProcess();
}
}
}
else
{
if (connection != null)
{
_table = _table.LoadData(connection, "SELECT * FROM `" + tablename + "`");
if (_table != null)
{
if (!string.IsNullOrEmpty(primarykey.RLTrim()))
{
if (_table.Constraints.Count > 0 &&
_table.Columns.Contains(primarykey))
{
_table.Constraints.Clear();
}
if (_table.Constraints.Count <= 0 &&
_table.Columns.Contains(primarykey))
{
_table.Constraints.Add("PK", _table.Columns[primarykey], true);
}
}
if (_table.Columns.Contains("Voided"))
{
DataRow[] _rows = _table.Select("[Voided] = 1");
if (_rows.Length > 0)
{
System.Collections.IEnumerator _enumerators = _rows.GetEnumerator();
while (_enumerators.MoveNext())
{
((DataRow)_enumerators.Current).Delete();
}
}
}
DataTable _deleted = null;
_deleted = _deleted.LoadData(connection, "SELECT * FROM `deleteditems` WHERE (`TableName` LIKE '" + tablename.ToSqlValidString() + "');");
if (_deleted != null)
{
if (_deleted.Rows.Count > 0)
{
for (int i = 0; i <= (_deleted.Rows.Count - 1); i++)
{
DataRow _delrow = _deleted.Rows[i];
string _pk = primarykey;
if (string.IsNullOrEmpty(_pk.RLTrim()))
{
foreach (DataColumn _col in _table.Columns)
{
if (_col.Unique)
{
_pk = _col.ColumnName; break;
}
}
}
if (!String.IsNullOrEmpty(_pk.RLTrim()))
{
DataRow[] _delrows = _table.Select("CONVERT([" + _pk + "], System.String) LIKE '" + _delrow["Value"].ToString().ToSqlValidString(true) + "'");
if (_delrows.Length > 0)
{
System.Collections.IEnumerator _selrows = _delrows.GetEnumerator();
while (_selrows.MoveNext())
{
((DataRow)_selrows.Current).Delete();
}
_table.AcceptChanges(); Materia.RefreshAndManageCurrentProcess();
}
}
}
}
_deleted.Dispose(); _deleted = null;
Materia.RefreshAndManageCurrentProcess();
}
_table.AcceptChanges(); _cacheddataset.Tables.Add(_table);
}
}
}
Save();
}
}