private void button1_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "SELECT * FROM Products";
OracleDependency.Port = 1200;
OracleDependency _dep = new OracleDependency(_cmdObj);
//Set notification settings
_dep.QueryBasedNotification = false;
_cmdObj.Notification.IsNotifiedOnce = false;
_dep.OnChange += new OnChangeEventHandler(OnNotificationReceived);
_cmdObj.ExecuteNonQuery();
MessageBox.Show("Change Notification Registered!");
_BoundToDB = true;
//Populate the DataGridView with data from the Products table
RefreshGrid();
while (_BoundToDB == true)
{
Application.DoEvents();
}
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
await Task.Yield();
_connection = new OracleConnection(_settings.GetConnectionString());
_connection.Open();
Console.WriteLine("Connected to ORACLE");
var sql = @$ "select * from VAN_SUBSIDIARY";
var cmd = new OracleCommand(sql, _connection);
// OracleDependency.Port = 49161;
// OracleDependency.Address = "adyensync-oracle";
var dependency = new OracleDependency(cmd);
// var orderId = 41851206;
dependency.QueryBasedNotification = false;
// dependency.AddCommandDependency(cmd);
dependency.OnChange += dependency_OnChange;
cmd.Notification.IsNotifiedOnce = false;
// cmd.Notification.IsPersistent = true;
// cmd.Notification.Timeout = 0;
cmd.AddRowid = true;
_dependecy = dependency;
try
{
cmd.ExecuteNonQuery();
Console.WriteLine("Subscribed");
await Task.Delay(1000, stoppingToken);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public ActionResult DBChange()
{
//initiate connetion
//get connection paramters from web.config
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings[ConfigurationManager.AppSettings["DBString"]].ConnectionString);
OracleCommand cmd = new OracleCommand();
//create the slect parameters
cmd.CommandText = "select ROWID, QUEUE_ARN, QUEUE_ID, QUEUE_NUMBER, ACTIVITY_DATE from KU_CUSTOM.ugapp_queue";
cmd.Connection = con;
con.Open();
//initiate
OracleDependency dependency = new OracleDependency(cmd);
dependency.QueryBasedNotification = true;
cmd.Notification.IsNotifiedOnce = false;
//on change call the action method or function.. our case alert user
dependency.OnChange += new OnChangeEventHandler(AlertUser);
//register the current state
cmd.ExecuteReader();
return(RedirectToAction("Index", "home"));
}
public void registerListenerTable()
{
try
{
//register code
string message = string.Empty;
//select snapshot from table...
OracleCommand selectCommand = new OracleCommand("select * from " + tableName, connection);
selectCommand.AddRowid = true;
//oracle dependancy saved screen shot of table and will be fire if any changes added to table
OracleDependency dependency = new OracleDependency(selectCommand);
selectCommand.Notification.IsNotifiedOnce = false;
//if false in all changes in table will fire event
dependency.QueryBasedNotification = false;
selectCommand.Notification.IsNotifiedOnce = false;
//onchanged fire event
dependency.OnChange += new OnChangeEventHandler(OnTableChange);
//execute to save first snapshot
OracleDataReader reader = selectCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
message = reader[0].ToString();
}
}
catch (Exception e)
{
}
}
public void StopService()
{
if (isRunning)
{
OracleDependency oraDep = OracleDependency.GetOracleDependency(oracleDependencyId);
if (oracleDependencyId == null)
{
throw new Exception("Unknown oracle dep");
}
using (OracleConnection conn = new OracleConnection(_connString))
{
conn.Open();
try
{
oraDep.RemoveRegistration(conn);
}
catch
{
throw;
}
finally
{
conn.Close();
}
}
isRunning = false;
}
}
private void button1_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "SELECT * FROM Products";
OracleDependency.Port = 1200;
_globalDep = new OracleDependency(_cmdObj);
_cmdObj.ExecuteNonQuery();
MessageBox.Show("Change Notification Registered!");
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
//Start a timer to keep polling for changes in the table every 1 second
timer1.Tick += new EventHandler(TimerTick);
timer1.Start();
}
/// <summary>
/// 创建监听
/// </summary>
/// <param name="StrConn">连接字符串</param>
/// <param name="SqlText">监听Sql</param>
/// <returns></returns>
public OracleDependency CreateDependency(string StrConn, string SqlText)
{
if (dictDependency == null)
{
dictDependency = new Dictionary <string, OracleDependency>();
dictCommand = new Dictionary <string, OracleCommand>();
}
else
{
CheckListenCmd(StrConn, SqlText);
}
OracleConnection oConn = new OracleConnection(StrConn);
oConn.Open();
OracleCommand depCmd = new OracleCommand(SqlText, oConn);
OracleDependency oraDep = new OracleDependency(depCmd, false, 0, false);
//oraDep.RowidInfo = OracleRowidInfo.Include;//是否包含Rowid
//oraDep.QueryBasedNotification = true;//true：按指定查询监听false：按数据表监听
//以下属性可在OracleDependency的构造函数中设定
//depCmd.Notification.IsPersistent: False表示Notification将被存于内存中，True表示存于数据库中，选择True可以保证即便数据库重启之后，消息仍然不会丢失
//depCmd.Notification.IsNotifiedOnce = false;是否在一次Notification后立即移除此次注册
//depCmd.Notification.Timeout = 0;此次注册的超时时间（秒），超过此时间，注册将被自动移除。0表示不超时。
dictConnection.Add(oraDep.Id, oConn);
dictDependency.Add(oraDep.Id, oraDep);
dictCommand.Add(oraDep.Id, depCmd);
return(oraDep);
}
private void button1_Click(object sender, EventArgs e)
{
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
//Register the change notification
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "SELECT * FROM Products";
OracleDependency.Port = 1200;
OracleDependency _dep = new OracleDependency(_cmdObj);
_dep.QueryBasedNotification = false;
_dep.OnChange += new OnChangeEventHandler(OnNotificationReceived);
_cmdObj.ExecuteNonQuery();
//Wait in a loop for the notification
while (_NotificationRaised == false)
{
Application.DoEvents();
}
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void StartService()
{
if (isRunning == false)
{
using (OracleConnection conn = new OracleConnection(_connString))
using (OracleCommand cmd = new OracleCommand(_filterSqlString, conn))
{
conn.Open();
try
{
cmd.AddRowid = true;
OracleDependency oraDep = new OracleDependency(cmd);
cmd.Notification.IsNotifiedOnce = false;
oraDep.OnChange += new OnChangeEventHandler(OnDBNotificationHandler);
oracleDependencyId = oraDep.Id;
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "INTERFACE_RUN_TIMES");
isRunning = true;
}
catch
{
throw;
}
finally
{
conn.Close();
}
}
}
}
/// <summary>
/// 停止监听
/// </summary>
public static void StopListen(string ListenID)
{
OracleDependency dep = DataClient.GetDependencyByID(ListenID);
dep.OnChange -= new OnChangeEventHandler(Data_OnChange);
DataClient.RemoveDependency(ListenID);
DictListen.Remove(ListenID);
}
public DatabaseFactory(int dbCode)
{
if (dep == null)
{
dep = new OracleDependency();
}
switchDBConnection(dbCode);
}
static void Main()
{
//Directory where your cloud or database credentials are located, if applicable
OracleConfiguration.TnsAdmin = Constants.TnsDir;
OracleConfiguration.WalletLocation = Constants.WalletDir;
//Turn on Client Initiated Continuous Query Notification
OracleConfiguration.UseClientInitiatedCQN = true;
using (OracleConnection con = new OracleConnection(Constants.ConString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
Console.WriteLine("Successfully connected to Oracle Database");
Console.WriteLine();
//CQN setup
OracleDependency dep = new OracleDependency(cmd);
cmd.Notification.IsNotifiedOnce = false;
dep.OnChange += new OnChangeEventHandler(JSON_Notification);
//Retrieve purchase order stored as JSON (OSON) type
//Store JSON data as string in disconnected DataSet
cmd.CommandText = Constants.Query;
OracleDataAdapter da = new OracleDataAdapter(cmd);
using (DataSet ds = new DataSet())
{
da.Fill(ds);
foreach (DataTable table in ds.Tables)
{
foreach (DataRow row in table.Rows)
{
//Output the query result to the console
Console.WriteLine(row[Constants.JsonColumn].ToString());
}
}
//While result set is disconnected from database, modify the purchase
// order data through ODT or SQL*Plus, such as with the following SQL:
//UPDATE j_purchaseorder SET po_document = json_transform(po_document, SET '$.LastUpdated' = SYSDATE);
//Changing the data on database triggers the CICQN event handler.
//Each time the update statement runs, a new notification will be sent to the app.
Console.ReadLine();
}
da.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
/// <summary>
/// Register callback and associate query result set with the command
/// </summary>
/// <param name="command"></param>
private void RegisterNotification(OracleCommand oraclelCmd)
{
oraclelCmd.AddRowid = true;
OracleDataReader reader = null;
try
{
// Create an OracleDependency instance and bind it to an OracleCommand
// instance.
// When an OracleDependency instance is bound to an OracleCommand
// instance, an OracleNotificationRequest is created and is set in the
// OracleCommand's Notification property. This indicates subsequent
// execution of command will register the notification.
// By default, the notification request is using the Database Change
// Notification.
this._oracleDep = new OracleDependency(oraclelCmd, // command instance to which dependency is bound
false, // notify once. we have to set it to false to track database shutdown and startup.
// Oracle do not provide notification on restart like SQL do. we will remove the dependency
// once we receive the startup call
0, // amount of time in seconds that the registration remains active. 0 means
// it'll stay active forever.
false); // Database performance is faster if the message is stored
// in an in-memory queue rather than in the database queue.
this._oracleDep.OnChange +=
new Oracle.ManagedDataAccess.Client.OnChangeEventHandler(this.OnOracleDependencyChanged);
// The notification registration is created and the query result sets
// associated with the command can be invalidated when there is a
// change. When the first notification registration occurs, the
// notification listener is started.
{
reader = oraclelCmd.ExecuteReader();
try
{
this._rowids.Clear();
// We go through all the records that were retrieved and save the rowid's
// That is only if user has included rowid in select statement
while (reader.Read())
{
this._rowids.Add(reader["rowid"]);
}
}
catch (IndexOutOfRangeException)
{
// thrown if user has not provided rowid as part of select query
}
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 停止所有监听
/// </summary>
public static void StopListen()
{
foreach (string ListenID in DictListen.Keys)
{
OracleDependency dep = DataClient.GetDependencyByID(ListenID);
dep.OnChange -= new OnChangeEventHandler(Data_OnChange);
DataClient.RemoveDependency(ListenID);
}
DictListen.Clear();
}
public SignalrDbDependency(OracleDependency dep)
{
_dep = dep;
_dep.OnChange += (sender, args) =>
{
if (OnChanged != null)
{
OnChanged(sender, new SignalRDbNotificationEventArgs(args));
}
};
}
public ISignalRDbDependency CreateDbDependency(IDbCommand command, bool isNotifiedOnce, long timeoutInSec, bool isPersistent)
{
var oracleCommand = command as OracleCommand;
if (oracleCommand == null)
{
throw new NotSupportedException();
}
OracleDependency od = new OracleDependency(oracleCommand, isNotifiedOnce, timeoutInSec, isPersistent);
return(new SignalrDbDependency(od));
}
/// <summary>
/// 开始监听
/// </summary>
public static string StartListen(string SqlText)
{
if (DictListen == null)
{
DictListen = new Dictionary <string, string>();
}
DBConnection dbConn = DBConfig.GetDBConnection(ClientCode, DbName);
OracleDependency dep = DataClient.CreateDependency(dbConn.ConnectionString, SqlText);
dep.OnChange += new OnChangeEventHandler(Data_OnChange);
DictListen.Add(dep.Id, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
DataClient.BeginListen(dep.Id);
return(dep.Id);
}
public void Connect()
{
conn = new OracleConnection("Data Source=192.168.128.152:1521/ora11g;PERSIST SECURITY INFO=True;User ID=d4a11;Password=d4a");
//conn = new OracleConnection("Data Source=212.152.179.117:1521/ora11g;PERSIST SECURITY INFO=True;User ID=d4a11;Password=d4a");
var cmd = new OracleCommand("select * from visitors", conn);
conn.Open();
OracleDependency.Port = -1;
dep = new OracleDependency(cmd);
dep.QueryBasedNotification = false;
cmd.Notification.IsNotifiedOnce = false;
dep.OnChange += new OnChangeEventHandler(dep_OnChange);
cmd.ExecuteNonQuery();
}
public static void OraclePipeListener()
{
con = new OracleConnection(ConfigurationManager.ConnectionStrings["AOPEntities"].ConnectionString);
con.Open();
OracleCommand cmd = new OracleCommand("select * from VTMOS_EVENTOS", con);
dep = new Oracle.ManagedDataAccess.Client.OracleDependency(cmd);
dep.OnChange += new OnChangeEventHandler(DependencyChange);
cmd.Notification.IsNotifiedOnce = false;
cmd.AddRowid = true;
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
public Task StartListener() => Task.Run(() =>
{
var dependency = new OracleDependency
{
QueryBasedNotification = false
};
dependency.OnChange += this.OnNotification;
var conn = this.CreateConnection();
var cmd = conn.CreateCommand();
dependency.AddCommandDependency(cmd);
cmd.CommandText = "SELECT * FROM CHATS";
cmd.AddRowid = true;
cmd.Notification.IsNotifiedOnce = false;
cmd.ExecuteNonQuery();
});
public OraclePipeListener(string connectionString, string dataBase, string tableName)
{
m_DateBase = dataBase;
m_TableName = tableName;
m_Connection = new OracleConnection(connectionString);
m_Connection.Open();
m_Command = m_Connection.CreateCommand();
m_Command.CommandText = string.Format("SELECT * FROM {0}.{1} WHERE rownum = 1", DateBase,TableName);
m_Dependency = new OracleDependency(m_Command);
m_Dependency.OnChange += new OnChangeEventHandler(DependencyChange);
m_Command.Notification.IsNotifiedOnce = false;
m_Command.AddRowid = true;
m_Command.ExecuteNonQuery();
m_Connection.Close();
m_Connection.Dispose();
}
/// <summary>
/// 移除监听
/// </summary>
public void RemoveDependency(string ListenID)
{
OracleDependency oraDep = dictDependency[ListenID];
if (oraDep != null)
{
OracleConnection oConn = dictConnection[ListenID];
oraDep.RemoveRegistration(oConn);
oraDep = null;
OracleCommand depCmd = dictCommand[ListenID];
depCmd.Dispose();
depCmd = null;
oConn.Close();
oConn.Dispose();
oConn = null;
dictConnection.Remove(ListenID);
dictCommand.Remove(ListenID);
dictDependency.Remove(ListenID);
}
}
private void BtnStop_Click(object sender, EventArgs e)
{
try
{
if (cbbMode.SelectedIndex == 1)
{
this.timer?.Stop();
this.timer?.Dispose();
this.timer.Tick -= new EventHandler(TimerIntervalCallback);
SetTextMonitor("Push SMS to Queue timer stoped!", Color.Aqua);
}
if (this.oracleDependency != null && this.oracleDependency.IsEnabled)
{
this.oracleDependency.RemoveRegistration(this.connection);
this.oracleDependency.OnChange -= new OnChangeEventHandler(OracleListenerCallback);
this.connection.Close();
this.connection.Dispose();
this.oracleDependency = null;
SetTextMonitor("Push SMS to Queue listener stoped!", Color.Aqua);
}
this.SMS_COUNT = 0;
btnStop.Enabled = false;
btnStart.Enabled = true;
txtOracleConnection.Enabled = true;
txtRabbitConnection.Enabled = true;
cbbSmsType.Enabled = true;
cbbCountSMS.Enabled = true;
cbbMode.Enabled = true;
cbbInterval.Enabled = true;
txtLimitLog.Enabled = true;
}
catch (Exception ex)
{
SetTextMonitor(ex.ToString(), Color.Red);
}
}
public void Connect()
{
try
{
conn = new OracleConnection(
"Data Source=192.168.128.152/ora11g;PERSIST SECURITY INFO=True;User ID=d4a06;Password=d4a");
var cmd = new OracleCommand("select * from stand", conn);
conn.Open();
OracleDependency.Port = -1;
dep = new OracleDependency(cmd);
dep.QueryBasedNotification = false;
cmd.Notification.IsNotifiedOnce = false;
dep.OnChange += new OnChangeEventHandler(dep_OnChange);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public void Dispose()
{
if (this.dbAdapter != null)
{
if (this.oraDependency != null)
{
this.oraDependency.OnChange -= this.Dependency_OnChange;
if (this.dbAdapter.Connection is OracleConnection)
{
this.oraDependency.RemoveRegistration(this.dbAdapter.Connection as OracleConnection);
}
this.oraDependency = null;
}
this.Disconnect();
this.dbAdapter.Dispose();
this.dbAdapter = null;
}
}
public void Subscribe(Action onUpdate)
{
var command = new OracleCommand("SELECT ID, NAME FROM AWESOME.MOVIE", _connection);
_connection.Open();
command.AddRowid = true;
var dependency = new OracleDependency(command);
command.Notification.IsNotifiedOnce = false;
dependency.OnChange += new OnChangeEventHandler((object o, OracleNotificationEventArgs e) =>
{
onUpdate();
});
command.ExecuteNonQuery();
_connection.Close();
command.Dispose();
}
public void connect()
{
try
{
connection = new OracleConnection("Data Source=192.168.128.152:1521/ora11g;PERSIST SECURITY INFO=True;User ID=d5a18;Password=d5a");
connection.Open();
}
catch (OracleException e)
{
connection = new OracleConnection("Data Source=212.152.179.117:1521/ora11g;PERSIST SECURITY INFO=True;User ID=d5a18;Password=d5a");
connection.Open();
}
// create dependency
OracleCommand command = new OracleCommand("select * from " + TABLE + "", connection);
dependency = new OracleDependency(command);
dependency.QueryBasedNotification = false;
command.Notification.IsNotifiedOnce = false;
//dependency.OnChange += new OnChangeEventHandler();
command.ExecuteNonQuery();
}
public void InitDBObjects(OracleConnection connection)
{
string sSelect = string.Empty;
try
{
//PL SQL commands for updating TQS status
_cmdReInitTQS = DBCommand_Write.ReInitTQS_Status(_oraConnection);
_cmdUpdTQSStatus = DBCommand_Write.UpdTQS_Status(_oraConnection);
//select command TQS
_cmdSel = DBCommand_Write.SelTQS(_oraConnection);
sSelect = "SELECT * FROM " + _sSelectedTable + " WHERE STATUS = 0";
_cmdSel.CommandText = sSelect;
//check if oracle dependency is active
if (sSelect != string.Empty && _sDBMode == "DEP" && _dependency == null)
{
_cmdNotification = new OracleCommand(sSelect, _oraConnection);
_cmdNotification.AddRowid = true;
_cmdNotification.NotificationAutoEnlist = true;
_cmdNotification.CommandTimeout = 2000;
_dependency = new OracleDependency(_cmdNotification, false, 0, false);
OracleDependency.Port = _iDependecyPort;
ServiceBaseX._logger.Log(Category.HLite, MethodBase.GetCurrentMethod().DeclaringType.Name + "_" + MethodBase.GetCurrentMethod().Name + ": OracleDependency.Port = {0}", OracleDependency.Port);
_dependency.OnChange += new OnChangeEventHandler(dependency_OnChangeOracle);
_cmdNotification.ExecuteNonQuery();
}
}
catch (Exception ex)
{
ServiceBaseX._logger.Log(Category.Error, MethodBase.GetCurrentMethod().DeclaringType.Name + "_" + MethodBase.GetCurrentMethod().Name + ": " + ex.Message);
}
}
public void CreateOracleListnerForCustomerTable()
{
string count = "";
using (OracleConnection con = new OracleConnection(Connection.connectionString))
{
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
using (OracleCommand command = con.CreateCommand())
{
string sql = "select count(*) FROM L2_CUSTOMERS";
command.CommandText = sql;
OracleDependency dep = new OracleDependency(command);
// command.AddRowid = true;
dep.QueryBasedNotification = true;
dep.OnChange += new OnChangeEventHandler(dep_OnChange);
command.Notification.IsNotifiedOnce = false;
count = Convert.ToString(command.ExecuteScalar());
}
}
}
public static Dependency GetProtoBufDependency(CacheDependency cacheDependency, Dependency dependency)
{
if (cacheDependency is Runtime.Dependencies.FileDependency)
{
Runtime.Dependencies.FileDependency fileDependency = cacheDependency as Runtime.Dependencies.FileDependency;
Protobuf.FileDependency protoDependency = new Protobuf.FileDependency();
protoDependency.filePaths.AddRange(fileDependency.fileNames);
protoDependency.startAfter = fileDependency.StartAfterTicks;
dependency.fileDep.Add(protoDependency);
return(dependency);
}
else if (cacheDependency is Runtime.Dependencies.KeyDependency)
{
Runtime.Dependencies.KeyDependency keyDependency = cacheDependency as Runtime.Dependencies.KeyDependency;
Alachisoft.NCache.Common.Protobuf.KeyDependency protoDependency = new Alachisoft.NCache.Common.Protobuf.KeyDependency();
protoDependency.keys.AddRange(keyDependency.CacheKeys);
protoDependency.startAfter = keyDependency.StartAfterTicks;
dependency.keyDep.Add(protoDependency);
return(dependency);
}
else if (cacheDependency is Runtime.Dependencies.DBCacheDependency)
{
Runtime.Dependencies.DBCacheDependency dbDependency = cacheDependency as Runtime.Dependencies.DBCacheDependency;
switch (dbDependency.Type)
{
case Runtime.Dependencies.DBDependencyType.OleDbCacheDependency:
OleDbDependency oleDbDependency = new OleDbDependency();
oleDbDependency.connectionString = dbDependency.ConnectionString;
oleDbDependency.dbCacheKey = dbDependency.PrimaryKey;
dependency.oleDbDep.Add(oleDbDependency);
break;
case Runtime.Dependencies.DBDependencyType.SqlCacheDependency:
Sql7Dependency sqlDependency = new Sql7Dependency();
sqlDependency.connectionString = dbDependency.ConnectionString;
sqlDependency.dbCacheKey = dbDependency.PrimaryKey;
dependency.sql7Dep.Add(sqlDependency);
break;
}
return(dependency);
}
else if (cacheDependency is Runtime.Dependencies.SqlCacheDependency)
{
Runtime.Dependencies.SqlCacheDependency sqlDependency = cacheDependency as Runtime.Dependencies.SqlCacheDependency;
YukonDependency yukonDependency = new YukonDependency();
yukonDependency.commandType = Convert.ToInt32(sqlDependency.CommandType);
yukonDependency.connectionString = sqlDependency.ConnectionString;
yukonDependency.query = sqlDependency.CommandText;
if (sqlDependency.CommandParams != null)
{
foreach (KeyValuePair <string, Runtime.Dependencies.SqlCmdParams> pair in sqlDependency.CommandParams)
{
Runtime.Dependencies.SqlCmdParams param = pair.Value;
YukonParam yukonParam = new YukonParam();
yukonParam.key = pair.Key;
YukonCommandParam yukonCmdParam = new YukonCommandParam();
yukonCmdParam.dbType = (int)param.SqlParamType;
yukonCmdParam.direction = (int)param.SqlParamDir;
yukonCmdParam.isNullable = param.IsNullable;
yukonCmdParam.localeId = param.LocaleID;
yukonCmdParam.offset = param.Offset;
yukonCmdParam.precision = param.Precision;
yukonCmdParam.scale = param.Scale;
yukonCmdParam.size = param.Size;
yukonCmdParam.sourceColumn = param.SourceColumn;
yukonCmdParam.sourceColumnNull = param.SourceColumnNullMapping;
yukonCmdParam.sqlValue = param.SqlValue != null?param.SqlValue.ToString() : "";
yukonCmdParam.version = (int)param.SrcVersion;
yukonCmdParam.typeName = param.TypeName;
yukonCmdParam.typeId = (int)param.Type;
yukonCmdParam.udtTypeName = param.UdtTypeName;
yukonCmdParam.nullValueProvided = param.Value == null ? true : false;
if (!yukonCmdParam.nullValueProvided)
{
if (param.Type == CmdParamsType.Binary || param.Type == CmdParamsType.VarBinary /*|| param.Type == CmdParamsType.Image */ || param.Type == CmdParamsType.Timestamp)
{
byte[] val = param.Value as byte[];
if (val == null)
{
throw new OperationFailedException("Expected 'System.Byte[]' value for parameter '" + param.SourceColumn + "'");
}
else
{
System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
yukonCmdParam.value = encoding.GetString(val);
}
}
else if (param.Type == CmdParamsType.DateTime || param.Type == CmdParamsType.DateTime2 || param.Type == CmdParamsType.Date || param.Type == CmdParamsType.SmallDateTime)
{
try
{
DateTime val = (DateTime)param.Value;
yukonCmdParam.value = val.Ticks.ToString();
}
catch (InvalidCastException ex)
{
throw new OperationFailedException("Expected 'System.DateTime' value for parameter type '" + param.Type + "'");
}
}
else if (param.Type == CmdParamsType.Time)
{
try
{
TimeSpan val = (TimeSpan)param.Value;
yukonCmdParam.value = val.Ticks.ToString();
}
catch (InvalidCastException ex)
{
throw new OperationFailedException("Expected 'System.TimeSpan' value for parameter '" + param.Type + "'");
}
}
else if (param.Type == CmdParamsType.DateTimeOffset)
{
try
{
DateTimeOffset val = (DateTimeOffset)param.Value;
yukonCmdParam.value = String.Concat(val.Date.Ticks, ",", val.Offset.Minutes);
}
catch (InvalidCastException ex)
{
throw new OperationFailedException("Expected 'System.DateTimeOffset' value for parameter '" + param.Type + "'");
}
}
else
{
yukonCmdParam.value = param.Value.ToString();
}
}
yukonParam.cmdParam = yukonCmdParam;
yukonDependency.param.Add(yukonParam);
}
}
dependency.yukonDep.Add(yukonDependency);
return(dependency);
}
else if (cacheDependency is Runtime.Dependencies.OracleCacheDependency)
{
Runtime.Dependencies.OracleCacheDependency oracleDependency = cacheDependency as Runtime.Dependencies.OracleCacheDependency;
OracleDependency protoDependency = new OracleDependency();
protoDependency.commandType = (int)oracleDependency.CommandType;
protoDependency.connectionString = oracleDependency.ConnectionString;
protoDependency.query = oracleDependency.CommandText;
if (oracleDependency.CommandParams != null)
{
foreach (KeyValuePair <string, Runtime.Dependencies.OracleCmdParams> pair in oracleDependency.CommandParams)
{
Runtime.Dependencies.OracleCmdParams oracleCommandParams = pair.Value;
OracleParam param = new OracleParam();
param.key = pair.Key;
OracleCommandParam oracleCmdParam = new OracleCommandParam();
oracleCmdParam.dbType = (int)oracleCommandParams.Type;
oracleCmdParam.direction = (int)oracleCommandParams.Direction;
oracleCmdParam.value = oracleCommandParams.Value != null?oracleCommandParams.Value.ToString() : "";
param.cmdParam = oracleCmdParam;
protoDependency.param.Add(param);
}
}
dependency.oracleDep.Add(protoDependency);
return(dependency);
}
else if (cacheDependency is Runtime.Dependencies.ExtensibleDependency)
{
Runtime.Dependencies.ExtensibleDependency extDependency = cacheDependency as Runtime.Dependencies.ExtensibleDependency;
IFormatter formatter = new BinaryFormatter();
MemoryStream stream = new MemoryStream();
formatter.Serialize(stream, extDependency);
Alachisoft.NCache.Common.Protobuf.ExtensibleDependency extensibleDependency = new Alachisoft.NCache.Common.Protobuf.ExtensibleDependency();
extensibleDependency.data = stream.ToArray();
dependency.xtDep.Add(extensibleDependency);
return(dependency);
}
else if (cacheDependency is NosDBDependency)
{
NosDBDependency sqlDependency = cacheDependency as NosDBDependency;
NosDbDependency nosDependency = new NosDbDependency();
nosDependency.timeout = Convert.ToInt32(sqlDependency.Timeout);
nosDependency.connectionString = sqlDependency.ConnectionString;
nosDependency.query = sqlDependency.CommandText;
if (sqlDependency.Parameters != null)
{
CommandHelper.PopulateValues(sqlDependency.Parameters, nosDependency.param);
}
dependency.NosDep.Add(nosDependency);
return(dependency);
}
else
{
foreach (Runtime.Dependencies.CacheDependency dep in cacheDependency.Dependencies)
{
dependency = GetProtoBufDependency(dep, dependency);
}
return(dependency);
}
}
/// <summary>Инициализирует процесс отслеживания изменений в БД</summary>
private void InitNotifier()
{
NotifierConnection = new OracleConnection(ConnectionString);
NotifierConnection.AutoCommit = false;
NotifierConnection.Open(); // открываем постоянное соединение с БД
// подписываемся на получение информации об обновлениях таблиц
Notifier = new OracleDependency();
Notifier.QueryBasedNotification = false;
Notifier.OnChange += Dependency_OnChange;
StringBuilder SQLText = new StringBuilder();
for (int i = 0; i < SharedDataInfo.Tables.Length; i++)
{
SQLText.AppendFormat("select 1 from {0} where 1=0", SharedDataInfo.Tables[i]);
if (i != SharedDataInfo.Tables.Length - 1)
SQLText.Append(" union all ");
}
NotifierCommand = NotifierConnection.CreateCommand(SQLText.ToString());
Notifier.AddCommandDependency(NotifierCommand);
ConfigureCommand(NotifierCommand);
NotifierCommand.ExecuteReader();
NotifierCheckThread = new Thread(CheckNotifier);
NotifierCheckThread.IsBackground = true;
NotifierCheckThread.Start();
Log.Message("Поток контроля нотификатора запущен. ThreadID: {0}", NotifierCheckThread.ManagedThreadId);
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
con = new OracleConnection("DATA SOURCE=172.16.2.77;PERSIST SECURITY INFO=True;USER ID=SMK; Password=smk");
OracleCommand cmd = new OracleCommand("select * from atest", con);
con.Open();
// Set the port number for the listener to listen for the notification
// request
OracleDependency.Port =1521;
// Create an OracleDependency instance and bind it to an OracleCommand
// instance.
// When an OracleDependency instance is bound to an OracleCommand
// instance, an OracleNotificationRequest is created and is set in the
// OracleCommand's Notification property. This indicates subsequent
// execution of command will register the notification.
// By default, the notification request is using the Database Change
// Notification.
dep = new OracleDependency(cmd);
// Add the event handler to handle the notification. The
// OnMyNotification method will be invoked when a notification message
// is received from the database
dep.OnChange +=
new OnChangeEventHandler(OnMyNotificaton);
// The notification registration is created and the query result sets
// associated with the command can be invalidated when there is a
// change. When the first notification registration occurs, the
// notification listener is started and the listener port number
// will be 1005.
cmd.ExecuteNonQuery();
/*
// Updating emp table so that a notification can be received when
// the emp table is updated.
// Start a transaction to update emp table
OracleTransaction txn = con.BeginTransaction();
// Create a new command which will update emp table
string updateCmdText =
"update emp set sal = sal + 10 where empno = 7782";
OracleCommand updateCmd = new OracleCommand(updateCmdText, con);
// Update the emp table
updateCmd.ExecuteNonQuery();
//When the transaction is committed, a notification will be sent from
//the database
txn.Commit();
* */
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
// Loop while waiting for notification
while (IsNotified == false)
{
Thread.Sleep(100);
}
}
private void BtnStart_Click(object sender, EventArgs e)
{
try
{
if (String.IsNullOrEmpty(txtOracleConnection.Text))
{
MessageBox.Show("Nhập chuỗi kết nối database!", "Thông báo");
return;
}
if (String.IsNullOrEmpty(cbbCountSMS.Text))
{
MessageBox.Show("Nhập số lượng SMS trên 1 truy vấn!", "Thông báo");
return;
}
if (String.IsNullOrEmpty(txtRabbitConnection.Text))
{
MessageBox.Show("Nhập chuỗi kết nối queue!", "Thông báo");
return;
}
if (String.IsNullOrEmpty(txtLimitLog.Text))
{
MessageBox.Show("Giới hạn log không đúng", "Thông báo");
return;
}
SetTextMonitor("Push SMS to Queue start!", Color.Aqua);
btnStart.Enabled = false;
btnStop.Enabled = true;
txtOracleConnection.Enabled = false;
txtRabbitConnection.Enabled = false;
cbbSmsType.Enabled = false;
cbbCountSMS.Enabled = false;
cbbMode.Enabled = false;
cbbInterval.Enabled = false;
txtLimitLog.Enabled = false;
this.rabbitHelper = new RabbitHelper();
this.rabbitHelper.Initialize();
if (cbbMode.SelectedIndex == 0)
{
this.connection = new OracleConnection(txtOracleConnection.Text);
using (OracleCommand command = new OracleCommand((cbbSmsType.SelectedIndex == 0) ? AppConfig.QUERY_SELECT_SMS : AppConfig.QUERY_SELECT_CAMPAIGN, this.connection)
{
AddRowid = true
})
{
connection.Open();
this.oracleDependency = new OracleDependency(command, false, 0, false);
this.oracleDependency.OnChange += new OnChangeEventHandler(OracleListenerCallback);
command.ExecuteNonQuery();
SetTextMonitor("Push SMS to Queue listener started!", Color.Aqua);
}
PushSMSToQueue();
}
else
{
if (String.IsNullOrEmpty(cbbInterval.Text))
{
MessageBox.Show("Nhập interval!", "Thông báo");
return;
}
this.timer = new Timer();
this.timer.Interval = Convert.ToInt32(cbbInterval.Text);
this.timer.Tick += new EventHandler(TimerIntervalCallback);
this.timer.Start();
SetTextMonitor("Push SMS to Queue timer started!", Color.Aqua);
}
}
catch (Exception ex)
{
btnStart.Enabled = true;
btnStop.Enabled = false;
if (this.oracleDependency != null)
{
this.oracleDependency.OnChange -= new OnChangeEventHandler(OracleListenerCallback);
}
SetTextMonitor("BtnStart_Click " + ex.ToString(), Color.Red);
logger.Error("BtnStart_Click", ex);
}
}
/// <summary>Удаление уведомителя</summary>
private void DisposeNotifier()
{
/*if (Notifiers != null)
{
foreach (var od in Notifiers.Values)
od.Dispose();
Notifiers.Clear();
Notifiers = null;
}*/
if (NotifierCommand != null)
{
NotifierCommand.Dispose();
NotifierCommand = null;
}
if (Notifier != null)
{
Notifier.OnChange -= Dependency_OnChange;
Notifier.Dispose();
Notifier = null;
}
if (NotifierCheckThread != null)
{
NotifierCheckThread.Interrupt(); // сигнализируем потоку о необходимости останова
if (!NotifierCheckThread.Join(TimeSpan.FromSeconds(2))) // ожидаем останова
NotifierCheckThread.Abort(); // терпение кончилось
}
// соединение убиваем в последнюю очередь
if (NotifierConnection != null)
{
try { NotifierConnection.Close(); }
catch { }
NotifierConnection.Dispose();
NotifierConnection = null;
}
}
