Consuming SQL Server/SQL Azure Data in Windows Phone 7.1 & Android through OData WCF Data Services & Entity Frameworks

Consuming data from SQL Server or SQL Azure database in Client Smart Devices like Windows Phone 7.1 , Android , iPhone or iPad is quite easy through the use of OData WCF Services. In previous posts , I already described how to consume data from live OData AtomPub feed showed in XML format. But, in this article I want to show how to consume SQL Server / SQL Azure data in Windows Phone & Android devices with the help of OData WCF services & entity frameworks where the service will be hosted in remote IIS & deployed SQL Server/SQL Azure database instance.

• For creating solutions, let’s first start by creating an empty asp.net web application from Visual Studio.

• Start by connecting with your SQL Server / SQL Azure database with Visual Studio .

• Next, add data in the tables of SQL Server / SQL Azure database.

• Next, Add an ADO.NET Entity Framework Model from Visual Studio to connect with SQL Server / SQL Azure database & save the connection string in web.config.

• Connect with your database with proper credentials

• Click on Next to select Tables, Views , Stored Procedures to select data.

•  Now Add a WCF Service in the project & modify the code as like this:

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 ODataSQLWP7

{

public class CustomerService : DataService<CustomersEntities>

{

// This method is called only once to initialize service-wide policies.

public static void InitializeService(DataServiceConfiguration config)

{

// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.

// Examples:

// config.SetEntitySetAccessRule(“MyEntityset”, EntitySetRights.AllRead);

// config.SetServiceOperationAccessRule(“MyServiceOperation”, ServiceOperationRights.All);

config.SetEntitySetAccessRule( “CustomerInfoes”, EntitySetRights.All);

config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;

}

}

}

•  Now check the service in browser & it looks like this:

• If you would check the URL as http://localhost/ODataSQLWP7/CustomerService.svc/  as to be modified as http://localhost/ODataSQLWP7/CustomerService.svc/CustomerInfoes , you will get an 500 internal error.

• Reason of the error: It’s due to the permission set not configured for IIS to deploy the SQL Server/SQL Azure database data. For SQL Azure database while creating the database in Windows Azure portal , you need to set the firewall settings definitely to 0.0.0.0 to 255.255.255.255 in order to deploy the SQL Azure database in IIS.
• Now Right-Click on the project , select Property -> Package/Publish Web tab: 

• Remember to check the settings: Include all databases in  Package/Publish SQL tab.
•  Next , Click on Package/Publish SQL options: Click on Import from Web.config to generate your SQL Server/SQL Azure database connection strings.
• Click on Connection String for destination database & add your destination database which will be hosted in IIS.
• Click on the checkbox under Source database information: Pull data/or schema from existing database.
• Under database scripting options select Schema only (if you would like generate schema only) or Schema & Data for generating schema & data deployement both.

• Now , add user to login to the deployed database from SQL Server Management Studio by right clicking on Security -> New Login 

• Add user for login as ODataUser from Windows Add Users – Groups utility , alternatively , you can add user from right-clicking My Computer -> Manage-> Users & Groups -> User -> Add User.

• Now under the User Mapping tab add the database that you would like to deploy & click on db_datareader & db_datawriter. Click OK.

• Deploy the Web Service in IIS from Web Deploy wizard in Visual Studio.

• Next, open the Application Pool in IIS manager which holds the deployed service & click on advanced settings : Select Process Model as Application Pool Idenity -> Under Custom account Set the account credentials which you have set under the SQL Server logins section.

• After this settings, now access the Customer Info  with the Service URL settings & it will show the entire RSS feed in AtomPub/ JSON format from the SQL Server / SQL Azure database.

• It means OData WCF service now able to access the SQL Server/ SQL Azure data in AtomPub format hosted in remote IIS. Next , It’s time to create the Client applications:
• First take a Windows Phone Panorama Project & modify the MainPage.XAML as like this:

