Inserting and Loading records in Silverlight using LINQ to SQL

Prerequisite

To understand this article, you should have very basic knoweldge about Silverlight and LINQ to SQL, rest this article should take care. To describe how to Insert and Load records using LINQ to SQL in Silverlight, I have taken following database table structure as an example. Remember to set AutoId as Primary key otherwise your LINQ to SQL will throw error while inserting the records into the table.

Picture 1

My sample data entry screen looks similar to below picture.

Picture 2

I have written hundreds of .NET How to's solution (ebook + source code + video) series based on real time project problems, click here to get it.

Creating a Silverlight User Control

Lets start by creating a new Silverlight project. So go to File > New Project ... and select Silverlight Application from the Template box, enter proper name (I have written SilverlightWeb) and create a new project (my solution file name and Silverlight project name will be SilverlightWeb and Silverlight hosting app name will be SilverlightWeb.Web).

You will get two projects, one with Web Project (the hosting app of Silverlight application) and other with Actual Silverlight project with two .xaml file (App.xaml and MainPage.xaml). Right click the Silverlight project and add a new Silverlight User Controld named "PersonalDetailCRUD.xaml".

Overwrite your .xaml file code with following.

<UserControl x:Class="SilverlightWeb.PersonDetailCRUD"
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"
d:DesignHeight="600" d:DesignWidth="600" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk">
<Grid x:Name="LayoutRoot" Background="White" Width="600">
<TextBlock Height="23" HorizontalAlignment="Left" Margin="21,85,0,0" Name="textBlock1" Text="First Name" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="89,81,0,0" Name="textBox1" VerticalAlignment="Top" Width="120" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="21,114,0,0" Name="textBlock2" Text="Last Name" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="89,110,0,0" Name="textBox2" VerticalAlignment="Top" Width="120" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="21,143,0,0" Name="textBlock3" Text="Age" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="89,139,0,0" Name="textBox3" VerticalAlignment="Top" Width="120" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="21,172,0,0" Name="textBlock4" Text="Address" VerticalAlignment="Top" />
<TextBox Height="23" HorizontalAlignment="Left" Margin="89,168,0,0" Name="textBox4" VerticalAlignment="Top" Width="294" />
<TextBlock Height="23" HorizontalAlignment="Left" Margin="21,201,0,0" Name="textBlock5" Text="Active?" VerticalAlignment="Top" />
<ComboBox Height="23" HorizontalAlignment="Left" Margin="89,201,0,0" Name="comboBox1" VerticalAlignment="Top" Width="120" ItemsSource="{Binding}">
<ComboBoxItem Content="True" />
<ComboBoxItem Content="False" />
</ComboBox>
 
<Button Content="Submit" Height="23" HorizontalAlignment="Left" Margin="89,248,0,0" Name="button1" VerticalAlignment="Top" Width="75" Click="button1_Click" />
 
<sdk:DataGrid Height="266" HorizontalAlignment="Left" Margin="12,0,0,31" Name="dataGrid1" VerticalAlignment="Bottom" Width="558">
</sdk:DataGrid>
 
<sdk:Label Height="28" HorizontalAlignment="Left" Margin="450,269,0,0" Name="label1" VerticalAlignment="Top" Width="120" />
<sdk:Label Height="39" HorizontalAlignment="Left" Margin="24,27,0,0" Name="label2" VerticalAlignment="Top" Width="336" Content="Add your personal details" FontSize="25" />
</Grid>
</UserControl>

As displayed in Picture 1 above, I have 4 textboxes and 1 combobox for FirstName, LastName, Age, Address and Active field respectively. Now its time to create LINQ to SQL Classes.

Creating LINQ to SQL

Right click your Web Project (Silverlight hosting application) and go to Add > New Item ... and select LINQ to SQL Classes from Data templates (I have named it as DemoDatabases.dbml). Now go to your Server Explorer window and connect your database and drag and drop the table we just created above as shown in Picture 1 above and Save the LINQ to SQL Classes.

Picture 3

Creating a WCF Service for Silverlight

As most of you know, Silvelight can't interact with ADO.NET directly so we will need to create a service. Lets create a WCF service now. Right click your Web Project (hosting app) and select Add New Item .... Select Silverlight-enabled WCF Service from Silverlight templates and name it as "DemoDatabaseService.svc". Now create two OperationContracts in this service as shown below.

