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