Database operations using Silverlight 2.0 WCF and LINQ to SQL

This article assumes that you have a minimum knowledge of technologies like “LINQ to SQL,” “WCF,” “Silverlight” and so forth. If you don’t, and if you need step-by-step walk-throughs for the above technologies, please go to the following links.

If you are new to Silverlight 2.0 development, please refer to the following articles that I have written before reading the rest of this one (available at http://www.aspfree.com/cp/bio/Jagadish-Chatarji/):

• Beginning Silverlight 2.0 Development using Visual Studio 2008

• Developing a Silverlight 2.0 Application that Consumes a WCF Service Using Visual Studio 2008

• Silverlight 2.0 Application Development with “LINQ to SQL” and WCF Service (HIGHLY RECOMMENDED)

• Introducing Two-Way Data Binding using Silverlight 2.0, WCF and “LINQ to SQL”

This article is based on the following configuration:

• Windows Server 2003 with SP2

• IIS

• SQL Server 2008

• Visual Studio 2008 (comes with .NET 3.5) with SP1

• Microsoft Silverlight 2.0 Tools for Visual Studio 2008 SP1 (available at http://www.microsoft.com/downloads/details.aspx?FamilyId=c22d6a7b-546f-4407-8ef6-d60c8ee221ed&displaylang=en)

To make this article simple, I created a table structure as seen at http://cid-41050f68f010a662.skydrive.live.com/self.aspx/Public/images/DeptEmp.gif

The entire source code for this article is available in the form of a free downloadable zip file. The solution was developed using Microsoft Visual Studio 2008 Team Edition (with SP1) with Microsoft SQL Server 2008 Developer Edition on Microsoft Windows Server 2003 Standard Edition (with SP2) with Silverlight 2.0 (RTM). I didn’t really test it in any other environment. I request that you post in the discussion area if you have any problems in execution.

Developing a WCF Service to interact with LINQ to SQL for all CRUD operations

Let us start with a new solution:

• Create a new Visual Studio 2008 solution.

• Add a new WCF Service project named “DemoEmpService.”

• Modify all “Service1” to “EmpService” everywhere in the project (steps are shown in my previous articles).

• Add a “LINQ to SQL Classes” item to the project (“DemoEmp.dbml”)

• Using “Server Explorer,” drag and drop “Emp” and “Dept” tables as shown in the following image.

• Modify “IEmpService.vb” as shown below:

<ServiceContract()> _

Public Interface IEmpService

<OperationContract()> _

Function GetEmployee(ByVal empno As Integer) As Emp

<OperationContract()> _

Function GetEmployeeList() As List(Of Emp)

<OperationContract()> _

Sub Add(ByVal objEmp As Emp)

<OperationContract()> _

Sub Update(ByVal objEmp As Emp)

<OperationContract()> _

Sub Delete(ByVal empno As Integer)

End Interface

• Implement the same in “EmpService” (service) class as follows:

Imports System.Data.Linq

Public Class EmpService

Implements IEmpService

Public Sub New()

End Sub

Public Function GetEmployeeList() As System.Collections.Generic.List(Of Emp) Implements IEmpService.GetEmployeeList

Using ctxt As New DemoEmpDataContext

ctxt.ObjectTrackingEnabled = False

Return ctxt.Emps.ToList

End Using

End Function

Public Sub Add(ByVal objEmp As Emp) Implements IEmpService.Add

Using ctxt As New DemoEmpDataContext

'adding a new row

ctxt.GetTable(Of Emp).InsertOnSubmit(objEmp)

'saving rows added

ctxt.SubmitChanges()

End Using

End Sub

Public Sub Delete(ByVal empno As Integer) Implements IEmpService.Delete

Using ctxt As New DemoEmpDataContext

Dim objEmpOld As Emp = ctxt.GetTable(Of Emp).SingleOrDefault(Function(p) p.Empno = empno)

If objEmpOld IsNot Nothing Then

'delete row

ctxt.GetTable(Of Emp).DeleteOnSubmit(objEmpOld)

'save changes

ctxt.SubmitChanges()

End If

End Using

End Sub

Public Function GetEmployee(ByVal empno As Integer) As Emp Implements IEmpService.GetEmployee

Dim objEmp As Emp = Nothing

Using ctxt As New DemoEmpDataContext

'retrieve emp row

objEmp = ctxt.GetTable(Of Emp).SingleOrDefault(Function(p) p.Empno = empno)

End Using

Return objEmp

End Function

Public Sub Update(ByVal objEmp As Emp) Implements IEmpService.Update

Using ctxt As New DemoEmpDataContext

Dim objEmpOld As Emp = ctxt.GetTable(Of Emp).SingleOrDefault(Function(p) p.Empno = objEmp.Empno)

If objEmpOld IsNot Nothing Then

'modify row

objEmpOld.Ename = objEmp.Ename

objEmpOld.Sal = objEmp.Sal

objEmpOld.Deptno = objEmp.Deptno

'save changes

ctxt.SubmitChanges()

End If

End Using

End Sub

End Class

Developing a Silverlight 2.0 page to demonstrate CRUD functionality

Now we have to develop a Silverlight 2.0 page which can consume our previously developed WCF Service. The page would look like the following:

The following are the steps necessary to develop the above Silverlight 2.0 page:

• Add a new “Silverlight” project (“DemoSL”) to the solution. This in turn will add a “DemoSL.Web” project (Silverlight hoster application) to the solution. Detailed steps are provided in previous articles.

• Add a new “Silverlight user control” (“Page4.xaml”) to the “DemoSL” project and modify “Application_Startup” in “App.xaml” as follows:

Private Sub Application_Startup(ByVal o As Object, ByVal e As StartupEventArgs) Handles Me.Startup

Me.RootVisual = New Page4()

End Sub

• Modify “Page4.xaml” as follows:

<UserControl x:Class="DemoSL.Page4"

xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

Width="400" Height="300" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="d">

 <Grid x:Name="LayoutRoot" Background="White">

 <Grid.RowDefinitions>

 <RowDefinition Height="0.54*"/>

 <RowDefinition Height="0.46*"/>

 </Grid.RowDefinitions>

 <Grid HorizontalAlignment="Stretch" Margin="0,0,0,2" VerticalAlignment="Stretch" Grid.RowSpan="1" d:IsLocked="True">

 <Grid.ColumnDefinitions>

 <ColumnDefinition Width="0.4*"/>

 <ColumnDefinition Width="0.4*"/>

 <ColumnDefinition Width="0.2*"/>

 </Grid.ColumnDefinitions>

 <Grid.RowDefinitions>

 <RowDefinition Height="0.25*"/>

 <RowDefinition Height="0.25*"/>

 <RowDefinition Height="0.25*"/>

 <RowDefinition Height="0.25*"/>

 </Grid.RowDefinitions>

 <TextBlock Margin="8,8,8,8" x:Name="lblEmpno" Text="Empno:" TextWrapping="Wrap" Grid.RowSpan="2"/>

 <TextBlock Margin="8,8,8,8" Text="Ename:" TextWrapping="Wrap" Height="23.796" x:Name="lblEname" VerticalAlignment="Stretch" Grid.Row="1" />

 <TextBlock Margin="8,8,8,8" Text="Sal:" TextWrapping="Wrap" Height="23.796" x:Name="lblSal" VerticalAlignment="Stretch" Grid.Row="2" />

 <TextBlock Margin="8,8,8,8" Text="Deptno:" TextWrapping="Wrap" Height="23.796" x:Name="lblDeptno" VerticalAlignment="Stretch" Grid.Row="3" />

 <TextBox Margin="8,8,8,8" Grid.Column="1" Text="{Binding Empno, Mode=TwoWay }" TextWrapping="Wrap" x:Name="txtEmpno" Grid.RowSpan="1"/>

 <TextBox Margin="8,8,8,8" Text="{Binding Ename, Mode=TwoWay }" TextWrapping="Wrap" Height="23.796" VerticalAlignment="Stretch" Grid.Column="1" Grid.Row="1" x:Name="txtEname"/>

 <TextBox Margin="8,8,8,8" Text="{Binding Sal, Mode=TwoWay }" TextWrapping="Wrap" Height="23.796" VerticalAlignment="Stretch" Grid.Column="1" Grid.Row="2" x:Name="txtSal"/>

 <TextBox Margin="8,8,8,8" Text="{Binding Deptno, Mode=TwoWay }" TextWrapping="Wrap" Height="23.796" VerticalAlignment="Stretch" Grid.Column="1" Grid.Row="3" x:Name="txtDeptno"/>

 <Button HorizontalAlignment="Stretch" Margin="8,8,8,8" VerticalAlignment="Stretch" Grid.Column="2" Content="Search" x:Name="btnSearch" Grid.RowSpan="1"/>

 </Grid>

 <Grid Margin="0,8,4,54" Height="76" Width="396" Grid.Row="1" d:IsLocked="True">

 <Grid.RowDefinitions>

 <RowDefinition Height="0.50*"/>

 <RowDefinition Height="0.50*"/>

 </Grid.RowDefinitions>

 <Grid.ColumnDefinitions>

 <ColumnDefinition Width="0.25*"/>

 <ColumnDefinition Width="0.25*"/>

 <ColumnDefinition Width="0.25*"/>

 <ColumnDefinition Width="0.25*"/>

 </Grid.ColumnDefinitions>

 <Button HorizontalAlignment="Stretch" Margin="17,6,17,8" VerticalAlignment="Stretch" Content="Clear" x:Name="btnClear"/>

 <Button HorizontalAlignment="Stretch" Margin="17,6,17,8" VerticalAlignment="Stretch" Content="Add" x:Name="btnAdd" Grid.Column="1"/>

 <Button HorizontalAlignment="Stretch" Margin="17,6,17,8" VerticalAlignment="Stretch" Content="Update" x:Name="btnUpdate" Grid.Column="2"/>

 <Button HorizontalAlignment="Stretch" Margin="17,6,17,8" VerticalAlignment="Stretch" Content="Delete" x:Name="btnDelete" Grid.Column="3"/>

 <TextBlock Margin="18,8,17,8" Grid.ColumnSpan="4" Grid.Row="1" Text="" TextWrapping="Wrap" x:Name="lblMsg" Foreground="#FF030557"/>

 </Grid>

 </Grid>

</UserControl>

Developing a Silverlight 2.0 page to demonstrate CRUD functionality: code behind

This is a continuation from the previous section. In the previous section, we developed the Siverlight 2.0 UI for CRUD operations against a WCF service. Now, we have to develop the code behind to access the WCF Service. Make sure that you have added a reference to the WCF service in your Silverlight 2.0 project.

The following is the code behind for the UI markup provided in the previous section. The explanation is provided in the next section.

Partial Public Class Page4

Inherits UserControl

Public Sub New()

InitializeComponent()

InitiateContext()

InitiateHandlers()

End Sub

#Region "Declarations"

Private objEmp As EmpService.Emp

Dim objService As EmpService.EmpServiceClient

#End Region

#Region "Helpers"

Private Sub InitiateContext()

objService = New EmpService.EmpServiceClient

objEmp = New EmpService.Emp

DoBinding()

End Sub

Private Sub DoBinding()

Me.LayoutRoot.DataContext = objEmp

End Sub

Private Sub InitiateHandlers()

AddHandler objService.AddCompleted, AddressOf AddEmployeeCompleted

AddHandler objService.UpdateCompleted, AddressOf UpdateEmployeeCompleted

AddHandler objService.DeleteCompleted, AddressOf DeleteEmployeeCompleted

AddHandler objService.GetEmployeeCompleted, AddressOf EmployeeFetched

End Sub

Private Sub ShowMessage(ByVal Msg As String)

Me.lblMsg.Text = Msg

End Sub

#End Region

#Region "Handlers"

Private Sub AddEmployeeCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.AsyncCompletedEventArgs)

ShowMessage("Added Successfully!")

End Sub

Private Sub UpdateEmployeeCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.AsyncCompletedEventArgs)

