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.

10.png

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.

Task.png

Step1 : Create a WebService/WebMethod 

[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"]);
 TaskList.Add(task);
 }

Application.Lock();
 Application["Tasks"] = TaskList;
 Application.UnLock();
 }

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

}

7.png

3

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;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
5.png
Create Procedure CallWebService 
AS

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
RETURN

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.

6.png
CREATE TRIGGER NOTIFY
 ON Task 
 AFTER INSERT, UPDATE,DELETE
 AS
BEGIN
 EXEC dbo.CallWebService 
END

 

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.

4.png

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

8.png

Happy Programming.!

 

Advertisements
%d bloggers like this: