Feed Icon  

Contact

  • Bryant Likes
  • Send mail to the author(s) E-mail
  • twitter
  • View Bryant Likes's profile on LinkedIn
  • del.icio.us
Get Microsoft Silverlight
by clicking "Install Microsoft Silverlight" you accept the
Silverlight license agreement

Hosting By

Hot Topics

Tags

Open Source Projects

Archives

Ads

Enabling WPF Magic Using WCF - Part 3

Posted in Sql and Xml | WCF | WPF at Thursday, 21 September 2006 06:45 Pacific Daylight Time

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:

  1. Create a new SQL Server 2005 project called ContactData
  2. In the project properties check the box for "Enable Service Broker"
  3. 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

Saturday, 23 September 2006 05:42:50 (Pacific Daylight Time, UTC-07:00)
I have done lot of work with WPF and WCF, in spite of all the things i knew about these two topics, I found your article very very useful. Thank you very much for sharing.
shanthu
Sunday, 24 September 2006 06:37:07 (Pacific Daylight Time, UTC-07:00)
//Window1.xaml.cs
public Window1()
{
InitializeComponent();
_content.Content = new ContactViewModel();
}

I have started employing this pattern in my project. There is scope for further decoupling. Wherever possible I would like to avoid knowing the Concrete classes.

The above code can be refactored to be

public Window1()
{
InitializeComponent();
IContactViewModel contactViewModel = MyViewFactory.GetViewModel<IContactViewModel>();
_content.Content = IContactViewModel;
}

This will allow us keep chaging the implementation independent of the clients.
shanthu
Wednesday, 04 October 2006 20:15:15 (Pacific Daylight Time, UTC-07:00)
Hi
I am working on WCF for 1 month now. I have integreted the wcf services into my project.

Flow of data in our project is like this:
UI--->Business Layer--->Service Layer-->DB

Here between Business Layer and Service Layer I have to implement WCF. This service is called through WWF.
In WWF they are using global.asax file for calling web services. I have to remove these web services and implement wcf services. I have implemented wcf as a class lib project and unable to access this global variable declared in global.asax file. Can you suggest me to get through this problem
Pratap Gaikwad
Thursday, 05 October 2006 03:38:06 (Pacific Daylight Time, UTC-07:00)
If your WCF layer is implemented in a class library then you will need to host it somehow. Either in IIS or a windows NT service. I usually use a console application to host the WCF service during development and then move to a Windows NT service when I move to production.
Monday, 16 October 2006 12:26:39 (Pacific Daylight Time, UTC-07:00)
can you provided the completed source code?
Thank you
Leo
Tuesday, 21 November 2006 14:04:54 (Pacific Standard Time, UTC-08:00)
If you are using WF and WCF together, you should examine the SDK for the CrossTechnology samples for .NET 3.0 (http://msdn2.microsoft.com/en-us/library/ms756478.aspx).

Cheers! Great example. Very useful!
Wednesday, 19 March 2008 20:26:55 (Pacific Standard Time, UTC-08:00)
How can I use the CollationViewSource (because I want to use it's sort, filter functions in a Master-detail example) in this Example?
Or how can I use sort, filter function in this Example?

Feco
Monday, 24 March 2008 18:32:57 (Pacific Standard Time, UTC-08:00)
Dear Feco!

I did it. I send the source code!

Szilard
Szili
Thursday, 13 August 2009 07:29:49 (Pacific Daylight Time, UTC-07:00)
Hello Bryan,
Thank you for this article! Very useful information!
I would like to have the source code of the solution, is that possible?
Thank you :)
Nicolas
Comments are closed.