|
download source code
Introduction
The intent of the article is not to let one know about the internals of SQL Dependency nor it is meant to guide
readers to scenarios where SQL Dependency should be used. We already have some very good content on the site
that details internals and usage of query notification in SQL Server 2005. Some of the articles are listed
below in the Suggested Reading section. The purpose of this article is to instead illustrate a mechanism via
which a developer can harness SQL dependency and bypass its limitations.
Constraints Faced When Using SQL Dependency
SQL Dependency is built on top of the query notification architecture in SQL Server which uses Indexed views to
implement query notifications (Refer: Suggested Reading). All views cannot be Indexed hence any limitations that
surface in indexing views also surface for using SQL Dependency. To view a list of limitations associated with
Indexed views refer to View Restrictions Section in
Improving Performance with SQL Server 2005 Indexed Views.
Sample Scenario
Consider a sample scenario where we need to get the count of all the employees from the employee table along with
the employee and jobs data. Our sample database schema is as below containing two tables as shown here:
CREATE TABLE Employee
(
EmployeeName VARCHAR(50),
EmployeeAddress VARCHAR(255),
JobId INT
)
GO
CREATE TABLE Job
(
JobId INT IDENTITY(1,1),
JobName VARCHAR(50),
JobDescription VARCHAR(255)
)
GO
The simplest case where we can retrieve the dataset of employee names with the jobs involves an OUTER JOIN.
If we refer the View Restrictions section in the above referred article on TechNet we will find a restriction
on OUTER JOIN. So if we had a requirement where our data needs to be updated or notified for any updates on
SQL Server tables we would not be able to use SQL Dependency in this case as a Query subscription will not get
built in this case. Below is a simple stored procedure that we will use to retrieve the employee data and the
jobs associated irrespective of whether they have any jobs assigned in their name or not.
CREATE PROC GetEmployeeAndJob
AS
BEGIN
SELECT EmployeeName,EmployeeAddress,JobName
FROM dbo.Employee EMP
LEFT OUTER JOIN dbo.Job JOB
ON EMP.JobId = JOB.JobId
END
The requirement is to get the data dependency created for the result set of the above procedure. We will not be able to create a direct SQL dependency on the above procedure at the OUTER JOIN prohibits to do that. Let's develop a work around going forward that will help us by pass this constraint of SQL Dependency.
Logical View
Refer to the logical view here
The above view illustrates that we use additional two procedures GetSurrogateEmployee and GetSurrogateJob
in the whole setup. The Intent of these two procedures is to create SQL Dependencies on behalf of the main
result set. Below is the step wise flow how this would work.
- Fire Main Procedure in this case GetEmployeeAndJob.
- Get the result set, cache it or refer it for usage later using static variables or some other technique to make references live.
- Fire the surrogate procedure GetSurrogateEmployee.
- Build a dependency on the procedure in Step 3 with a defined callback.
- Fire the surrogate procedure GetSurrogateJob.
- Build a dependency on the procedure in Step 4 with a defined callback (same call back as in step 4).
The above six steps will help you understand that the nature of the surrogate procedure should be such that
they comply with all restrictions on Index views in other words they can have a successful dependency
built over them. In our case these surrogate procedures are two, divided as per the tables you want to
monitor for changes here. The Result set in the main procedure will change if the data in table Job or
Employee changes hence the surrogate procedures are as simple as this.
CREATE PROC SurrogateEmployee
AS
SELECT EmployeeName,EmployeeAddress,JobId
FROM
dbo.Employee EMP
GO
CREATE PROC SurrogateJob
AS
SELECT JobId,JobName,JobDescription
FROM
dbo.Job JOB
GO
Notice the explicit schema binding and simplicity in the queries that make them suitable candidates
for notifications in SQL Server.
SurrogateEmployee will create a dependency that will fire when anything changes in the Employee table,
which inturn will invoke a callback that can refresh the referred data in the main procedure
(Fire GetEmployeeAndJob again in the callback to refresh the data).
SurrogateJob will behave the same way but invoke the dependency when the anything changes in the
Job table.
To sum up, the setup here behaves as if the Main Procedure was created with a corresponding SQL Dependency.
You can have any logic in the surrogate procedures above till you restrain yourself from using forbidden
constructs for SQL dependency.
SQL Dependency Test Harness
The methodology above to achieve SQL dependency on custom result sets that don't follow the query notification
rules is implemented in the Test Harness below, you can get the corresponding code and change it the way you
want to get an insight into the same.
DependencyController ctrl = new DependencyController();
string[] procedures = new string[2];
//These are the procedures that will support the dataset
//usage for the main procedure that cannot be supported
//directly with SQL Dependency
procedures[0] = "SurrogateJob";
procedures[1] = "SurrogateEmployee";
//This is the main procedure, as it has the outer join,
//SQL dependency will not work for the same so we have
//the above two procedures for rescue here
string actualprocedure = "GetEmployeeAndJob";
//My sql server string
string connstr = "Server=sandeepb;Trusted_Connection=yes;database=msdb";
//Main procedure that does all plumbing work for you to
//create dependencies and uses surrogate procedures.
//For it to work with sql procedure with parameters
//you just need to modify the plumbing code slightly.
ctrl.ExecuteProcedureAndCreateDependency(connstr, procedures, actualprocedure);
ExecuteProcedureAndCreateDependency is the procedure that will create a logical SQL Dependency construct by creating actual dependencies on procedures SurrogateJob, SurrogateEmployee passed as a string array here. You can modify the implementation to support parameterized procedures, here we avoid that to focus on the implementation code for the technique.
The implementation of the ExecuteAndCreateDependency creates dependency on each surrogate procedure as below
//This is the first time the method is run
foreach(string surrogate in procedureForCache)
{
//create dependency using the surrogate procedures
Utility.CreateDependency(connStr, surrogate, CachedResultSet, ActualProcedure);
}
Dependency creation registers a call back that takes care to update the main result set, refer to the lines of code in CreateDependency.
DependencyState stateObject = new DependencyState(connStr, procedureForCache, ActualProcedure, cachedResult);
depend.OnChange += new OnChangeEventHandler(stateObject.CallBackOnQuery);
After the dependencies are created the main dataset is retrieved by RunAndCache Method that stores the reference for call back based updating later.
Callback created on dependencies does two jobs, one to create dependency again another to refresh the data of the main procedure.
//callback again creates the dependencies and fires the
//main procedure, you need to recreate deps everytime
//they are fired.
Utility.CreateDependency(connectionString, spToCache, CachedResult, ActualProcedure);
//A operation the main procedure when callbacks on
//surrogate are fired is the main action point here.
Utility.RunAndCache(connectionString, ActualProcedure,CachedResult);
//MessageBox.Show(DependencyController.CachedResultSet.Tables[0].Rows.Count.ToString());
Refer to the attached code to view the detailed implementation, you can run the code after creating
the tables and data with the attached script. Modify your connection string accordingly.
Every time an update is made on the Job or Employee Table you will be able to see the data updated
in the referenced dataset, you can check the row count being increased after each run
(In this case it just appends the data at each execution.). In the screen shot below,
CreateDependency is used to do the first main execution on the sample; every time we run
an update on the table as shown below the data value returned by the Check Cached Count will change.
Conclusion
The example presented here is just a suggested work around to address the limitations of SQL Dependency.
It is not meant to be a generic best known method and may not be the best solution for your particular
needs. Determining that is left to the reader since every situation is different.
Suggested Reading
|
