Stored Procedure in WCF Data Service

In this article we will explore, how could we use Stored Procedure with WCF Data Service?

You can read Introduction to WCF Data service and ODATA here

To use Stored Procedure, at time of creation of Data Model, select Stored Procedure as part of Data Model.

clip_image001

Now open EDMX file and right click on that. Select Add and then select Function Import.

clip_image002

Popup window will be open.

1. Give Function import name

2. Choose Stored procedure from drop down

3. Choose the Entity type Stored Procedure is returning.

clip_image003

Stored Procedure we are selecting is GetStudentGrades and it is returning one or more entities of StudentGrade.

After clicking OK you can see columns are mapped

clip_image005

You can see in model browser that GetStudentGrades has been listed in Function Imports section and it is having one input parameter StudentD.

clip_image006

We can see now that Stored Procedure has been mapped to Entity model and can be exposed as WCF Data Service.

Next step we need to create a function in DataService class. This function will return list of entities.Client will call this function to execute Stored Procedure. clip_image008

In above function,

1. Creating instance of Entity class.

2. Calling the Function on this object. We need to search function import name to call on object of entity class.

3. Function is having one input parameter to pass as parameter to execute stored Procedure.

For Reference full source code of service class is as below,

WcfDataService.svc.cs


using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Data.Services.Common;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;

namespace WebApplication6
{
 public class WcfDataService1 : DataService<SchoolEntities>
 {

 public static void InitializeService(DataServiceConfiguration config)
 {

 config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
 config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
 config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
 }

[WebGet]
 public List<StudentGrade> GetStudentGrade(string studentId)
 {
 SchoolEntities entities = new SchoolEntities();
 return entities.GetStudentGrades(Convert.ToInt32(studentId)).ToList();
 }
 }
}

To call the Stored Procedure in browser

http://localhost:14469/WcfDataService1.svc/GetStudentGrade?studentId=’2

Now to call Stored Procedure at Client side

clip_image010

We need to pass the exact URL of the function in service class executing Stored Procedure.

For Reference full source code at client is as below,

Program.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Services;
using System.Data.Services.Client;
using ConsoleApplication1.ServiceReference1;

namespace ConsoleApplication1
{
 class Program
 {
 static void Main(string[] args)
 {

DataServiceContext context = new DataServiceContext(new Uri("http://localhost:14469/WcfDataService1.svc/"));

 List<StudentGrade> lstStudentsGrade = context.Execute<StudentGrade>
 (new Uri("http://localhost:14469/WcfDataService1.svc/GetStudentGrade?studentId='2'")).ToList();

 foreach (var r in lstStudentsGrade)
 {
 Console.WriteLine("Student :" + r.StudentID.ToString() +" Grade : " +  r.Grade);

}
 Console.ReadKey(true);

}
 }
}

Output we are expecting is as below,

clip_image012

We saw in this article, how we could call a Stored Procedure using WCF Data Service.

What is XAP file in Silverlight?

Target Audience: Beginners in Silverlight

When you start learning Silverlight and create your first Silverlight application, one thing you will notice XAP file along with HTML and ASPX files. First time after running your Silverlight application you can see XAP file in client bin folder. Before running of Silverlight application, you will not find XAP file in client bin folder.

clip_image001

Usually below steps followed while running a Silverlight application.

1. Create a Silverlight Application

2. Compile it to IL.

3. Packaged it to XAP file.

4. XAP file hosted in web server

5. XAP file get downloaded to browser.

Flow diagram of a Silverlight application from creation to running at client browser can depicted as below,

clip_image003

If you notice whenever we are creating a new Silverlight application, you usually host Silverlight application in a new Web Site. This is the web site which will host the XAP file.

clip_image004

Every time you run Silverlight application, automatically visual studio creates the XAP file and deploy to the web site.

XAP file is basic deployment in Silverlight.

clip_image005

XAP file is a ZIP file. It contains many files needed to run Silverlight application at the client browser. There are many files contained in a XAP file.

clip_image006

Just to have a look what all files a XAP file contains,

1. Right click XAP file

2. Rename the extension from XAP to ZIP

3. Un Zip the ZIP file. You will find below files inside XAP file