<controls:PanoramaItem Header=”first item”>

<!–Double line list with text wrapping–>

<ListBox x:Name=”lst” Margin=”0,0,-12,0″ ItemsSource=”{Binding}”>

<ListBox.ItemTemplate>

<DataTemplate>

<StackPanel Margin=”0,0,0,17″ Width=”432″>

<TextBlock Text=”{Binding FirstName}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextExtraLargeStyle}” />

<TextBlock Text=”{Binding LastName}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextExtraLargeStyle}” />

<TextBlock Text=”{Binding Address}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextSubtleStyle}” />

<TextBlock Text=”{Binding City}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextSubtleStyle}” />

<TextBlock Text=”{Binding State}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextSubtleStyle}” />

<TextBlock Text=”{Binding Zip}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextSubtleStyle}” />

</StackPanel>

</DataTemplate>

</ListBox.ItemTemplate>

</ListBox>

</controls:PanoramaItem>

• Modify 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;

using Microsoft.Phone.Controls;

using WP7PanoramaOData.CustomersModel;

using System.Data.Services.Client;

namespace WP7PanoramaOData

{

public partial classMainPage : PhoneApplicationPage

{

// Constructor

public MainPage()

{

InitializeComponent();

// Set the data context of the listbox control to the sample data

DataContext = App.ViewModel;

this.Loaded += newRoutedEventHandler(MainPage_Loaded);

}

//   Load data for the ViewModel Items

private void MainPage_Loaded(object sender, RoutedEventArgs e)

{

var ctx = newCustomersEntities(newUri(“http://10.12.1.223/ODataSQLWP7/CustomerService.svc/&#8221;));

var coll = new System.Data.Services.Client.DataServiceCollection<CustomerInfo>(ctx);

lst.ItemsSource = coll;

coll.LoadCompleted += new EventHandler<LoadCompletedEventArgs>(coll_LoadCompleted);

var qry = “/CustomerInfoes”;

coll.LoadAsync(new Uri(qry, UriKind.Relative));

}

void coll_LoadCompleted(object sender, LoadCompletedEventArgs e)

{

if (e.Error != null)

{

MessageBox.Show(e.Error.Message);

}

}

• Remember to add Service Reference to your Windows Phone application by referencing the service hosted in IIS:

• Check the View in Windows Phone 7 :

• Adding View for Android SmartPhones consuming data from SQL Server / SQL Azure database through OData WCF Data Services:

package com.example.ODataAndroid;

import java.util.ArrayList;

import java.util.List;

import android.app.Activity;

import android.os.Bundle;

import org.odata4j.consumer.ODataConsumer;

import org.odata4j.core.OEntity;

import android.app.ListActivity;

import android.widget.ArrayAdapter;

public class ODataSQLAndroidActivity extends ListActivity {

/** Called when the activity is first created. */

@Override

public void  onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

        setListAdapter(new ArrayAdapter<String>(this,  android.R.layout.simple_list_item_1, GetExpenseReports()));

        getListView().setTextFilterEnabled(true);

    }

    ArrayList<String> GetExpenseReports()

    {

    ArrayList<String> listUI =new ArrayList<String>();

    ODataConsumer c = ODataConsumer.create( http://10.12.1.223/ODataSQLWP7/CustomerService.svc/);

    List<OEntity> listExpenses = c.getEntities( “CustomerInfoes”).execute().toList();

for(OEntity expense:listExpenses) {

    listUI.add(expense.getProperty( “ID”).getValue().toString()

    + “:” + expense.getProperty(“FirstName”).getValue().toString()

    + expense.getProperty(“LastName”).getValue().toString()

    + “\n”+ expense.getProperty(“Address”).getValue().toString()

    + “\n” + expense.getProperty(“City”).getValue().toString()

    + “\n”+ expense.getProperty(“State”).getValue().toString()

    );

    }

return  listUI;

    }

    }

• Check out the SQL Server / SQL Azure database data in Android device:

Consuming OData WCF REST service from Windows Phone 7 Panorama Application

Consuming an OData (Open Data Protocol) in Client devices , Client services are quite easy. Open Data protocol , is a web protocol for querying and updating data and it was born of the need to break down data silos and increase their shared value. This allows data silos to interoperate between producers such as SQL Server, SharePoint servers, Cloud Storage Services, and consumers, for example Java, PHP, Silverlight, IIS, ASP.NET, AJAX.

OData incorporates with JSON, AtomPub, RSS to provide access to the  information from a range of applications, services, relational databases, file systems, content management systems (CMS), traditional websites.

Supported Platforms for OData Services:

1. Microsoft Visual Studio 2008 SP1
2. Microsoft Visual Studio 2010
3. Microsoft SQL Server 2008 R2
4. Microsoft Sharepoint 2010
5. Microsoft Windows Azure Storage(Blobs, Tables, Queues)
6. Microsoft SQL Azure
7. Microsoft Office Excel 2010 PowerPivot

• Lets create an OData WCF REST Service that for this purpose lets create an empty web application from Visual Studio

• Lets add a new WCF Service in the application & named it as Service.SVC

• Next , add the following Code to the service & check the resulting feed in browser.

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 ODataSample1

{

public class Service : DataService<SampleDataSource>

{

// This method is called only once to initialize service-wide policies.

public static void InitializeService(DataServiceConfiguration config)

{

// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.

// Examples:

// config.SetEntitySetAccessRule(“MyEntityset”, EntitySetRights.AllRead);

// config.SetServiceOperationAccessRule(“MyServiceOperation”, ServiceOperationRights.All);

config.SetEntitySetAccessRule(“*”, EntitySetRights.All);

config.SetServiceOperationAccessRule(“*”, ServiceOperationRights.All);

config.MaxResultsPerCollection = 100;

config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;

}

}

[ EntityPropertyMappingAttribute(“CustomerName”, SyndicationItemProperty.Title, SyndicationTextContentKind.Plaintext, true)]

[DataServiceKey(“CustomerID”)]

public class CustomerRecord

{

public int CustomerID { get; set; }

public string CustomerName { get; set; }

public string CustomerEmail { get; set; }

public string CustomerNotes { get; set; }

public DateTime CustomerLastContact { get; set; }

}

public class SampleDataSource

{

private readonly List<CustomerRecord> _sampleCustomerRecordList;

public SampleDataSource()

{

_sampleCustomerRecordList = newList<CustomerRecord>();

for (int i = 0; i < 100; i++)

{

CustomerRecord CR = newCustomerRecord();

CR.CustomerID = i;

CR.CustomerName =string.Format(“FirstName{0} LastName{1}”, i.ToString(), i.ToString());

CR.CustomerEmail =string.Format(“Email{0}@{1}.com”, i.ToString(), i.ToString());

CR.CustomerNotes =string.Format(“Notes{0}.Notes{1}”, i.ToString(), i.ToString());

CR.CustomerLastContact =DateTime.Now.AddDays(-10000).AddHours(i);

_sampleCustomerRecordList.Add(CR);

}

}

public IQueryable<CustomerRecord> SampleCustomerData

{

get

{

return _sampleCustomerRecordList.AsQueryable();

}

}

}

}

• Now check the Service status in Linqpad (http://www.linqpad.com) by adding the WCF service endpoint to the database/service endpoint connection.

• Lets check the status of the feed by entering query (<atom:title>)(e.g : SampleCustomerData  for this demo)in the browser

•  Check after entering query in the URL of the feed

• Service endpoint shows successful OData feed , next add a Windows Phone Panorama Application with the solution to implement a smartClient to the OData REST application.

• Now check the OData Codeplex site:http://odata.codeplex.com/releases/view/54698  & Download ODataClient_BinariesAndCodeGenToolForWinPhone.zip  , extract it in windows explorer & run the following command in the command prompt by browsing the corresponding directory.

datasvcutil /uri:http://localhost:8554/Service.svc/ /out:.\ServiceModel.cs /Version:2.0 /DataServiceCollection

<!–Panorama item one–>

<controls:PanoramaItem Header=”first item”>

<ListBox x:Name=”lst” Margin=”0,0,-12,0″ ItemsSource=”{Binding}”>

<ListBox.ItemTemplate>

<DataTemplate>

<StackPanel Margin=”0,0,0,17″ Width=”432″>

<TextBlock Text=”{Binding CustomerID}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextExtraLargeStyle}” />

<TextBlock Text=”{Binding CustomerName}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextExtraLargeStyle}” />

<TextBlock Text=”{Binding CustomerEmail}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextExtraLargeStyle}” />

<TextBlock Text=”{Binding CustomerNotes}” TextWrapping=”Wrap” Margin=”12,-6,12,0″ Style=”{StaticResource PhoneTextExtraLargeStyle}” />

</StackPanel>

</DataTemplate>

</ListBox.ItemTemplate>

</ListBox>

</controls:PanoramaItem>

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;

using Microsoft.Phone.Controls;

using ODataWP7Panorama.CustomersModel;

using System.Data.Services.Client;

namespace ODataWP7Panorama

{

public partial classMainPage : PhoneApplicationPage

{

public  SampleDataSource ctx = new SampleDataSource(new Uri(“http://localhost:8554/Service.svc/&#8221;, UriKind.Absolute));

// Constructor

public MainPage()

{

InitializeComponent();

this.Loaded += newRoutedEventHandler(MainPage_Loaded);

// Set the data context of the listbox control to the sample data

}

// Load data for the ViewModel Items

private void MainPage_Loaded(object sender, RoutedEventArgs e)

{

var ctx = newSampleDataSource(newUri(http://localhost:8554/Service.svc/));

var coll = new  DataServiceCollection<CustomerRecord>(ctx);

lst.ItemsSource = coll;

coll.LoadCompleted +=newEventHandler<LoadCompletedEventArgs>(coll_LoadCompleted);

var qry = “/SampleCustomerData”;

coll.LoadAsync(newUri(qry, UriKind.Relative));

}

void coll_LoadCompleted(object sender, LoadCompletedEventArgs e)

{

if (e.Error != null)

{

MessageBox.Show(e.Error.Message);

}

}

}

}

• Now , lets the OData REST Service in Windows Phone 7.1 Client :

Windows Azure Claims based Identity & Access Control ebook available

Most enterprise applications include a certain amount of logic that supports Identity -related features. Applications that can’t rely on Integrated Windows Authentication tend to have more of this than appeared to do. For example : web based applications that store usernames & passwords must handle password reset, lockout and other issues. Enterprise facing applications that use integrated Windows Authentication can rely on the domain controller.

• Claims based identity allows you to factor out the authentication logic from individual applications instead of application determining who the user is, it receives claims that identifies claims that identify the user.
• Microsoft annouced the second release of Claims Based Idenity & Access Control Book from Patterns & Practices Team.

Download from here: http://www.microsoft.com/download/en/details.aspx?id=28362

• For better explanation about SAML (Security Assertion Markup Language) Token 2.0, Simple Web Token (SWT) , Relying Party, Requesting Security Service (RST), Secure Token Service(STS) , ADFS (Active Directory Federation Server 2.0 Single Sign On ) issues with asp.net authentication & far more illustration about Windows Identity foundation 4.0 (WIF) , Identity Runtime, Web Resource Authorization Protocol 2.0 (WRAP) covered in this book.

• Concise explanation about Claims based identity in Windows Azure Web , Access Control Service(ACS), Service Bus Queues, Claims based identity with Sharepoint 2010 integration, Federated authentication with Sharepoint with Access Control Service(ACS V2)