Performing Select, Insert, Update and Delete with ease in Silverlight 4 using WCF RIA Services

• Download source code - 3.83 MB

Introduction

The main idea behind this article is to learn performing the basic database operations in Silverlight 4 using the WCF RIA services. I have been searching the internet for any article which performs these CRUD operations but every article I get was only using the Datagrid control to execute this. So I decided to write an article which helps understanding it in more easy way by writing the code from scratch. By the end of this article you will find yourself very comfortable in interacting with database in Silverlight 4.

Background

As we all know Silverlight is a client technology, so it cannot directly interact with Database running at server, It needs a layer like Web service/WCF service to do so. I will be using WCF RIA services to interact with database from Silverlight application. WCF RIA services: WCF RIA Services is based on the notion of creating a data access layer on the server and at the same time creating the client code in Silverlight. It focuses on sharing the code between the client and the server including validation logic. In RIA Services, you expose data from the server project to client project by adding domain services. The RIA Services framework implements each domain service as a Windows Communication Foundation (WCF) service. Let’s jump up to creating a demo application. Open the Visual Studio 2010 IDE and select the Silverlight Navigation application template installed.

Creating the Silverlight Navigation Application

After that make sure you host the Silverlight application in an ASP.NET web project by checking the check box also check the Enable WCF RIA to enable the WCF RIA services.

This will get two projects in a single solution a Web Project and a Silverlight project. So by enabling RIA services at project creation time we have tied the Silverlight project to the Web project for RIA services.

Using the Database

I will be using the Database named WCFriadb which I have created in SQL server 2008 which has a table named students having the schema:

Make sure the ID field is auto increment by 1.

Now add ADO.NET Entity model named StudentModel.edmx (you will get this in Data installed templates) in the Web project of solution.

Then follow the steps to connect to your database making the connection.

Then select the students table by expanding the Tables node.

Then you have StudentModel.edmx file like this

Make sure you Build the solution now.

Creating the Domain Service

Now we will add another new item called Domain Service Class named StudentDomainService.cs (you will get this in Web installed templates) in the Web project of solution.

This class represents the service the Silverlight client connects to.

Then a dialogue will appear to select the entities which we define by adding the ADO.NET entity Data model. Select the student entity make it editable by clicking the checkboxes. 

Now build the Solution again. So far our solution looks like: 

If you Show All Files in the SilverlightWCFRIA project, you will see a Generated_Code folder with a SilverlightWCFRIA.Web.g.cs file in it. This contains the generated code which RIA services generate for you.

In this the RIA Services generates client proxy classes for the client application based on entities and operations we have exposed in the middle tier. Whenever you make changes to the Domain Service Class StudentDomainService.cs like writing any application logic etc. and build the solution the Ria services will generate the code accordingly in SilverlightWCFRIA.Web.g.cs you don’t need to do anything in this class.

Using the code

Let’s add the interface for user to perform the CRUD operation on database, In the views folder we will add a Silverlight page named AddData.xaml to do the insertion.

In the same way add three more Silverlight pages like ShowData.xaml, UpdateData.xaml and DeleteData.xaml for retrieval, updating and deletion.

Now in MainPage.xaml we will modify the code so that navigation frame should link to the pages we created, we will show ShowData page on startup. Here is the modified code of MainPage.xaml:

<navigation:Frame x:Name="ContentFrame" Style="{StaticResource ContentFrameStyle}"
                              Source="/ShowData"
                              Navigated="ContentFrame_Navigated" 
                              NavigationFailed="ContentFrame_NavigationFailed">
    <navigation:Frame.UriMapper>
      <uriMapper:UriMapper>
            <uriMapper:UriMapping Uri="" MappedUri="/Views/ShowData.xaml"/>
        <uriMapper:UriMapping Uri="/{pageName}" MappedUri="/Views/{pageName}.xaml"/>
      </uriMapper:UriMapper>
    </navigation:Frame.UriMapper>
</navigation:Frame>

And add the HyperlinkButton to link the to the pages like this:

