@send2prasan wrote:
Dear All,
Please let me know if anybody worked on changing the database dynamically using Power BI API. If you have a sample code please pass it on.
The requirement is like this. We are having a multi-tenant C# application (Different Dbs on single Azure SQL server). We are storing all the database name in session.
I have downloaded the sample application by following the link for Power BI dashboard embeding.
https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-embed-sample-app-owns-data/
The embeded dashboard is appearing fine. I would like to pass the database name dynamically based on the user login, rest of the connection parameters remains same.
data source=prasanna\\SQLEXPRESS;initial catalog=" + Session["DatabaseName"].ToString() + ";user id=yeats;password=yeats;persist security info=False;encrypt=True;trustservercertificate=True;
Thanks for your help.
Prasanna V.
@send2prasan
You can only change the connection string of the datasource in DirectQuery mode. See below demo setting connection string or a Azure SQL Server.
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
}
}
}
}
}
}
}
