Simple Metro Style Application with WCF service and MS Excel Database

• Download PrayerTimeService.zip - 854.3 KB 

 Introduction   

Today, more techies are interested in creating apps in windows 8 and other platforms. When we analyses those apps, most of them have data which are static in nature. For apps, it is recommended to be lightweight application as it has to be downloaded into mobile devices.  

This article explains about creating a WCF service with excel database and consuming the service from a metro style application. 

It gives a possibility for hosting the WCF service to an internet domain and consume the service from the metro apps.   

Prerequisite  

You need the following tools installed in your development environment before going to the next steps:

• Microsoft Visual Studio 2012 
• Microsoft Office Excel 2007 or Higher version 
• Blend for Microsoft Visual Studio 2012  (Optional)   

Blend is a great tool for making better UI for metro apps. It really helps designer to create wonderful XAML designs in less time.  

Setting Up the WCF Service in Visual Studio 

Setting up a WCF service is easy since the visual studio creates the required folders and class files for a sample WCF service. So what we have to change in the service are 

•   Service Name
•  Data Contract
•  Operation Contract
•  Data Access 

The following steps will explain how to set up a WCF service and read data from MS excel sheet.<o:p>    

1. Open Visual Studio 2012 and click on the link “New Project”. In the popup window, select WCF and then WCF Service Application. We can rename the service as “PrayerTimeService” and click OK.  
   
    
   
   
2. We can find that visual studio has created a default service as “Service1”. We can rename this into “PrayerTime.svc”.
   
   
    
3. Now we have to add our database file into the service project. For this, right click the Project “PrayerTimeService” and add “New Folder” and rename it as “Data”. 
   
    
   
   Our excel datasheet will look like this.
   
   


4. You can create an excel file similar to this or download it from the “Download Source” link.  The data fetching query fetches a row for a particular day in a month. So we will have a total 366 rows as in the sheet.
5. Now we have to add this excel file inside the “Data” folder.
6.  Then, open the interface file, IPrayerTime.cs, and add the Data Contract and Service contract definitions.
C#

    [ServiceContract]
    public interface IPrayerTime
    {
 
        [OperationContract]
        PrayerTimeData GetDohaPryaerTime(int month, int day);
    }
 
    [DataContract]
    public class PrayerTimeData
    {
        string fajr;
        string sunrise;
        string duhr;
        string asr;
        string magrib;
        string isha;
 
        [DataMember]
        public string Fajr
        {
            get { return fajr; }
            set { fajr = value; }
        }
 
        [DataMember]
        public string Sunrise
        {
            get { return sunrise; }
            set { sunrise = value; }
        }
 
        [DataMember]
        public string Duhr
        {
            get { return duhr; }
            set { duhr = value; }
        }
 
        [DataMember]
        public string Asr
        {
            get { return asr; }
            set { asr = value; }
        }
 
        [DataMember]
        public string Magrib
        {
            get { return magrib; }
            set { magrib = value; }
        }
 
        [DataMember]
        public string Isha
        {
            get { return isha; }
            set { isha = value; }
        }
    }
}




7. Right click the file “PrayerTime.svc” and go to “View markup”. Change the service to “PrayerTimeService” as below     
XML

<%@ ServiceHost Language="C#" Debug="true" Service="PrayerTimeService.PrayerTime" CodeBehind="PrayerTime.svc.cs" %> 

 

8. Open the PrayerTime.svc class file and make the following changes in the file.
• Change the class name from “Service1” to “PrayerTime”
• Add the namespace at the header 
C#

using System.Web.Hosting;

Since we are using Microsoft Excel as database, we need to provide the physical file path. So we use the static method HostingEnvironment.MapPath(“Path”) 
• Add the namespace at the header 
C#

using System.Data.Common;

This namespace is required for using the “DBProvideFactory.GetFactory” method.  
• Copy and paste the following code to the class file. 
C#

 public class PrayerTime : IPrayerTime
{
    public PrayerTimeData GetDohaPryaerTime(int day, int month)
    {
        string excelPath = HostingEnvironment.MapPath("~/Data/Doha_PrayerTimes.xlsx");
        String ConnectionString = @"Data Source=" + excelPath + ";Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;";
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        PrayerTimeData prayerTimeData = new PrayerTimeData();
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = ConnectionString;
            using (DbCommand command = connection.CreateCommand())
            {
                connection.Open();
                command.CommandText = "SELECT Fajr, Sunrise,    Duhr,   Asr,    Magrib, Isha FROM [PrayerTime$] where Month = " + month.ToString() + " and  Day = " + day.ToString() + " ;"; //Sheet1 here is the name of the sheet which is to be read
                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        prayerTimeData.Fajr = Convert.ToDateTime(reader[0]).ToString("H:mm");
                        prayerTimeData.Sunrise = Convert.ToDateTime(reader[1]).ToString("H:mm");
                        prayerTimeData.Duhr = Convert.ToDateTime(reader[2]).ToString("H:mm");
                        prayerTimeData.Asr = Convert.ToDateTime(reader[3]).ToString("H:mm");
                        prayerTimeData.Magrib = Convert.ToDateTime(reader[4]).ToString("H:mm");
                        prayerTimeData.Isha = Convert.ToDateTime(reader[5]).ToString("H:mm");
                    }
                }
            }
        }

        return prayerTimeData;
    }
}

 


