Silverlight 4 Linq to Sql and RIA sevice for CRUD operations

Silverloght 4 has lots of new fearures, for details visit Silverlight 4 . Let's start with a small application to perform CRUD operations using Linq to
SQL and RIA Services.

Open VS 2010 and create a new project named "Silverlight4LinqToSqlRiaServices"



The resulting screen will be



In Silverlight project view folder add new silverlight Page "EmployeeList"



Add Employee page title in application strings file under Assets/Resources



On the MainPage.xaml add new hyperlink to EmployeeList page

<HyperlinkButton Style="{StaticResource LinkStyle}"
NavigateUri="/EmployeeList" TargetName="ContentFrame" Content="{Binding Path=ApplicationStrings.EmployeePageTitle, Source={StaticResource ResourceWrapper}}"/>



Add new Linq to Sql class



And add the Employee and Company tables and Employee_Select sp from Server Explorer



Add new domain service



And rebuild the solution





Update the EmployeeService to use the stored procedure (Employee_Select) to select records

public IList<Employee_SelectResult> Employee_Select(string employeeId, string firstName, string lastName, string companyId)
{
long? id = null; long? compId = null; string fName = null; string lName = null;
id = string.IsNullOrEmpty(employeeId.Trim()) ? id : Convert.ToInt64(employeeId);
compId = string.IsNullOrEmpty(companyId.Trim()) ? compId : Convert.ToInt64(companyId);
fName = string.IsNullOrEmpty(firstName.Trim()) ? fName : firstName;
lName = string.IsNullOrEmpty(lastName.Trim()) ? lName : lastName;
return this.DataContext.Employee_Select(id, fName, lName, compId).ToList();
}

If you try to build the solutions you may have the error saying



I am not sure how to overcome this, but as a work around you can add [Key] before the id property in EmployeeDataClasses.designer.cs



Now we get an error free build.

We can add the Employee_SelectResult or Employee datasource to EmployeeList.xaml from DataSources tab, I would go here with Employee datasource



You can have the validation and descriptionViewer in EmployeeService.Metadata.cs as below=

...

internal sealed class EmployeeMetadata
{

// Metadata classes are not meant to be instantiated.
private EmployeeMetadata()
{
}

[Display(AutoGenerateField = false)]
public Company Company { get; set; }

[Display(AutoGenerateField = false)]
public long companyId { get; set; }

[Display(AutoGenerateField = false)]
[Key()]
public long id { get; set; }

[Display(Name = "First Name", Description = "Person's first name", Order = 1)]
[Required()]
[StringLength(10)]
public string firstName { get; set; }

[Display(Name = "Last Name", Description = "Person's last name", Order = 2)]
[Required()]
[StringLength(10)]
public string lastName { get; set; }

[Display(Name = "Gender", Description = "Male/Female", Order = 3)]
public Nullable<char> gender { get; set; }

[Display(Name = "Active", Description = "IsActive", Order = 4)]
public Boolean isActive { get; set; }

[Display(AutoGenerateField = false)]
public Binary image { get; set; }

}
...



Now drop the datapager from toolbox and set its datasource same as datagrid.

<sdk:DataPager Height="26" Name="dataPager1" PageSize="10" Source="{Binding ElementName=employeeDomainDataSource, Path=Data}" />

Add Silverlight toolkit busy indicator and put the datagrid in side it.

<toolkit:BusyIndicator BusyContent="{Binding Path=ApplicationStrings.ActivityEmployeeList, Source={StaticResource ResourceWrapper}}" IsBusy="{Binding ElementName=employeeDomainDataSource, Path=DomainContext.IsLoading}">
<sdk:DataGrid AutoGenerateColumns="True" ItemsSource="{Binding ElementName=employeeDomainDataSource, Path=Data}" Name="employeeDataGrid" RowDetailsVisibilityMode="VisibleWhenSelected" Width="400" IsReadOnly="True">

</sdk:DataGrid>
</toolkit:BusyIndicator>

So the resultant screen will be



Lets add the dataform to EmployeeList.xaml from to edit the records. In employeeList.xaml you need to add

xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data.DataForm.Toolkit"

to include dataform. Add NewTemplate and EditTemplate to this DataForm as below

