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)
{
}
}