Eclipsed4utoo's Blog

Not Your Ordinary Programmer

In this tutorial, I will use the SqlDependency class and Query notifications to monitor SQL Server 2005 database data changes. Query Notifications allow an application to be notified when data has changed in the database.

The purpose of this class is to save you from having to continuously re-query the database to get new data. You would have probably done this by setting up a timer that executes every X amount of seconds so that your display control would be displaying the most up-to-date information. You will no longer have to do this.

We will be using a Service Broker and a QUEUE in SQL Server 2005. These were new additions to SQL Server 2005. I will assume these are also in SQL Server 2008, but can’t guarantee.

My example will be doing something very simple. I have a “Users” table in my database with two fields: FirstName and LastName. I am simply displaying these in a ListBox on one form. On a second form, I have textboxes to insert the data into the database.

So first, we need to create the Queue and the Service broker and assign the privileges to the SQL user.

USING [YourDatabaseName]

CREATE QUEUE NameChangeQueue;
CREATE SERVICE NameChangeService ON QUEUE NameChangeQueue
([http://schemas.microsoft.com/sql/notifications/postquerynotification]);

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName;

You can now see that we have a new queue and a new service.

Now we move on to the code. The first thing you will need to do is to test if the connecting user has the privileges for the query notifications.

private bool DoesUserHavePermission()
{
     try
     {
           SqlClientPermission clientPermission = new SqlClientPermission(PermissionState.Unrestricted);

           // will throw an error if user does not have permissions
           clientPermission.Demand();

           return true;
     }
     catch
     {
           return false;
     }
}

Next, we have our method to get the user names from the database.

private void GetNames()
{
    if (!DoesUserHavePermission())
        return;

    lbNames.Items.Clear();

    SqlDependency.Stop(connectionString);
    SqlDependency.Start(connectionString);

    using (SqlConnection cn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT FirstName, LastName FROM dbo.[Users]";

            cmd.Notification = null;

            SqlDependency dep = new SqlDependency(cmd);
            dep.OnChange += new OnChangeEventHandler(dep_OnChange);

            cn.Open();

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    lbNames.Items.Add(dr.GetString(0) + " " + dr.GetString(1));
                }
            }
        }
    }
}

THIS IS VERY IMPORTANT.

1. In the previous code, you will notice that my SQL query does not use the “*” wildcard to return all columns. You MUST return the exact columns that you want. If you use the “*”, it will cause you to have unwanted consequences.

2. Also in the previous code, you will notice that my SQL query contains the “two-part” table name. This is also REQUIRED. Using just “TableName” instead of “owner.TableName” will also cause unwanted consequences.

Here is the method for the OnChange event

void dep_OnChange(object sender, SqlNotificationEventArgs e)
{
    // this event is run asynchronously so you will need to invoke to run on UI thread.
    if (this.InvokeRequired)
        lbNames.BeginInvoke(new MethodInvoker(GetNames));
    else
        GetNames();

    // this will remove the event handler since the dependency is only for a single notification
    SqlDependency dep = sender as SqlDependency;
    dep.OnChange -= new OnChangeEventHandler(dep_OnChange);
}

You will also need to stop the dependency when the form closes so that it doesn’t leave it running.

private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
    SqlDependency.Stop(connectionString);
}

The other events…

private void Form1_Load(object sender, EventArgs e)
{
    GetNames();
}

private void btnShowForm_Click(object sender, EventArgs e)
{
    Form2 f = new Form2();
    f.Show();
}

And my simple second form’s code..

private void btnSave_Click(object sender, EventArgs e)
{
    using (SqlConnection cn = new SqlConnection("Data Source=alfordr;Initial Catalog=MyTestDatabase;User Id=dev;Password=dev;"))
    {
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO Users VALUES (@FirstName, @LastName)";
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);

            cn.Open();

            cmd.ExecuteNonQuery();
        }
    }
}

And that is really all you have to do. Here are a couple of screenshots.

Before clicking “Save”…

After clicking “Save”…..

As you can see from my code, I am simply inserting data into my database when the Save button is clicked.  When the data is inserted, a notification is sent to the application, which is handled by the OnChange event.  The OnChange event then invokes the GetNames method which re-queries the database to get the new information.  This makes a huge performance improvement because I ONLY query when I need to.