<dataForm:DataForm ItemsSource="{Binding ElementName=employeeDomainDataSource, Path=Data}" Name="dataFormEmployee" AutoEdit="False" AutoCommit="False" DeletingItem="dataFormEmployee_DeletingItem" EditEnded="dataFormEmployee_EditEnded" CurrentItemChanged="dataFormEmployee_CurrentItemChanged" AddingNewItem="dataFormEmployee_AddingNewItem" DescriptionViewerPosition="BesideContent" Header="Employee Details" AutoGenerateFields="False" IsReadOnly="False">
<dataForm:DataForm.ReadOnlyTemplate>
<DataTemplate>
<StackPanel>
<dataForm:DataField Label="First Name" >
<TextBox Text="{Binding firstName, Mode=TwoWay}" />
</dataForm:DataField>
<dataForm:DataField Label="Last Name" >
<TextBox Text="{Binding lastName, Mode=TwoWay}" /> </dataForm:DataField>
<dataForm:DataField Label="Company" >
<StackPanel>
<ComboBox SelectedValuePath="id" DisplayMemberPath="name" ItemsSource="{Binding Source={StaticResource companyDomainDataSource}, Path=Data}" SelectedValue="{Binding companyId, Mode=OneWay}">
<ComboBox.ItemsPanel>
<ItemsPanelTemplate>
<VirtualizingStackPanel />
</ItemsPanelTemplate>
</ComboBox.ItemsPanel>
</ComboBox>
</StackPanel>
</dataForm:DataField>
<dataForm:DataField Label="Gender" >
<TextBox Text="{Binding gender, Mode=TwoWay,NotifyOnValidationError=True, ValidatesOnExceptions=True }" />
</dataForm:DataField>
<dataForm:DataField Label="Active" >
<CheckBox IsChecked="{Binding isActive, Mode=TwoWay,NotifyOnValidationError=True, ValidatesOnExceptions=True }" />
</dataForm:DataField>
</StackPanel>
</DataTemplate>
</dataForm:DataForm.ReadOnlyTemplate>
<dataForm:DataForm.EditTemplate>
<DataTemplate>
<StackPanel>
<dataForm:DataField Label="First Name" >
<TextBox Text="{Binding firstName, Mode=TwoWay}" />
</dataForm:DataField>
<dataForm:DataField Label="Last Name" >
<TextBox Text="{Binding lastName, Mode=TwoWay}" />
</dataForm:DataField>
<dataForm:DataField Label="Company" >
<StackPanel>
<ComboBox SelectedValuePath="id" DisplayMemberPath="name" ItemsSource="{Binding Source={StaticResource companyDomainDataSource}, Path=Data}" SelectedValue="{Binding companyId, Mode=TwoWay}">
<ComboBox.ItemsPanel>
<ItemsPanelTemplate>
<VirtualizingStackPanel />
</ItemsPanelTemplate>
</ComboBox.ItemsPanel>
</ComboBox>
</StackPanel>
</dataForm:DataField>
<dataForm:DataField Label="Gender" >
<TextBox Text="{Binding gender, Mode=TwoWay,NotifyOnValidationError=True, ValidatesOnExceptions=True }" />
</dataForm:DataField>
<dataForm:DataField Label="Active" >
<CheckBox IsChecked="{Binding isActive, Mode=TwoWay,NotifyOnValidationError=True, ValidatesOnExceptions=True }" />
</dataForm:DataField>
</StackPanel>
</DataTemplate>
</dataForm:DataForm.EditTemplate>
<dataForm:DataForm.NewItemTemplate>
<DataTemplate>
<StackPanel>
<dataForm:DataField Label="First Name" >
<TextBox Text="{Binding firstName, Mode=TwoWay}" />
</dataForm:DataField>
<dataForm:DataField Label="Last Name" >
<TextBox Text="{Binding lastName, Mode=TwoWay}" />
</dataForm:DataField>
<dataForm:DataField Label="Company" >
<StackPanel>
<riaControls:DomainDataSource AutoLoad="True" d:DesignData="{d:DesignInstance my1:Company, CreateList=true}" Height="0" LoadedData="companyDomainDataSource_LoadedData2" Name="companyDomainDataSource" QueryName="GetCompaniesQuery" Width="0">
<riaControls:DomainDataSource.DomainContext>
<my:EmployeeContext />
</riaControls:DomainDataSource.DomainContext>
</riaControls:DomainDataSource>
<ComboBox SelectedValuePath="id" DisplayMemberPath="name" ItemsSource="{Binding ElementName=companyDomainDataSource, Path=Data}" SelectedValue="{Binding companyId, Mode=TwoWay}">
<ComboBox.ItemsPanel>
<ItemsPanelTemplate>
<VirtualizingStackPanel />
</ItemsPanelTemplate>
</ComboBox.ItemsPanel>
</ComboBox>
</StackPanel>
</dataForm:DataField>
<dataForm:DataField Label="Gender" >
<TextBox Text="{Binding gender, Mode=TwoWay,NotifyOnValidationError=True, ValidatesOnExceptions=True }" />
</dataForm:DataField>
<dataForm:DataField Label="Active" >
<CheckBox IsChecked="{Binding isActive, Mode=TwoWay,NotifyOnValidationError=True, ValidatesOnExceptions=True }" />
</dataForm:DataField>
</StackPanel>
</DataTemplate>
</dataForm:DataForm.NewItemTemplate>
</dataForm:DataForm>

Add the following events in the EmployeeList.xaml.cs

private void dataFormEmployee_DeletingItem(object sender, System.ComponentModel.CancelEventArgs e)
{
if (MessageBox.Show("Are you sure you want to delete this item?\nThis cannot be undone", "Delete Item", MessageBoxButton.OKCancel) == MessageBoxResult.Cancel)
e.Cancel = true;
else
_isDeleting = true;
}

private void dataFormEmployee_EditEnded(object sender, DataFormEditEndedEventArgs e)
{
if (e.EditAction == DataFormEditAction.Commit)
{
if (dataFormEmployee.IsItemChanged)
employeeDomainDataSource.SubmitChanges();
if (_isInserting)
if (employeeDomainDataSource.HasChanges)
{
employeeDomainDataSource.SubmitChanges();
_isInserting = false;
}
}
else
_isInserting = false;
}

private void dataFormEmployee_CurrentItemChanged(object sender, EventArgs e)
{
if (_isDeleting)
{
if (employeeDomainDataSource.HasChanges)
{
employeeDomainDataSource.SubmitChanges();
_isDeleting = false;
}
}
}

private void dataFormEmployee_AddingNewItem(object sender, DataFormAddingNewItemEventArgs e)
{
_isInserting = true;
}

And yes you are done, you have a master/detail form to edit employees that use LinqToSQL with RIA services.