using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;

namespace Microsoft.Samples.SqlServer
{
	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 

    // Constructor

    public ReviewWatch(ReviewWatchForm form)
		{
			//Get the ID of the last review
			using (SqlConnection conn =
								 new SqlConnection("server=(local);database=AdventureWorks;Integrated Security=SSPI"))
			{
				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;
		}

    // Create SqlDependency Class

		public void WatchForReviews()
		{
			using (SqlConnection conn =
								 new SqlConnection("server=(local);database=AdventureWorks;Integrated Security=SSPI"))
			{
				// 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.
        SqlDependency.Start(conn.ConnectionString);
        dependency = new SqlDependency(cmd);

				// Register the event handler which will be called when new rows are added to the ProductReview table.
        dependency.OnChange += new OnChangeEventHandler(MyEventHandler);
				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.
			}
		}

    private void MyEventHandler(object sender, SqlNotificationEventArgs e)
		{
			WatchForReviews();
		}
	}
}