[OperationContract]
public void Insert(PersonalDetail p)
{
using (DemoDatabaseDataContext db = new DemoDatabaseDataContext())
{
PersonalDetail pd = new PersonalDetail()
{
Active = p.Active,
Address = p.Address,
Age = p.Age,
FirstName = p.FirstName,
LastName = p.LastName
};
db.PersonalDetails.InsertOnSubmit(pd);
db.SubmitChanges();
} 
}
[OperationContract]
public IList<PersonalDetail> LoadAll()
{
using (DemoDatabaseDataContext db = new DemoDatabaseDataContext())
{
var records = from record in db.PersonalDetails
select record;
return records.ToList();
}
}

I am assuming that you know that in order to expose a consumable method in WCF Service, you need to set method attribute as OperationContract. As your LINQ to SQL classes are in this project, you do not need to add any other namespaces apart from System.Collections.Generic, this namespace is needed to return the IList collection object in the LoadAll() method. Its time to add the reference of web service we just created.

Adding reference of WCF Service in Silverlight Prjoect

Right click Silverlight project (in my case SilverlightWeb) and select Add Service Reference .... Make sure that you have run your application and browsed the service you have just created (http:// localhost:14524/DemoDatabaseService.svc) to ensure that you are able to retrieve its methods when you add the service reference. Enter the service Url in the Address box and click Go and then click OK button.

My Solution explorer looks similar to below picture.

Picture 4

Great stuff, now lets write code behind for the PersonalDetailsCRUD.xaml file that will access the service and do our work.

Accessing the WCF Services in Silverlight

Write following methods in the code behind of user control we created above (PersonalDetailCRUD.xaml). Remember that PersonalDetailCRUD is the constructor in below code where I am loading the default data when my page loads.

public PersonDetailCRUD()
{
InitializeComponent();
LoadData();
}
 
private void LoadData()
{
DemoServiceReference.DemoDatabaseServiceClient client = new DemoServiceReference.DemoDatabaseServiceClient();
client.LoadAllCompleted += new EventHandler<DemoServiceReference.LoadAllCompletedEventArgs>(client_LoadAllCompleted);
client.LoadAllAsync();
}
 
void client_LoadAllCompleted(object sender, DemoServiceReference.LoadAllCompletedEventArgs e)
{
label1.Content = "Loading ...";
IEnumerable<DemoServiceReference.PersonalDetail> list = e.Result as IEnumerable<DemoServiceReference.PersonalDetail>;
dataGrid1.ItemsSource = list;
label1.Content = "";
}
 
private void button1_Click(object sender, RoutedEventArgs e)
{
DemoServiceReference.DemoDatabaseServiceClient client = new DemoServiceReference.DemoDatabaseServiceClient();
client.InsertCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(client_InsertCompleted);
DemoServiceReference.PersonalDetail pd = new DemoServiceReference.PersonalDetail();
pd.Active = bool.Parse(comboBox1.SelectionBoxItem.ToString());
pd.Address = textBox4.Text;
pd.Age = short.Parse(textBox3.Text);
pd.FirstName = textBox1.Text;
pd.LastName = textBox2.Text;
 
client.InsertAsync(pd);
}
 
void client_InsertCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
{
LoadData(); // load the data again
System.Windows.Browser.HtmlPage.Window.Alert("Record inserted!"); 
}

In the LoadData() method I have instantiated the client object of the WCF service and added an EventHandler that will fire once the LoadAll method has completed its execution. In that handler method (client_LoadCompleted) I have converted the result to IEnuerable and set the ItemSource of the DataGrid.

Similarly, on the click event of the Submit button, I have accessed the WCF serivce and instantiated its client object. Instantiated the PersonDetail object that is nothing but the mapping object of corresponding database table here and set its properties. At last I have called the client.InsertAsync method by passing the PersonalDetail object.

The event hander attached with InsertCompleted event will fire once the Insert method has completed its execution and in this method, I have shown an alert message to the user.

Conclusion

Hope this article will help to undestand techniques to use to insert and load data in Silverlight applications using LINQ to SQL and WCF Services. Please feel free to respond to this article if you have any question.

If you are interested in Silverlight training, you can enroll by going to http://training.dotnetfunda.com/

Thanks and keep reading and sharing your knoweldge!