ShowMessage("Updated Successfully!")

End Sub

Private Sub DeleteEmployeeCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.AsyncCompletedEventArgs)

ShowMessage("Deleted Successfully!")

btnClear_Click(Nothing, Nothing)

End Sub

Private Sub EmployeeFetched(ByVal sender As Object, ByVal e As EmpService.GetEmployeeCompletedEventArgs)

If e.Result Is Nothing Then

ShowMessage("Employee not found!")

Else

objEmp = e.Result

DoBinding()

ShowMessage("Fetched!")

End If

End Sub

#End Region

#Region "Button Events"

Private Sub btnClear_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnClear.Click

objEmp = New EmpService.Emp

DoBinding()

End Sub

Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnAdd.Click

ShowMessage("Communicating...")

objService.AddAsync(objEmp)

End Sub

Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnDelete.Click

ShowMessage("Communicating...")

objService.DeleteAsync(objEmp.Empno)

End Sub

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnUpdate.Click

ShowMessage("Communicating...")

objService.UpdateAsync(objEmp)

End Sub

Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnSearch.Click

ShowMessage("Communicating...")

objService.GetEmployeeAsync(objEmp.Empno)

End Sub

#End Region

End Class

Press F5 and you should be able to do all CRUD operations against the “Emp” table in database using Silverlight 2.0 page together with WCF and LINQ to SQL.

