Connect Windows Store Apps to SQL Server Database Using WCF

Prabhakar Maurya
11y
79.3k
0
0

Article

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>

</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.

Up Next

Ebook Download

View all

Microsoft's Windows AppStudio

Read by 1.7k people

Download Now!

Learn

View all