public void SaveLastVersion(string tableName, VersionInfo lastVersion) { var v = GetLastVersion(tableName); if(v == null) { v = lastVersion; versions.Add(v); } else { v.LastUpdateTime = lastVersion.LastUpdateTime; v.LastUpdateVersion = lastVersion.LastUpdateVersion; } jsonSerializer.SaveObject(versions); }
public void Start() { SqlDependency.Start(connectionString); VersionInfo lastUpdateVersion = VersionManager.GetLastVersion(TableName); if (lastUpdateVersion == null) { lastUpdateVersion = new VersionInfo() { TableName = TableName, LastUpdateVersion = 0, LastUpdateTime = DateTime.Now }; } try { using (var conn = new SqlConnection(connectionString)) { using (var cmd = conn.CreateCommand()) { cmd.CommandText = string.Format("SELECT {1} FROM dbo.[{0}]", TableName, ColumnNames); cmd.CommandType = System.Data.CommandType.Text; conn.Open(); SqlDependency sd = new SqlDependency(cmd); sd.OnChange += new OnChangeEventHandler((s, e) => { if (e.Type == SqlNotificationType.Change) { using (var cn = new SqlConnection(connectionString)) { cn.Open(); string cmdText = string.Format("SELECT * FROM CHANGETABLE(CHANGES {0}, {1}) CT", TableName, lastUpdateVersion.LastUpdateVersion); using (var cm = new SqlCommand(cmdText, cn)) { using (var r = cm.ExecuteReader(CommandBehavior.CloseConnection)) { List<ChangeInfo> citems = new List<ChangeInfo>(); int fcount = r.FieldCount; int lastVersion = lastUpdateVersion.LastUpdateVersion; while (r.Read()) { ChangeInfo item = new ChangeInfo(); item.TableName = TableName; item.ChangeVersion = r["SYS_CHANGE_VERSION"].ConvertTo<int>(0); item.CreationVersion = r["SYS_CHANGE_CREATION_VERSION"].ConvertTo<int?>(null); item.Operation = GetOperation(r["SYS_CHANGE_OPERATION"].ToString()); item.Key = r[fcount - 1].ToString(); citems.Add(item); if (lastVersion < item.ChangeVersion) lastVersion = item.ChangeVersion; } r.Close(); if (Handlers != null) { Handlers.ForEach(h => h.HandleChange(citems)); lastUpdateVersion.LastUpdateVersion = lastVersion; lastUpdateVersion.LastUpdateTime = DateTime.Now; VersionManager.SaveLastVersion(TableName, lastUpdateVersion); } } } } } }); using (var r = cmd.ExecuteReader()) { r.Close(); } } } } catch (Exception ex) { } }