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;
}
}