DataGrid Binding and CRUD Operations in Silverlight 4 using WCF Service

In this article we will create a Phone Book Application using Silverlight 4. We will create a WCF Service to consume data and to bind the DataGrid. We will then implement CRUD operations in it.

First we will create a Silverlight Application

Open VS2010 -> File -> New Project -> Silverlight Application

Enter Project Name -> Click OK

New Silverlight Application window will appear

Click Ok

Design the page for Phone Book as shown below.

Once design is over Add the “Silverlight-enabled WCF Service”

Solution Explorer -> Right Click on the MSCoderSilverlightGridSampleWithWCF.Web -> Add -> New Item -> Select Silverlight-enabled WCF Service

Click Add

Then right click on the MSCoderService.svc -> Select Code

We will write 3 methods as below

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Configuration;

namespace MSCoderSilverlightGridSampleWithWCF.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class MSCoderService
    {
        string myConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True;User Instance=True";

        [OperationContract]
        public List<Person> GetAllPersons()
        {
            List<Person> persons = new List<Person>();
            using (SqlConnection con = new SqlConnection(myConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "GetAllPersons";
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Person person = new Person();
                        person.ID = int.Parse(reader["ID"].ToString());
                        person.Name = Convert.ToString(reader["NAME"]);
                        person.City = Convert.ToString(reader["CITY"]);
                        person.PhoneNo = Convert.ToString(reader["PHONENO"]);

                        persons.Add(person);
                    }
                }
            }


            return persons;
        }

        [OperationContract]
        public int SavePerson(Person person)
        {
            using (SqlConnection con = new SqlConnection(myConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "SavePerson";
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = person.ID;
                    cmd.Parameters.Add("@NAME", System.Data.SqlDbType.VarChar).Value = person.Name;
                    cmd.Parameters.Add("@CITY", System.Data.SqlDbType.VarChar).Value = person.City;
                    cmd.Parameters.Add("@PHONENO", System.Data.SqlDbType.VarChar).Value = person.PhoneNo;

                    con.Open();

                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
            }

        }

        [OperationContract]
        public bool DeletePerson(int id)
        {
            using (SqlConnection con = new SqlConnection(myConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "DeletePerson";
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = id;

                    con.Open();

                    return Convert.ToBoolean(cmd.ExecuteNonQuery() > 0);
                }
            }
        }
    }
}

Now add Service Reference to “MSCoderSilverlightGridSampleWithWCF” Project

Solution Explorer -> Right Click “MSCoderSilverlightGridSampleWithWCF” Project -> Select Add Service Reference…Add Service Reference Dialog will be appearing..

Click Discover

Enter Namespace as “PersonService” -> Click OK

Now Open MainPage.xaml.cs

In Constructor We will add required columns to the DataGrid

 

public MainPage()
{
    InitializeComponent();

    grdPerson.Columns.Add(new DataGridTextColumn
    {
        Header = "ID",
        Binding = new Binding("ID")
    });

    grdPerson.Columns.Add(new DataGridTextColumn
    {
        Header = "Name",
        Binding = new Binding("Name"),
        Width = new DataGridLength(100)

    });

    grdPerson.Columns.Add(new DataGridTextColumn
    {
        Header = "City",
        Binding = new Binding("City")
    });

    grdPerson.Columns.Add(new DataGridTextColumn
    {
        Header = "Phone No",
        Binding = new Binding("PhoneNo")
    });

    LoadGrid();
}

 

And then we will call LoadGrid() method

private void LoadGrid()
{
    MSCoderServiceClient client = new MSCoderServiceClient();
    client.GetAllPersonsCompleted += new EventHandler<GetAllPersonsCompletedEventArgs>(client_GetAllPersonsCompleted);
    client.GetAllPersonsAsync();
}

 

In LoadGrid() method we will create a instance of MSCoderServiceClient to get the data from Service.

