Accessing data in Silverlight is not direct, as it is a client application we
have to use the service to provide data and interact with database.
LINQ to SQL is an ORM (stands for Object Relational Mapper/Mapping), which
provides a data access layer for the application.
LINQ to SQL classes that are mapped to database tables and views are called
entity classes. The entity class maps to a record, whereas the individual
properties of an entity class map to the individual columns that make up a
record. Create entity classes that are based on database tables or views by
dragging tables or views from Server Explorer/Database Explorer onto the Object
Relational Designer (O/R Designer).
The O/R Designer generates the classes and applies the specific LINQ to SQL
attributes to enable LINQ to SQL functionality (the data communication and
editing capabilities of the DataContext). Reference:
MSDN
The following example will combine the following skills:
-
Using LINQ to query and
retrieve data that our Silverlight Application can use
-
Connecting to a WCF Web
Service.
-
Using DataGrid control to
display data.
To start, create a Silverlight 4
Application in Visual Studio 2010 .
Figure 1
As you see in the figure above, Silverlight 4 is part of .NET Framework 4.0,
Visual Studio 2010 supports multi target support such as you can build
Silverlight 3 applications in Visual Studio 2010.
Figure 2
After creating the Silverlight Application in .NET Framework 4 you will find a
dialog asking for Silverlight Project details.
Figure 3
As you see in the figure above, the project template automatically creates a
sample ASP.NET Web Application Project with the default name in the format <SolutionName>
Web.
In the Silverlight Version dropdown we have two options such as Silverlight 4.0
and Silverlight 3.0.
You would see a checkbox field saying "Enable .NET RIA Services", this will
enable RIA Services features in the Silverlight project. But now we will not
select this. As we have a section following completely for RIA Services.
The following solution structure is created.
Figure 4
In the above figure, the first project is the Silverlight 4 project and the
second project is the ASP.NET Web project.
By default it creates two pages such as one .aspx page and one .html page to
host the Silverlight plugin. ClientBin folder is created to keep the XAP file
inside it.
Now let's add a LINQ to SQL file into Web project.
Figure 5
In the New Item dialog box select the Data and from the templates available
select LINQ to SQL classes. Give a name for your file (<Filename>.dbml) and then
add it to the Web project.
After creating the LINQ to SQL Data Classes you will see the designer opened for
you, initially it would be blank as we have not added any connection.
Figure 6
As you see in the above figure above, the designer is empty, ; click on Server
Explorer to connect to a Database in SQL Server.
Figure 7
As you see in the figure above, we have no data connections available, let's add
one. Right click on Data Connections and select Add Connection from the
displayed menu.
Figure 8
After you select Add Connection, the following "Add Connection" dialog box will
open. Initially all the fields are empty.
Figure 9
Let's add the Server name (<SystemName>\SQLEXPRESS), and click on Refresh. This
will refresh the Database list based on the Server name provided.
Figure 10
Figure 11
Select your database from the dropdown "Select or enter a database name:", now
we can test the connection by clicking the "Test Connection" button. Press OK to
add the connection and proceed further.
Figure 12
After adding the connection to the project, you can explore the database. Such
as Tables, Views, Stored Procedures, etc… The Web.config file now has the
information about the connection string.
<configuration>
<connectionStrings>
<add
name="EmployeeDBConnectionString"
connectionString="Data
Source=B314LTRV\SQLEXPRESS;Initial Catalog=EmployeeDB;Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
Listing
To Adding a table into LINQ to SQL is very easy, just drag the table and drop on
the designer of LINQ to SQL, it will be added.
Figure 13
The preceding figure displays the entity (table) added to LINQ to SQL. As per
the information given for the table, it is replicated as it is. Now the good
thing is we can change the names by giving some good names.
Figure 14
As you see in the preceding figure, we have changed the default names of the
Entity and it's properties. Although we can use the default property without any
issues; for better understanding we changed the names.
We have successfully connected to our database, now we need a service that will
communicate with the Silverlight client application for the database operations.
Let's add a WCF Service in the Web project.
Figure 15
As you see in the preceding figure, we have selected Silverlight from the Add
New Item dialog and then select "Silverlight – enabled WCF Service". Give an
appropriate name and click on Add to add the service.
After the service is created it will open the class file (<Servicename>.svc.cs)
associated with it. This will change the Web.config file for the information
about the service.
After you have added the service you will see the following error message saying
"Failed to generate code for the service reference 'ServiceReference1'".
Figure 16
To generate the code automatically we need to configure the Service Reference.
Figure 17
After selecting Configure Service Reference you will see a dialog for Service
Reference Settings. Uncheck "Reuse types in referenced assemblies"
Figure 18
Now update the Service Reference and you will see no errors and the
ServiceReference.ClientConfig file is successfully auto generated.
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior
name="">
<serviceMetadata
httpGetEnabled="true"/>
<serviceDebug
includeExceptionDetailInFaults="false"/>
</behavior>
</serviceBehaviors>
</behaviors>
<bindings>
<customBinding>
<binding
name="DataAccessSampleLINQ.Web.EmpService3.customBinding0">
<binaryMessageEncoding/>
<httpTransport/>
</binding>
</customBinding>
</bindings>
<serviceHostingEnvironment
aspNetCompatibilityEnabled="true"/>
<services>
<service
name="DataAccessSampleLINQ.Web.EmpService3">
<endpoint
address=""
binding="customBinding"
bindingConfiguration="DataAccessSampleLINQ.Web.EmpService3.customBinding0"
contract="DataAccessSampleLINQ.Web.EmpService3"/>
<endpoint
address="mex"
binding="mexHttpBinding"
contract="IMetadataExchange"/>
</service>
</services>
</system.serviceModel>
Listing
Delete the default code inside the class and add some methods as [Operation
Contract].
Let's have a method as GetAllEmployees(), which will give us all employee
details.
[OperationContract]
List<Employee>
GetAllEmployees()
{
EmployeeDataClassesDataContext
context = new EmployeeDataClassesDataContext();
var
result = from emp in
context.Employees
select emp;
return
result.ToList();
}
Listing
As you see in the preceding listing, we have a method GetEmployees(), which
returns a List of type Employee (As we created the Employee entity in LINQ to
SQL).
We have used the EmployeeDataClassesDataContex as the data context to create a
new instance of the LINQ to SQL data classes.
By using LINQ query we can query entity or entities.
Now we will add the service reference to the Silverlight project. Right click on
the Reference and select Add Service Reference from the Context menu displayed.
Figure 19
It will pop up a dialog box for "Add Service Reference". Click on Discover to
identify running services.
Figure 20
As you see in preceding figure, we have discovered the Service and given a
Namespace to it, ; we will keep the default name here.
Figure 21
As soon as you add the service reference a ServiceReferences.ClientConfig file
will be generated. See the figure above.
The ServiceReferences.ClientConfig file is an XML formatted file that contains
the information and settings for the ServiceReference to be used at client side.
<configuration>
<system.serviceModel>
<bindings>
<customBinding>
<binding
name="CustomBinding_EmployeeService">
<binaryMessageEncoding
/>
<httpTransport
maxReceivedMessageSize="2147483647"
maxBufferSize="2147483647"
/>
</binding>
</customBinding>
</bindings>
<client>
<endpoint
address=http://localhost:62782/EmployeeService.svc
binding="customBinding"
bindingConfiguration="CustomBinding_EmployeeService
" contract="ServiceReference1.EmployeeService
"
name="CustomBinding_EmployeeService
" />
</client>
</system.serviceModel>
</configuration>
Listing
Now we will add a DataGrid to the Silverlight page to display data. Drag and
drop DataGrid from the Toolbox to the design surface.
<data:DataGrid
x:Name="dgEmployee"
AutoGenerateColumns="True"
Height="300"
HorizontalAlignment="Left"
VerticalAlignment="Top"
Width="400"
/>
Listing
In above listing above we have a DataGrid with the above properties.
Let's use the ServiceReference to get the Employee details.
EmployeeServiceClient
service = new EmployeeServiceClient();
Now we will add the Completed event handler, where we will bind the service
returned object to DataGrid.
Figure 22
To perform asynchronously we have to use the method.
EmployeeServiceClient
service = new EmployeeServiceClient();
service.GetAllEmployeesCompleted += new
EventHandler<GetAllEmployeesCompletedEventArgs>(service_GetAllEmployeesCompleted);
service.GetAllEmployeesAsync();
Listing
In the event handler we will bind the Result to DataGrid.
void
service_GetAllEmployeesCompleted(object sender,
GetAllEmployeesCompletedEventArgs e)
{
dgEmployee.ItemsSource =
e.Result;
}
Listing
Now if we run the application we will see the data returned from the service
bound to the DataGrid.
Figure 23
As you see in the figure above, the columns are not structured; rather it took
the columns in alphabetic order. To display it according to our choice we need
to customize the DataGrid Columns.
<data:DataGrid
x:Name="dgEmployee"
AutoGenerateColumns="False"
Height="300"
HorizontalAlignment="Left"
VerticalAlignment="Top">
<data:DataGrid.Columns>
<data:DataGridTextColumn
Header="ID"
Binding="{Binding
ID}"/>
<data:DataGridTextColumn
Header="First
Name"
Binding="{Binding
FirstName}"/>
<data:DataGridTextColumn
Header="Last
Name"
Binding="{Binding
LastName}"/>
<data:DataGridTextColumn
Header="Email
ID"
Binding="{Binding
EmailID}"
Width="*"/>
<data:DataGridTextColumn
Header="Contact"
Binding="{Binding
Contact}"/>
</data:DataGrid.Columns>
</data:DataGrid>
Listing
Now the DataGrid Columns are set to static columns by setting
AutoGenerateColumns as False. Let's run the application and we will see the
proper output as follows:
Figure 24