Connect Windows Store Apps to SQL Server Database Using WCF

Introduction

Today we are describing how to connect Windows Store apps to a SQL Server database using Windows Services. As we know, there is not an option in Windows Store apps to directly connect to a SQL Server database. So if you want to create a connection between Windows Store apps to SQL Server then you must use Windows Services.

This application connects a Windows Store app to a SQL Server database using WCF Services throough a Windows Service so no one can see the connection or no end user can easily close the service. That's why it is a better way than connecting the Windows Store apps to a Desktop application and the Desktop application does the database part. In this application we are using a database named "EmpDatabase" and a table named "EmployeeTable". In the EmployeeTable there are three fields, Id, Name and Address.

Step 1

Open Visual Studio 2012 and create a new project called "WCF Services library".

Step 2

In this step add the database you want to use in your application using "ADO.NET Entity data model". To do that right-click on your services application in Solution Explorer and select "Add new item". Whatever table you are using in the application, must have a primary key.

Step 3

After adding an entity model your database table will be as follows:

Step 4

Go to the "IService.cs" page and replace all the code with the following code. In this page we declared the services interface.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.Text;

namespace WindowsService1

{

[ServiceContract]

public interface IService1

{

[OperationContract]

bool InsertEmployee(Employee EmpInsert);

[OperationContract]

List<Employee> GetEmployee();

}

[DataContract]

public class Employee

{

private string id;

private string name;

private string address;

[DataMember]

public string EmpId

{

get { return id; }

set { id = value; }

}

[DataMember]

public string EmpName

{

get { return name; }

set { name = value; }

}

[DataMember]

public string EmpAddress

{

get { return address; }

set { address = value; }

}

}

}

Step 5

In "Service.cs" page replace all code with following code. In this page we declared services method.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Runtime.Serialization;

using System.ServiceModel;

using System.Text;

namespace WindowsService1

{

public class Service1 : IService1

{

public bool InsertEmployee(Employee EmpInsert)

{

EmpDatabaseEntities EmpData = new EmpDatabaseEntities();

EmployeeTable NewEmp = new EmployeeTable();

NewEmp.Id = EmpInsert.EmpId;

NewEmp.Name = EmpInsert.EmpName;

NewEmp.Address = EmpInsert.EmpAddress;

EmpData.EmployeeTables.Add(NewEmp);

EmpData.SaveChanges();

return true;

}

public List<Employee> GetEmployee()

{

var Emplist = new List<Employee>();

EmpDatabaseEntities EmpData=new EmpDatabaseEntities();

foreach (var Emp in EmpData.EmployeeTables.ToList())

{

Employee GetEmp = new Employee();

GetEmp.EmpId = Emp.Id;

GetEmp.EmpName = Emp.Name;

GetEmp.EmpAddress = Emp.Address;

Emplist.Add(GetEmp);

}

return Emplist;

}

}

}

Step 6

After doing that, run WCF services and copy to the services URL.

Step 7

Now open Visual Studio 2012 in a new window and start a new "Windows Store apps" project.

Step 8

In this step, add a service reference. To do that right-click on the project and in Solution Explorer and select "Add Service Reference". Paste the Service URL and click on "Go". After finding the service click on "OK".

Step 9

Go to Solution Explorer and double-click on "MainPage.xaml". Your "MainPage.xaml" page is as in the following code:

<Page

x:Class="WindowsStoreToSql.MainPage"

xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

xmlns:local="using:WindowsStoreToSql"

xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

mc:Ignorable="d">

<Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}" RenderTransformOrigin="0.508,0.484">

<TextBox HorizontalAlignment="Left" x:Name="IdTextbox" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Margin="352,249,0,0" Width="246"/>

<TextBox HorizontalAlignment="Left" x:Name="NameTextbox" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Margin="352,303,0,0" Width="246"/>

<TextBox HorizontalAlignment="Left" x:Name="AddTextBox" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Margin="352,358,0,0" Width="246"/>

<Button Content="ShowData" x:Name="ShowData" HorizontalAlignment="Left" VerticalAlignment="Top" Margin="496,422,0,0" Click="ShowData_Click"/>

<Button Content="InsertData" x:Name="InsertData" HorizontalAlignment="Left" VerticalAlignment="Top" Margin="352,422,0,0" Click="InsertData_Click"/>

<GridView HorizontalAlignment="Left" x:Name="EmpGridview" VerticalAlignment="Top" Width="150" Margin="646,162,0,0" />

</Grid>

</Page>

Step 10

Your "MainPage.xaml.cs" page is as in the following code:

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using Windows.Foundation;

using Windows.Foundation.Collections;

using Windows.UI.Xaml;

using Windows.UI.Xaml.Controls;

using Windows.UI.Xaml.Controls.Primitives;

using Windows.UI.Xaml.Data;

using Windows.UI.Xaml.Input;

using Windows.UI.Xaml.Media;

using Windows.UI.Xaml.Navigation;

using Windows.UI.Popups;

namespace WindowsStoreToSql

{

public sealed partial class MainPage : Page

{

ServiceReference1.Service1Client MyService;

public MainPage()

{

this.InitializeComponent();

}

protected override void OnNavigatedTo(NavigationEventArgs e)

{

MyService = new ServiceReference1.Service1Client();

}

private async void InsertData_Click(object sender, RoutedEventArgs e)

{

await MyService.InsertEmployeeAsync(new ServiceReference1.Employee { EmpId = IdTextbox.Text, EmpName = NameTextbox.Text, EmpAddress = AddTextBox.Text });

}

private async void ShowData_Click(object sender, RoutedEventArgs e)

{

var EmpList = await MyService.GetEmployeeAsync();

foreach (var Emp in EmpList)

{

GridViewItem EmpView = new GridViewItem();

StackPanel Spanel = new StackPanel();

Spanel.Children.Add(new TextBlock() { Text = Emp.EmpId });

Spanel.Children.Add(new TextBox() { Text = Emp.EmpName });

Spanel.Children.Add(new TextBlock() { Text = Emp.EmpAddress });

EmpView.Content = Spanel;

EmpGridview.Items.Add(EmpView);

}

}

}

}

Step 11

Now run project. Click on "ShowData" to show your table data and click on "InsertData" to insert data into the table.