Introduction
Some applications need to share data between instances, as in a chat program, or receive constant updates, as in a stock broker application. There are many ways to accomplish this, some good, some better. This sample will explore using ADO.NET 2.0 SqlDependency and SQL Server 2005 to provide data flow between separate instances of an application. This simple chat program will demonstrate the usage, and give a test for the potential of these new features.
Cautions
This sample uses features of SQL Server 2005 and ADO.NET 2.0, and was created using Visual Studio 2005.
SQL Server 2005
Among the many new features for SQL Server 2005 are Service Broker and Query Notifications. The Service Broker is a queued, reliable messaging mechanism that is built into SQL Server 2005 and provides a robust asynchronous programming model. The details of SSB are not covered in this article, more information can be found in MSDN.
Query Notifications allow applications to receive a notice when the results of a query have been changed. This improves performance by not having to periodically query the database for changes. More information can be found in MSDN.
Database
The database for this sample is not very elaborate, just enough to demonstrate the features for this article. There are two tables, Message and Person. Person stores the users for the application, and Message stores the messages that are sent. To use SqlDependecy, the database must support a Service Broker. If the database was not created with this option enabled, you will need to enable it.
ALTER DATABASE Chatter SET ENABLE_BROKER
SqlDependency
SqlNotificationRequest can also be used to provide the same services, however, it requires a good deal of manual setup. SqlDependency sets up the plumbing for you. While it is simpler to implement, it obviously doesn't allow the degree of customization that may be necessary for some applications, and SqlNotificationRequest would be the best choice.
A dependency is created between the application and the database via a SqlCommand. Before that can be established, the SqlDependency must be started for this session.
SqlDependency.Start(m_ConnectionString);
After processing this command, SQL Server will automatically create a queue and a service in the database designated in the connection string that is passed in. In order to create this queue, the database user must have the SUBSCRIBE QUERY NOTIFICATIONS permission. As you can see, GUIDs are used for naming these objects. Each time the application is run, a new GUID will be generated, and a new queue and a service created. Although the documentation says these will be removed when the application exits, I have found this to not be the case.
As stated before, the dependency is created based on a SqlCommand.
SqlDependency dependency = new SqlDependency(cmd);
There are some restrictions, of course, as to what can be included in this command. The command must use two part names and not use *. It also must obviously not be an UPDATE or INSERT statement.
The following won't work:
SELECT * FROM Message
This will work:
SELECT ID, Message FROM dbo.Message
If the query is not correct, an event will immediately be sent with:
SqlNotificationEventArgs.Info = Query
SqlNotificationEventArgs.Source = Statement
SqlNotificationEventArgs.Type = Subscribe
The very vague explanation for this is "The statement is not valid for notifications". Unfortunately, I have not been able to find a good source for what comprises a valid statement. In the testing process, I have used an in-line SQL statement which was shown as valid, and then a stored procedure with the very same statement and it was shown as invalid.
Update: Thanks to DylanTheDeveloper for finding this resource describing valid queries: Special Considerations When Using Query Notifications. Also, using SET NOCOUNT ON in your stored procedure will invalidate it for usage in Query Notifications.
Notification
Once the dependency is setup and the application is running, all you have to do is sit back and wait to receive the event.
In this sample application, when a new message is sent by a user, it will be inserted into the database which will cause a message to be sent to the Service Broker Queue and picked up by the Notification Service which will in turn fire the OnChange event to be handled by the application.
void OnChange(object sender, SqlNotificationEventArgs e)
{
SqlDependency dependency = sender as SqlDependency;
dependency.OnChange -= OnChange;
if (OnNewMessage != null)
{
OnNewMessage();
}
}
The notification is a one shot deal, so after the event has been received, it must be connected again to continue receiving notifications. In the sample application, the OnChange event is removed and an event is fired to the client, which will reload the messages and cause the dependency to be re-established.
public DataTable GetMessages()
{
DataTable dt = new DataTable();
try
{
SqlCommand cmd =
new SqlCommand("usp_GetMessages", m_sqlConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Notification = null;
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange +=
new OnChangeEventHandler(OnChange);
if(m_sqlConn.State == ConnectionState.Closed)
m_sqlConn.Open();
dt.Load(cmd.ExecuteReader(
CommandBehavior.CloseConnection));
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
You'll notice that the connection to the database does not need to be kept open to receive the change event.
Conclusion
This article and sample are just a quick introduction to one of the new features of SQL Server 2005 and ADO.NET 2.0 and how it may be used in an application.
These features are, of course, very new, and information is still somewhat sketchy.
As well as the previously mentioned resources, this blog from Sushil Chordia has some good, though outdated, information about the necessary permissions: DataWorks WebLog.
|
|
 |
 | Using a non-visual class instead of a form
Kobus du Bruyn | 7:35 3 Sep '09 |
|
|
Thanks a million for your example.
I solved my initial problem but then had to transfer the solution into an application where my 'controlling' class is not a form.
What type of class could I use that is non-visual? My problem arouse when the cast to ISynchronizeInvoke below failed.
void OnNewMessage()
{
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
because 'this' was now my own defined class.
Kobus
|
|
|
|
|
|
|
Kobus du Bruyn wrote: Thanks a million for your example.
Your welcome, I'll take that in a money order please. 
The ISynchronizeInvoke is only for the WinForm for thread safety, it isn't required in a non-UI context.
only two letters away from being an asset
|
|
|
|
|
|
|
Great and thanks for speedy response.
|
|
|
|
|
| Getting Infinite
divyesh1432 | 2:19 27 May '09 |
|
|
Hi,
I have implemented same thing but it is getting infinite what can be the reason.
The OnChange event fires infinite times.
Thanks
|
|
|
|
|
|
|
Not sure if I understand your problem, could you be a little more vague. 
only two letters away from being an asset
|
|
|
|
|
| Does this really work?
daroczy | 20:23 22 Dec '08 |
|
|
I did experiment using SQL Service 2008 and VS 2008 and I never got the SQLDependency.OnChange event fired. I also did create a new user that has the SUBSCRIBE QUERY NOTIFICATIONS and QueryNotificationErrorsQueue set, have established the connection as that user but still did not see any notification coming from the database.
Thank you, eugen
|
|
|
|
|
| Re: Does this really work?
codemunch | 12:48 15 Apr '09 |
|
|
|
| for sql 2000????
qingtianyu9 | 0:24 29 Nov '07 |
|
|
can work for sql 2000?????
|
|
|
|
|
|
|
The SqlDependency object represents a query notification dependency between an application and an instance of SQL Server 2005.
only two letters away from being an asset
|
|
|
|
|
|
|
I remember that there was a tool that could create triggers for all tables of a sql 2000 database so that events could work.
Dimitris Papadimitriou
Software Development Professional
|
|
|
|
|
| Needs help, kept getting OnChange event
cliffton | 22:45 12 Jul '07 |
|
|
When I add message to the message table. I kept getting OnChange event, what could be he reason. Any help will be much appreciated.
Clifford
|
|
|
|
|
|
|
I found the problem, It is my stupid mistake. I put truncate message table statment inside LoadMessages which caused OnChange event fire again.
The reason I want to truncate message table is I don't want to process "old" messages again. Is there anyway to do it?
Clifford
|
|
|
|
|
|
|
It won't process the old messages. Once it has been fired you won't get the event until another action causes it. (after reconnecting the event handler of course)
only two letters away from being an asset
|
|
|
|
|
|
|
|
|
|
What problem are you referring to?
If you read the article you will see were it is addressed that the schema must be specified to work. Your example makes no sense.
only two letters away from being an asset
|
|
|
|
|
| SQL Dependency over multiple tables
Stephane Therrien | 13:56 24 May '07 |
|
|
Suppose i have 3 tables: Customers, Orders and Providers. I i want to fire an event in my application if any of these tables has changed and know exactly in wich table change has occured. Is there a way to do it or maybe i should create 3 events for this??
Thanks for your future answer
|
|
|
|
|
|
|
Although you could use the same event for each table, it would be better to seperate them.
only two letters away from being an asset
|
|
|
|
|
| Web service
zmrcic | 4:13 24 Apr '07 |
|
|
Is there a way to refresh web service using SqlDependency
zmrcic
|
|
|
|
|
|
|
What would be the point? The web service must be called by someone so at that point a database call is made anyway. Unless you are considering keeping the webservice call alive, in which case that would be a poor design.
only two letters away from being an asset
|
|
|
|
|
|
|
Actually, there could be a valid use. It is true that what we normally think of as a web service only lives for the duration of the request, but it is still an ASP.NET application, just no DHTML user interfaces. Your web service can still have a Global.asax and create objects in the application scope during Application.Start() and dispose of them in Application.Stop().
You could improve performance of your web method if it required data from a relatively long query by tracking the results of that query with a dependency in this global object. Yor web method would return this globally cached reault rather than issuing the query on every call.
The application scope lives until the app pool is recycled, which would typically be hours or longer.
Dave
|
|
|
|
|
|
|
No you would not improve the performance of you application in this manner. Keeping it open in your application scope severely reduces scalability. It doesn't matter if its a webservice, ASP.NET or other application, keeping an open database connection any longer than absolutely necessary is poor design.
If you dispute this, please show some empirical evidence to the contrary.
only two letters away from being an asset
|
|
|
|
|
|
|
Poor design is regurgitating a slogan without understanding it.
This is not keeping an open connection, it is keeping an SqlDependancy object in the global scope. If you equate keeping a dependency object with an open connection (which is factually incorrect), than the SqlDependancy class has no usefulness whatsoever. Please provide single use case for it where you don't hold onto a reference of it.
This is, incidentally, EXACTLY how ASP.NET implements its SQL cache. And what scalability are you talking about? Do you think ASP.NET creates multiple global contexts for a single web applicaton?
Dave
|
|
|
|
|
|
|
I'll say it again, If you dispute this, please show some empirical evidence to the contrary.
only two letters away from being an asset
|
|
|
|
|
|
|
OK,
Suppose you have a web service application with a web method which returns data reflecting, say, the state of a distribted system, say which nodes are active/inactive, or how heavilly some equipment is loaded). The results of this query change perhaps on average once per minute. However (this is a scalable application), there may be dozens, hundreds, or even thousands of clients calling the web method every second.
You suggest the web method should be implemented by opening a connection to the database for ever invocation, issuing a query, and returning the result to the client. Yes, connection pooling will reduce the number of concurrent connections, but there will still be more than one. Likewise, the database may cache the query results, but they are still being sent back across the transport to the web service each time, and if the database server is on a different box than the web service, that won't be trivial bandwidth.
I propose creating a single dependancy object in the global context, managing a single connection to the database, and querying the database on average once per minute or less (only when the dependancy object is notified by the database the results will in fact be different), and maintaining a single copy of the data in a a global cache, returing data from this cache for the multiple client-driven invocations of the web method.
So you have an implementation with many connections in a pool, passing the same data many times a second between the database and the web service, and you think that is scalable.
I have an implementation with one connection for an unlimited number of clients, which transfers data between the database and web service one tenth, one hundredth, or one thousandth as frequently as yours, and actually only does so when the data has in fact changed. You call this poor design and unscalable.
Of course, if the query results are likely to change many times a second, the direct approach is better, and the global caching and dependency notifications just get in the way (MS explicitly recommends dependency objects only be used for data with relatively low-volume changes, which applies to any type of client, ASP.NET, Windows Forms, etc.).
Finally, I have given you a fairly generalized scenario where I believe my design is better, but if it is not, it is your turn to explain why Microsoft uses this "always poor design" in the implementation of its own ASP.NET caching.
Dave
|
|
|
|
|
|
|
Perhaps you misunderstood the word
em·pir·i·cal /?m'p?r?k?l/ Pronunciation Key - Show Spelled Pronunciation[em-pir-i-kuhl] –adjective
1. derived from or guided by experience or experiment.
2. depending upon experience or observation alone, without using scientific method or theory, esp. as in medicine.
3. provable or verifiable by experience or experiment.
http://dictionary.reference.com/browse/empirical[^]
What you have presented is
the·o·ret·i·cal /??i?'r?t?k?l/ Pronunciation Key - Show Spelled Pronunciation[thee-uh-ret-i-kuhl]–adjective
1. of, pertaining to, or consisting in theory; not practical (distinguished from applied).
2. existing only in theory; hypothetical.
3. given to, forming, or dealing with theories; speculative.
http://dictionary.reference.com/search?r=2&q=theoretical[^]
I look forward to the article explaining your theory.
only two letters away from being an asset
|
|
|
|
|
|
|
Last Updated 28 Jun 2006 |
Advertise |
Privacy |
Terms of Use |
Copyright ©
CodeProject, 1999-2010
