0

I have a need to call external web service on certain events in my application. I don't want to modify my application and make any dependencies of that external web service. So, I need to think of a way to do this with some sort of external component.

One possible approach is that I make database view which will get filled up when some events in my application occurs. Then I will set up trigger on that view which will call CLR function. In that CLR function I will make call to external web service. By doing this, I will get "real-time" integration which is good. But, this approach has downsides. Major one is that it seems that calling web service from CLR is not a good idea since it will block main SQL thread (?!) until CLR receives some answer.

Until now, I have only found that setting this property will help with performance issues:

System.Net.ServicePointManager.DefaultConnectionLimit = 9999

More about it you can find here.

Now, since you know my needs (that is real-time or at least close-to-real-time integration without any calls from my application to exteranl web service) is there some better way to do it?

One other approach I can think of is having some service which will periodically check for changes in my DB that needs to trigger calls to external web service. Once this service detects such change, it will call web service and transfer data. This is not true real-time integration of course. I must admit that, except for performance issues, I like having triggers and CLR much more since it guarantees real-time integration and has no affect on my application whatsoever.

buhtla
  • 2,819
  • 4
  • 25
  • 38
  • Don't call web services from CLR based on triggers. That is a bad idea and will fail. Instead use a queuing mechanism. This might be a DB trigger that fills up a queue table, and a _seperate external_ service or process that works through the queue table and calls the web service. If you tightly bind triggers to SQL CLR you will have big issues. – Nick.Mc Sep 12 '18 at 04:46

1 Answers1

2

I am not sure that I would agree with the design of moving the web-service call to a database. However, I am sure there are reasons as to why you wouldn't want to change the application.

Here are a couple of options that you can try -

1) Instead of a database, and CLR making web-service calls, use a message queue. NServiceBus is a good choice for passing event occurrences as message, which can trigger this call

2) If you are stuck with using SQL server to store the events, look at SQL server Service broker

Srikanth Venugopalan
  • 9,011
  • 3
  • 36
  • 76
  • Thanks for proposals, I will look into them. Changing application is not an option since it is more like a product. So any specifics that are related with particular deployment are made as some external components, services or similar. – buhtla Jan 24 '13 at 09:17
  • Unfortunaltely, Service Broker is out since I'm working with SQL Express edition only. – buhtla Jan 25 '13 at 15:10
  • In that case, try looking at the Bus option, if Bus is too heavy, an asynchronous queue should help. – Srikanth Venugopalan Jan 25 '13 at 16:16