Web API和Oracle数据库更改通知 [英] Web API and the Oracle Database Change Notification

问题描述

大家好，



我没有很多手动构建异步调用和保持线程活动的经验。我怀疑我的问题与我对此缺乏了解有关。



我希望实现Oracle数据库更改通知（与此人）但在C＃.Net framework 4.0 Web API中。在我的测试中，我想从数据库表中获取一个整数，并将其存储在Web服务器应用程序缓存中。然后订阅Oracle数据库更改通知系统，以便在数据库值更改时调用Web API，然后更新缓存并重新提交Oracle数据库更改通知请求。



我使用了这篇文章中的代码控制台应用程序，它工作正常。我还找到了这个例子，我可以使用WCF工作和订阅机制。但是，我无法在我的Web API中使用它。



我最初可以从表中获取数据，就像WCF示例一样，我设置了Oracle Dependency at同一时间。我可以看到这个依赖项是在USER_CHANGE_NOTIFICATION_REGS表上创建的，当我对数据库进行更改时，似乎数据库发送通知，因为记录从USER_CHANGE_NOTIFICATION_REGS消失（这是工作示例所发生的情况）。但是我的'OnChange'事件并不会触发。



正如我所说，我认为这是我对线程和异步编程领域缺乏了解。我一直在寻找异步并等待，但我使用的示例代码似乎与模型不匹配（我可能错了）。我正在做更多关于这个问题的阅读，但我想知道是否有人可以给我任何评论。下面是一些示例代码。



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

经理与oracle网站的示例代码非常相似。 OracleDependency _dep在类的顶部声明。

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

最后返回功能也很简单。它只是重新注册了Oracle Notification。但是这个功能没有被调用。

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

如果有人能给我任何关于我应该去哪里的指示，我将不胜感激。

谢谢



p.s.我已检查端口是否已打开

推荐答案

如果这在WCF和控制台程序集中有效，就像你编写它一样。然后我的猜测是，IIS正在每个调用实例上运行您的Web API，并且您的实例在方法调用返回时被删除，从而阻止了对您的事件的回调。



这是ASP.Net Web API技术的正常行为。你想要做的可能不太适合。正如我在快速谷歌搜索中发现的那样，在技术中强制执行会话行为有一种骇人的方式。

If this works in a WCF and console assembly, exactly as you've written it. Then my guess is that IIS is running your Web API on a per-call instance and your instance is getting teared down at the return of your method call, thereby preventing the callback on your event.

This is normal behavior for the ASP.Net Web API technology. What you are trying to do might not be a good fit. There is a hacky way to enforce session behavior in the technology as I found in a quick google search.

嗨Jacob，感谢您的回复。



事实证明我有3个问题;

1.正如你的建议，正在调用数据库的实例正在被降低。将它移到Application_Start（）的调用帮助那里。

2.我需要更新我的Oracle客户端。我是

Hi Jacob, thanks for the response.

It turns out that I had 3 problems;
1. As you suggested, the instance that was making the call to the database was getting reared down. Moving it to a call from Application_Start() helped there.
2. I needed to update my Oracle client. I was

using Oracle.DataAccess.Client

并且需要使用Oracle.ManagedDataAccess.Client升级到

and needed to upgrade to

using Oracle.ManagedDataAccess.Client

3.然后我遇到端口阻塞问题。代码工作一次，然后在几分钟内不起作用，然后再次工作。这是因为我的TCP端口未正确关闭并且保持打开/保留默认为240秒。



希望此细节可以帮助某人。

3. I then had a problem with port blocking. The code worked once, then wouldn't work for a couple of minutes, then would work again. This was because my TCP port was not getting closed properly and would stay open/reserved for he default 240 seconds.

Hope this detail helps someone.

这篇关于Web API和Oracle数据库更改通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！