Forum Discussion

send2prasan's avatar
send2prasan
Helper I
8 years ago

Power BI API Integration with C#

Dear All,

 

We are having a multi-tenant C# application. 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 stored in ASP.Net application to Power BI dashboard dynamically when the respective tenant logs into the application. Please see the following code snippet:

 

// Create a Power BI Client object. It will be used to call Power BI APIs.
            using (var client = new PowerBIClient(new Uri(ApiUrl), tokenCredentials))
            {
                // Get a list of dashboards.
                ConnectionDetails cn = new ConnectionDetails();
                cn.ConnectionString = "data source=Computer1\\SQLEXPRESS;initial catalog=Db_Tent1;persist security info=True;User Id=Username;Password=Password;encrypt=True;trustservercertificate=False";

                //var datsetList = client.Datasets.GetDatasources(GroupId);

                var datasets = client.Datasets.GetDatasetsInGroup(GroupId);

                var dashboards1 = client.Datasets.SetAllDatasetConnections(datasets.Value[0].Id, cn);

.............

 

This is throwing error: Operation returned an invalid status code 'NotFound' 

 

Please need your help to solve this problem.

 

Thanks,

Prasanna V.

Need Help

8 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago

    send2prasan

    I see you're getting the dataset from a group, so when set the connection string, use SetAllDatasetConnectionsInGroup instead.

    Also, to get more specific information, please add a try..catch block to capture the response details message.

     

    try
                {
                    //client.Datasets.PostRowsInGroup(groupId, "dcb49dcc-7392-4277-9258-ec26bd842b42", "RealTimeData", dataObj);

                    ConnectionDetails cn = new ConnectionDetails();
                    cn.ConnectionString = "data source=Computer1\\SQLEXPRESS;initial catalog=Db_Tent1;persist security info=True;User Id=Username;Password=Password;encrypt=True;trustservercertificate=False";
                    //var datsetList = client.Datasets.GetDatasources(GroupId);
                    var datasets = client.Datasets.GetDatasetsInGroup(groupId);
                    var dashboards1 = client.Datasets.SetAllDatasetConnectionsInGroup(groupId,datasets.Value[0].Id, cn);


                }
                catch (HttpOperationException ex)
               { 
                    //Bad Request
                    var content = ex.Response.Content;
                    Console.WriteLine(content); 
                }
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I am getting the following exception :

       

      Operation returned an invalid status code 'BadRequest'

    • send2prasan's avatar
      send2prasan
      Helper I
      8 years ago

      Dear Eric,

       

      Thanks for the reply. I could able to fix the bug partially.

       

      As I have already explained, we are having multiple tenant databases on the same Azure SQL server. Based on the user login I have to change the database on runtime. For this we have created session to store database name. This database name is passes to connection string (refer the code below):

       

      The problem I am facing now is: When I change the connection string dynamically, the dashboard is not able to pull the data from data source. Please see the code below:

       

      // Get a list of dashboards.
      ConnectionDetails cn = new ConnectionDetails();
      cn.ConnectionString = "data source=Azure link\\SQLEXPRESS;initial catalog=" + Session["DatabaseName"].ToString() + ";user id=test;password=password;persist security info=False;encrypt=True;trustservercertificate=True";

       

      var datasets = await client.Datasets.GetDatasetsInGroupAsync(GroupId);

      try
      {
      var dbobject = await client.Datasets.SetAllDatasetConnectionsInGroupAsync(GroupId, datasets.Value[0].Id, cn);
      }
      catch (HttpOperationException ex)
      {
      //Bad Request
      var content = ex.Response.Content;
      Console.WriteLine(content);
      }

      var dashboards = await client.Dashboards.GetDashboardsInGroupAsync(GroupId);

      // Get the first report in the group.
      var dashboard = dashboards.Value.FirstOrDefault();

      if (dashboard == null)
      {
      return View(new EmbedConfig()
      {
      ErrorMessage = "Group has no dashboards."
      });
      }

       

      Please assume that I have designed the report using "Database 1" from power bi desktop and I have published the same. If this report/ dashboard is accessed from the code it works fine. If I change the db to say "Database 2" and pass the connection string to above method it is not working.

       

      Please I need you advice.

       

      Thanks,

      Prasanna V.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        This is the Error that I get, and the dataset is a DirectQuery dataset.

         

        {"error":{"code":"InvalidRequest","message":"Invalid dataset. This API can only be called on a DirectQuery dataset"}}