Then we will attach an event handler for GetAllPersonCompleted.

void client_GetAllPersonsCompleted(object sender, GetAllPersonsCompletedEventArgs e)
{
    grdPerson.ItemsSource = e.Result;
}

 

In this event handler we will be binding the grid after that we will call function

client.GetAllPersonsAsync() to get the data asynchronously.

Just like this we will be attaching the event handlers for Saving and Deleting Records also.

private void btnNew_Click(object sender, RoutedEventArgs e)
{
    ClearFields();
}

private void ClearFields()
{
    lblID.Content = "-1";
    txtName.Text = string.Empty;
    txtCity.Text = string.Empty;
    txtPhoneNo.Text = string.Empty;
    txtName.Focus();
}

 

private void btnSave_Click(object sender, RoutedEventArgs e)
{
    if (Validate())
    {
        MSCoderServiceClient client = new MSCoderServiceClient();
        client.SavePersonCompleted += new EventHandler<SavePersonCompletedEventArgs>(client_SavePersonCompleted);

        Person person = new Person();
        person.ID = int.Parse(lblID.Content.ToString());
        person.Name = txtName.Text;
        person.City = txtCity.Text;
        person.PhoneNo = txtPhoneNo.Text;

        client.SavePersonAsync(person);
    }
}

 

void client_SavePersonCompleted(object sender, SavePersonCompletedEventArgs e)
{
    if (e.Result > -1)
    {
        MessageBox.Show("Record Updated Successfully", "Save", MessageBoxButton.OK);
        ClearFields();
        LoadGrid();
    }
}

 

private bool Validate()
{
    if (txtName.Text.Trim().Length == 0)
    {
        MessageBox.Show("Name cannot be blank", "Error", MessageBoxButton.OK);
        txtName.Focus();
        return false;
    }
    else if (txtPhoneNo.Text.Trim().Length == 0)
    {
        MessageBox.Show("Phone No cannot be blank", "Error", MessageBoxButton.OK);
        txtPhoneNo.Focus();
        return false;
    }
    else
    {
        return true;
    }
}

 

private void btnDelete_Click(object sender, RoutedEventArgs e)
{
    if (lblID.Content.ToString() == "-1")
    {
        MessageBox.Show("Select a record to delete", "Delete", MessageBoxButton.OK);
    }
    else
    {
        if (MessageBox.Show("Are you sure you want to delete ? ", "Delete", MessageBoxButton.OKCancel) == MessageBoxResult.OK)
        {

            MSCoderServiceClient client = new MSCoderServiceClient();
            client.DeletePersonCompleted += new EventHandler<DeletePersonCompletedEventArgs>(client_DeletePersonCompleted);
            client.DeletePersonAsync(int.Parse(lblID.Content.ToString()));
        }
    }
}

 

void client_DeletePersonCompleted(object sender, DeletePersonCompletedEventArgs e)
{
    if (e.Result)
    {
        MessageBox.Show("Record Deleted", "Delete", MessageBoxButton.OK);
        ClearFields();
        LoadGrid();
    }
    else
    {
        MessageBox.Show("Deletion failed", "Delete", MessageBoxButton.OK);
    }
}

Now We will handle the Click event of the Grid Suppose If we click on a particular row of the grid that record should get displayed in the controls

For this I am using LoadingRow event of the Grid and in this I am attaching an eventhandler.

private void grdPerson_LoadingRow(object sender, DataGridRowEventArgs e)
{
    e.Row.MouseLeftButtonUp += new MouseButtonEventHandler(Row_MouseLeftButtonUp);
}

 

void Row_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)
{
    Person person = grdPerson.SelectedItem as Person;

    lblID.Content = person.ID;
    txtName.Text = person.Name;
    txtCity.Text = person.City;
    txtPhoneNo.Text = person.PhoneNo;
}

 

The final result of this will be looking like this.

 

That’s it. Enjoy

Link to Download the Code