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:
- 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>
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/”));
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:
Anindita's Blog 
You must be logged in to post a comment.