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();
}
}
}
