Eclipsed4utoo's Blog

This tutorial will show how to connect to a database that is located on the deploy server from a Silverlight application.

I will be using Visual Studio 2010 and Silverlight 4.

So first, we will create a Silverlight 4 application.

Next, we are just going to put a DataGrid on our MainPage.xaml.

<UserControl x:Class="SilverlightWCFTutorial.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/exp ression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d"
    d:DesignHeight="300" d:DesignWidth="400" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk">

    <Grid x:Name="LayoutRoot" Background="White">
        <sdk:DataGrid 
            Name="dataGridPerson" 
            AutoGenerateColumns="True" 
            Height="206" 
            HorizontalAlignment="Left" 
            Margin="12,44,0,0"  
            VerticalAlignment="Top" 
            Width="376"
            ItemsSource="{Binding}">
        </sdk:DataGrid>
    </Grid>
</UserControl>

So for the Grid, we are simply going to show a list of names that are in a database table.  I have a database table setup as:

Table Name:   Person

Columns:
ID
FirstName
LastName
Age

I am going to concatenate the First Name and Last Name, then bind that to the grid.

Now that we have our Silverlight application ready, we will add a WCF service to our existing “SilverlightWCFTutorial.Web” project that was automatically created for us.

Now we are going to setup the interface that was created for us.  The interface automatically creates a method called “DoWork”.  We are going to change the name of this to be a little more specific.  I gave the method the name of “GetNames”.

[ServiceContract]
public interface IDatabaseService
{
    [OperationContract]
    List<string> GetNames();
}

Now that the interface is finished, we will move to the service file.  Double-click on the “DatabaseService.svc” file to get to the code.  This code is just going to run some simple ADO to get the data from the database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace SilverlightWcfService
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "DatabaseService" in code, svc and config file together.
    public class DatabaseService : IDatabaseService
    {
        string myConnectionString = "someConnectionString";

        public List<string> GetNames()
        {
            List<string> list = new List<string>();

            using (SqlConnection cn = new SqlConnection(myConnectionString))
            {
                using (SqlCommand cmd = cn.CreateCommand())
                {
                    cmd.CommandText = "SELECT FirstName + ' ' + LastName FROM Person";
                    cmd.CommandType = CommandType.Text;

                    cn.Open();

                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            list.Add(dr.GetString(0));
                        }
                    }
                }
            }

            return list;
        }
    }
}

Next, we will need to fix the “Markup” of the “DatabaseService.svc” file.   Right-click on the file, then choose “View Markup”.  In the markup, you will notice a “Service” attribute.  This should be in the format of “Namespace.ServiceName”.  In our case, the autogenerated value could be incorrect.  Change the code to this..

<%@ ServiceHost Language="C#" Debug="true" Service="SilverlightWCFTutorial.Web.DatabaseService" CodeBehind="DatabaseService.svc.cs" %>

Now that our WCF service is complete, we are going back to the Silverlight app.  We are going to add an event handler for the “Loaded” event of the MainPage.xaml.

public MainPage()
{
    InitializeComponent();

    this.Loaded += new RoutedEventHandler(MainPage_Loaded);
}

You will need to rebuild your solution at this point.

We will also need to add a Reference to the Service for our Silverlight project.  Right-click on the “SilverlightWCFTutorial” project, and choose “Add Service Reference…”.  Once the Add Service Reference window comes up, click the “Discover” button.  This will find our service.  Fill in the “Namespace”(called mine “MyDatabaseService”).  Then click OK.

Now we are ready to do some code.  Remember that the requests in Silverlight are made asynchronously.

In our “MainPage_Loaded’ event handler, we will have this code..

void MainPage_Loaded(object sender, RoutedEventArgs e)
{
    // The DatabaseServiceClient class was automatically created for us.
    // The autogeneration takes the name of the service, then appends "Client" to it,
    //   giving us the "DatabaseServiceClient" class.
    DatabaseServiceClient client = new DatabaseServiceClient();

    // Since requests in Silverlight are asynchronous, we have a Completed method
    //   that will be fired when the request has been completed.
    client.GetNamesCompleted += delegate(object s, GetNamesCompletedEventArgs es)
    {
        // when the request has been completed, we want to bind the data to the grid.
        // the Result property of the EventArgs contains the returned data.
        // ObservableCollection is a common collection that is used when databinding to
        //    a DataGrid.
        ObservableCollection<string> myList = es.Result;

        dataGridPerson.DataContext = myList;
    };

    client.GetNamesAsync();
}

And that’s it.  We now run the Silverlight app, and we get this…

Here is the full code of the MainPage.xaml…

using System.Collections.ObjectModel;
using System.Windows;
using System.Windows.Controls;
using SilverlightWCFTutorial.MyDatabaseService;

namespace SilverlightWCFTutorial
{
    public partial class MainPage : UserControl
    {
        public MainPage()
        {
            InitializeComponent();

            this.Loaded += new RoutedEventHandler(MainPage_Loaded);
        }

        void MainPage_Loaded(object sender, RoutedEventArgs e)
        {
            // The DatabaseServiceClient class was automatically created for us.
            // The autogeneration takes the name of the service, then appends "Client" to it,
            //   giving us the "DatabaseServiceClient" class.
            DatabaseServiceClient client = new DatabaseServiceClient();

            // Since requests in Silverlight are asynchronous, we have a Completed method
            //   that will be fired when the request has been completed.
            client.GetNamesCompleted += delegate(object s, GetNamesCompletedEventArgs es)
            {
                // when the request has been completed, we want to bind the data to the grid.
                // the Result property of the EventArgs contains the returned data.
                // ObservableCollection is a common collection that is used when databinding to
                //    a DataGrid.
                ObservableCollection<string> myList = es.Result;

                dataGridPerson.DataContext = myList;
            };

            client.GetNamesAsync();
        }
    }
}

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.