Create, Update & Delete(CUD) Operations of SQL Server / SQL Azure Data through OData WCF Services from Windows Phone 7.1 Mango

In previous posts, I discussed about the consumption of SQL Server/ SQL Azure data in Windows Phone 7.1 Mango device through the use of OData WCF services. OData Services  support not only HTTP GET operations but also supports for HTTP POST, PUT & DELETE by which we can perform CRUD (Create, Read, Update, Delete) operations from SQL Server/SQL Azure/ Oracle / SharePoint 2010 / SSRS 2008 / SAP Netweaver BI data etc.

• In this post, Let’s discuss how to add new data by HTTP POST through XML ATOMPUB format from Windows Phone 7.1 client to the SQL Server database.
• Created a new .XAML page called ‘Add.xaml’ which will capture data from Phone UI & save it to the SQL Server / SQL Azure database.

• Lets check the Source Code for the Create operation:

<phone:PhoneApplicationPage

x:Class=”WP7PanoramaOData.Add”

xmlns=”http://schemas.microsoft.com/winfx/2006/xaml/presentation&#8221;

xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml&#8221;

xmlns:phone=”clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone”

xmlns:shell=”clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone”

xmlns:d=”http://schemas.microsoft.com/expression/blend/2008&#8243;

xmlns:mc=”http://schemas.openxmlformats.org/markup-compatibility/2006&#8243;

FontFamily=”{StaticResource PhoneFontFamilyNormal}”

FontSize=”{StaticResource PhoneFontSizeNormal}”

Foreground=”{StaticResource PhoneForegroundBrush}”

SupportedOrientations=”PortraitOrLandscape” Orientation=”Portrait”

mc:Ignorable=”d” d:DesignHeight=”768″ d:DesignWidth=”480″

shell:SystemTray.IsVisible=”True”>

<!–LayoutRoot is the root grid where all page content is placed–>

<Grid x:Name=”LayoutRoot”>

<Grid.Background>

<ImageBrush ImageSource=”images/Back.png”/>

</Grid.Background>

<Grid.RowDefinitions>

<RowDefinition Height=”Auto”/>

<RowDefinition Height=”*”/>

</Grid.RowDefinitions>

<!–TitlePanel contains the name of the application and page title–>

<StackPanel x:Name=”TitlePanel” Grid.Row=”0″ Margin=”12,17,0,28″>

<TextBlock x:Name=”ApplicationTitle” Text=”MY APPLICATION” Style=”{StaticResource PhoneTextNormalStyle}”/>

<TextBlock x:Name=”PageTitle” Text=”Customer Data” Margin=”9,-7,0,0″ Style=”{StaticResource PhoneTextTitle1Style}”/>

</StackPanel>

<!–ContentPanel – place additional content here–>

<Grid x:Name=”ContentPanel” Grid.Row=”1″ Margin=”12,-15,12,0″>

<StackPanel Orientation=”Vertical”>

<TextBlock Text=”First Name:” FontSize=”16″ Margin=”5″/>

<TextBox Name=”txtFirstName” />

<TextBlock Text=”Last Name:” FontSize=”16″ Margin=”5″ />

<TextBox Name=”txtLastName”/>

<TextBlock Text=”Address:” FontSize=”16″ Margin=”5″/>

<TextBox Name=”txtAddress” />

<TextBlock Text=”City:” FontSize=”16″ Margin=”5″ />

<TextBox Name=”txtCity”/>

<TextBlock Text=”Zip:” FontSize=”16″ Margin=”5″/>

<TextBox Name=”txtZip” />

<TextBlock Text=”State:” FontSize=”16″ Margin=”5″ />

<TextBox Name=”txtState”/>

</StackPanel>

</Grid>

</Grid>

<!–Sample code showing usage of ApplicationBar–>

<phone:PhoneApplicationPage.ApplicationBar>

<shell:ApplicationBar IsVisible=”True” IsMenuEnabled=”True” Opacity=”.2″>

<shell:ApplicationBarIconButton x:Name=”btnSave” IconUri=”images/Save.png” Text=”Save” Click=”btnSave_Click”/>

<shell:ApplicationBarIconButton x:Name=”btnCancel” IconUri=”images/Cancel.png” Text=”Cancel” Click=”btnCancel_Click”/>

</shell:ApplicationBar>

</phone:PhoneApplicationPage.ApplicationBar>

