using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Globalization; /* ===================================================================== File: ReviewWatch.cs for Adventure Works Cycles ReviewWatcher Sample Summary: Defines the ReviewWatch class which sets up a query notification to monitor new rows added to the ProductReview table, and inserts the data contained in those new rows into the user interface in ReviewWatchForm.cs. Date: September 17, 2003 --------------------------------------------------------------------- This file is part of the Microsoft SQL Server Code Samples. Copyright (C) 2003 Microsoft Corporation. All rights reserved. This source code is intended only as a supplement to Microsoft Development Tools and/or on-line documentation. See these other materials for detailed information regarding Microsoft code samples. THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. ======================================================= */ namespace Microsoft.Samples.SqlServer { /// <summary> /// This class sets up a query notification to monitor /// new rows added to the ProductReview table, and inserts the data contained /// in those new rows into the user interface in ReviewWatchForm.cs. /// </summary> public class ReviewWatch { private Int32 CurrentReviewID = 0; //The primary key of the last product review row we've processed so far. private ReviewWatchForm form; //The part of the user interface we should update when new rows arrive. private SqlDependency dependency; //The request to receive notifications when a query result changes. We need //to hang on to this object so that it is not garbage collected. /// <summary> /// Initializes an instance of the class. Keeps track of the ui object to update when new rows are /// discovered, and determines what the last product review key was. /// </summary> /// <param name="form">The ui object to update when new product review rows are inserted</param> public ReviewWatch(ReviewWatchForm form) { //Get the ID of the last review using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ConnectionString)) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT TOP 1 ProductReviewID FROM Production.ProductReview ORDER BY ProductReviewID DESC"; conn.Open(); object result = cmd.ExecuteScalar(); // If there are reviews, set CurrentReviewID to the ReviewID of the latest one. If not, do nothing. if (result is Int32) CurrentReviewID = (Int32)result; } //Keep track of the ui object to update. this.form = form; } /// <summary> /// This method is used to create the SqlDependency class which will be used to obtain notifications when /// a row is added to the ProductReview table. It also reads any new rows and updates the user interface /// accordingly. This is called one time when the user interface is initialized, and also once every time a notification /// is received. /// </summary> public void WatchForReviews() { using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ConnectionString)) { // Construct the command to get any new ProductReview rows from the database along with the corresponding product name // from the Product table. SqlCommand cmd = new SqlCommand( "SELECT PR.ProductReviewID, P.[Name], PR.Rating, PR.ReviewerName, PR.ReviewDate, PR.Comments" + " FROM Production.ProductReview as PR JOIN Production.Product as P ON P.ProductID = PR.ProductID" + " WHERE ProductReviewID > @CurrentReviewID" + " ORDER BY ProductReviewID ASC", conn); cmd.Parameters.Add("@CurrentReviewID", SqlDbType.Int); cmd.Parameters[0].Value = CurrentReviewID; // Create a dependency on this query. dependency = new SqlDependency(cmd); // Register the event handler which will be called when new rows are added to the ProductReview table. dependency.OnChanged += new OnChangedEventHandler(Dependency_OnChanged); conn.Open(); // Get any new rows SqlDataReader rdr = cmd.ExecuteReader(); // Process the rows by updating the user interface with the data returned for each row. while (rdr.Read()) { Int32 reviewID = (Int32)rdr.GetInt32(0); // This "if" statement is necessary as it is possible to get a notification even when there is no new data. if (reviewID > CurrentReviewID) { //Note that just calling the AddRowToList method will not work correctly as the event is //processed in a different thread from the control's thread. So, we must use Invoke to make //sure the form is updated in the control's thread. form.Invoke(new ReviewWatchForm.AddReviewerRow(form. AddRowToList), new object[] { rdr.GetInt32(2), rdr.GetString(1), rdr.GetString(3), rdr.GetDateTime(4).ToString(CultureInfo. CurrentUICulture), rdr. GetString(5) }); CurrentReviewID = reviewID; } } //Note: We do not need to close the reader as the "using" statement will dispose of the connection. } } /// <summary> /// This is the event handler which is invoked when new rows have been added to the ProductReview table. /// It simply calls WatchForReviews method. It is important to call that method rather than just perform the /// data access because the previous dependency is cancelled when the notification is fired ("fire and forget"), so /// reregistration of the dependency is essential. /// </summary> /// <param name="sender">Who sent the event</param> /// <param name="e">Additional information about the event</param> private void Dependency_OnChanged(object sender, SqlNotificationEventArgs e) { WatchForReviews(); } } }