topic Re: Simple example needed for rest api with azure in Developer

Simple example needed for rest api with azure

paulinuk2017 — Tue, 07 Nov 2017 13:52:39 GMT

Hello

Does anyone know of an start to finish sample for power bi api?

I need to get data in real time and also to clear existing data from a data set in my dashboard

Most of the samples I have seen are not clear on the rest application side

I need to get data from my azure database and put this into the dataset

For example if a set of records is added at 14:00 then 14:05 I need to totally clear all records from the table just before I insert the 14:05 records

Paul

Re: Simple example needed for rest api with azure

Eric_Zhang — Wed, 08 Nov 2017 02:33:18 GMT

@paulinuk2017 wrote:
Hello

Does anyone know of an start to finish sample for power bi api?

I need to get data in real time and also to clear existing data from a data set in my dashboard

Most of the samples I have seen are not clear on the rest application side

I need to get data from my azure database and put this into the dataset

For example if a set of records is added at 14:00 then 14:05 I need to totally clear all records from the table just before I insert the 14:05 records

Paul

@paulinuk2017

To clear all records from a table, you can call the API Delete Rows before adding rows. See demo below.

using System;
using System.Net;
//Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612
using Microsoft.IdentityModel.Clients.ActiveDirectory; 
//Install-Package Newtonsoft.Json 
using Newtonsoft.Json;
using System.IO;
using System.Web; 
using System.Collections.Specialized;

namespace ConsoleApplication39
{

    class Program
    {

        //Step 1 - Replace {client id} with your client app ID. 
        //To learn how to get a client app ID, see Register a client app (https://msdn.microsoft.com/en-US/library/dn877542.aspx#clientID)
        private static string clientID = "{client_id}";

        //RedirectUri you used when you registered your app.
        //For a client app, a redirect uri gives AAD more details on the specific application that it will authenticate.
        private static string redirectUri = "https://login.live.com/oauth20_desktop.srf";

        //Resource Uri for Power BI API
        private static string resourceUri = "https://analysis.windows.net/powerbi/api";

        //OAuth2 authority Uri
        private static string authority = "https://login.windows.net/common/oauth2/authorize";

        //the account used to login Power BI
        private static string username = "your power bi account";
        private static string password = "your password";

        private static AuthenticationContext authContext = null;
        private static string token = String.Empty;

        //The power bi app workspace id(the GUID after /groups/ in below link
        //when viewing a dataset in Power BI Service, the link is like
        private static string groupId = "group id";

        //The target datasetId 
        private static string datasetId = "dataset id";

        //The target table name
        private static string tableName = "table name";

        static void Main(string[] args)
        {

            //token = getAccessTokenWithLoginPopUp();
            token = getAccessTokenSilently();

            clearTableData(groupId, datasetId,tableName);

            Console.ReadKey();

        }

         


        static void clearTableData(string groupId, string datasetId,string tableName)
        {

            HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp(String.Format("https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets/{1}/tables/{2}/rows", groupId, datasetId, tableName));
            //POST web request to create a datasource.
            request.KeepAlive = true;
            request.Method = "DELETE";
            request.ContentLength = 0;

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            //Write JSON byte[] into a Stream
            using (Stream writer = request.GetRequestStream())
            {

                var response = (HttpWebResponse)request.GetResponse();
                Console.WriteLine("DELETE ROWS", response.StatusCode.ToString());
            }


        }

        static string getAccessTokenSilently()
        {

            HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp("https://login.windows.net/common/oauth2/token");
            //POST web request to create a datasource.
            request.KeepAlive = true;
            request.Method = "POST";
            request.ContentLength = 0;
            request.ContentType = "application/x-www-form-urlencoded";

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            NameValueCollection parsedQueryString = HttpUtility.ParseQueryString(String.Empty);
            parsedQueryString.Add("client_id", clientID);
            parsedQueryString.Add("grant_type", "password");
            parsedQueryString.Add("resource", resourceUri);
            parsedQueryString.Add("username", username);
            parsedQueryString.Add("password", password);
            string postdata = parsedQueryString.ToString();


            //POST web request
            byte[] dataByteArray = System.Text.Encoding.ASCII.GetBytes(postdata); ;
            request.ContentLength = dataByteArray.Length;

            //Write JSON byte[] into a Stream


            using (Stream writer = request.GetRequestStream())
            {
                try
                {
                    writer.Write(dataByteArray, 0, dataByteArray.Length);
                    var response = (HttpWebResponse)request.GetResponse();
                    var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
                    dynamic responseJson = JsonConvert.DeserializeObject<dynamic>(responseString);
                    return responseJson["access_token"];
                }
                catch (WebException ex)
                {
                    using (WebResponse response = ex.Response)
                    {
                        HttpWebResponse httpResponse = (HttpWebResponse)response;
                        Console.WriteLine("Error code: {0}", httpResponse.StatusCode);
                        using (Stream data = response.GetResponseStream())
                        using (var reader = new StreamReader(data))
                        {
                            string text = reader.ReadToEnd();
                            Console.WriteLine(text);
                        }
                    }
                }
            }


            return null;


        }
 
    }
}

Re: Simple example needed for rest api with azure

paulinuk2017 — Wed, 08 Nov 2017 07:08:34 GMT

Hello

I get a bad request error?

Do I need do any special formatting anywhere?

Paul

Re: Simple example needed for rest api with azure

paulinuk2017 — Wed, 08 Nov 2017 07:09:57 GMT

Can I have the code for the logging in with a gui please?

I want to check if the token is returned that way?

Paul

Re: Simple example needed for rest api with azure

paulinuk2017 — Wed, 08 Nov 2017 07:11:54 GMT

Here is the post request generated

client_id=7f8439f9-26dd-4db9-893e-be42f89f74a60&grant_type=password&resource=https%3a%2f%2fanalysis.windows.net%2fpowerbi%2fapi&username=USERNAME&password=PASSWORD"

Re: Simple example needed for rest api with azure

Eric_Zhang — Wed, 08 Nov 2017 08:00:36 GMT

@paulinuk2017 wrote:

Can I have the code for the logging in with a gui please?

I want to check if the token is returned that way?

Paul

static string getAccessTokenWithLoginPopUp()
        {
            if (token == String.Empty)
            {
                //Get Azure access token
                // Create an instance of TokenCache to cache the access token
                TokenCache TC = new TokenCache();
                // Create an instance of AuthenticationContext to acquire an Azure access token
                authContext = new AuthenticationContext(authority, TC);
                // Call AcquireToken to get an Azure token from Azure Active Directory token issuance endpoint
                token = authContext.AcquireToken(resourceUri, clientID, new Uri(redirectUri), PromptBehavior.RefreshSession).AccessToken;
            }
            else
            {
                // Get the token in the cache
                token = authContext.AcquireTokenSilent(resourceUri, clientID).AccessToken;
            }

            return token;
        }

Re: Simple example needed for rest api with azure

Eric_Zhang — Wed, 08 Nov 2017 08:02:43 GMT

@paulinuk2017 wrote:

Hello

I get a bad request error?

Do I need do any special formatting anywhere?

Paul

No special formatting at all. Postman is a good tool for REST API testing.

Re: Simple example needed for rest api with azure

paulinuk2017 — Wed, 08 Nov 2017 09:39:51 GMT

Ok thanks

I am actually getting an error saying app not authorised

I am now trying to guide people through how to make my account a global administrator as I am assuming that’s what I need to be able to give permission in azure?

Paul