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.