This works for inserts, updates, and deletes.

This is going to be a real short post about an issue that I ran into recently with WPF.

I was wanting to use an app config file with my WPF application.  I was running into an issue with the ConfigurationManager calls not getting the information from the app config file.

I added the app config just like I would in a Windows Form..

My app config file looked something like this…

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
     <add key="myKey" value="SomeValue" />
  </appSettings>
</configuration>

I was using the standard code that works fine in Windows Forms(needed to add a reference to the System.Configuration):

using System.Configuration

private void Window_Loaded(object sender, RoutedEventArgs e)
{
     var myKey = ConfigurationManager.AppSettings["myKey"];
}

However, the app settings were not being found.  After scratching my head for a while and searching numerous places online, I found a small response on a forum.  None of the “MVP”s picked up on it.  It was because of the name of the app config file.  By default, VS2008 added the file as “App1.config”.  For some reason, the ConfigurationManager class looks for a file named exactly “App.config”.

So simply changing the name of the config file to “App.config” fixed the issue and the code started working.

In this tutorial, will show how to log into a website through code.  I am going to use Twitter in my example.

THIS IS FOR EDUCATIONAL PURPOSES ONLY.  I WOULD NOT ADVISE THE USE OF THIS TO ALWAYS LOG-IN TO TWITTER.

First, you will need to download and install Tamper Data.  It’s an add-on for Firefox that allows you to view and tamper with GET/POST web request data.  We won’t be doing any tampering.  We will be using it to view what POST parameters the page is expecting.

Next, navigate to the log in page for Twitter(http://twitter.com/login).  Once the page has loaded, go to Tools –> Tamper Data to open Tamper Data.  At the top of Tamper Data, click the Start Tamper button.  After clicking the button, click the “Sign In” button on Twitter’s log in page.  Once you hit the Sign In button, Tamper Data will prompt you with this popup….

Click “Tamper”.  You will then be presented with this window…

If you notice on the right-hand side of the window, you will see the POST parameters.  These are:

authenticity_token
session[username_or_email]
session[password]
commit

Now that we have those, we can close the Tamper Data window, and close Firefox.

Now for the code.  The code is actually fairly simple.  We are just going to use the WebBrowser class to make the requests to the server to get the html source of the page.  This will give us the “authenticity_token” for us to use in the POST request.

string url = "https://twitter.com/login";
string username = "someUserName";
string password = "somePassword";
string commit = "Sign+In"; //this matches the data from Tamper Data

private void Login()
{
     WebBrowser b = new WebBrowser();
     b.DocumentCompleted += new WebBrowserDocumentCompletedEventHandler(b_DocumentCompleted);
     b.Navigate(url);
}

private void b_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
{
     WebBrowser b = sender as WebBrowser;
     string response = b.DocumentText;

     // looks in the page source to find the authenticity token.
     // could also use regular expressions here.
     int index = response.IndexOf("authenticity_token");
     int startIndex = index + 41;
     string authenticityToken = response.Substring(startIndex, 40);

     // unregisters the first event handler
     // adds a second event handler
     b.DocumentCompleted -= new WebBrowserDocumentCompletedEventHandler(b_DocumentCompleted);
     b.DocumentCompleted += new WebBrowserDocumentCompletedEventHandler(b_DocumentCompleted2);

     // format our data that we are going to post to the server
     // this will include our post parameters.  They do not need to be in a specific
     //    order, as long as they are concatenated together using an ampersand ( & )
     string postData = string.Format("authenticity_token={2}&session[username_or_email]={0}&session[password]={1}&commit={3}", username, password, authenticityToken, commit);

     ASCIIEncoding enc = new ASCIIEncoding();

     //  we are encoding the postData to a byte array
     b.Navigate("https://twitter.com/sessions", "", enc.GetBytes(postData), "Content-Type: application/x-www-form-urlencoded\r\n");
}

private void b_DocumentCompleted2(object sender, WebBrowserDocumentCompletedEventArgs e)
{
     WebBrowser b = sender as WebBrowser;
     string response = b.DocumentText;

     if (response.Contains("Sign out"))
     {
         MessageBox.Show("Login Successful");
     }
}

And that’s all you need to do.  You can now use the response variable to see the tweets that are in your timeline.