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 e...
Need Help
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"}}

      • send2prasan's avatar
        send2prasan
        Helper I
        8 years ago

        Dear shilpikanade,

         

        These are the steps I followed to solve the similar error:

         

        1. From Power BI desktop publish your report. this requires gateway installation. Please follow the instructions from the link below: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/
        2. Your report is published once this done. see you are able get the report on web.
        3. Now use the Power BI API code this should render the report/ dashboard without any problem.
        4. Getting your report on power bi web is important in direct query.

        Thanks,

        Prasanna V.