Forum Discussion
BartHuls
Helper I
1 year agoHow 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...
- 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
BartHuls
Helper I
1 year agoAfter 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