Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
send2prasan
Helper I
Helper I

Power BI API to change Database dynamically

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.

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


@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
                        }
                    }
                }
            }
             
        }


    }
}

View solution in original post

2 REPLIES 2

What is the alternative of "SetAllDatasetConnections"  as it is deprecated?  I want to change the DB server name, DB name, DB user name, and password. 

Eric_Zhang
Employee
Employee


@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
                        }
                    }
                }
            }
             
        }


    }
}

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.