In Part 1 we created a simple WPF application that demonstrated WPF's ability to automatically update bindings and in Part 2 we extended the application to use WCF by using callbacks to notify WPF of the changes. In this part we will get rid of the XML file and move our application to use a SQL Server 2005 database for storage. In doing so we will replace the file system watcher with a SQL Notification. In order to learn about SQL Notifications I would suggest this article for a good overview and this article for code examples.
The first thing we will do is create our database. To do this I used VS Database Professional which is currently at CTP5. Below are the steps to create this database in VSDP:
- Create a new SQL Server 2005 project called ContactData
- In the project properties check the box for "Enable Service Broker"
- Add a new table called Contact (definition shown below)
The contact table is very similar to our contact XML data:
create table [dbo].[Contact]
(
ContactID int identity(1,1) primary key not null,
FirstName nvarchar(50) not null,
LastName nvarchar(50) not null,
Phone nvarchar(15) not null
);
In the project I also edited the Scripts\Post-Deployment\Script.PostDeployment.sql script to add in our default contacts:
insert into Contact values ('Tom', 'Jones', '800-333-1111')insert into Contact values ('Jill', 'Smith', '800-222-1111')insert into Contact values ('Ed', 'Baker', '877-666-1111')insert into Contact values ('Mary', 'Johnson', '866-777-1111')Now we just right click our database project and select "Deploy". The database should get created and our table should get created and populated. Note: if you're not using VSDP you can still manually create the database and table and enable service broker following the instructions in the first article.
Now that we have a database to connect to we need to update our app.config file for our WCF service with the connection string. Below is the updated config file:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="cDB"
connectionString="Database=ContactData;Server=(local);
Integrated Security=SSPI;"
providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.serviceModel>...</system.serviceModel>
</configuration>
After we've added the connection string to the database we can reference it in our ContactProvider class as follows:
private static readonly string ConnStr =
ConfigurationManager.ConnectionStrings["cDB"].ConnectionString;
Now that we have a connection string we can pull the contacts from the database and return them to the client. To do this we will create a new method called GetClientsFromDb as shown below:
private List<Contact> GetContactsFromDb()
{ List<Contact> contacts = new List<Contact>();
using (SqlConnection conn = new SqlConnection(ConnStr))
{ SqlCommand cmd = new SqlCommand(
"select ContactID, FirstName, LastName, Phone from dbo.Contact",
conn);
conn.Open();
IDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{ Contact c = new Contact();
c.FirstName = dr.GetString(1);
c.LastName = dr.GetString(2);
c.Phone = dr.GetString(3);
contacts.Add(c);
}
}
return contacts;
}
Normally we would use something like Enterprise Library to deal with the data access, but since this is a very simple example I just used the standard SqlClient objects. All we are doing is pulling all the contacts from the database and returning them as a list of Contact objects. Notice that the t-sql is very specific with column naming and table referencing, this will be important later when we add our Sql Dependency object. At this point we could return this to the client by changing GetContacts to use this method instead of the GetContactsFromFile method. However, let's continue to move forward and add in our Sql Dependency now. Below is the updated class definition for the ContactProvider class:
namespace ContactService
{ public class ContactProvider : IContactProvider, IDisposable
{ private static readonly string ConnStr = ...
private SqlDependency _sqlDep;
private List<Contact> _contacts;
public ContactProvider()
{ SqlDependency.Start(ConnStr);
}
private static List<IListChangedCallback> _callbacks = ...
public List<Contact> GetContacts()
{ ...
if (_contacts == null)
{ _contacts = GetContactsFromDb();
}
return _contacts;
}
private List<Contact> GetContactsFromDb()
{ ...
SqlCommand cmd = new SqlCommand(...);
_sqlDep = new SqlDependency(cmd);
_sqlDep.OnChange += OnListChanged;
conn.Open();
...
}
private void OnListChanged(object sender, EventArgs e)
{ if (_callbacks.Count > 0)
{ _contacts = GetContactsFromDb();
Action<IListChangedCallback> invoke =
delegate(IListChangedCallback callback)
{ callback.OnCallback(_contacts.ToArray());
};
_callbacks.ForEach(invoke);
}
}
public void Dispose()
{ SqlDependency.Stop(ConnStr);
}
}
}
I've highlighted the changed in bold, hopefully you can see them. The first thing to note is that the class now implements IDisposable. This is to enable use to call SqlDependency.Start when the class is contructed and to call SqlDependency.Stop when the class is disposed. This is done, to quote Sanchan Sahai Saxena in the article above, to "create the necessary queue, service and procedure and starts a listener to monitor the queue".
The next item to note is that when we get our initial list of contacts we are creating the dependency object using the same command. This ensures us that when any item in our list of contacts changes the dependency will be fired. We are also storing the list of contacts locally to avoid hitting the database multiple times. The dependency object's Change event is then wired to our OnListChanged event handler.
Finally we update our OnListChanged method to pull the contacts from the database and make the callback to the client with the updated list of contacts. To test the application fire up the service and then one or more of the client applications. Then open the table using SQL Server Management Studio and update/insert/delete records in the table. The results should be reflected almost instantly in the client applications. That's it!
Hopefully this has been a helpful series that demonstrates some of the cool features in both WCF and WPF (and SQL Server 2005). It has been a good learning experience for me. As we progress on our real application using some of these techniques I will try to add to this example.
Technorati Tags: WPF WCF Sql Server