<StackPanel x:Name="LinksStackPanel" Style="{StaticResource LinksStackPanelStyle}">
 
    <HyperlinkButton x:Name="Link1" Style="{StaticResource LinkStyle}"
       NavigateUri="/ShowData"
       TargetName="ContentFrame" Content="Show Data"/>
                                 
    <Rectangle x:Name="Divider1" Style="{StaticResource DividerStyle}"/>

    <HyperlinkButton x:Name="Link2" Style="{StaticResource LinkStyle}"
       NavigateUri="/AddData"
       TargetName="ContentFrame" Content="Add Data"/>
                    
    <Rectangle x:Name="Divider2" Style="{StaticResource DividerStyle}"/>
    
    <HyperlinkButton x:Name="Link3" Style="{StaticResource LinkStyle}"
                     NavigateUri="/UpdateData"
                     TargetName="ContentFrame" Content="Update Data"/>
    <Rectangle x:Name="Divider3" Style="{StaticResource DividerStyle}"/>
    <HyperlinkButton x:Name="Link4" Style="{StaticResource LinkStyle}"
                     NavigateUri="/DeleteData"
                     TargetName="ContentFrame" Content="Delete Data"/>
</StackPanel> 

Now let’s do the Insertion first, in AddData.xaml, we will create a simple Layout to enter the student name, age with a submit button code is:

<Grid x:Name="LayoutRoot" Width="400" Height="250">
    <Grid.RowDefinitions>
        <RowDefinition ></RowDefinition>
        <RowDefinition ></RowDefinition>
        <RowDefinition ></RowDefinition>
    </Grid.RowDefinitions>
    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="30*">

        </ColumnDefinition >
        <ColumnDefinition Width="70*"></ColumnDefinition>
    </Grid.ColumnDefinitions>
<sdk:Label Content="Student Name:" Grid.Row="0" 
           FontSize="12" Grid.Column="0"/>
<TextBox x:Name="txtstudentName" Margin="0" HorizontalAlignment="Left" 
             Grid.Row="0" Height="25"  Width="200" Grid.Column="1"/>
    <sdk:Label Content="Student Age:" Grid.Row="1" FontSize="12" Grid.Column="0"/>
    <TextBox x:Name="txtstudentage" Margin="0" 
           HorizontalAlignment="Left" Grid.Row="1" 
           Height="25" Width="200" Grid.Column="1"/>
<Button Grid.Row="2" Grid.Column="1" Height="30" Width="100" 
   Content="Submit" x:Name="btnsubmit" Click="btnsubmit_Click" HorizontalAlignment="Left"/>
</Grid> 

In this we have taken a grid layout with three rows and two columns. The two textboxes for entering the name and age of student with named as txtstudentName and txtstudentage and a button name btnsubmit. The created design should look like this:

Now on Click event of btnsubmit I will be writing the code for insertion. So the code in AddData.xaml.cs looks like this:

namespace SilverlightWCFRIA.Views
{
    public partial class AddData : Page
    {
        StudentDomainContext objctx;
        // StudentDomainContext class in the generated code acts as
        //the "proxy" to communicate from the Silverlight client to the domain
        //service
        
    
        public AddData()
        {
           InitializeComponent();
        }
 
        // Executes when the user navigates to this page.
        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
        }
 
        private void btnsubmit_Click(object sender, RoutedEventArgs e)
        {
            objctx = new StudentDomainContext();
            //creating the instance of DomainContext corresponding the
            // StudentDomainService 
            student st = new student();//creating the instance of entity class
            st.StudentName = txtstudentName.Text;

            st.StudentAge = int.Parse(txtstudentage.Text);
            objctx.students.Add(st);//adding the entities to the entity set
            try
            {
               objctx.SubmitChanges();
               MessageBox.Show("Data added Successfully!");           
            }
            catch (Exception ex)
            {
                MessageBox.Show("Adding Data failed due to " + ex.Message);
           }
        }
    }
}

Now run the application and navigate to Add Data page in browser and insert the record.

Now we will display the data inserted in Show Data page for open the ShowData.xaml and add the following code:

<navigation:Page xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"  x:Class="SilverlightWCFRIA.ShowData"
    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"
    xmlns:navigation="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Navigation"
    mc:Ignorable="d" d:DesignWidth="640" d:DesignHeight="480"
    Title="Show Data"
    Style="{StaticResource PageStyle}">
 
   
