DataGrid Binding and CRUD Operations in Silverlight 4 using WCF Service

59 Replies

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

clip_image002

Enter Project Name -> Click OK

New Silverlight Application window will appear

clip_image004

Click Ok

Design the page for Phone Book as shown below.

clip_image006

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

clip_image008

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

clip_image002[4]

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.

clip_image002[1]

 

That’s it. Enjoy

Link to Download the Code

59 thoughts on “DataGrid Binding and CRUD Operations in Silverlight 4 using WCF Service”

    1. mscoder Post author

      Hi
      It seems that you have not added service reference to the project. After adding service reference you will be able to access MSCoderServiceClient.

      Follow these steps to add reference

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

      Click Discover -> Select the service -> Click Ok

      If you have any other doubt let me know.

      Reply ↓
  6. TIPS

    I have done added the service reference like instructed I am thinking maybe am missing a namespace? coz still i cant get that working.

    Reply ↓
    1. mscoder Post author

      If you are still facing the problem mail me a sample project.. I will look at it and will get back to you.

      Reply ↓
  7. Paul

    MsCoder, been reading the article there see. Jim dandy one. Problematic in the way of deployment. I did send you an email to your accounts listed on file. Could you offer any pointers,

    I read your article
    DataGrid Binding and CRUD Operations in Silverlight 4 using WCF Service

    here

    http://mscoder.wordpress.com/2010/07/11/datagrid-binding-and-crud-operations-in-silverlight-4-using-wcf-service/

    I found it ran very easily in visual studio 2010.

    I am having a problem deploying it on a non-dev system using iis 6 and windows server 2003. I have spent some time
    on the problem and it seems like a database issue. I used your app to create an app of my own and I use a database
    connection string on my windows server 2003 non-dev system which I have used for asp.net apps.

    In summary, I have

    1. added my mime types as shown here

    http://www.c-sharpcorner.com/UploadFile/nipuntomar/ConfigureIISforSilverlight08122008145106PM/ConfigureIISforSilverlight.aspx

    I can load up my service similar to this

    2. I load my service up like this and it loads up

    http://localhost:109/MyService.svc

    where the 109 is a tcp port I set in iis not a dev port, so the service is accessible.

    3. I have tried this technique in the article below on my own example, works in dev, not in non-dev
    however my example like yours has database access.

    Note this technique in the article below does work both dev and non-dev, but there is no database!!!
    http://www.dotnetcurry.com/ShowArticle.aspx?ID=355

    4.
    I have all these installed on the server
    .net Framework 4
    Silverlight Runtime
    Silverlight SDK
    Download the RIA Services MSI and save it on your machine
    Open up a command prompt and type the following command.
    msiexec /i RIAServices.msi SERVER=true

    5. I have attempted to address cross domain issues using

    a-clientAccessPolicy.xml:

    b-crossDomain.xml :

    6. Gave Permissions for these 2 users in database

    NT AUTHORITYNETWORK SERVICE

    NT AUTHORITYIUSR

    7.
    I believe this is a database problem because I have been able to deploy a non-database-enabled silverlight app with silverlight enabled wcf with no issue, however I am using a connection string which works in a .net asp app I have on the same non-dev system
    my connection string has the form
    Data Source=localhost;Initial Catalog=DatabaseName;Integrated Security=True;UID=username;password=mypassword

    If you could explain how to deploy
    How To Deploy DataGrid Binding and CRUD Operations in Silverlight 4 using WCF Service

    as shown here

    http://mscoder.wordpress.com/2010/07/11/datagrid-binding-and-crud-operations-in-silverlight-4-using-wcf-service/

    on a windows server 2003 non-dev system, I would be most appreciative.

    When I attempt to deploy your app on a windows server 2003 non-dev system, I can load the app and see the gui
    controls, I can load the service directly similar to this http://localhost:109/MyService.svc, but the data never
    fills in.

    Thanks,
    Paul

    Reply ↓
    1. mscoder Post author

      Hi Paul,
      I will be happy to help you out. First you tell me whether you are getting any exception while running the hosted application ?
      If yes then send me the detail of that exception.
      and Once you remove the database related stuff from one Method and just try to call that method and then see whether you are able to call that method or not.

      Just like this

      [OperationContract]
      public List GetAllPersons()
      {
      List persons = new List();
      persons.Add(new Person() { ID = 1, Name = “Dheeraj”, City = “Bangalore”, PhoneNo = “234234″ });
      persons.Add(new Person() { ID = 2, Name = “Surya”, City = “Bangalore”, PhoneNo = “234234″ });
      persons.Add(new Person() { ID = 3, Name = “Rahul”, City = “Bangalore”, PhoneNo = “234234″ });

      return persons;
      }
      by doing this you will come to know whether problem is with configuration or database.

      Thanks
      Surya Gahlot

      Reply ↓
  8. Paul

    As I said from Visual Studio 2010 development server it runs fine and on my system happens to use port 3471. So I copied the project to the development server and from iis manager I created a new web site called MSCoder with home directory at the level of the apx/html test page. I set one of those as the website document ( I tried each one ). IIS allows you to set the tcp port, so I set it to 3471. Then from within IIS I right clicked on the website MSCoder and selected browse from the context menu and the gui controls of your example show followed by a windows message box opening up with reports the following exception:

    An error has occurred in the script page.

    Line: 1
    Char: 1
    Error: Unhandled Error in Silverlight Application [Async_ExceptionOccurred]
    Arguments:
    Debugging resource strings are unavailable. Often the key and
    arguments provide insufficient information to diagnose the problem. See
    http://go.microsoft.com/fwlink/?
    linkid=106663&Version=4.0.50826.0&File=System.dll&Key=Async_ExceptionOccurred
    at
    Do you want to continue running scripts on this page?

    Yes (Button ) No ( Button )

    MSCoderSilverlightGridSampleWithWCF.MainPage.client_GetAllPersonsCompleted
    (Object sender.GetAllPersonsCompletedEventArgs e)
    at
    MSCoderSilverlightGridSampleWithWCF.PersonService.MSCoderServiceClient.OnGetAllPersonsCompleted
    (Object state)
    Code: 0
    URL: http://localhost:3471

    Reply ↓
  11. Paul

    I also did try your sample on a windows vista system using iis 7 with checking to verify needed mime types. The windows vista system has visual studio 2008 installed on it, however, I did not try to run from within that environment. The windows vista system does have ms sql server 2008 installed on it. I created a website in iis 7 with port 3471. The gui loads, but the data never fills in. Error on page is noted. I don’t know how to run from iis 7 and generate an error as I have done with iis 6 as posted above. Technically the windows vista system is a silerlight 3 ready vs studio 2008 development system. The windows server 2003 system which I am calling non-development does have visual studio 2005, but there is no off the shelf support for silverlight in such an environment, so I am referring to it as above as a non-dev system.

    I am working on completing the remainder of your advisements.

    Thank you kindly for your continued guidance.

    Reply ↓
  12. Paul

    I changed as you advised here

    Just like this

    [OperationContract]
    public List GetAllPersons()
    {
    List persons = new List();
    persons.Add(new Person() { ID = 1, Name = “Dheeraj”, City = “Bangalore”, PhoneNo = “234234″ });
    persons.Add(new Person() { ID = 2, Name = “Surya”, City = “Bangalore”, PhoneNo = “234234″ });
    persons.Add(new Person() { ID = 3, Name = “Rahul”, City = “Bangalore”, PhoneNo = “234234″ });

    return persons;
    }
    by doing this you will come to know whether problem is with configuration or database.

    executed from within iis 6 on windows server 2003 and from a browser without iis as http://localhost:3471/ and it worked.

    So it does seem that I am having a database problem.

    Reply ↓
  16. Paul

    I wondered if it might be the AttachDbFilename=|DataDirectory| bit, however as I noted above I am using a connection string which works in a .net asp app I have on the same non-dev system for my own silverlight app which accesses a database in mssql server 2005. My connection string in my own silverlight app has the form Data Source=localhost;Initial Catalog=DatabaseName;UID=username;password=mypassword

    Above ( item 7 of my first post ) I list also Integrated Security=True, I tried both with and without.

    I get the same error in all these cases whether with your example or my own silverlight app.

    Reply ↓
    1. mscoder Post author

      Hi Paul,

      I have gone through your all the comments (You seem to be a very active person :) ).

      I think problem is with the configuration of the application.
      Once you check ServiceReferences.ClientConfig and EndPoints of the application.

      Or if you think that problem is with AttachDbFilename then you can add the same DB File to your Sql Server and modify the connection string as required.

      If still problem exists let me know.
      I will deploy the same application myself and will get back to you soon.

      Reply ↓
    2. mscoder Post author

      Attached DB File is created in SQL Server 2008 and you are having SQL Server 2005 installed on your server So That db file is not getting attached. This might be the reason.

      Extract the script from that db file and create a new database in SQL Server 2005 and then have a try.

      It might work for you.

      Reply ↓
      1. Paul

        I here you and will probably try it just to see, but if you recall, I did try and run under iis 7, on windows vista, with ms sql server 2008 with the same problem.

        Reply ↓
  18. Paul

    Ok, well it didn’t follow did it. Take my work for that the extract file lists

    endpoint address=”http://localhost:3471/MSCoderService.svc”
    binding=”customBinding” bindingConfiguration=”CustomBinding_MSCoderService”
    contract=”PersonService.MSCoderService” name=”CustomBinding_MSCoderService”

    Reply ↓
  19. Paul

    It actually makes sense that maybe the database was not being attached to ms sql server 2005 under windows server 2003 because the database file is probably a ms sql server 2008 database, but that doesn’t explain why it would not work in vista with iis 7 and ms sql server 2008….maybe some other reason here.

    I am going to script and regenerate the database on the windows server 2003 ms sql server 2005 anyway.

    Reply ↓
  20. Paul

    Well rescripting worked. In the haste and excitement, and overburdensomeness of it all, I neglected to realize that I was going from ms sql 2008 to ms sql 2005. Of course, that doesn’t explain why it didn’t work on windows vista with ms sql 2008, but I am sure there is a reason.

    I loaded up http://localhost:3471/ and deleted a record which I verified beforehand as being in the database using ms sql server 2005 database manager and after the deletion I reviewed the database data again from ms sql server 2005 manager and that was record was gone.

    So much for a long sad chapter of time wasted…and on to happy days of knowing what I should have already been able to figure out, lol.

    Thanks MSCoder. Sometimes you just need someone to talk with it about and then you come to see what you are not doing and should or are doing and should not be.

    Reply ↓
    1. mscoder Post author

      Its nice to hear from you that the issue has been resolved. Yes I agree with you sometimes when we discuss something with someone we get ideas and we learn something new.

      Anyway Thanks for sharing all this experience here.

      Reply ↓

  23. Pingback: Nested DataGrid using Silverlight 4 and WCF « MS Coder

  24. Paul

    MSCoder, this is not the happy story that I once thought it was. My efforts to move the code to a production system are failing. Along the way your code, this example, has stopped working on the non-dev system I was speaking of in earlier posts. My code still works on the non-dev system, however it will not consistently work on the production system. It worked a few times and then stopped. Did you ever deploy your code? My problem remains the same, something to do with the database.

    Reply ↓
  25. Eric Rodenberg

    MSCoder,

    I’ve tried to bind a combo box to the grid control but can’t seem to get it working. Seems like it should be a fairly simple thing to do but it is not the case.

    I would think that I could bind to the Static Resource personViewSource for my ItemsSource and then specify the column I want to bind to as the Selected Value.

    Any Ideas?

    Reply ↓
  26. Shekhar

    Hi,

    This example is good to start with wcf services.

    I am implementing this example but when trying to add service reference I’m facing following error :
    system.serviceModel/serviceHostingEnvironment/serviceActivations could not be found

    Please guide

    Thanks a lot

    Shekhar

    Reply ↓
  27. Devesh Kumar

    Seriously, It Awesome!!!

    Anyhow how to add an MDF File. I am getting errors when I am adding a new mdf file generated when i make the another database with same tables.

    Reply ↓
  30. omar olguin

    I have the same problem, the MSCoderServiceClient is missing

    the type or namespace ‘MSCoderServiceClient’ could no be found

    I have added the service reference in the project
    what else could be bad???
    tks

    Reply ↓
  32. DaveCS

    Awesome stuff. I’m moving from Winforms with the old ADO model to silverlight with WCF and RIA services. This has been the most straight forward starting point I’ve found. Thanks again

    Reply ↓
  34. hari

    i am a beginner in silverlight 4 with wcf service. need ur help to solve the problem.
    in btnSave_click, i get error “reference missing” in the line
    Person person = new Person();
    i have created the Class “Person” in web project as follows,
    i get the error in client side – mainpage.xaml.cs
    public class Person
    {
    public int ID = 0;
    public string Name = string.Empty;
    public string City = string.Empty;
    public string PhoneNo = string.Empty;
    }

    what should i do to clear this error.

    Reply ↓
  35. M.Ezzat

    The program is running, and there are no syntax errors. but, when I’m adding data and press save this will be shown

    “An unhandled exception (“Unhandled error in silverlight application an exception occurred during the operation, making the result invalid, Check Inner exception details: at System.ComponentModel.AsyncCompletedEventArgsRaiseExceptionIfNecessary()”) ”

    why is that ?!

    Reply ↓
  40. Brandon

    I am having trouble getting access to the Database used in this example. This isn’t a problem since I am using my own database, but I would really like to see how you did your Stored Procedures within your database. Can you post those Stored Procedures (GetAllPersons, DeletePerson, SavePerson)?

    Reply ↓
  42. bob

    Excellent examples that actually work! most examples i’ve downloaded don’t work right out of the box, so i’m reluctant to dig in further, but MSCODER’s examples work great, he write his own book or work as a consultant to technical book writers to insure that they’re examples work with basic CRUD and right out of the box as to a techie that’s what will kill a book right off! we need working examples like this!! hat’s off MSCODER!

    Reply ↓
  43. Dega

    Hi, I have done the same project in VB. When i am trying to Add Service Reference from my Silverlight Project, after clicking Discover, it shows the address, and 1 source found, but when I try to expand or Click GO, it raises error saying – There was an error downloading metadata…
    I am stuck there.
    When I downloaded the original project in CS by MSCODER, it works without any issue.
    Please help me to resolve this.
    Thanks in advance

    DEGA

    Reply ↓
  44. nero

    hi…very nice tutorial and useful too.., but i have two questions..
    first : the binding u did in the mainpage() what is called?
    second: if i want to bind to different tables,- i mean the same datagrid controller but according to a button or a radiobutton the binding will differ.- with different column numbers how can i do it??
    thanx in advance.

    Reply ↓
  45. Dega

    Hi,
    No doubt, this is a great article. How to use Silverlight validation mechanism instead of this messagebox?
    ThanQ
    Dega

    Reply ↓
  47. Sharathrr

    Hai

    i have one query in this
    i have one combobox control and getting both value field and text field and easily inserting into a database but not possible bind value but am getting value on edit of that grid

    Can u please let me know

    thanx in adv

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>