Call Web-Service from Database

How to efficiently notify your Application when there is a change in your Database/Tables? Probable solutions you are thinking may be the SQL Dependency, Query Notification, Table Dependency or Signal-R (for notifying multiple consumers)??

In the above all scenarios whether you are aware or not polling is keep on hitting your database and monitoring the table changes. Isn’t it? sometimes the application having more visitors on the day time (citation required)  during that time polling and getting the latest changes is somewhat acceptable but how about the time when there is no visitors on your site but still the polling will hit your Database and making it busy is not acceptable. More over in Cloud  polling means it counts as Network traffic/usage cost etc. unnecessary. Right?

So how to avoid this costly hit in Azure? any workarounds?

The one option I wish to bring to your notice is Calling a WebService from DB whenever a change is made to Table. In the WebSerive you can pull the data from the Table so as to get the changes. So if you do in this way you are not making unnecessary polling and making the SQL server busy.


How to implement this? There are mainly two ways you can achieve it. Either using .NET CLR and write a SP or using TSQL and create a SP. I am going to show a sample using TSQL.

In DB I have Table named Task with TaskId, TaskName and TaskStatus as mentioned below. I want to monitor  this table and the changes needs to update in my ASP.NET Application object and shared with all visitors to show the current Tasks in progress.


Step1 : Create a WebService/WebMethod 

 public void RefreshTasks()
 List<Task> TaskList = new List<Task>();

string connectionString = "Data Source = POP1977\\SQLEXPRESS; Initial Catalog = WEBSERVICE; Integrated Security = True";
 SqlConnection con = new SqlConnection(connectionString);

SqlDataAdapter adapter = new SqlDataAdapter("Select TaskId, TaskName, TaskStatus 
From Task WITH (NO LOCK)", con);
 DataSet dsTask = new DataSet();
 adapter.Fill(dsTask, "Task");

Task task = null;
 foreach (DataRow dr in dsTask.Tables["Task"].Rows)
 task = new Task();
 task.TaskId = Convert.ToString(dr["TaskId"]);
 task.TaskName = Convert.ToString(dr["TaskName"]);
 task.TaskStatus = Convert.ToString(dr["TaskStatus"]);

 Application["Tasks"] = TaskList;

public class Task
 public string TaskId { get; set; }
 public string TaskName { get; set; }
 public string TaskStatus { get; set; }




Note: The Select Query needs to include WITH NO LOCK option otherwise a Dead Lock will happen during Update Trigger.

Step 2: Create a Stored Procedure (or) Trigger

To use OLEAutomation you need to enable the below in SQL server:
sp_configure 'show advanced options', 1;
sp_configure 'Ole Automation Procedures', 1;
Create Procedure CallWebService 

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @WebServiceUrl as varchar(max)
Set @WebServiceUrl = 'http://localhost:60711/WebService1.asmx/RefreshTasks'

--Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'GET', @WebServiceUrl, false
Exec sp_OAMethod @Object, 'send'
Exec sp_OAGetProperty @Object, 'responseText', @ResponseText OUT
Select @ResponseText as response
Exec sp_OADestroy @Object

Next Create a Trigger On Table to be monitored (Task) and invoke the Stored Procedure (CallWebService). T he same can be achieved with the Trigger alone but for better understanding I have made it into SP and Trigger.

 ON Task 
 EXEC dbo.CallWebService 

Now Run your webservice and make a change in the Table and see the Application object is filled with the latest values from DB without continuous polling.


Now Run the Webservice and make a change in the DB table it will update the Application Object with latest values.


Happy Programming.!


Technical Lead for Microsoft Technologies especially in Azure, ASP.Net and in Angular JS.

Posted in Azure
One comment on “Call Web-Service from Database
  1. Shanu says:

    Implemented and works like a charm. looks forward for gimmicks like this.👍

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s