There is one more way to easily extract the entire XAP file. Follow the steps as below,

1. Open Command prompt as administrator

2. Type command as below

clip_image007

3. Navigate to folder containing XAP file. On right click of XAP file you can find Extract All option.

clip_image008

After extracting XAP file on unzipping the Zip file, you can see there are at least two files

1. Silverlight application dll

2. AppManifest.Xaml file.

clip_image010

AppManifest.xaml file contains key information like,

1. Version of Silverlight application is targeting

2. Entry point of the application

3. Name of the Silverlight application to be loaded

If you open AppManifest.xaml file, you will see above information are provided there. There is only one assembly of Silverlight application is listed in Deplyment.Parts section. If you have referred any other assembly in Silverlight application then information of that assembly will also be listed in Deployment.Parts section.

clip_image012

Algorithm how Silverlight application loads at client browser

Step1

HTML files gets loaded at client browser

Step2

Browser reads the HTML and finds the object tag and XAP file associated with object tag.

clip_image014

Step3

Browser downloads the XAP file using Silverlight plugin installed at browser.

Step4

Silverlight plugin reads the AppManifest.Xaml file and finds Entry Point of the application and minimum version of Silverlight application is targeting.

Step5

Application gets executed at client browser.

Size of the XAP file is vital factor in performance of Silverlight application.

clip_image015

For better performance we should try to have size of XAP file as low as possible. All the resource files, images and Videos should be downloaded asynchronously at client browser. We should put images and media files on the server.

AutoCompleteBox in Silverlight 4.0

Target Audience: Beginners

AutoCompleteBox control is part of Silverlight 4.0 SDK. It allows us to have a Google style text box.

It works like below,

clip_image001[4]

AutoCompleteBox will look for suggestion in IEnumerable list.

Create a DataSource to find the suggestion

Very first let us create a Country Class. This class will serve as Entity class.

clip_image003

Create a function and this function will return list of country,

clip_image005

We will set above function as DataContext() of AutoCompleteBox.

Drag and Drop AutoCompleteBox on XAML

1. From the tool box select AutoCompleteBox and drop on the XAML page

clip_image006[4]

2. Set the Binding, ValueMemberPath and FilterMode.

clip_image008

3. Set the DataTemplate and bind the TextBlock where user will type the text.

clip_image009

Set the DataContext

We need to set the DataContext of AutoCompleteBox in code behind

clip_image010

For reference,

MainPage.Xaml

<Grid x:Name="LayoutRoot" Background="White">
 <sdk:AutoCompleteBox x:Name="atcTextBox" ItemsSource="{Binding}"
 ValueMemberPath="CountryName"
 FilterMode="Contains"
 IsTextCompletionEnabled="True"
 Height="30" Margin="62,22,54,248">
 <sdk:AutoCompleteBox.ItemTemplate>
 <DataTemplate>
 <TextBlock Text="{Binding CountryName}" />
 </DataTemplate>
 </sdk:AutoCompleteBox.ItemTemplate>

 </sdk:AutoCompleteBox>

</Grid>

MainPage.Xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;

namespace SilverlightApplication4
{
 public partial class MainPage : UserControl
 {
 public MainPage()
 {
 InitializeComponent();
 atcTextBox.DataContext = GetCountry();

}

List<Country> GetCountry()
 {
 List<Country> lstCountry = new List<Country>();
 lstCountry.Add(new Country { CountryName = "India" });
 lstCountry.Add(new Country { CountryName = "USA" });
 lstCountry.Add(new Country { CountryName = "Australia" });
 lstCountry.Add(new Country { CountryName = "Germany" });
 lstCountry.Add(new Country { CountryName = "England" });
 lstCountry.Add(new Country { CountryName = "Brazil" });
 lstCountry.Add(new Country { CountryName = "China" });
 lstCountry.Add(new Country { CountryName = "Japan" });
 lstCountry.Add(new Country { CountryName = "Denmark" });
 lstCountry.Add(new Country { CountryName = "France" });
 lstCountry.Add(new Country { CountryName = "Germany" });
 return lstCountry;
 }
 }
}

We can set the source from WCF service, Database, cloud anywhere.