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.

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

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
   • http://www.microsoft.com/en-us/showcase/details.aspx?uuid=1bd6bcb1-87c8-4f77-8425-2624a5f96976
3. Notice that I've previously created a server named:
   • ol9d1pjpmi.database.windows.net
4. Figure 3 below shows the created database.

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.

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.

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.

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.

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

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

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

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

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

Figure 13: Choosing ASP.NET MVC 4 WEB ROLE

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

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

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

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

1. In Figure 16, click on SHOW CONNECTION STRINGS

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

   

   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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

   // GET api/values public IEnumerable<string> Get() {     DataTable table = new DataTable();     string _connStr = "Server=tcp:ol9d1pjpmi.database.windows.net,1433;Database=brunoeasydblesson;" +         "User ID=DBAdministrator@ol9d1pjpmi;Password={ you get this from the portal};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

   1 2 3	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.

    
   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

    
   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

   

   Figure 21: Package up your application
   

   

   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

   

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

   

   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

   

   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

   

   Figure 26: Deployment almost complete
   
   1. You can see the the URL is now available to access at the portal
   2. 
   3. Yours will be different because they must be globally unique
      • Site URL
        • http://brunoeasycloudlesson.cloudapp.net/
        • To access the GET() method, type:
          • http://brunoeasycloudlesson.cloudapp.net/api/values
          • In Figure 27 notice that I can choose to save the returned JSON data
        • Notice api/values added to the end
          • That causes the GET() method to be called
        • I will shut down this deployment so those links won’t work when you click on them
        • I wish to save money

    

   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.

   

   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

    
   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

    
   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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

   <Page     x:Class="Windows8WebServiceClient.MainPage"     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"     xmlns:local="using:Windows8WebServiceClient"     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}">         <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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

   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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

   using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Net.Http; using Windows.Data.Json; 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("http://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:

   
   
   

   Figure 31: Setting the Simulator

   
   

   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.