ARTICLE

Silverlight 4 Linq to Sql and RIA sevice for CRUD operations

Posted by Nipun Tomar Articles | Silverlight with C# April 26, 2010
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.
Reader Level:

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"

Silverlight4LinqToSqlRiaSer.gif

The resulting screen will be

Silverlight4LinqToSqlRia2.gif

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

Silverlight4LinqToSqlRia3.gif

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

Silverlight4LinqToSqlRia4.gif

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
}}"/>

Silverlight4LinqToSqlRia5.gif

Add new Linq to Sql class

Silverlight4LinqToSqlRia6.gif

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

Silverlight4LinqToSqlRia7.gif

Add new domain service

Silverlight4LinqToSqlRia8.gif

And rebuild the solution

Silverlight4LinqToSqlRia9.gif

Silverlight4LinqToSqlRia10.gif

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

Silverlight4LinqToSqlRia11.gif

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

Silverlight4LinqToSqlRia12.gif

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

Silverlight4LinqToSqlRia13.gif

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; }

        }
...

Silverlight4LinqToSqlRia14.gif

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

Silverlight4LinqToSqlRia15.gif

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.

COMMENT USING
Employers - Post Free Jobs