Page 1 of 1

Silverlight - Connecting to remote database - WCF Tutorial on connecting to remote database using WCF

#1 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Posted 11 May 2010 - 01:50 PM

This tutorial will show how connect to a server database from a Silverlight application.

I will be using Visual Studio 2010 and Silverlight 4.

So first, we will create a Silverlight 4 application..
Attached Image
Attached Image

Next, we are just going to put a DataGrid on our MainPage.xaml. I have given the XAML so it can be copied and pasted.

<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/expression/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" application that was automatically created for us.
Attached Image

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 page.

public MainPage()
{
    InitializeComponent();

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



You will need to rebuild your solution at this point.

We 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.
Attached Image

Now we are ready to do some code. Remember that 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..
Attached Image

Here is the full code of the "MainPage.xaml.cs" file.
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();
        }
    }
}


This post has been edited by eclipsed4utoo: 12 May 2010 - 07:11 AM


Is This A Good Question/Topic? 2
  • +

Replies To: Silverlight - Connecting to remote database - WCF

#2 Guest_megan111*


Reputation:

Posted 12 May 2010 - 03:44 AM

thank you :)
Was This Post Helpful? 0

#3 Guest_megan_111*


Reputation:

Posted 13 May 2010 - 06:16 AM

If you are returning all record column names and records from a large database, how would you set up the output result?

example, output a table containing

Name Address Number
John 123 Fake St 123456789
Was This Post Helpful? 0

#4 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 294
  • View blog
  • Posts: 1,533
  • Joined: 07-April 08

Posted 25 August 2010 - 07:10 AM

can you talk about doing windows authentication using the WCF service. This code works great if your passing in the username and password in your connection string.
Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Posted 25 August 2010 - 07:21 AM

check out this link...

http://msdn.microsof...y/ms733089.aspx
Was This Post Helpful? 0
  • +
  • -

#6 Guest_Dan*


Reputation:

Posted 17 January 2011 - 10:16 AM

Could someone assist me with translating this code to VB? I'm stumbling on the service code a smidge...
Was This Post Helpful? 0

#7 bflosabre91  Icon User is offline

  • go sabres

Reputation: 105
  • View blog
  • Posts: 1,439
  • Joined: 22-February 08

Posted 27 January 2011 - 07:22 AM

i followed up until you add service reference. it keeps sayings "An error(Details) occurred while attempting to find services at 'http://localhost:2843/DatabaseService.svc'". Im not sure what the port should be if that might be the issue. I'm using sql server 2008 if that makes a difference. Any help i would appreciate it, thanks.
Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Posted 02 February 2011 - 03:40 PM

Run the web project. It will start an ASP.Net development server. This will give you the correct port number(you should see the notification come up).
Was This Post Helpful? 0
  • +
  • -

#9 Guest_MarkusR*


Reputation:

Posted 23 February 2011 - 11:14 AM

Sorry about the previous post attempt...

I am getting the following error when I am after I discover the service reference and click ok.

"There was an error downloading metadata from the address. Please verify that you have entered a valid address."

Thoughts?

-Markus
Was This Post Helpful? 0

Page 1 of 1