If you run into any problems, please refer tp my dedicated sections (especially on troubleshooting) in my previous articles. Please go through all of the troubleshooting steps in my previous articles; do so, and you should have no problems.

Developing a Silverlight 2.0 page to demonstrate CRUD functionality: Explanation

This section explains the code given in the previous section.

The constructor in UI class is as follows:

Public Sub New()

InitializeComponent()

InitiateContext()

InitiateHandlers()

End Sub

“InitiateContext” and “InitiateHandlers” are custom methods developed especially to ease our process. The “InitiateContext” method instantiates a WCF Service client object (objService) along with a business object (objEmp). Once the instantiatiations are completed, the business object (objEmp) is assigned to the “DataContext” property of the “LayourRoot” container control to enable “Two-Way” data binding (explained in detail in my previous article).

Every WCF method from Silverlight UI is accessed asynchronously. Once the method at the WCF Service gets processed, the call back mechanism gets back to the method in Silverlight UI and then executes it. For call back mechanisms to work, we should register the handlers. This is handled in “InitiateHandlers.”

Every operation (a WCF method, for example) gets executed at the WCF Service in an asynchronous fashion. The following are the statements which access the WCF methods asynchronously:

objService.AddAsync(objEmp)

objService.DeleteAsync(objEmp.Empno)

objService.UpdateAsync(objEmp)

objService.GetEmployeeAsync(objEmp.Empno)

You can also observe that the same business object (objEmp) is passed while calling the respective WCF method.

In my upcoming articles, we will see more and more examples of Silverlight 2.0 development together with LINQ to SQL and WCF. I hope you enjoyed the article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com