private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
//MessageBox.Show("modification Occurred");
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired)
{
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
//++changeCount;
//lblChanges.Text = String.Format(statusMessage, changeCount);
GetData();
// Get accounts
GetAccounts();
// Search and pagination
SearchAndPaginationAttendance();
}
/// <summary>
/// Add action that will be executed once the underlying data source is changed.
/// Make sure that you call SqlDependency.Start(connString) once application starts, and SqlDependency.Stop(connString); when application stops.
/// </summary>
/// <param name="query">Mapper object that will execute the query.</param>
/// <param name="action">Action that will be executed once the query results change.</param>
/// <returns>Mapper.</returns>
public static IQuery OnChange(this IQuery query, OnChangeEventHandler action)
{
var cmd = ((query as Common.BaseStatement).Command as SqlCommand);
new SqlDependency(cmd).OnChange += action;
return(query);
}
/// <summary>
/// Add action that will be executed once the underlying data source is changed.
/// Make sure that you call SqlDependency.Start(connString) once application starts, and SqlDependency.Stop(connString); when application stops.
/// </summary>
/// <param name="mapper">Mapper object that will execute the query.</param>
/// <param name="action">Action that will be executed once the query results change.</param>
/// <returns>Mapper.</returns>
public static IQueryMapper OnChange(this IQueryMapper mapper, OnChangeEventHandler action)
{
var cmd = ((mapper as Common.BaseStatement).Command as SqlCommand);
new SqlDependency(cmd).OnChange += action;
return(mapper);
}
internal EventContextPair(OnChangeEventHandler eventHandler, SqlDependency dependency)
{
Debug.Assert(eventHandler != null && dependency != null, "Unexpected arguments!");
_eventHandler = eventHandler;
_context = ExecutionContext.Capture();
_dependency = dependency;
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired)
{
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = new[] { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
changeCount += 1;
//this.label5.Text = string.Format(statusMessage, changeCount);
{
var withBlock = this.listBox2.Items;
withBlock.Clear();
withBlock.Add("Info: " + e.Info.ToString());
withBlock.Add("Source: " + e.Source.ToString());
withBlock.Add("Type: " + e.Type.ToString());
}
GetData();
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
// This event will occur on a thread pool thread.
// It is illegal to update the UI from a worker thread
// The following code checks to see if it is safe update the UI.
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
// If InvokeRequired returns True, the code is executing on a worker thread.
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = new[] { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}
// Remove the handler since it's only good
// for a single notification
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
// Reload the dataset that's bound to the grid.
GetData();
}
private static void RunWatchTask(Subscription subscription, OnChangeEventHandler callback)
{
Task.Factory.StartNew(async() =>
{
while (!subscription.Watching.IsCompleted)
{
try
{
var result = await subscription.DetectChangesAsync();
if (!result.IsCompleted && !result.IsCanceled)
{
callback(subscription, new PlcNotificationEventArgs(result.Results));
}
else
{
// is cancelled or completed, so set whatching is compleded now!
callback(subscription, new PlcNotificationEventArgs());
return;
}
}
catch (Exception ex)
{
// todo
subscription.CancelCurrentDetection();
callback(subscription, new PlcNotificationEventArgs(ex));
return;
}
}
}, TaskCreationOptions.LongRunning);
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired)
{
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = new[] { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
//tang change khi co thay doi
change++;
//show result
lbChange.Items.Clear();
lbChange.Items.Add("Update: " + (change - 1) + " " + e.Type.ToString());
lbChange.Items.Add("Info: " + e.Info.ToString());
GetData();
}
/// <summary>
/// Extends BeginInvoke so that when a state object is not needed, null does not need to be passed.
/// <example>
/// onchangeeventhandler.BeginInvoke(sender, e, callback);
/// </example>
/// </summary>
public static IAsyncResult BeginInvoke(this OnChangeEventHandler onchangeeventhandler, Object sender, SqlNotificationEventArgs e, AsyncCallback callback)
{
if (onchangeeventhandler == null)
{
throw new ArgumentNullException("onchangeeventhandler");
}
return(onchangeeventhandler.BeginInvoke(sender, e, callback, null));
}
public int StartDBListening(string query, OnChangeEventHandler func)
{
int result = -1;
OracleCommand cmd = null;
try
{
if (ConnectionOpen())
{
cmd = new OracleCommand(query, _Connection);
cmd.AddRowid = true;
//OracleDependency dep = new OracleDependency(cmd);
dep.AddCommandDependency(cmd);
cmd.Notification.IsNotifiedOnce = false;
bool buldu = false;
foreach (var item in dep.RegisteredResources)
{
if (item.ToString() == query)
{
buldu = true;
}
}
if (buldu == false)
{
dep.OnChange += new OnChangeEventHandler(func);
}
cmd.ExecuteNonQuery();
}
}
catch (OracleException oex)
{
}
catch (Exception ex)
{
LogIslemleri.LogYaz(ex.Message);
result = -1;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
CloseConnection();
}
return(result);
}
private async Task <DataTable> AddQueryDependency(OnChangeEventHandler Handler)
{
try
{
//remove some SQLDependency limitation
string correctCommandText = Regex.Replace(CommandText, @"getdate\(\)", "@getdate", RegexOptions.IgnoreCase);
this.WriteLog("AddQueryDependency");
using (SqlConnection cn = new SqlConnection(ConnectionString))
using (SqlCommand cmd = new SqlCommand(correctCommandText, cn))
{
cmd.CommandTimeout = CommandTimeout;
cmd.CommandType = IsStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
cmd.Notification = null;
foreach (var param in prms)
{
cmd.Parameters.Add(new SqlParameter(param.ParameterName, param.SqlDbType)
{
Value = param.Value
});
}
if (correctCommandText.Contains("@getdate"))
{
cmd.Parameters.Add(new SqlParameter("@getdate", SqlDbType.DateTime)
{
Value = DateTime.Now
});
}
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += Handler;
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
var dr = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
DataTable dt = new DataTable();
dt.Load(dr);
return(dt);
}
}
catch (Exception ex)
{
Debug.WriteLine(new Exception("AddSqlDependency", ex));
this.WriteLog("AddSqlDependency Exception {0}", ex.ToDetailString());
throw new Exception("AddSqlDependency", ex);
}
}
/// <summary>
/// Subscribe to changes of variables
/// </summary>
/// <param name="mapper">Reference to plc data mapper</param>
/// <param name="callback">Callback method</param>
/// <param name="items">items to watch</param>
/// <param name="changeDetectionStrategy">setup the strategy to detect changes. <see cref="ChangeDetectionStrategy"/>. This setting depends on the access library.</param>
/// <returns></returns>
public static Subscription SubscribeDataChanges(this PlcDataMapper mapper,
OnChangeEventHandler callback,
int interval,
IEnumerable <PlcWatchReference> items,
ChangeDetectionStrategy changeDetectionStrategy = ChangeDetectionStrategy.Polling)
{
var subscription = new Subscription(mapper, changeDetectionStrategy, items, interval);
RunWatchTask(subscription, callback);
return(subscription);
}
public void OnChangeRemove()
{
SqlDependency dep = new SqlDependency();
OnChangeEventHandler tempDelegate = delegate(object o, SqlNotificationEventArgs args)
{
Console.WriteLine("Notification callback. Type={0}, Info={1}, Source={2}", args.Type, args.Info, args.Source);
};
dep.OnChange += tempDelegate;
dep.OnChange -= tempDelegate;
}
/// <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();
}
}
/// <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();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Appender{TResult}" /> class.
/// </summary>
/// <param name="previousLink">The previous link.</param>
/// <param name="eventHandler">The event handler to fire when then associated SQL Dependency is fired..</param>
public NotifyChangeAppender(ILink <TResult> previousLink, OnChangeEventHandler eventHandler)
: base(previousLink)
{
if (previousLink == null)
{
throw new ArgumentNullException(nameof(previousLink), $"{nameof(previousLink)} is null.");
}
if (eventHandler == null)
{
throw new ArgumentNullException(nameof(eventHandler), $"{nameof(eventHandler)} is null.");
}
m_EventHandler = eventHandler;
}
internal static void ListenCommand(string sql, OnChangeEventHandler Event)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlDependency.Start(connstr);
SqlDependency dependency = new SqlDependency(cmd);
conn.Open();
cmd.ExecuteNonQuery();
dependency.OnChange += new OnChangeEventHandler(Event);
}
}
}
public void OnChangeAddHasChanges()
{
SqlDependency dep = new SqlDependency();
Type sqlDependencyType = typeof(SqlDependency);
FieldInfo dependencyFiredField = sqlDependencyType.GetField("_dependencyFired", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
dependencyFiredField.SetValue(dep, true);
OnChangeEventHandler tempDelegate = delegate(object o, SqlNotificationEventArgs args)
{
Console.WriteLine("Notification callback. Type={0}, Info={1}, Source={2}", args.Type, args.Info, args.Source);
};
dep.OnChange += tempDelegate;
}
private void dependency_OnChange(
object sender, SqlNotificationEventArgs e)
{
// This event will occur on a thread pool thread.
// Updating the UI from a worker thread is not permitted.
// The following code checks to see if it is safe to
// update the UI.
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
// If InvokeRequired returns True, the code
// is executing on a worker thread.
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch.
OnChangeEventHandler tempDelegate =
new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}
// Remove the handler, since it is only good
// for a single notification.
SqlDependency dependency =
(SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
// At this point, the code is executing on the
// UI thread, so it is safe to update the UI.
++changeCount;
label1.Text = String.Format(statusMessage, changeCount);
// Add information from the event arguments to the list box
// for debugging purposes only.
listBox1.Items.Clear();
listBox1.Items.Add("Info: " + e.Info.ToString());
listBox1.Items.Add("Source: " + e.Source.ToString());
listBox1.Items.Add("Type: " + e.Type.ToString());
// Reload the dataset that is bound to the grid.
GetData();
}
private static void PerformRawDataChange(PlcDataMapper papper)
{
Console.WriteLine();
Console.WriteLine($"Start PerformRawDataChange");
Console.ForegroundColor = ConsoleColor.Red;
var writeData = new Dictionary <string, object> {
{ "W0", 3 },
{ "X5_0", !_toggle ? true : false },
};
_toggle = !_toggle;
var are = new AutoResetEvent(false);
OnChangeEventHandler callback = (s, e) =>
{
foreach (var item in e)
{
Console.WriteLine($"DataChanged detected:{item.Address} = {item.Value}");
}
are.Set();
};
//papper.SubscribeRawDataChanges("DB15", callback);
//papper.SetRawActiveState(true, "DB15", writeData.Keys.ToArray());
////waiting for initialize
//if (!are.WaitOne(10000))
// Console.WriteLine($"Error-> change!!!!!");
//foreach (var item in writeData)
// Console.WriteLine($"Write:{item.Key} = {item.Value}");
//var result = papper.WriteAbs("DB15", writeData);
////waiting for write update
//if (!are.WaitOne(10000))
// Console.WriteLine($"Error-> change!!!!!");
////test if data change only occurred if data changed
//if (are.WaitOne(5000))
// Console.WriteLine($"Error-> no change!!!!!");
//papper.SetRawActiveState(false, "DB15", writeData.Keys.ToArray());
//papper.Unsubscribe("DB15", callback);
Console.ResetColor();
Console.WriteLine($"Finished PerformDataChange");
}
public void OnChangeAddDuplicate_Throws()
{
SqlDependency dep = new SqlDependency();
OnChangeEventHandler tempDelegate = delegate(object o, SqlNotificationEventArgs args)
{
Console.WriteLine("Notification callback. Type={0}, Info={1}, Source={2}", args.Type, args.Info, args.Source);
};
dep.OnChange += tempDelegate;
InvalidOperationException ex = Assert.Throws <InvalidOperationException>(() =>
{
dep.OnChange += tempDelegate;
});
Assert.Contains("same", ex.Message, StringComparison.OrdinalIgnoreCase);
}
public void NotificationTest(int changesCount, bool useAssert = true)
{
SqlDependency.Stop(TEST_CONNECTION_STRING);
SqlDependency.Start(TEST_CONNECTION_STRING);
using (SqlConnection connection = new SqlConnection(TEST_CONNECTION_STRING))
using (SqlCommand command = new SqlCommand("SELECT TestField FROM dbo.TestTable", connection))
{
connection.Open();
int changesReceived = 0;
OnChangeEventHandler onChange = null;
onChange = (s, e) =>
{
if (e.Info == SqlNotificationInfo.Insert)
{
changesReceived++;
}
// SqlDependency magic to receive events consequentially.
SqlDependency dep = (SqlDependency)s;
dep.OnChange -= onChange;
command.Notification = null;
dep = new SqlDependency(command);
dep.OnChange += onChange;
command.ExecuteReader().Close();
};
// Create a dependency and associate it with the SqlCommand.
SqlDependency dependency = new SqlDependency(command);
// Subscribe to the SqlDependency event.
dependency.OnChange += onChange;
// Execute the command.
command.ExecuteReader().Close();
MakeTableInsertChange(changesCount);
if (useAssert)
{
Assert.AreEqual(changesCount, changesReceived);
}
}
SqlDependency.Stop(TEST_CONNECTION_STRING);
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired)
{
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = new[] { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
GetData();
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
// This event will occur on a thread pool thread.
// Updating the UI from a worker thread is not permitted.
// The following code checks to see if it is safe to
// update the UI.
ISynchronizeInvoke i = new DispatcherSynchronizeInvoke(this.Dispatcher);
// If InvokeRequired returns True, the code
// is executing on a worker thread.
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch.
OnChangeEventHandler tempDelegate =
new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}
// Remove the handler, since it is only good
// for a single notification.
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
// At this point, the code is executing on the
// UI thread, so it is safe to update the UI.
// Reload the dataset that is bound to the grid.
if (!timer.IsEnabled)
{
timer.Interval = new TimeSpan(0, 0, 5);
timer.Tick += Timer_Tick;
timer.Start();
}
}
void SubscribeChange(SqlCommand template)
{
var notification = new SqlDependency(template);
OnChangeEventHandler onChange = null;
onChange = (sender, args) =>
{
((SqlDependency)sender).OnChange -= onChange;
if (args.Type == SqlNotificationType.Subscribe)
{
Notifications.OnError(new ArgumentException("The command is not compliant with the notifications system or the database is not enabled for notifications."));
}
else
{
Notifications.OnNext(template.Clone());
}
};
notification.OnChange += onChange;
template.ExecuteScalar();
}
public void RegisterNotificationImportResult(OnChangeEventHandler eventHandler)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string query = "SELECT Id, Status, Seen FROM [dbo].[Import]";
using (var command = new SqlCommand(query, connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(eventHandler);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
/// <summary>
/// 数据库依赖方法
/// </summary>
/// <param name="strCon"></param>
/// <param name="strSql"></param>
/// <param name="sqlDep_OnChange"></param>
private static void AddSqlDependency(string strCon, string strSql, OnChangeEventHandler sqlDep_OnChange)
{
try
{
using (SqlConnection conn = new SqlConnection(strCon))
{
SqlCommand comm = new SqlCommand(strSql, conn);
SqlDependency sqlDep = new SqlDependency(comm);
sqlDep.OnChange += sqlDep_OnChange;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
comm.ExecuteNonQuery();
}
}
catch (Exception ex)
{
LogUtil.Logger.Error(ex.Message, ex);
}
}
/// <summary>
/// 监测数据库数据更新情况
/// </summary>
/// <param name="changeMethod">dependency_OnChange(object sender, SqlNotificationEventArgs e)</param>
/// <param name="sql">select ID,UserID,[Message] From [dbo].[Messages]</param>
public static void autoUpdateForm(OnChangeEventHandler changeMethod, String sql)
{
try
{
using (SqlConnection connection = Connection.Connection.getSqlConnection())
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += changeMethod;
SqlDataReader objReader = command.ExecuteReader();
}
}
}
catch
{
return;
}
}
/// <summary>
/// 监测数据库数据更新情况
/// </summary>
/// <param name="changeMethod">dependency_OnChange(object sender, SqlNotificationEventArgs e)</param>
/// <param name="sql">select ID,UserID,[Message] From [dbo].[Messages]</param>
public static void autoUpdateForm(OnChangeEventHandler changeMethod,String sql)
{
try
{
using (SqlConnection connection = Connection.Connection.getSqlConnection())
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += changeMethod;
SqlDataReader objReader = command.ExecuteReader();
}
}
}
catch
{
return;
}
}
public List <@type> ExecReaderSelSPHub <@type>(string SProc, OnChangeEventHandler even = null, params SqlParameter[] Params) where @type : class
{
using (var conne = new SqlConnection(connStr))
{
conne.Open();
var comm = new SqlCommand(SProc, conne);
comm.Notification = null;
SqlDependency dependency = new SqlDependency(comm);
dependency.OnChange += new OnChangeEventHandler(even);
comm.CommandType = CommandType.StoredProcedure;
if (Params != null)
{
comm.Parameters.AddRange(Params);
}
var rdr = comm.ExecuteReader(CommandBehavior.CloseConnection);
List <@type> result = rdr.ToList <@type>();
return(result);
}
}
/// <summary>
/// Adds a SQL Dependency based change listener.
/// </summary>
/// <param name="eventHandler">The event handler.</param>
/// <remarks>This requires SQL Dependency to be active.</remarks>
public void AddChangeListener(OnChangeEventHandler eventHandler)
{
if (eventHandler == null)
{
throw new ArgumentNullException(nameof(eventHandler), $"{nameof(eventHandler)} is null.");
}
var disp = DataSource as SqlServerDataSource;
if (disp == null)
{
throw new InvalidOperationException("Change listeners can only be added to non-transactional data sources.");
}
if (!disp.IsSqlDependencyActive)
{
throw new InvalidOperationException("SQL Dependency is not active on the associated data source.");
}
m_OnChangeEventHandler += eventHandler;
return;
}
public void Observe(string query, string connectionString, OnChangeEventHandler func)
{
try
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand(query, connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += func;
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
_logger.Error("Error observing table. Query: {0}", query, ex);
throw;
}
}
public void ExecuteDataReaderDependency(SqlPreCommandSimple preCommand, OnChangeEventHandler change, Action reconect, Action<FieldReader> forEach, CommandType commandType)
{
bool reconected = false;
retry:
try
{
using (SqlConnection con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL-Dependency"))
using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql()))
{
try
{
if (change != null)
{
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += change;
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
FieldReader fr = new FieldReader(reader);
int row = -1;
try
{
while (reader.Read())
{
row++;
forEach(fr);
}
}
catch (Exception ex)
{
FieldReaderException fieldEx = fr.CreateFieldReaderException(ex);
fieldEx.Command = preCommand;
fieldEx.Row = row;
throw fieldEx;
}
}
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
throw;
throw nex;
}
}
}
catch (InvalidOperationException ioe)
{
if (ioe.Message.Contains("SqlDependency.Start()") && !reconected)
{
reconect();
reconected = true;
goto retry;
}
throw;
}
}
/// <summary>
/// Különböző üzeneteket vet be, ha a megfigyelt táblában változás történik
/// </summary>
/// <author> KZ,VT </author>
/// <date> 2013.08.23 </date>
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
int preMessageID = lastMessageID;
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch.
OnChangeEventHandler tempDelegate =
new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
// IAsyncResult result =
this.BeginInvoke(tempDelegate, args);
return;
}
try
{
isNetwork = NetworkInterface.GetIsNetworkAvailable();
if (isNetwork )
{
SqlDependency dependency = sender as SqlDependency;
// Notices are only a one shot deal
// so remove the existing one so a new
// one can be added
dependency.OnChange -= dependency_OnChange;
GetData(Constans.enumSqlDependencyTimeOut.OnChange);
// if lastMessageID
if (dataToWatch.Tables[0]!=null && dataToWatch.Tables[0].Rows.Count!=0) {
lastMessageID = (int)dataToWatch.Tables[0].Rows[0][9];
if (preMessageID != lastMessageID)
{
MessageType(dataToWatch.Tables[0].Rows[0][2].ToString());
}
}
}
SQLdepSource = e.Source.ToString();
SQLdepInfo = e.Info.ToString();
SQLdepType = e.Type.ToString();
Console.WriteLine( "sqldependencyinfo" + e.Source.ToString() + e.Info.ToString() + e.Type.ToString());
}
catch (Exception ex)
{
ExceptionHandler.WriteLog(ex);
}
return;
}
internal EventContextPair(OnChangeEventHandler eventHandler, SqlDependency dependency)
{
this._eventHandler = eventHandler;
this._context = ExecutionContext.Capture();
this._dependency = dependency;
}
private void listen_ri_cheng()
{
try
{
long thisDay = DateTime.Now.Ticks;
long nextDay = DateTime.Now.Date.Ticks + new DateTime(1, 1, 2).Date.Ticks;
//设置监测
OnChangeEventHandler onChange = new OnChangeEventHandler(ri_cheng_onChange);
BaseService.autoUpdateForm(onChange, "select ID from [dbo].LOG_T_STAFFSCHEDULE where WkTUserId=" + user.Id + " and STATE=" + (int)IEntity.stateEnum.Normal + " and IfRemind=" + (int)StaffSchedule.IfRemindEnum.Renmind + " and ScheduleTime>=" + thisDay + " and ShceduleTime<" + nextDay);
}
catch
{
}
}
internal EventContextPair(OnChangeEventHandler eventHandler, SqlDependency dependency) {
Debug.Assert(eventHandler != null && dependency != null, "Unexpected arguments!");
_eventHandler = eventHandler;
_context = ExecutionContext.Capture();
_dependency = dependency;
}
private void dependency_OnChange(
object sender, SqlNotificationEventArgs e)
{
// This event will occur on a thread pool thread.
// Updating the UI from a worker thread is not permitted.
// The following code checks to see if it is safe to
// update the UI.
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
// If InvokeRequired returns True, the code
// is executing on a worker thread.
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch.
OnChangeEventHandler tempDelegate =
new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}
// Remove the handler, since it is only good
// for a single notification.
SqlDependency dependency =
(SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
// At this point, the code is executing on the
// UI thread, so it is safe to update the UI.
++changeCount;
label1.Text = String.Format(statusMessage, changeCount);
// Add information from the event arguments to the list box
// for debugging purposes only.
listBox1.Items.Clear();
listBox1.Items.Add("Info: " + e.Info.ToString());
listBox1.Items.Add("Source: " + e.Source.ToString());
listBox1.Items.Add("Type: " + e.Type.ToString());
// Reload the dataset that is bound to the grid.
GetData();
}