9.  Now the WCF project is ready to “Build”. We can build the project and view the service properties in the browser. Right click the service project and click on “View in Browser”.
   
   

Setting Up the Metro App in Visual Studio  

In the previous section, we have created a WCF service that provides prayer time data. Now we can create a Windows store application to consume this service and show it in the metro interface, Here we will have four tasks 

• Create the project
• Design XAML interface 
• Make code  behind class  
• Consume WCF service   

The following 10 steps will explain how to set up a WCF service

and read data from MS excel sheet.<o:p> 

1. Right click the solution, Add new project 
   
   
2. Select Windows Store, then Blank App (XAML)
3. Change the name as “DohaPrayerTime” and click OK 
4. Right click the new project and click “Set as startup project” option.
5. Go to assets folder, add a new image “Prayer_Time_Icon.jpg”. This image will be used inside the Main Page.  Also we can change the pictures for Logo, small logo, splash screen and store logo images.  





6. Now we need to add the service reference to the application.  Right click the “Service” folder, and click “Add Service Reference” and click on “Discover” button. Since our service is available within the solution, visual studio will discover it. Provide service name as “PrayerTimeService” in the popup window and click OK.  




7. Open MainPage.xaml file and replace the XAML Grid tags with the new codes. 



Paste this XAML code


XML