<Grid x:Name="LayoutRoot">
     
<sdk:DataGrid Width="400" x:Name="studentgrid" Height="300" 
  HorizontalAlignment="Center" VerticalAlignment="Center"/>
   
</Grid>
 
</navigation:Page> 

Here we have taken a DataGrid control named studentgrid. Now to retrieve all the data from database and show it in this DataGrid we will add the following code in the ShowData.xaml.cs but before the we will add a GetStudents() method in StudentDomainService.cs which fires the query to retrieve all the columns from database ordering by student name:

public IQueryable<student> GetStudents()
{
    return this.ObjectContext.students.OrderBy(s=>s.StudentName);
}

And ShowData.xaml.cs looks like:

namespace SilverlightWCFRIA
{
    public partial class ShowData : Page
    {
        StudentDomainContext objctx;// StudentDomainContext class in the generated code acts as
        //the "proxy" to communicate from the Silverlight client to the domain service
        public ShowData()
        {
            InitializeComponent();
            objctx = new StudentDomainContext();//creating the instance of DomainContext 
            // corresponding the StudentDomainService
            EntityQuery<student> query = objctx.GetStudentsQuery();//passing the query
            // to EntityQuery
            LoadOperation<student> loadOp = this.objctx.Load(query);
           
            studentgrid.ItemsSource = loadOp.Entities;
            //setting the Itemsource of DataGrid to the entities loaded.
        }
 
 
        // Executes when the user navigates to this page.
        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
        }
    }
}

Run the code to see the output in browser

Now we will do the update. For this we all create an interface in UpdateData.xaml by adding the following code:

<navigation:Page x:Class="SilverlightWCFRIA.Views.UpdateData" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"
           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"
           xmlns:navigation="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Navigation"
           d:DesignWidth="640" d:DesignHeight="480"
           Title="Update Data">
   
<Grid x:Name="LayoutRoot" Width="400" Height="250">
 
        <Grid.RowDefinitions>
            <RowDefinition ></RowDefinition>
            <RowDefinition ></RowDefinition>
            <RowDefinition ></RowDefinition>
            <RowDefinition ></RowDefinition>
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="60*">
 
            </ColumnDefinition >
            <ColumnDefinition Width="70*"></ColumnDefinition>
        </Grid.ColumnDefinitions>
        <sdk:Label Content="Enter student ID to Edit:" Grid.Row="0" FontSize="12" Grid.Column="0"/>
        <TextBox x:Name="searchstudentName" Margin="0" HorizontalAlignment="Left" 
           Grid.Row="0" Height="25"  Width="150" Grid.Column="1"/>
        <Button Width="30" Height="25" x:Name="btngo" Click="btngo_Click" 
           Grid.Row="0" Grid.Column="1" Margin="125,0,0,0" Content="Go" />
        
        <sdk:Label Content="Student Name:" Grid.Row="1" FontSize="12" Grid.Column="0"/>
        <TextBox x:Name="txtstudentName" Margin="0" HorizontalAlignment="Left" 
           Grid.Row="1" Height="25"  Width="200" Grid.Column="1"/>
        <sdk:Label Content="Student Age:" Grid.Row="2" FontSize="12" Grid.Column="0"/>
        <TextBox x:Name="txtstudentage" Margin="0" HorizontalAlignment="Left" 
               Grid.Row="2" Height="25" Width="200" Grid.Column="1"/>
        <Button Grid.Row="3" Grid.Column="1" Height="30" Width="100" 
           Content="Update" Click="btnupdate_Click" x:Name="btnupdate"  HorizontalAlignment="Left"/>
</Grid>
</navigation:Page> 

The idea is to get the record which is to be edited in the textboxes and then update the changes by hitting the update button. For this the design is similar to AddData.xaml except a go button and an extra textbox for receiving the record to be edited.

The code in UpdateData.xaml.cs for showing the record to edited in two textboxes after receiving the Id from searchstudentName textbox and then updating the changes but before the we will add a GetStudentsByID(int id) method in StudentDomainService.cs which will take student id as a parameter from searchstudentName textbox. So add the following code in StudentDomainService.cs:

