public IEnumerable<JobInfo> GetData()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["KPIConnectionString"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(@"SELECT [JobID],[Name],[LastExecutionDate],[Status] FROM [dbo].[JobInfo]", connection))
{
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
using (var reader = command.ExecuteReader())
return reader.Cast<IDataRecord>()
.Select(x => new JobInfo()
{
JobID = x.GetInt32(0),
Name = x.GetString(1),
LastExecutionDate = x.GetDateTime(2),
Status = x.GetString(3)
}).ToList();
}
}
}
public SqlChangeMonitor (SqlDependency dependency)
{
if (dependency == null)
throw new ArgumentNullException ("dependency");
throw new NotImplementedException ();
}
private static void UpdateGrid()
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages] where id>0", connection))
{
command.CommandType = CommandType.Text;
connection.Open();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataReader sdr = command.ExecuteReader())
{
Console.WriteLine();
while (sdr.Read())
{
Console.WriteLine(
"Id:{0}\tUserId:{1}\tMessage:{2}",
sdr["ID"].ToString(), sdr["UserId"].ToString(),
sdr["Message"].ToString()
);
}
}
}
}
}
/// <summary>
/// On cache miss.
/// </summary>
internal protected override void OnCacheMiss(Container container, IQueryable query, string key)
{
// Patch model to include schema information in LinqToSql, this is a requirement for SqlDependency to work.
LinqToSql.AddSchemaToModel(query);
var dependency = new System.Data.SqlClient.SqlDependency();
dependency.OnChange += (sender, args) =>
{
if (args.Info == SqlNotificationInfo.Invalid)
{
var unsupportedQueryHandler = UnsupportedQuery;
if (unsupportedQueryHandler != null)
{
unsupportedQueryHandler(this, new SqlDependencyEventArgs(query));
}
}
else
{
container.Delete(key);
}
OnChangeReceived.Set();
};
CallContext.SetData(CookieName, dependency.Id);
}
public string[] RegisterForChanges()
{
string[] result = null;
using (SqlConnection conn = new SqlConnection(mSubscriberConnectionString))
{
if (conn.State != ConnectionState.Open) conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT [BilgilendirmeTipi],[Baslik],[Icerik] FROM [dbo].[Bilgilendirmeler]", conn))
{
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += new OnChangeEventHandler(OnNotificationChange);
using (SqlDataAdapter adap = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
adap.Fill(dt);
if (dt.Rows.Count > 0)
{
result = new string[3];
result[0] = (string)dt.Rows[0][0];
result[1] = (string)dt.Rows[0][1];
result[2] = (string)dt.Rows[0][2];
}
}
}
}
}
return result;
}
public DataTable SomeMethod(Action<object, SqlNotificationEventArgs> handler)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// notificationas bus iskvieciamas visuomet, kai pasikeis bent vienas duomuo, is sio SELECTO grazinamu duomenu set`o (bus atliktas UPDATE, DELETE, INSERT)
using (SqlCommand command = new SqlCommand("SELECT StudentasID, Vardas, Pavarde, IBAN, AK FROM dbo.tblIseiviaiStudentai", connection))
{
dependency = new SqlDependency();
command.Notification = null;
dependency.AddCommandDependency(command);
dependency.OnChange += new OnChangeEventHandler(handler);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
// kad veiktu notificationas butini sie conection`o nustatymai, kitaip eventas bus kvieciamas nuolatos
var ON_PARAMETERS = @"SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ARITHABORT ON";
var OFF_PARAMETERS = @"SET NUMERIC_ROUNDABORT OFF";
var cmd_ON_PARAMETERS = new SqlCommand(ON_PARAMETERS, connection);
var cmd_OFF_PARAMETERS = new SqlCommand(OFF_PARAMETERS, connection);
cmd_ON_PARAMETERS.ExecuteNonQuery();
cmd_OFF_PARAMETERS.ExecuteNonQuery();
dataTable.Clear();
dataTable.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
dependency = null;
return dataTable;
}
}
}
public DataBase()
{
ConnectToDB();
sqlDependency = new SqlDependency();
//sqlDependency.AddCommandDependency(new SqlCommand("INSERT", conn));
sqlDependency.OnChange += new OnChangeEventHandler(onChange);
}
public void RegisterDiscussMessages()
{
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT [Id]
,[Username]
,[Message]
,[FilePath]
,[Status]
,[CreatedDate]
FROM [dbo].[Discuss] ORDER BY [dbo].[Discuss].CreatedDate desc", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
}
}
}
public IEnumerable<Messages> GetAllMessages()
{
var messages = new List<Messages>();
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages]", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
messages.Add(item: new Messages { MessageID = (int)reader["MessageID"], Message = (string)reader["Message"], EmptyMessage = reader["EmptyMessage"] != DBNull.Value ? (string) reader["EmptyMessage"] : "", MessageDate = Convert.ToDateTime(reader["Date"]) });
}
}
}
return messages;
}
public IEnumerable<Info> GetData()
{
using (
var connection =
new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (
var command = new SqlCommand(@"SELECT [Id],[Name],[Family],[Status] FROM [dbo].[Info]", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += dependency_OnChange;
if (connection.State == ConnectionState.Closed)
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
return reader.Cast<IDataRecord>()
.Select(x => new Info
{
Id = x.GetInt32(0),
Name = x.GetString(1),
Family = x.GetString(2),
Status = x.GetString(3)
}).ToList();
}
}
}
public static bool IsInMaintenanceMode()
{
bool inMaintenanceMode;
string connStr = "Data Source=KIM-MSI\\KIMSSQLSERVER;Initial Catalog=MVWDataBase;User ID=sa;Password=mis123;MultipleActiveResultSets=True";
if (MemoryCache.Default["MaintenanceMode"] == null)
{
Console.WriteLine("Hitting the database...");
CacheItemPolicy policy = new CacheItemPolicy();
SqlDependency.Start(connStr);
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand("Select MaintenanceMode From dbo.MaintenanceMode", conn))
{
command.Notification = null;
SqlDependency dep = new SqlDependency();
dep.AddCommandDependency(command);
conn.Open();
inMaintenanceMode = (bool)command.ExecuteScalar();
SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
policy.ChangeMonitors.Add(monitor);
dep.OnChange += Dep_OnChange;
}
}
MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
}
else
{
inMaintenanceMode = (bool)MemoryCache.Default.Get("MaintenanceMode");
}
return inMaintenanceMode;
}
public List<DiscussMessageModel> RegisterDiscussMessages()
{
var messageChat = new List<DiscussMessageModel>();
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT *
FROM [dbo].[Comments]", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
messageChat.Add(item: new DiscussMessageModel
{
Id = reader["Id"].ToString(),
Username = reader["Username"].ToString(),
Message = reader["Message"].ToString(),
FilePath = reader["FilePath"].ToString(),
Avatar = "dist/img/avatar.png",
Status = reader["Status"].ToString(),
CreatedDate = DateTime.Parse(reader["CreatedDate"].ToString())
});
}
}
}
return messageChat;
}
public IEnumerable<MessageLog> GetAllMessagesLog()
{
string conStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection connection = new SqlConnection(conStr);
string query = "SELECT Message,EventID,LL.Name as LogLevelID,OC.Name as OperationCodeID,ML.ServerName,ML.ComponentName,ML.SubComponentName FROM [dbo].[MessageLog] ML inner join [dbo].[LogLevel] LL on ML.LogLevelID = LL.ID inner join [dbo].[OperationCode] OC on ML.OperationCodeID = OC.ID";
SqlDependency.Start(conStr);
SqlCommand command = new SqlCommand(query, connection);
SqlDependency dependency = new SqlDependency(command);
//If Something will change in database and it will call dependency_OnChange method.
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
connection.Open();
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
List<MessageLog> messageList = new List<MessageLog>();
for (int i = 0; i < dt.Rows.Count; i++)
{
MessageLog ml = new MessageLog();
ml.Name = dt.Rows[i]["Message"].ToString();
ml.EventID = Convert.ToInt32(dt.Rows[i]["EventID"].ToString());
ml.LogLevelName = dt.Rows[i]["LogLevelID"].ToString();
ml.OperationCodeName = dt.Rows[i]["OperationCodeID"].ToString();
ml.ServerName = dt.Rows[i]["ServerName"].ToString();
ml.ComponentName = dt.Rows[i]["ComponentName"].ToString();
ml.SubComponentName = dt.Rows[i]["SubComponentName"].ToString();
messageList.Add(ml);
}
return messageList;
}
public IEnumerable<Message> GetData()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT [ID], [Date], [Message] FROM [dbo].[Messages]", connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += DependencyOnChange;
if (connection.State == ConnectionState.Closed)
connection.Open();
using (var reader = command.ExecuteReader())
return reader.Cast<IDataRecord>()
.Select(x => new Message()
{
ID = x.GetInt32(0),
Date = x.GetDateTime(1),
Text = x.GetString(2)
}).ToList();
}
}
}
private void OnDependencyChanged(object sender, SqlNotificationEventArgs e)
{
if (_eventHandler != null)
_eventHandler.Invoke(this, e);
_dependency = null;
Start();
}
public IEnumerable<JobModel> GetData(short minLevel)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["PubsEntitiesSimpleConnectionString"].ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(string.Format(@"SELECT [job_id],[job_desc]
FROM [dbo].[jobs]"), connection))
//WHERE [min_lvl] > {0}", minLevel), connection))
{
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += dependency_OnChange;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (var reader = command.ExecuteReader())
{
return reader.Cast<IDataRecord>()
.Select(x => new JobModel
{
job_id = x.GetInt16(0),
job_desc = x.GetString(1)
}).ToList();
}
}
}
}
public ChangeMonitor Enlist()
{
using (var connection = new SqlConnection(connectionString))
{
using (var exeCommand = new System.Data.SqlClient.SqlCommand(command, connection))
{
//is the command a sproc
if (isStoredProcedure)
{
exeCommand.CommandType = CommandType.StoredProcedure;
}
if (commandTimeout.HasValue)
exeCommand.CommandTimeout = this.commandTimeout.Value;
var dependency = new SqlDependency(exeCommand);
var monitor = new SqlChangeMonitor(dependency);
connection.Open();
exeCommand.ExecuteNonQuery();
return monitor;
}
}
}
/// <summary>
/// On cache miss.
/// </summary>
protected internal override void OnCacheMiss(Container container, IQueryable query, string key)
{
// Patch model to include schema information in LinqToSql, this is a requirement for SqlDependency to work.
LinqToSql.AddSchemaToModel(query);
var dependency = new System.Data.SqlClient.SqlDependency();
dependency.OnChange += (sender, args) =>
{
if (args.Info == SqlNotificationInfo.Invalid)
{
var unsupportedQueryHandler = UnsupportedQuery;
if (unsupportedQueryHandler != null)
{
unsupportedQueryHandler(this, new SqlDependencyEventArgs(query));
}
}
else
{
container.Delete(key);
}
OnChangeReceived.Set();
};
CallContext.SetData(CookieName, dependency.Id);
}
public IEnumerable<Person> GetPersons()
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT [ID],[FirstName],[LastName] FROM [dbo].[Person]", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += (x, y) => _hubContext.Clients.All.displayStatus(GetPersons());
using (var reader = command.ExecuteReader())
{
return reader.Cast<IDataRecord>()
.Select(x => new Person
{
ID = x.GetInt64(0),
FirstName = x.GetString(1),
LastName = x.GetString(2),
}).ToList();
}
}
}
}
public SqlDependencyCacheWatcher(string tableName)
{
this.connectionString = connectionString;
this.sqlQueue = sqlQueue;
this.listenerQuery = listenerQuery;
this.dependency = null;
}
private static void AddToCache(string key, object cacheItem, SqlDependency dependency)
{
var policy = new CacheItemPolicy();
policy.ChangeMonitors.Add(new SqlChangeMonitor(dependency));
Cache.Add(new CacheItem(key, cacheItem), policy);
}
private void UnSubscribe()
{
if (dependency != null)
{
dependency.OnChange -= OnDependencyChange;
dependency = null;
}
}
private void OnDependencyChanged(object sender, SqlNotificationEventArgs e)
{
if (_eventHandler != null)
{
_eventHandler.Invoke(this, e);
}
_dependency = null;
Start();
}
private void Start()
{
_command = new SqlCommand(_command.CommandText, new SqlConnection(_connectionString));
OpenConnectionIfClosed(_command);
AdoNetSqlDependency.Start(_connectionString);
_dependency = new AdoNetSqlDependency(_command);
_depReader = _command.ExecuteReader();
_dependency.OnChange += OnDependencyChanged;
}
private void Start()
{
_command = new SqlCommand(_command.CommandText, new SqlConnection(_connectionString));
OpenConnectionIfClosed(_command);
AdoNetSqlDependency.Start(_connectionString);
_dependency = new AdoNetSqlDependency(_command);
_depReader = _command.ExecuteReader();
_dependency.OnChange += OnDependencyChanged;
}
public static void AddSqlDependency(this IDbCommand command, Action<SqlNotificationEventArgs> callback)
{
var sqlCommand = command as SqlCommand;
if (sqlCommand == null)
{
throw new NotSupportedException();
}
var dependency = new SqlDependency(sqlCommand, null, (int)_dependencyTimeout.TotalSeconds);
dependency.OnChange += (o, e) => callback(e);
}
/// <summary>
/// 监控数据库中表的数据变化
/// </summary>
/// <param name="monitorSql">监控表查询语句</param>
/// <param name="method">添加代理事件的代理类并委托给一个方法,例：new OnChangeEventHandler(dependency_OnChange);
/// private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)</param>
public static void UpdateGrid(String monitorSql,OnChangeEventHandler method)
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand(monitorSql, Connection.Connection.getSqlConnection()))
{
command.CommandType = CommandType.Text;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += method;
SqlDataReader sdr = command.ExecuteReader();
sdr.Close();
}
}
public IEnumerable<test_results> GetLineTestResults(SqlDependency dependency)
{
if (Cache.Contains(LINETEST_CACHE_KEY))
{
return Cache[LINETEST_CACHE_KEY] as IEnumerable<test_results>;
}
using (var db = LineTestContextFactory.CreateContext())
{
AddToCache(LINETEST_CACHE_KEY, db.test_results.ToList(), dependency);
}
return Cache[LINETEST_CACHE_KEY] as IEnumerable<test_results>;
}
private void RegisterForNotifications()
{
using (var connection = new SqlConnection(_connString))
{
using (SqlCommand command = new SqlCommand(_selectQuery, connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
var reader = command.ExecuteReader();
}
}
}
static void ReadMessages()
{
DatabaseHelper databaseHelper = new DatabaseHelper();
SqlCommand cmd = databaseHelper.CreateCommand(SQLQueries.GET_MESSAGES);
System.Data.SqlClient.SqlDependency sqlDependency = databaseHelper.SqlDependency(cmd);
databaseHelper.DependencyStart();
sqlDependency.OnChange += SqlDep_OnChange;
SqlDataReader dr = databaseHelper.ExecuteReader(cmd);
Console.WriteLine("Time:" + DateTime.Now);
while (dr.Read())
{
Console.WriteLine(dr["Message"]);
}
}
private void RegisterSqlDependency()
{
if (connection == null || command == null)
{
throw new ArgumentException("command and connection cannot be null");
}
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object
// to the command object.
dependency = new System.Data.SqlClient.SqlDependency(command);
Console.WriteLine("Id of sqldependency:{0}", dependency.Id);
RegisterSqlCommand();
}
public void ListenTable(string conStr, SyncMonitor monitor)
{
using (SqlConnection connection = new SqlConnection(conStr))
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand(monitor.listenSql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += monitor.callBack;
SqlDataReader sdr = command.ExecuteReader();
}
}
}
static private void AutoRefresh(IEnumerable collection)
{
var oldCookie = CallContext.GetData(sqlDependencyCookie);
try
{
var dependency = new SqlDependency();
collections.Add(dependency.Id, collection);
CallContext.SetData(sqlDependencyCookie, dependency.Id);
dependency.OnChange += dependency_OnChange;
ctx.Refresh(refreshMode, collection);
}
finally
{
CallContext.SetData(sqlDependencyCookie, oldCookie);
}
}
private void ResgisterNotification()
{
using (_connection = new SqlConnection(_connectionsString))
{
using (_command = new SqlCommand(_query, _connection))
{
_command.Notification = null;
_dependencyObj = new SqlDependency(_command);
_dependencyObj.OnChange += dependency_OnChange;
if (_connection.State == ConnectionState.Closed)
_connection.Open();
using (_command.ExecuteReader())
{
}
}
}
}
private void ListenForChanges()
{
//Remove existing dependency, if necessary
UnSubscribe();
var connection = new SqlConnection(connectionString);
connection.Open();
var command = new SqlCommand(listenerQuery, connection);
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
SqlDependency.Start(connectionString);
command.ExecuteReader();
connection.Close();
}
void ISqlDependency.Terminate(string connectionString)
{
try
{
_depReader.Close();
}
finally
{
try
{
AdoNetSqlDependency.Stop(connectionString);
}
finally
{
_dependency = null;
}
}
}
private void RegisterSqlDependency()
{
if (command == null || connection == null)
{
throw new ArgumentException("command and connection cannot be null");
}
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object to the command object.
dependency = new System.Data.SqlClient.SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(DependencyOnChange);
// After register SqlDependency, the SqlCommand must be executed, or we can't
// get the notification.
RegisterSqlCommand();
}
protected void Dispose(Boolean disposed)
{
if (disposed)
{
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Dispose();
command = null;
}
iquery = null;
dependency = null;
}
}
static void Main(string[] args)
{
string _SqlConn = @"Data Source=rutwik-c\sqlexpress;Initial Catalog=Student.Data.StudentContext;Integrated Security=True";
SqlConnection _Conn = new SqlConnection(_SqlConn);
SqlCommand _Cmd = new SqlCommand("SELECT RollNo, Name, Marks, Branch, Year FROM [dbo].[Studs]", _Conn);
System.Data.SqlClient.SqlDependency listener = new System.Data.SqlClient.SqlDependency(_Cmd);
listener.OnChange += Listener_OnChange;
System.Data.SqlClient.SqlDependency.Start(_SqlConn);
Console.WriteLine("Listening..");
_Conn.Open();
_Cmd.ExecuteReader();
System.Data.SqlClient.SqlDependency.Stop(_SqlConn);
Console.WriteLine("Stopped!");
_Conn.Close();
}
protected void Dispose(Boolean disposed)
{
if (disposed)
{
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Dispose();
connection = null;
}
OnChanged = null;
iquery = null;
dependency.OnChange -= DependencyOnChange;
dependency = null;
}
}
// Methods for Timer maintenance and firing.
internal void StartTimer(SqlDependency dep)
{
// If this dependency expires sooner than the current next timeout, change
// the timeout and enable timer callback as needed. Note that we change _nextTimeout
// only inside the hashtable syncroot.
lock (_instanceLock)
{
// Enable the timer if needed (disable when empty, enable on the first addition).
if (!_sqlDependencyTimeOutTimerStarted)
{
_timeoutTimer.Change(15000 /* 15 secs */, 15000 /* 15 secs */);
// Save this as the earlier timeout to come.
_nextTimeout = dep.ExpirationTime;
_sqlDependencyTimeOutTimerStarted = true;
}
else if (_nextTimeout > dep.ExpirationTime)
{
// Save this as the earlier timeout to come.
_nextTimeout = dep.ExpirationTime;
}
}
}
// This method is called by SqlCommand to enable ASP.Net scenarios - map from ID to Dependency.
internal SqlDependency LookupDependencyEntry(string id)
{
if (null == id)
{
throw ADP.ArgumentNull(nameof(id));
}
if (string.IsNullOrEmpty(id))
{
throw SQL.SqlDependencyIdMismatch();
}
SqlDependency entry = null;
lock (_instanceLock)
{
if (_dependencyIdToDependencyHash.ContainsKey(id))
{
entry = _dependencyIdToDependencyHash[id];
}
}
return(entry);
}
// Remove from commandToDependenciesHash all references to the passed dependency.
private void RemoveDependencyFromCommandToDependenciesHash(SqlDependency dependency)
{
lock (_instanceLock)
{
List <string> notificationIdsToRemove = new List <string>();
List <string> commandHashesToRemove = new List <string>();
foreach (KeyValuePair <string, DependencyList> entry in _notificationIdToDependenciesHash)
{
DependencyList dependencies = entry.Value;
if (dependencies.Remove(dependency))
{
if (dependencies.Count == 0)
{
// this dependency was the last associated with this notification ID, remove the entry
// note: cannot do it inside foreach over dictionary
notificationIdsToRemove.Add(entry.Key);
commandHashesToRemove.Add(entry.Value.CommandHash);
}
}
// same SqlDependency can be associated with more than one command, so we have to continue till the end...
}
Debug.Assert(commandHashesToRemove.Count == notificationIdsToRemove.Count, "maps should be kept in sync");
for (int i = 0; i < notificationIdsToRemove.Count; i++)
{
// cleanup the entry outside of foreach
_notificationIdToDependenciesHash.Remove(notificationIdsToRemove[i]);
// Cleanup the map between the command hash and associated notification ID
_commandHashToNotificationId.Remove(commandHashesToRemove[i]);
}
Debug.Assert(_notificationIdToDependenciesHash.Count == _commandHashToNotificationId.Count, "always keep these maps in sync!");
}
}
internal SqlDependency LookupDependencyEntry(string id)
{
SqlDependency dependency2;
IntPtr ptr;
Bid.NotificationsScopeEnter(out ptr, "<sc.SqlDependencyPerAppDomainDispatcher.LookupDependencyEntry|DEP> %d#, Key: '%ls'", this.ObjectID, id);
try
{
if (id == null)
{
throw ADP.ArgumentNull("id");
}
if (ADP.IsEmpty(id))
{
throw SQL.SqlDependencyIdMismatch();
}
SqlDependency dependency = null;
lock (this)
{
if (this._dependencyIdToDependencyHash.ContainsKey(id))
{
dependency = this._dependencyIdToDependencyHash[id];
}
else
{
Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.LookupDependencyEntry|DEP|ERR> ERROR - dependency ID mismatch - not throwing.\n");
}
}
dependency2 = dependency;
}
finally
{
Bid.ScopeLeave(ref ptr);
}
return(dependency2);
}
public System.Data.SqlClient.SqlDependency SqlDependency(SqlCommand command)
{
System.Data.SqlClient.SqlDependency sqlDependency = new System.Data.SqlClient.SqlDependency(command);
return(sqlDependency);
}
private static void TimeoutTimerCallback(object state)
{
SqlDependency[] dependencies;
// Only take the lock for checking whether there is work to do
// if we do have work, we'll copy the hashtable and scan it after releasing
// the lock.
lock (SingletonInstance._instanceLock)
{
if (0 == SingletonInstance._dependencyIdToDependencyHash.Count)
{
// Nothing to check.
return;
}
if (SingletonInstance._nextTimeout > DateTime.UtcNow)
{
// No dependency timed-out yet.
return;
}
// If at least one dependency timed-out do a scan of the table.
// NOTE: we could keep a shadow table sorted by expiration time, but
// given the number of typical simultaneously alive dependencies it's
// probably not worth the optimization.
dependencies = new SqlDependency[SingletonInstance._dependencyIdToDependencyHash.Count];
SingletonInstance._dependencyIdToDependencyHash.Values.CopyTo(dependencies, 0);
}
// Scan the active dependencies if needed.
DateTime now = DateTime.UtcNow;
DateTime newNextTimeout = DateTime.MaxValue;
for (int i = 0; i < dependencies.Length; i++)
{
// If expired fire the change notification.
if (dependencies[i].ExpirationTime <= now)
{
try
{
// This invokes user-code which may throw exceptions.
// NOTE: this is intentionally outside of the lock, we don't want
// to invoke user-code while holding an internal lock.
dependencies[i].Invalidate(SqlNotificationType.Change, SqlNotificationInfo.Error, SqlNotificationSource.Timeout);
}
catch (Exception e)
{
if (!ADP.IsCatchableExceptionType(e))
{
throw;
}
// This is an exception in user code, and we're in a thread-pool thread
// without user's code up in the stack, no much we can do other than
// eating the exception.
ADP.TraceExceptionWithoutRethrow(e);
}
}
else
{
if (dependencies[i].ExpirationTime < newNextTimeout)
{
newNextTimeout = dependencies[i].ExpirationTime; // Track the next earlier timeout.
}
dependencies[i] = null; // Null means "don't remove it from the hashtable" in the loop below.
}
}
// Remove timed-out dependencies from the hashtable.
lock (SingletonInstance._instanceLock)
{
for (int i = 0; i < dependencies.Length; i++)
{
if (null != dependencies[i])
{
SingletonInstance._dependencyIdToDependencyHash.Remove(dependencies[i].Id);
}
}
if (newNextTimeout < SingletonInstance._nextTimeout)
{
SingletonInstance._nextTimeout = newNextTimeout; // We're inside the lock so ok to update.
}
}
}
// This method is called upon Execute of a command associated with a SqlDependency object.
internal string AddCommandEntry(string commandHash, SqlDependency dep)
{
IntPtr hscp;
string notificationId = string.Empty;
Bid.NotificationsScopeEnter(out hscp, "<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> %d#, commandHash: '%ls', SqlDependency: %d#", ObjectID, commandHash, dep.ObjectID);
try {
lock (this) {
if (!_dependencyIdToDependencyHash.ContainsKey(dep.Id)) // Determine if depId->dep hashtable contains dependency. If not, it's been invalidated.
{
Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> Dependency not present in depId->dep hash, must have been invalidated.\n");
}
else
{
// check if we already have notification associated with given command hash
if (_commandHashToNotificationId.TryGetValue(commandHash, out notificationId))
{
// we have one or more SqlDependency instances with same command hash
DependencyList dependencyList = null;
if (!_notificationIdToDependenciesHash.TryGetValue(notificationId, out dependencyList))
{
// this should not happen since _commandHashToNotificationId and _notificationIdToDependenciesHash are always
// updated together
Debug.Assert(false, "_commandHashToNotificationId has entries that were removed from _notificationIdToDependenciesHash. Remember to keep them in [....]");
throw ADP.InternalError(ADP.InternalErrorCode.SqlDependencyCommandHashIsNotAssociatedWithNotification);
}
// join the new dependency to the list
if (!dependencyList.Contains(dep))
{
Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> Dependency not present for commandHash, adding.\n");
dependencyList.Add(dep);
}
else
{
Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> Dependency already present for commandHash.\n");
}
}
else
{
// we did not find notification ID with the same app domain and command hash, create a new one
// use unique guid to avoid duplicate IDs
// prepend app domain ID to the key - SqlConnectionContainer::ProcessNotificationResults (SqlDependencyListener.cs)
// uses this app domain ID to route the message back to the app domain in which this SqlDependency was created
notificationId = string.Format(System.Globalization.CultureInfo.InvariantCulture,
"{0};{1}",
SqlDependency.AppDomainKey, // must be first
Guid.NewGuid().ToString("D", System.Globalization.CultureInfo.InvariantCulture)
);
Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> Creating new Dependencies list for commandHash.\n");
DependencyList dependencyList = new DependencyList(commandHash);
dependencyList.Add(dep);
// map command hash to notification we just created to reuse it for the next client
// do it inside finally block to avoid ThreadAbort exception interrupt this operation
try {}
finally {
_commandHashToNotificationId.Add(commandHash, notificationId);
_notificationIdToDependenciesHash.Add(notificationId, dependencyList);
}
}
Debug.Assert(_notificationIdToDependenciesHash.Count == _commandHashToNotificationId.Count, "always keep these maps in [....]!");
}
}
}
finally {
Bid.ScopeLeave(ref hscp);
}
return(notificationId);
}
internal EventContextPair(OnChangeEventHandler eventHandler, SqlDependency dependency)
{
this._eventHandler = eventHandler;
this._context = ExecutionContext.Capture();
this._dependency = dependency;
}
private static void TimeoutTimerCallback(object state)
{
IntPtr ptr;
Bid.NotificationsScopeEnter(out ptr, "<sc.SqlDependencyPerAppDomainDispatcher.TimeoutTimerCallback|DEP> AppDomainKey: '%ls'", SqlDependency.AppDomainKey);
try
{
SqlDependency[] dependencyArray;
lock (SingletonInstance)
{
if (SingletonInstance._dependencyIdToDependencyHash.Count == 0)
{
Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.TimeoutTimerCallback|DEP> No dependencies, exiting.\n");
return;
}
if (SingletonInstance._nextTimeout > DateTime.UtcNow)
{
Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.TimeoutTimerCallback|DEP> No timeouts expired, exiting.\n");
return;
}
dependencyArray = new SqlDependency[SingletonInstance._dependencyIdToDependencyHash.Count];
SingletonInstance._dependencyIdToDependencyHash.Values.CopyTo(dependencyArray, 0);
}
DateTime utcNow = DateTime.UtcNow;
DateTime maxValue = DateTime.MaxValue;
for (int i = 0; i < dependencyArray.Length; i++)
{
if (dependencyArray[i].ExpirationTime <= utcNow)
{
try
{
dependencyArray[i].Invalidate(SqlNotificationType.Change, SqlNotificationInfo.Error, SqlNotificationSource.Timeout);
}
catch (Exception exception)
{
if (!ADP.IsCatchableExceptionType(exception))
{
throw;
}
ADP.TraceExceptionWithoutRethrow(exception);
}
}
else
{
if (dependencyArray[i].ExpirationTime < maxValue)
{
maxValue = dependencyArray[i].ExpirationTime;
}
dependencyArray[i] = null;
}
}
lock (SingletonInstance)
{
for (int j = 0; j < dependencyArray.Length; j++)
{
if (dependencyArray[j] != null)
{
SingletonInstance._dependencyIdToDependencyHash.Remove(dependencyArray[j].Id);
}
}
if (maxValue < SingletonInstance._nextTimeout)
{
SingletonInstance._nextTimeout = maxValue;
}
}
}
finally
{
Bid.ScopeLeave(ref ptr);
}
}