Web API and the Oracle Database Change Notification

See more:

C#

Oracle

database

threads

web

API

, +

Hi All,

I do not have a lot of experience with manually constructed async calls and keeping threads alive. And I suspect my question here is something to do with my lack of understanding in this.

I am looking to implement the Oracle Database Change Notification (same as this person) but in a C#.Net framework 4.0 web API. In my test, I want to get a single integer from a database table, and store it in a web server application cache. Then subscribe to the Oracle Database Change Notification system so when the database value changes, the Web API is called, which in turn updates the cache and resubmits the Oracle Database Change Notification request.

I have used the code in this article which is a console application and it works fine. I also found this example which I can get working using WCF and a subscription mechanism. However, I cannot get this to work in my Web API

I can get the data from the table initially, and like the WCF example, I setup an Oracle Dependency at the same time. I can see that this dependency is created on the USER_CHANGE_NOTIFICATION_REGS table and when I make a change on the database, it appears that the database sends the notification because the record disappears from USER_CHANGE_NOTIFICATION_REGS (which is what happens with the working examples). But my 'OnChange' event does not fire.

As I said, I think this is my lack on knowledge in the world of threads and async programming. I have been looking at async and await specifically, but the example code I have used does not seem to match up with the model (I may be wrong on this). I am doing some more reading around on the subject, but I was wondering if anyone could give me any comments on this. Some example code below.

The web api is really simple. It checks the application cache first

[HttpGet]
public string CanDo()
{
    bool newValue;
    string cachedValue = HttpContext.Current.Application["MyValue"] as string;
    if (cachedValue != null)
    {
        newValue = (cachedValue == "0");
    }
    else
    {
        newValue = (RegisterOracleWatch() == 0);
    }
    return newValue;
}

The manager is very similar to the example code from the oracle site. The OracleDependency _dep is declared at the top of the class.

private int RegisterOracleWatch()
{
    int res = 0; // assume success for this test

    try
    {
        HttpContext.Current.Application.Lock();

        using (var con = new OracleConnection(constr))
        {
            var cmd = new OracleCommand("select val from myTable ", con);
            con.Open();

            _dep = new OracleDependency(cmd);
            _dep.OnChange += OnOracleWatchNotificaton;
            object result = cmd.ExecuteScalar();

            if (null != result)
            {
                res = Convert.ToInt32(result);
                HttpContext.Current.Application["MyValue"] = res.ToString();
            }
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
    finally
    {
        HttpContext.Current.Application.UnLock();
    }
    return res;
}

Finally the return function is also simple. It just re-registers the Oracle Notification. But this function is not getting called.

public void OnOracleWatchNotificaton(object sender, OracleNotificationEventArgs e)
{
    try { RegisterOracleWatch(); }
    catch (Exception ex){ throw ex; }
}

If anyone can give me any pointers on where I should go with this, it would be greatly appreciated.
Thanks

p.s. I have checked that the ports are open