public IQueryable<student> GetStudentsByID(int id)
        {
            return this.ObjectContext.students.Where(s
=> s.ID == id);
        } 

And UpdateData.xaml.cs looks like:

namespace SilverlightWCFRIA.Views
{
    public partial class UpdateData : Page
    {
        StudentDomainContext objctx;// StudentDomainContext class in the generated code acts as
        // the "proxy" to communicate from the Silverlight client to the domain service
        public UpdateData()
        {
           InitializeComponent();
        }
 
        // Executes when the user navigates to this page.
        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
        }
        //code for Retrieving the record and filling it in two textboxes
        private void btngo_Click(object sender, RoutedEventArgs e)
        {
           objctx = new StudentDomainContext();
           //creating the instance of DomainContext corresponding the StudentDomainService
           var studentid=int.Parse(searchstudentName.Text);
           var query = objctx.GetStudentsByIDQuery(studentid);
           objctx.Load(query, LoadData, null);//LoadData is a callback function for Load operation
        }
        private void LoadData(LoadOperation lo)
        {
            foreach (student st in lo.Entities)
            {
                txtstudentName.Text = st.StudentName;
                txtstudentage.Text = st.StudentAge.ToString();
            }
        }
        //code for updating the changes made in two textboxes back to database
        private void btnupdate_Click(object sender, RoutedEventArgs e)
        {
           objctx = new StudentDomainContext();
           var studentid = int.Parse(searchstudentName.Text);
           var query = objctx.GetStudentsByIDQuery(studentid);
           objctx.Load(query, EDitData, null);//LoadData is a callback function for Load operation
        }
        private void EDitData(LoadOperation<student> lo)
        {
            student st = lo.Entities.First();
            st.StudentName = txtstudentName.Text;
            st.StudentAge = int.Parse(txtstudentage.Text);
            try
            {
                objctx.SubmitChanges();
                MessageBox.Show("Data updated successfully!");
            }
            catch(Exception ex)
            {
                MessageBox.Show("Data updation failed due to "+ex.Message);
            }
        }
    }
}

Now running the application and going to Update Data page will give the output:

Now the last thing deletion, the design for deleting a record in DeleteData.xaml will be same as in UpdateData.xaml except for a delete button instead of update. Hence we quickly move to the DeleteData.xaml.cs code:

namespace SilverlightWCFRIA.Views
{
    public partial class DeleteData : Page
    {
        StudentDomainContext objctx;
        // StudentDomainContext class in the generated code acts as
        // the "proxy" to communicate from the Silverlight client to the domain service
        public DeleteData()
        {
           InitializeComponent();
        }
 
        // Executes when the user navigates to this page.
        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
        }
        //code for Retrieving the record which to be deleted and
        //filling it in two textboxes
        private void btngo_Click(object sender, RoutedEventArgs e)
        {
           objctx = new StudentDomainContext();
           //creating the instance of DomainContext corresponding the StudentDomainService
           var studentid = int.Parse(searchstudentName.Text);
           var query = objctx.GetStudentsByIDQuery(studentid);
           objctx.Load(query, LoadData, null);//LoadData is a callback function for Load operation
        }
        private void LoadData(LoadOperation lo)
        {
 
            foreach (student st in lo.Entities)
            {
                txtstudentName.Text = st.StudentName;
                txtstudentage.Text = st.StudentAge.ToString();
            }
        }
        //code for deleting the record shown in two textboxes from the database
        private void btndelete_Click(object sender, RoutedEventArgs e)
        {
            objctx = new StudentDomainContext();
            var studentid = int.Parse(searchstudentName.Text);
            var query = objctx.GetStudentsByIDQuery(studentid);
            objctx.Load(query, Datadelete, null);//Datadelete is a callback function for Load operation
        }
        private void Datadelete(LoadOperation<student> lo)
        {
            student st = lo.Entities.First();
            objctx.students.Remove(st);//Removing the entity from the students entity set.
            try
            {
                objctx.SubmitChanges();
                MessageBox.Show("Data deleted successfully!");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Data deletion failed due to " + ex.Message);
            }
        }
    }
}

The output of the Delete Data page: