For a web project we use and OracleDependency to listen to changes on a few tables and update the clients using signalR. This works fine under normal circumstances.
Below you can see the code of a helper object i wrote:
namespace ONP.Repositories.Helpers
{
using System.Configuration;
using System.Data;
using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;
using OnChangeEventHandler = Oracle.ManagedDataAccess.Client.OnChangeEventHandler;
public class OracleNotificationHelper : IOracleNotificationHelper
{
public OracleDependency RegisterOracleNotification(string[] queries, OnChangeEventHandler del)
{
OracleDependency sqlDependency;
OracleConnection oracleConnection = null;
try
{
// create DataBase connection
oracleConnection = new OracleConnection(GetConnectionString());
oracleConnection.Open();
// Set the port property of the oracle dependency
if (OracleDependency.Port == -1) // only when it's not already set.
{
OracleDependency.Port = int.Parse(ConfigurationManager.AppSettings["OracleNotificationPort"]);
}
// create depency
var firstCommand = new OracleCommand(queries[0], oracleConnection) {CommandType = CommandType.Text};
sqlDependency = new OracleDependency(firstCommand, false, 0, false);
// exec db command == register notification
firstCommand.ExecuteNonQuery();
for (int i = 1; i < queries.Length; i++)
{
// Add other commands
var sqlCommand = new OracleCommand(queries[i], oracleConnection) {CommandType = CommandType.Text};
sqlDependency.AddCommandDependency(sqlCommand);
sqlCommand.ExecuteNonQuery();
}
sqlDependency.OnChange += del;
}
finally
{
oracleConnection?.Close();
oracleConnection?.Dispose();
}
return sqlDependency;
}
public void DeregisterOracleNotification(OracleDependency dependency, OnChangeEventHandler del)
{
// remove event handler
if (dependency != null)
{
OracleConnection dbConn = null;
try
{
// create DataBase connection (it's returned open).
dbConn = new OracleConnection(GetConnectionString());
dbConn.Open();
dependency.OnChange -= del;
dependency.RemoveRegistration(dbConn);
}
finally
{
// set object to null and let it be eaten by the garbage collector.
dbConn?.Close();
dbConn?.Dispose();
}
}
}
private string GetConnectionString()
{
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ONPEntities"].ConnectionString;
var builder = new EntityConnectionStringBuilder(connectionString);
var builder2 = new SqlConnectionStringBuilder(builder.ProviderConnectionString);
return builder2.ToString();
}
}
}
So when the project starts up, the dependency with port 1005 is created using the RegisterOracleNotification method and on Application_End the DeregisterOracleNotification method is called. The problem is that when I for example change the web.config file while the site is running, the site is restarted and application_end is called, but the used port is not closed. When it then tries to create the OracleDependency again Im getting an error that the port is already in use. Does anyone know how I can close the port?
The error after the site restarts:
[SocketException (0x2740): Elk socketadres (protocol/netwerkadres/poort) kan normaal slechts één keer worden gebruikt]
(Translated: System.Net.Sockets.SocketException: Only one usage of each socket address (protocol/network address/port) is normally permitted)
Oracle server version info (Update not possible)
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
.Net lib version:
"Oracle.ManagedDataAccess": "12.1.24160719"
Thank you.