Reading SQL Server Data From a Windows 8 Application using the ASP.NET Web API running as a cloud service

  1. I often get asked how to read from SQL Server in a Windows 8 application.
  2. Windows 8 applications are expected to run directly from the Windows Store, without the limitations imposed by connecting to local servers via a connection string.
  3. The recommended approach is to expose your database via a web service.
  4. Two Microsoft technologies that do this are:
    • Windows Communication Foundation
    • ASP.NET Web API (the more modern approach)
      • We will use this technology in this post.

Start by building the database We will being by building the database in the MS Cloud using Windows Azure.

image

Figure 1: Windows Azure Portal, SQL Database

  1. Figure 1 shows the Windows Azure Portal
  2. We will select SQL DATABASE
  3. In the lower left corner you can see the plus sign with the word NEW
  4. Select NEW and name your database

image

Figure 2: Creating a SQL Database

  1. In Figure 2, we are creating a database called BrunoEasyDBLession
  2. If you'd like a video tutorial with more details see the link below
  3. Notice that I've previously created a server named:
    • ol9d1pjpmi.database.windows.net
  4. Figure 3 below shows the created database.

image

Figure 3: BrunoEasyDBLesson is created


image

Figure 4: Drilling into the database to add tables

  1. The next step is to create some tables and add some data to test with.
  2. Click MANAGE as seen in Figure 4.
    • This took me several attempts so that I can login as seen in Figure 5
    • The Username and Password were defined at the SERVER level. You did this step previously. Note that this is not a DATABASE username/password. It is at the SERVER level.
      • See the previously mentioned video for more details.
      • We have not shown the creation of the server in this post.

image

Figure 5: Logging into the server


image

Figure 6: Database Management Portal

  1. At this point we can start to define tables.
  2. We will create a table called CUSTOMERS.
  3. We will also insert a little data.
  4. Click DESIGN in the lower left corner.

image

Figure 7: Adding a new table

  1. Type in the table name (Customers) as seen in Figure 7.
    • It turns out, the typing in Customers didn't do much for me.
      • You have to wait for the next step.
  2. Then click NEW TABLE.
  3. Type in a table name
    • Customers
  4. Modify the table structure as seen in Figure 8.
    • There should just be two columns
      • firstname, nvarchar 128
  5. Click SAVE at the top.

image

Figure 8: The Customers Table

  1. We will now insert some data.
  2. Click NEW QUERY at the top.
  3. Type in the 3 SQL Insert statements you see in Figure 9.
  4. Then click RUN.

image

Figure 9: Inserting data into the Customers table.


  1. Now that our table has data, we can turn our attention to exposing this data to our web service
    • We need to allow access to our server from the Azure Web Service
  2. We need to return to the main Azure Portal Screen as seen in Figure 10

image

Figure 10: Main Portal page to manage our database

  1. Notice in the lower right corner of Figure 10 you can see:
    • MANAGE ALLOWED IP ADDRESSES
    • This lets us determine which IP addresses can connect to our server
    • Recall that our server is:
      • ol9d1pjpmi.database.windows.net

image

Figure 11: Managing Allowed IP Addresses

  1. Notice in Figure 11, the bottom reads, ALLOWED SERVICES
    • YES means that our Azure Web Service is allowed to reach our server where we have our database.
      • Database
        • brunoeasydblesson
      • Server
        • ol9d1pjpmi.database.windows.net
  2. No further action is needed for the database
  3. We are ready to build our Azure Web Service

Creating the ASP.NET Web API Web Server

This next section is about building the Web Service that will expose SQL data to our Windows 8 clients.

image

Figure 12: Creating a new cloud project

  1. In Visual Studio 2012, select FILE/NEW PROJECT
    • Choose CLOUD/WINDOWS AZURE CLOUD SERVICE
  2. Provide a name
    • I called mine EASYWEB SERVICE
  3. See Figure 12

image

Figure 13: Choosing ASP.NET MVC 4 WEB ROLE

  1. Choose ASP.NET MVC 4 WEB ROLE and click OK

image

Figure 14: Choosing WEB API

  1. Choose WEB API
  2. This our web service
  3. It will read SQL data and return it to Windows 8 clients
  4. We will deploy this service to a MS data center as a cloud-based web service

image

Figure 15: ValuesController.cs

  1. Figure 15 shows ValuesController.cs
  2. This will be the code that executes once a Windows 8 client calls into the web service
  3. Specifically, it will call into the Get method as seen below
