Unable to update dataset credentials after uploading a pbix report

Anonymous

You can see my demo. I get stuck by using the Power BI Api lib to update the credential so I just call the REST API directly.
By the way,  this demo only works for the datasource in direct connect, in this demo, the datasource is a Azure SQL DB. For the datasources that need gateways and datasources configured, you can see this thread.

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//Install-Package Microsoft.PowerBI.Api -Version 2.0.2
using Microsoft.PowerBI.Api.V2;
using Microsoft.PowerBI.Api.V2.Models; 
//Install-Package Newtonsoft.Json 
using Newtonsoft.Json;

using Microsoft.Rest;
using System.Net;
using System.IO;

namespace SetCredential
{
    class Program
    {
        static string accesstoken = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6IlZXVkljMVdEMVRrc2JiMzAxc2FzTTVrT3E1USIsImtpZCI6IlZXVkljMVdEMVRrc2JiMzAxc2FzTTVrT3E1USJ9.eyJhdWQiOiJodHRwczovL2FuYWx5c2lzLndpbmRvd3MubmV0L3Bvd2VyYmkvYXBpIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvMDViYWYzZTUtNTZmYi00OTZlLTljNDEtNjkyYzA2NTMyM2E0LyIsImlhdCI6MTUwMzM5NDAxNiwibmJmIjoxNTAzMzk0MDE2LCJleHAiOjE1MDMzOTc5MTYsImFjciI6IjEiLCJhaW8iOiJBU1FBMi84RUFBQUFwMmZob2UrMzdhTHhhWVZCVXJWc3ppbEdPdDJSSTZsNHJXRmVQcjBLdWY4PSIsImFtciI6WyJwd2QiXSwiYXBwaWQiOiI4NzFjMDEwZi01ZTYxLTRmYjEtODNhYy05ODYxMGE3ZTkxMTAiLCJhcHBpZGFjciI6IjIiLCJlX2V4cCI6MjYyODAwLCJmYW1pbHlfbmFtZSI6IlpoYW5nIiwiZ2l2ZW5fbmFtZSI6IkVyaWMiLCJpcGFkZHIiOiIxNjcuMjIwLjI1NS4xMiIsIm5hbWUiOiJFcmljIFpoYW5nIiwib2lkIjoiNzYzNGI5MzYtNjk1Yy00M2IzLWJkMTYtOWUxNDJkYjZjYjZkIiwib25wcmVtX3NpZCI6IlMtMS01LTIxLTMwMTc0MzkxODUtMzI3NTMyODQxNi0xMTI3NTIxMzg1LTcwMzc0IiwicGxhdGYiOiIzIiwicHVpZCI6IjEwMDM3RkZFOTc5NzczM0MiLCJzY3AiOiJ1c2VyX2ltcGVyc29uYXRpb24iLCJzdWIiOiJpd3NWZE5iLU1YTmlITlB1MTJqWGZ4cnhmaGxSdE4zWUw5N3pRSnR4dGpBIiwidGlkIjoiMDViYWYzZTUtNTZmYi00OTZlLTljNDEtNjkyYzA2NTMyM2E0IiwidW5pcXVlX25hbWUiOiJlcmljemhAd2ljcmVzb2Z0LmNvbSIsInVwbiI6ImVyaWN6aEB3aWNyZXNvZnQuY29tIiwidmVyIjoiMS4wIn0.PPPYobO52n71mQhbdRkuG9WgYymhLrLvadZCGkJLgTVjbJ6kEvZs6yYCJ5E9sd0a1cl6rfJWkz6gCI3Vi2caexCUBj38NGR5BYXPK1kTm-_DOKOpkOtusd-HAJmL4Zqgv6Cooia9f3mX_-92kSQUYlsaiZ7iVYL4pUkNMheZeIvBCAs2xU0LUgTg8B3bCL1Ezgr_iBKF_jbZcz7SNrq93K12YiL2xjmoJbBSi2LYOeCP4bUTo1tb2LRnmyCTR8pK8JvhM19u22VfmFOL0SyAL-11UBjfDp9Uk6pYwRzNtCApMsW73kmuIi52AkADZ4048tZNhqoWZsFz2lqmrg0CYw";

        static void Main(string[] args)
        {


             var tokenCredentials = new TokenCredentials(accesstoken, "Bearer");
            string datasetid = "0894c31c-1425-4a3c-a1fc-57f8fbef0e88";


            try
            {
                using (var client = new PowerBIClient(new Uri("https://api.powerbi.com/"), tokenCredentials))
                {



                    ConnectionDetails connDetail = new ConnectionDetails();
                    connDetail.ConnectionString = "data source = linl3.database.windows.net; initial catalog = AdventureWorksLT; persist security info = True; encrypt = True; trustservercertificate = False";

                    client.Datasets.SetAllDatasetConnections(datasetid, connDetail);



                    var dataSources = client.Datasets.GetGatewayDatasources(datasetid);




                    /**

                    CredentialDetails credential = new CredentialDetails();

                    credential.CredentialType = "Basic";

                    //I WAS STUCK ON what is the credentials string like
                    credential.Credentials = "{\"credentialData\":[{\"name\":\"username\",\"value\":\"LinLeng\"},{\"name\":\"password\",\"value\":\"Password01!\"}]}";

                    credential.Credentials= "{\"basicCredentials\": {    \"username\": \"LinLeng\", \"password\": \"Password01!\"  }}";

                    UpdateDatasourceRequest udr = new UpdateDatasourceRequest(credential);

                    var a = client.Gateways.UpdateDatasource(dataSources.Value[0].GatewayId, dataSources.Value[0].Id, udr);

                    **/


                    string credential = " {\"credentialType\": \"Basic\", \"basicCredentials\": { \"username\": \"LinLeng\", \"password\": \"Password01!\"  } }";


                    updateCredential(credential, dataSources.Value[0].GatewayId, dataSources.Value[0].Id);





                }
            }
            catch (HttpOperationException ex) {

                Console.WriteLine(ex.Response.Content);

            }
             
            Console.ReadKey(); 
        }


        public static void updateCredential(string credential, string gatewayid, string datasourceid)
        {
            string responseStatusCode = string.Empty;

            string url = "https://api.powerbi.com/v1.0/myorg/gateways/"+gatewayid+"/datasources/"+datasourceid;
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

            request.ContentType = "application/json";
            request.Method = "PATCH";
            request.KeepAlive = true;
            request.Headers.Add("Authorization", String.Format("Bearer {0}", accesstoken));


            //POST web request
            byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(credential);
            request.ContentLength = byteArray.Length;

            //Write JSON byte[] into a Stream 

            try
            {
                using (Stream writer = request.GetRequestStream())
                {
                    writer.Write(byteArray, 0, byteArray.Length);

                    var response = (HttpWebResponse)request.GetResponse();

                    Console.WriteLine(string.Format("credential is updated {0}", response.StatusCode.ToString()));

                }
            }
            catch (WebException wex)
            {
                if (wex.Response != null)
                {
                    using (var errorResponse = (HttpWebResponse)wex.Response)
                    {
                        using (var reader = new StreamReader(errorResponse.GetResponseStream()))
                        {
                            string errorString = reader.ReadToEnd();
                            dynamic respJson = JsonConvert.DeserializeObject<dynamic>(errorString);
                            Console.WriteLine(respJson.ToString());
                            //TODO: use JSON.net to parse this string and look at the error message
                        }
                    }
                }
            }
             
        }


    }
}