Wednesday, August 19, 2009

Silverlight and Database without LINQ and EDM

 

Hi All,

I am writing this Blog because of one reason. Many people have a question about accessing the data in Silverlight applications without LINQ, LINQ-To-SQL Or EDM (Entity Data Model).

The answer for this question is “Yes, You can!!”. So, to achieve this, there can be multiple ways. I have achieved this by my way.

So, let’s start looking at the Steps-

1) Create a Silverlight project using VS 2008/ VS 2010 and name it as “SLWithoutLINQ”.

2) Design the Page.xaml as per your specifications. I have taken only a single button which will give a call to WCF Service method and that method will return a collection of class called “Students”.

Page.xaml-

<UserControl x:Class="SL3WithoutLINQ.Page"
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="300" d:DesignWidth="400">

      <Grid x:Name="LayoutRoot" Background="Black">
           <Button x:Name="btnShowResult" Content="Show Result" Click="btnShowResult_Click" Height="100" Width="200"/>
      </Grid>
</UserControl>

3) My database description is as below-

Database Name – Sample.

Table Name – Test.

Table structure -

Field Name Data Type
InstID Int
StudID Int
Subject Varchar(50)
Result Varchar(50)
FailedReason Varchar(50)

4) Now, let’s add a class in our web site or web application with name “Students” as shown below-

public class Students
{
      public int InstanceID { get; set; }
      public int StudentID { get; set; }
      public string Subject { get; set; }
      public string Result { get; set; }
      public string FailedReason { get; set; }
}
5) Now, let’s add a simple WCF Service by the name “FetchResults” and write a code as below(Once you add a WCF Service, you will get an Interface and a class which implements that Interface)-
Interface-
[ServiceContract]
public interface IFetchResults
{
[OperationContract]
List<Students> FetchStudentResult();
}
 
Class-
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.SqlClient;

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "FetchResults" in code, svc and config file together.
public class FetchResults : IFetchResults
{

      public List<Students> FetchStudentResult()
      {
          SqlConnection CN = new SqlConnection(@"Server=.\SQLExpress;Database=Sample;Integrated Security=true");
          SqlCommand CMD = new SqlCommand("Select * from Test",CN);
          CN.Open();

          SqlDataReader DR = CMD.ExecuteReader();
          List<Students> result=new List<Students>();
          while (DR.Read())
          {
               result.Add(new Students() {StudentID=int.Parse(DR[1].ToString()),InstanceID=int.Parse(DR[0].ToString()),Subject=DR[2].ToString(),Result=DR[3].ToString(),FailedReason=DR[4].ToString() });
          }
return result;
      }
}
 
6) Now once you finish your service code, Now the most important thing is change the Web.Config file.
So, When you add a WCF Service, it by default gives binding as wsHttpBinding. Silverlight supports only basicHttpBinding.
So, change it as below-
 
<endpoint address="" binding="basicHttpBinding" contract="IFetchResults">
       <identity>
             <dns value="localhost"/>
       </identity>
</endpoint>
7) The method “FetchStudentResult” returns a collection of “Students” class. Now let’s move to out next step.

8) Now add a WCF Service reference to your Silverlight project. and create a proxy of a class “FetchResultsClient” as shown below-

List<StudentProxy.Students> studCollection;
private void btnShowResult_Click(object sender, RoutedEventArgs e)
{
      StudentProxy.FetchResultsClient proxy = new SL3WithoutLINQ.StudentProxy.FetchResultsClient();
      proxy.FetchStudentResultCompleted += new  EventHandler<SL3WithoutLINQ.StudentProxy.FetchStudentResultCompletedEventArgs>(proxy_FetchStudentResultCompleted);

      proxy.FetchStudentResultAsync();

}
 
void proxy_FetchStudentResultCompleted(object sender, SL3WithoutLINQ.StudentProxy.FetchStudentResultCompletedEventArgs e)
{
      studCollection = e.Result.ToList();
}
 
9) Now put a break point on “proxy_FetchStudentResultCompleted” method. and run your application. Click the button “Show Result”. and when the break point is invoked, you will see the no. of instances in the studCollection as shown below-

 

DebugView

In the above result, I have six rows return from the table called “Test”. Now you can use this collection as a data context for any control !!
10) So, That’s all !! Enjoy.

1 comment: