Create, Update & Delete(CUD) Operations of SQL Server / SQL Azure Data through OData WCF Services from Windows Phone 7.1 Mango
January 17, 2012 Leave a comment
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”
xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml”
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″
xmlns:mc=”http://schemas.openxmlformats.org/markup-compatibility/2006″
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/”, 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”
xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml”
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″
xmlns:mc=”http://schemas.openxmlformats.org/markup-compatibility/2006″
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/”, 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));
}
}
}
Anindita's Blog 
You must be logged in to post a comment.