</phone:PhoneApplicationPage>

• Paste the code in Add.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 class Add : PhoneApplicationPage

{

public  CustomersEntities ctx = newCustomersEntities(new Uri(“http://10.12.1.223/ODataSQLWP7/CustomerService.svc/&#8221;, UriKind.Absolute));

     public Add()

{

InitializeComponent();

}

//   Load data for the ViewModel Items

 private void btnSave_Click(object sender, EventArgs e)

{

// Instantiate the Client

var newCustomer = newCustomerInfo();

newCustomer.FirstName = this.txtFirstName.Text.Trim();

newCustomer.LastName = this.txtLastName.Text.Trim();

newCustomer.Address = this.txtAddress.Text.Trim();

newCustomer.City = this.txtCity.Text.Trim();

newCustomer.Zip = this.txtZip.Text.Trim();

newCustomer.State = this.txtState.Text.Trim();

ctx.AddObject(“CustomerInfoes”, newCustomer);

ctx.BeginSaveChanges(insertUserInDB_Completed, ctx);

}

   private void insertUserInDB_Completed(IAsyncResult result)

{

ctx.EndSaveChanges(result);

}

   private  void btnCancel_Click(object sender, EventArgs e)

{

     this.NavigationService.Navigate(newUri(“/MainPage.xaml”, UriKind.Relative));

}

}

}

• Lets check after addition of new data , the Windows Phone 7.1 UI:

• Lets check the steps of updating the existing data :

• Lets update the list with new data & perform HTTP PUT operation for updating data of Astoria services.

•  After updating existing data, you can refresh the Main list to get updated data. Same update happens in database too.

• Source code to perform Update Operation with OData WCF Services for SQL Server / SQL Azure database for Windows Phone 7.1 Mango:

<phone:PhoneApplicationPage

x:Class=”WP7PanoramaOData.Edit”

xmlns=”http://schemas.microsoft.com/winfx/2006/xaml/presentation&#8221;

xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml&#8221;

xmlns:phone=”clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone”

xmlns:shell=”clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone”

xmlns:d=”http://schemas.microsoft.com/expression/blend/2008&#8243;

xmlns:mc=”http://schemas.openxmlformats.org/markup-compatibility/2006&#8243;

FontFamily=”{StaticResource PhoneFontFamilyNormal}”

FontSize=”{StaticResource PhoneFontSizeNormal}”

Foreground=”{StaticResource PhoneForegroundBrush}”

SupportedOrientations=”Portrait” Orientation=”Portrait”

mc:Ignorable=”d” d:DesignHeight=”768″ d:DesignWidth=”480″

shell:SystemTray.IsVisible=”True”>

<!–LayoutRoot is the root grid where all page content is placed–>

<Grid x:Name=”LayoutRoot”>

<Grid.Background>

<ImageBrush ImageSource=”images/Back.png”/>

</Grid.Background>

<Grid.RowDefinitions>

<RowDefinition Height=”Auto”/>

<RowDefinition Height=”*”/>

</Grid.RowDefinitions>

<!–TitlePanel contains the name of the application and page title–>

<StackPanel x:Name=”TitlePanel” Grid.Row=”0″ Margin=”12,17,0,28″>

<TextBlock x:Name=”ApplicationTitle” Text=”MY APPLICATION” Style=”{StaticResource PhoneTextNormalStyle}”/>

<TextBlock x:Name=”PageTitle” Text=”Edit Data” Margin=”9,-7,0,0″ Style=”{StaticResource PhoneTextTitle1Style}”/>

</StackPanel>

<!–ContentPanel – place additional content here–>

<Grid x:Name=”ContentPanel” Grid.Row=”1″ Margin=”12,-15,12,0″>

<StackPanel Orientation=”Vertical”>

<TextBlock Text=”First Name:” FontSize=”16″ Margin=”5″/>

<TextBox Name=”txtFirstName” />

<TextBlock Text=”Last Name:” FontSize=”16″ Margin=”5″ />

<TextBox Name=”txtLastName”/>

<TextBlock Text=”Address:” FontSize=”16″ Margin=”5″/>

<TextBox Name=”txtAddress” />

<TextBlock Text=”City:” FontSize=”16″ Margin=”5″ />

<TextBox Name=”txtCity”/>

<TextBlock Text=”Zip:” FontSize=”16″ Margin=”5″/>

<TextBox Name=”txtZip” />

<TextBlock Text=”State:” FontSize=”16″ Margin=”5″ />

<TextBox Name=”txtState”/>

</StackPanel>

</Grid>

</Grid>

<!–Sample code showing usage of ApplicationBar–>

<phone:PhoneApplicationPage.ApplicationBar>

<shell:ApplicationBar IsVisible=”True” IsMenuEnabled=”True” Opacity=”.2″>

<shell:ApplicationBarIconButton x:Name=”btnSave” IconUri=”images/Save.png” Text=”Save” Click=”btnSave_Click”/>

<shell:ApplicationBarIconButton x:Name=”btnCancel” IconUri=”images/Cancel.png” Text=”Cancel” Click=”btnCancel_Click”/>

</shell:ApplicationBar>

</phone:PhoneApplicationPage.ApplicationBar>

</phone:PhoneApplicationPage>

• Edit.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 partialclassEdit : PhoneApplicationPage

{

public CustomersEntities ctx = newCustomersEntities(newUri(“http://10.12.1.223/ODataSQLWP7/CustomerService.svc/&#8221;, UriKind.Absolute));

public Edit()

{

InitializeComponent();

}

privatevoid btnSave_Click(object sender, EventArgs e)

{

var qry = ctx.CreateQuery<CustomerInfo>(“CustomerInfoes”).AddQueryOption(“$filter”, “FirstName eq” + “\'” + “Maria” + “\'”);

qry.BeginExecute(r =>

{

var query = r.AsyncState asDataServiceQuery<CustomerInfo>;

               try

{

            Deployment.Current.Dispatcher.BeginInvoke(() =>

{

                var result = query.EndExecute(r).First();

result.FirstName = this.txtFirstName.Text;

result.LastName = this.txtLastName.Text.Trim();

result.Address =  this.txtAddress.Text.Trim();

result.City = this.txtCity.Text.Trim();

result.Zip = this.txtZip.Text.Trim();

result.State = this.txtState.Text.Trim();

ctx.UpdateObject(result);

ctx.BeginSaveChanges(changeUserInDB_Completed, ctx);

});

}

      catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

}, qry);

}

private void changeUserInDB_Completed(IAsyncResult result)

{

ctx.EndSaveChanges(result);

}

private void btnCancel_Click(object sender, EventArgs e)

{

this.NavigationService.Navigate(newUri(“/MainPage.xaml”, UriKind.Relative));

}

}

}

 

Consuming OData WCF REST Service from Android Client Device

As in my previous blog , already mentioned to create about OData Service feed in ATOM format which can be consumed in Windows Phone 7, Android, iPhone/iPAD , Silverlight,PHP, Windows Azure Table Storage clients.

• Lets check , how to consume the OData WCF REST service in Android client. To work with OData Android client lets download Odata4j Android client library from RESTlet: http://www.restlet.org/downloads/ & select the OData4j-Bundle.jar in Java Build Path of your Android project.
• Next, to consume OData service from android device, we need to host the service in IIS. In my demo, i have hosted it in IIS 7.5.
• Now, Start develop an android client application which can consume OData feed.

• Modify AndroidManifest.xml as it can consume feed from internet from native application:

<uses-permissionandroid:name=“android.permission.INTERNET”>

</uses-permission>

   Write code for  Activity.java :

package com.example.android.OData;

import java.util.ArrayList;

import java.util.List;

import org.odata4j.consumer.ODataConsumer;

import org.odata4j.core.OEntity;

import android.app.ListActivity;

import android.os.Bundle;

import android.widget.ArrayAdapter;

public class JsonGrabbingConsumerExampleActivity  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);

    }

// read expenses odata feed

    ArrayList<String> GetExpenseReports()

    {

// build a simple array list of strings to test things out

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

// use odata4j consumer

        ODataConsumer c = ODataConsumer.create(http://10.12.1.223/ODataSample1/Service.svc);

// run a query just for Pending states

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

for(OEntity expense : listExpenses) {

    listUI.add(expense.getProperty(

“CustomerID”).getValue().toString()

    +

“; “ + expense.getProperty(“CustomerName”).getValue().toString()

    +

“; “ + expense.getProperty(“CustomerNotes”).getValue().toString()

    );

        }

return  listUI;   

    }

}

• Checkout the output in Android 2.3:

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 :