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(); }
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); } }
/// <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); }
/// <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; } }