I just spent some time checking out how SqlDependency
works. SqlDependency is an ADO.NET 2.0 class that is a high level wrapper for detecting changes in a database and getting automatic notification back to your application. This technology is very useful for caching rarely changing data. Essentially, you register interest in a query, and if something changes that would make that query different, you get a callback to your application to let you know that you need to update your local cache.
All in all, it's a pretty mixed review right now. It's pretty stringent on the requirements, not terribly efficient, and probably not the most practical thing for a multi-tier system. However, what it does, it does well. Here is my quick list of things learned:
- You need to make sure that the DB is compatible with MSSQL2005. You can set the compatibility level in SQL Server Management Studio by selecting the Properties page of the database.
- You need to SET ENABLE_BROKER on the database, along with making sure other OPTIONS are in compliance, to allow Query Notifications to work.
- Be sure to prefix the table in the select with the schema information (e.g. select CategroyId, CategoryName from dbo.Categories). Also be sure to check out the documentation for other limitations on queries.
- The OnChange event fires once and then gets consumed, so you need to hook up the event again after it fires.
- OnChange runs in a separate thread, so be sure to update the UI properly.
- The Queues and Services in MSSQL 2005 are not being automatically deleted when you call SqlDependency.Stop. This is a known issue, and the query to delete the extra objects can be found in this discussion.
- It appears that the notification fires if ANY field in the table tied to the query is changed - even if that field is not part of the query you registered for notification. This seems like a bug to me.
- Query Notifications are not very granular in reporting WHAT changed. Right now, this essentially forces you to invalidate an entire cache as opposed to just updating individual elements that have changed.
- SqlDependency is the high-level wrapper. You can also use the low-level SqlNotificationRequest class to get more control over the process, if you so choose.
- For ASP.NET, you will most likely use the SqlCacheDependency class.
- Oracle also has support for this technology. Check out the article, Following the changes. Part I and Part II.
The following code snippet shows how to pick up the SQL that will be generated by a given LINQ query. The DataContext.GetQueryText() method makes this very easy. However, what we REALLY
need is an easy way to go the other way, too. For example, given an adhoc string, generate the DLINQ query to allow further processing. I've looked through the Interactive Query sample, but that's not exactly what I want. The 2 main things wrong with it are that the baseQuery is already defined as an IQueryable variable, as opposed to a simple string; and the code to build up the expression tree is much too complex for every day needs. If we had a generic string to IQueryable method, we wouldn't need all of the expression code.
static void Main(string args)
var q = from e in db.Employees select e.FirstName;
s = db.GetQueryText(q);
OK, so it doesn't have the same ring as Eminem's song, but it is accurate.
I was going through some of the older sections of my hard drive looking to reorganize and cleanup unwanted files. While I was doing that, I came across a project, TerraService, that I did ages ago. To the best of my recollection, it may have been ported from a VB application that I found somewhere on MSDN, but a search on that site isn't turning up any positive hits. Sorry for any lack of proper attribution. If this was written by someone else in another language, let me know, and I'll give the proper credit.
The reason I'm making the Delphi source available is that it shows how Delphi has improved over the years. I originally wrote this application using Delphi 6. As a result, I had to manually deal with the TByteDynArray types to decode them from base64 before display. Using Delphi 2006, this is no longer necessary - the types come back decoded and in usable fashion without any extra work on your part. This sample shows several interesting concepts: stream to variant conversion, drawing tiled images, calling a webservice, dealing with GIF and JPG files, and interception of webservice traffic for debugging.
While I don't know which specific version of Delphi fixes the TByteDynArray/Base64 de/encoding, this much I know for certain: Delphi 2006 makes web services very easy to do.
In the last in the series of posts on writing a .NET application to communicate over HTTP, I thought I'd talk about HtmlAgilityPack
(download available here
). This tool allows you to write XPATH-like expressions to parse HTML files easily.
In my application, I upload a file and some form variables to a web server, which then responds with an HTML page. On that HTML page, buried in the middle, is a confirmation ID. In order to easily grab that ID and store it for later use, I use code like the following:
private string CheckResponse(string response)
HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
HtmlNodeCollection coll = doc.DocumentNode.SelectNodes("//td[@id]");
if (coll == null)
foreach (HtmlNode node in coll)
When using a .NET client to communicate via HTTP, you may notice an HTTP header that looks like this:
Depending on how stringent the web server is, it may or may not be fine to have this extra header. If you must remove this header, it's very easy to do in .NET 2.0. Just include the following line of code in your application:
ServicePointManager.Expect100Continue = false;
I've been writing a .NET client application that must upload a file to a IBM WebSphere (Java) server. This journey was met with mis-steps all over the place. For starters, it looked like the WebClient class was what I wanted, but it turns out you can't POST both a file and HTML form variables. That meant that I needed to go to the low-level class, HttpWebRequest, to get this done. There is code on google that got me most of the way there, but the web server always ended up rejecting the submission with errors. To make matters worse, I don't own the web server, so support on exactly what was wrong was extremely limited.
Then I stumbled across a paper, Retrieving HTTP content in .NET by Rick Strahl. In that paper, Rick ends up writing a very nice wrapper class to deal with all of the details for you. I put together a test case to try this out, and the amount of code I had to write dropped from 123 lines down to 15. The new code even works with WebSphere. What's more, the wrapper class provides a very nice model for programming applications that need to communicate via HTTP. In my opinion, this is what Microsoft should have delivered with the framework, as opposed to making people write their own wrapper or find Rick's code (which was last updated in 2002, yet still works great).
I made 2 changes to Rick's base class:
- I changed the cMultiPartBoundary variable to look like this in order to give a unique boundary marker: string cMultiPartBoundary = "-----------------------------" + DateTime.Now.Ticks.ToString("x");
- In the method GetUrlStream, there is a place where the end boundary marker is written to the stream. However, according to the RFC on uploading files via POST, you need to have 2 trailing dashes to mark the last end. I added those dashes like this: this.oPostData.Write(Encoding.GetEncoding(1252).GetBytes( "--" + this.cMultiPartBoundary + "--\r\n" ) );
I am one happy camper. I've done this very thing with other web applications in Delphi before, and with the Indy components, it was very easy. Now with Rick's wrapper class, it's easy to do the same kind of thing in .NET.
Using LINQ, you can use variables for evaluation of the query. For example, the following query works just fine. It would also work if you used something like textBox1.Text instead of the variable s.
string s = "Beverages";
var results = from c in db.Categories
where c.CategoryName == s