<Grid Width="1000">
        <Grid.Background>
            <LinearGradientBrush EndPoint="0.005,0.185" StartPoint="0.999,0.982">
                <GradientStop Color="#333399"/>
                <GradientStop Color="#00CC99" Offset="1"/>
            </LinearGradientBrush>
        </Grid.Background>
        <Grid.ColumnDefinitions>
            <ColumnDefinition ></ColumnDefinition>
            <ColumnDefinition ></ColumnDefinition>
            <ColumnDefinition Width="280" ></ColumnDefinition>
            <ColumnDefinition ></ColumnDefinition>
            <ColumnDefinition ></ColumnDefinition>
        </Grid.ColumnDefinitions>
        <Grid.RowDefinitions>
            <RowDefinition  Height="150"></RowDefinition>
            <RowDefinition  Height="150"></RowDefinition>
            <RowDefinition  Height="50"></RowDefinition>
            <RowDefinition  Height="50"></RowDefinition>
            <RowDefinition  Height="50"></RowDefinition>
            <RowDefinition  Height="50"></RowDefinition>
            <RowDefinition  Height="50"></RowDefinition>
            <RowDefinition  Height="50"></RowDefinition>
        </Grid.RowDefinitions>
        <Canvas Grid.Column="0" Grid.Row="0" Grid.ColumnSpan="5" >
            <Canvas.Background>
                <LinearGradientBrush EndPoint="1.007,1" StartPoint="0.005,0.013">
                    <GradientStop Color="#000066"/>
                    <GradientStop Color="#00CC99" Offset="1"/>
                </LinearGradientBrush>
            </Canvas.Background>
            <TextBlock Canvas.Left="25" Canvas.Top="30" Height="55" Width="437" Style="{StaticResource HeaderTextStyle}" >Doha Prayer Time</TextBlock>
            <Image  Canvas.Left="640" Source="Assets/Prayer_Time_Icon.jpg"   Stretch="Fill" Canvas.Top="0" Height="300" Width="360" />

        </Canvas>

        <TextBlock Name="txtDate" HorizontalAlignment="Left" Margin="107,61,0,0" Grid.Row="1" Grid.Column="1" Grid.ColumnSpan="4" 
                   TextWrapping="Wrap"  VerticalAlignment="Top" Style="{StaticResource SubheaderTextStyle}" Width="675"/>

        <StackPanel  Grid.Row="2" Grid.Column="3" Grid.ColumnSpan="2"  Background="#577599" Orientation="Horizontal">
            <TextBlock  Margin="15,5,0,0"  Style="{StaticResource SubheaderTextStyle}" >Fajr</TextBlock>
            <TextBlock Name="txtFajr" HorizontalAlignment="Left"  Margin="15,5,0,0"   Text="Time" 
                   TextWrapping="Wrap"  VerticalAlignment="Top" Style="{StaticResource SubheaderTextStyle}" Width="275"/>
        </StackPanel>

        <StackPanel  Grid.Row="3" Grid.Column="3" Grid.ColumnSpan="2"  Background="#478599" Orientation="Horizontal">
            <TextBlock  Margin="15,5,0,0"  Style="{StaticResource SubheaderTextStyle}" >Sunrise</TextBlock>
            <TextBlock Name="txtSunrise" HorizontalAlignment="Left" Margin="15,5,0,0" Text="Time" 
                   TextWrapping="Wrap"  VerticalAlignment="Top" Style="{StaticResource SubheaderTextStyle}" Width="275"/>
        </StackPanel>

        <StackPanel  Grid.Row="4" Grid.Column="3" Grid.ColumnSpan="2"  Background="#389499" Orientation="Horizontal">
            <TextBlock  Margin="15,5,0,0"  Style="{StaticResource SubheaderTextStyle}" >Duhr</TextBlock>
            <TextBlock Name="txtDuhr" HorizontalAlignment="Left" Margin="15,5,0,0" Text="Time" 
                   TextWrapping="Wrap"  VerticalAlignment="Top" Style="{StaticResource SubheaderTextStyle}" Width="275"/>
        </StackPanel>

        <StackPanel  Grid.Row="5" Grid.Column="3" Grid.ColumnSpan="2" Background="#24A899" Orientation="Horizontal">
            <TextBlock  Margin="15,5,0,0"  Style="{StaticResource SubheaderTextStyle}" >Asr</TextBlock>
            <TextBlock Name="txtAsr" HorizontalAlignment="Left" Margin="15,5,0,0"  Text="Time" 
                   TextWrapping="Wrap"  VerticalAlignment="Top" Style="{StaticResource SubheaderTextStyle}" Width="275"/>
        </StackPanel>
        <StackPanel  Grid.Row="6" Grid.Column="3" Grid.ColumnSpan="2"  Background="#0FBD99" Orientation="Horizontal">
            <TextBlock  Margin="15,5,0,0"  Style="{StaticResource SubheaderTextStyle}" >Magrib</TextBlock>
            <TextBlock Name="txtMagrib" HorizontalAlignment="Left"  Margin="15,5,0,0"  Text="Time" 
                   TextWrapping="Wrap"  VerticalAlignment="Top" Style="{StaticResource SubheaderTextStyle}" Width="275"/>
        </StackPanel>

        <StackPanel  Grid.Row="7" Grid.Column="3" Grid.ColumnSpan="2"  Background="#00CC99" Orientation="Horizontal">
            <TextBlock  Margin="15,5,0,0"  Style="{StaticResource SubheaderTextStyle}" >Isha</TextBlock>
            <TextBlock Name="txtIsha" HorizontalAlignment="Left" Margin="15,5,0,0" Text="Time" 
                   TextWrapping="Wrap"  VerticalAlignment="Top" Style="{StaticResource SubheaderTextStyle}" Width="275"/>
        </StackPanel>
    </Grid> 

 

8. Go to “Design” view to see the design and layout of the page.
   
   
   
   
   
9. Now we need to write the code behind logic. Open the MainPage.xaml.cs and paste the code inside the class file.


C#

protected override void OnNavigatedTo(NavigationEventArgs e)
        {
            txtDate.Text = DateTime.Now.ToString("dddd dd MMM yyyy hh:mm");
            ShowPrayerTime();
        }
 
        private async void ShowPrayerTime()
        {
            PrayerTimeService.PrayerTimeClient client = new PrayerTimeService.PrayerTimeClient();
 
PrayerTimeService.PrayerTimeData data = await client.GetDohaPryaerTimeAsync(DateTime.Today.Day, DateTime.Today.Month);
            
            txtFajr.Text = data.Fajr;
            txtSunrise.Text = data.Sunrise;
 
            txtDuhr.Text = data.Duhr;
            txtAsr.Text = data.Asr;
 
            txtMagrib.Text = data.Magrib;
            txtIsha.Text = data.Isha;
        }



10. Run the application. We can see the application with our design as shown below and the values are coming from the excel sheet through the WCF service. This
    
    is a single page application and we can enhance this by adding more pages, services
    
    and functionalities into this. 

One important factor is, since we are using the WCF service using the "await" keyword, the values may render after few milli seconds or even seconds.