Forum Discussion

BartHuls's avatar
BartHuls
Helper I
1 year ago
Solved

How to set a dataconnection of a DataSet

I loaded a workspace with a Semantic Semantic (c# PowerBIClient SDK) Of this Semantic Model I want to set the "GateWay and cloud connects" The datasource is a Sql server which is available in the a...
  • BartHuls's avatar
    1 year ago

    After some struggling:

    public async Task PatchSqlDatasourceCredentials(Guid groupId, string datasetId, string reportingSqlServer, string reportingSqldatabase, string userName, string password)
    {
        var pbiClient = await GetPowerBIClient() ?? throw new PowerBiException(PowerBiClientIsNull);
    
        var datasources = (await pbiClient.Datasets.GetDatasourcesInGroupAsync(groupId, datasetId)).Value;
    
        var sqlDataSource = datasources.FirstOrDefault(d => d.DatasourceType.Equals("Sql", StringComparison.OrdinalIgnoreCase)) ?? throw new PowerBiException(PowerBiClientIsNull);
    
        var selectedServer = sqlDataSource.ConnectionDetails.Server;
        var selectedDatabase = sqlDataSource.ConnectionDetails.Database;
    
        if (!selectedServer.Equals(reportingSqlServer, StringComparison.OrdinalIgnoreCase) ||
            !selectedDatabase.Equals(reportingSqldatabase, StringComparison.OrdinalIgnoreCase))
        {
            await pbiClient.Datasets.UpdateDatasourcesInGroupAsync(groupId, datasetId, new UpdateDatasourcesRequest(new UpdateDatasourceConnectionRequest
            {
                DatasourceSelector = new Datasource
                {
                    DatasourceType = "Sql",
                    ConnectionDetails = new DatasourceConnectionDetails()
                    {
                        Server = selectedServer,
                        Database = selectedDatabase
                    }
                },
                ConnectionDetails = new DatasourceConnectionDetails()
                {
                    Server = reportingSqlServer,
                    Database = reportingSqldatabase
                },
    
            }));
        }
    
        datasources = (await pbiClient.Datasets.GetDatasourcesInGroupAsync(groupId, datasetId)).Value;
        sqlDataSource = datasources.FirstOrDefault(d => d.DatasourceType.Equals("Sql", StringComparison.OrdinalIgnoreCase)) ?? throw new PowerBiException(PowerBiClientIsNull);
    
        var datasourceId = sqlDataSource.DatasourceId!.Value;
        var gatewayId = sqlDataSource.GatewayId!.Value;
    
        // Create UpdateDatasourceRequest to update Azure SQL datasource credentials
        var updateDatasourceRequest = new UpdateDatasourceRequest
        {
            CredentialDetails = new CredentialDetails(
                new BasicCredentials(userName, password),
                PrivacyLevel.None,
                EncryptedConnection.NotEncrypted)
        };
    
        // Execute Patch command to update Azure SQL datasource credentials
        await pbiClient.Gateways.UpdateDatasourceAsync(gatewayId, datasourceId, updateDatasourceRequest);
    }

     

    Apperently I must  "Replace"  the SQL connection. 
    Question: Is it also possible to add or create a connection.

    Can I also make the connection based on a Mangedd Identity