GET METHOD
12345 // GET api/valuespublic IEnumerable<string> Get(){    return new string[] { "value1", "value2" };}
  1. We will modify the GET METHOD to retrieve data from the previously created SQL Database
  2. The method returns a list of strings
    • We will return a list of firstnames
  3. We will need to get a connection string first
  4. We can get this at the Windows Azure Portal

image

Figure 16: The Windows Azure Portal

  1. In Figure 16, click on SHOW CONNECTION STRINGS

image

Figure 17: The connection string needed by the web service

  1. The connection string will be needed by the SQL code we are about to add to the GET method. Copy the string to the clipboard

    1. Server=tcp:ol9d1pjpmi.database.windows.net,1433;Database=BrunoEasyDBLesson;User ID=DBAdministrator@ol9d1pjpmi; Password={your_password_here}; Trusted_Connection=False;Encrypt=false;Connection Timeout=30;
    2. I am sending the data not encrypted in this demo. That is why you see ENCRYPT=FALSE

    image

    Figure 18: Modified Get() method

    1. The complete code listing is provided below.
    2. For this to work the connection string is crucial
    3. You will need to take note of:
      • Database Name
      • Server Name
      • User Name
      • Password
      • Encrypt Setting
    Updated GET() Method
    12345678910111213141516171819202122232425262728293031 // GET api/valuespublic IEnumerable<string> Get(){    DataTable table = new DataTable();    string _connStr = "Server=tcp:ol9d1pjpmi.database.windows.net,1433;Database=brunoeasydblesson;" +        "User ID=DBAdministrator\@ol9d1pjpmi;Password=;Trusted_Connection=False;Encrypt=false;Connection Timeout=30;";    // get the connection    using (SqlConnection conn = new SqlConnection(_connStr))    {        // write the sql statement to execute        string sql = "SELECT firstname from Customers";        // instantiate the command object to fire        using (SqlCommand cmd = new SqlCommand(sql, conn))        {            // get the adapter object and attach the command object to it            using (SqlDataAdapter ad = new SqlDataAdapter(cmd))            {                // fire Fill method to fetch the data and fill into DataTable                ad.Fill(table);            }        }    }    // Loop through and extract firstname as a list of strings    List<string> results = new List<string>();    foreach (DataRow dr in table.Rows)    {        results.Add(dr[0].ToString());    }    return results;}
    1. You will also need to add the following using statements
    123 using System.Data;using System.Data.SqlClient;

    Deploy to the Cloud

    Now that we’ve created our web service, we will now deploy to the cloud. We will need to go back to the Windows Azure portal to do that.

    image 
    Figure 19: Creating a new cloud service

    1. We will select CLOUD SERVICES on the left pane
    2. From there we will select NEW in the lower left corner
    3. Next select QUICK CREATE
    4. Specify the URL
      • I chose BRUNOEASYCLOUDLESSON
      • Yours will be different
    5. Choose a region
      • I chose WEST US, because I live near San Francisco.
    6. Then click CREATE CLOUD SERVICE
    7. This should take less than a few seconds

    image 
    Figure 20: brunoeasycloudlesson is created

    1. The next step is package up our web service so we can upload it to brunoeasycloudlesson
    2. Return back to Visual Studio
    3. Right mouse click on EASYWEBSERVICE and choose PACKAGE
    4. Then click PACKAGE again

    image

    Figure 21: Package up your application

    image

    Figure 22: The package files we will upload to the portal to perform the deployment

    1. The package files are now available for upload to the portal
      • On my computer they are here:
        • C:\temp\EasyWebService\EasyWebService\bin\Release\app.publish
      • You should copy the path to the clipboard

    image

    Figure 23: Uploading our package files

    1. Notice we can now upload our package files
    2. Click on UPLOAD A NEW PRODUCTION DEPLOYMENT

    image

    Figure 24: Completing the upload

    1. In Figure 24, you will provide:
      • A deployment name
      • A package file (created previously)
      • A configuration file (created previously)
      • Select DEPLOY EVEN IF ONE ORE MORE ROLES CONTAIN A SINGLE INSTANCE
        • There is plenty of online material regarding the understanding of scaling in Azure
    2. You will click on FROM LOCAL and choose the location of the package file

    image

    Figure 25: The deployment underway

    1. At this point it will take about 10 minutes to complete the deployment
    2. We are ready to turn our attention to the Windows 8 client to call into the web service

    image

    Figure 26: Deployment almost complete

    1. You can see the the URL is now available to access at the portal
    2. image
    3. Yours will be different because they must be globally unique

     image

    Figure 27: Testing our deployed service


    Let us now create the Windows 8 Client

    The Windows 8 client will call into the web service, which, in turn, will return to our Windows 8 client.

    image

    Figure 28: Creating a Windows 8 Client

    1. Start Visual Studio 2012
    2. Choose FILE/NEW PROJECT/WINDOWS STORE/BLANK APP (XAML) , as seen in Figure 28
    3. Set the following information
      • Name
        • Windows8WebServiceClient
      • Location of your choice

    image 
    Figure 29: Adding controls to MainPage.xaml (notice the Button and ListView controls in the TOOLBOX)

    1. Notice that I double-clicked on MainPage.xaml
    2. Also notice that I have the TOOLBOX pinned and visible
    3. We will now drag a BUTTON and LISTVIEW control
      • The button will call the web service to retrieve the data
      • The listview control will get populated with data coming back from the web service
        • Ultimately, this data came from SQL Database

    image 
    Figure 30: A button and listview control added

    1. We have added the button and listview control
    2. Hitting f4 once a control is selected brings up the properties window
    3. Here are the properties specified:
      • Button
        • x:Name="butCallWebService"
        • Content="Call Web Service"
        • HorizontalAlignment="Left"
        • Margin="214,123,0,0"
        • VerticalAlignment="Top"
        • Height="180"
        • Width="515"
        • FontSize="36"
      • ListView
        • x:Name="MyListView"
        • HorizontalAlignment="Left"
        • Height="320"
        • Margin="214,340,0,0"
        • VerticalAlignment="Top"
        • Width="667"
      • In Figure 30, double click on the button, while in design view
        • This will create an event routine that executes when the user clicks on the button
      • The XAML can be seen below
    MainPage.xaml
    1234567891011121314151617181920212223242526272829 <Page    x:Class="Windows8WebServiceClient.MainPage"    xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation"    xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml"    xmlns:local="using:Windows8WebServiceClient"    xmlns:d="https://schemas.microsoft.com/expression/blend/2008"    xmlns:mc="https://schemas.openxmlformats.org/markup-compatibility/2006"    mc:Ignorable="d">    <Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}">        <Button            x:Name="butCallWebService"            Content="Call Web Service"            HorizontalAlignment="Left"            Margin="214,123,0,0"            VerticalAlignment="Top"            Height="180"            Width="515"            FontSize="36"             Click="butCallWebService_Click"/>        <ListView            x:Name="MyListView"            HorizontalAlignment="Left"            Height="320"            Margin="214,340,0,0"            VerticalAlignment="Top"            Width="667"/>    </Grid></Page>
    1. MainPage.xaml.cs is the code-behind
    2. The method butCallWebService_Click is there
    1234567891011121314151617181920212223242526272829303132 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;namespace Windows8WebServiceClient{    public sealed partial class MainPage : Page    {        public MainPage()        {            this.InitializeComponent();        }        protected override void OnNavigatedTo(NavigationEventArgs e)        {        }        private void butCallWebService_Click(object sender, RoutedEventArgs e)        {            // We will add our code here        }    }}
    1. Line 28 is where we need to insert our code to call into the web service
    2. It will parse the returning JSON data and add it to the ListView control
    123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051 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;namespace Windows8WebServiceClient{    public sealed partial class MainPage : Page    {        public MainPage()        {            this.InitializeComponent();        }        protected override void OnNavigatedTo(NavigationEventArgs e)        {        }        async private void butCallWebService_Click(object sender, RoutedEventArgs e)        {            // We will add our code here            var client = new HttpClient();            client.MaxResponseContentBufferSize = 1024 * 1024; // Read up to 1 MB of data             var response = await client.GetAsync(new Uri("https://brunoeasycloudlesson.cloudapp.net/api/values"));            var result = await response.Content.ReadAsStringAsync();            //             // Parse the JSON data             //             var webServiceResults = JsonArray.Parse(result);            //             // Loop through the data and add to ListView control            //             JsonArray array = webServiceResults;            foreach (var item in array)            {                var obj = item.GetString();                MyListView.Items.Add(obj);            }        }    }}
    1. Some things to notice about the code
    2. Line 33 will differ from yours
      • You have a different web service name and hence DNS/URL

    Running the Code

    Now it is time to enjoy the fruits of our labor. Start by setting Visual Studio 2012 to use the Simulator as follows:

    image

    Figure 31: Setting the Simulator

    image

    Figure 32: The finished solution working correctly


    Mission Accomplished So that is it. I’ve been seeing a lot of questions, such on StackOverflow, about people wanting to consume relational data from SQL Server. I have provided a simple, end-to-end solution demonstrating how.