public static void UpdateConnection()
{
// Create a dev token for update
var devToken = PowerBIToken.CreateDevToken(WorkspaceCollection, WorkspaceId);
using (var client = CreatePowerBiClient(devToken))
{
// Get DataSets
var dataset = DatasetsExtensions.GetDatasets(client.Datasets, WorkspaceCollection, WorkspaceId).Value.Last();
var datasources = DatasetsExtensions.GetGatewayDatasources(client.Datasets, WorkspaceCollection, WorkspaceId, dataset.Id).Value;
// Build Credentials
var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = WingtipTicketApp.Config.DatabaseUser,
Password = WingtipTicketApp.Config.DatabasePassword
}
};
// Update each DataSource
foreach (var datasource in datasources)
{
// Update the datasource with the specified credentials
GatewaysExtensions.PatchDatasource(client.Gateways, WorkspaceCollection, WorkspaceId, datasource.GatewayId, datasource.Id, delta);
}
}
}
static void UpdateAzureSqlDataSource(string workspaceCollectionName, string workspaceId, string datasetId)
{
using (var client = CreatePowerBIClient()) {
IList <Dataset> datasets = client.Datasets.GetDatasetsAsync(workspaceCollectionName, workspaceId).Result.Value;
foreach (Dataset dataset in datasets)
{
if (dataset.Name == datasetId)
{
var datasources = client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, dataset.Id).Result;
// Reset your connection credentials
var delta = new GatewayDatasource {
CredentialType = "Basic",
BasicCredentials = new BasicCredentials {
Username = azureSqlUser,
Password = azureSqlPassword
}
};
// Update the datasource with the specified credentials
client.Gateways.PatchDatasourceAsync(workspaceCollectionName,
workspaceId,
datasources.Value[0].GatewayId,
datasources.Value[0].Id,
delta).Wait();
}
}
}
}
static async Task UpdateConnection(string workspaceCollectionName, string workspaceId, string datasetId, string login, string password)
{
using (var client = await CreateClient())
{
var datasources = await client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, datasetId);
// Reset your connection credentials
var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = login,
Password = password
}
};
if (datasources.Value.Count != 1)
{
Console.Write("Expected one datasource, updating the first");
}
// Update the datasource with the specified credentials
await client.Gateways.PatchDatasourceAsync(workspaceCollectionName, workspaceId, datasources.Value[0].GatewayId, datasources.Value[0].Id, delta);
}
}
/// <summary>
/// Updates the Power BI dataset connection info for datasets with direct query connections
/// </summary>
/// <param name="workspaceCollectionName">The Power BI workspace collection name</param>
/// <param name="workspaceId">The Power BI workspace id that contains the dataset</param>
/// <param name="id"></param>
/// <returns></returns>
static async Task UpdateConnection(string workspaceCollectionName, string workspaceId, string datasetId)
{
if (string.IsNullOrWhiteSpace(username))
{
Console.Write("Username: "******"Password: "******"Connection String (enter to skip): ");
connectionString = Console.ReadLine();
Console.WriteLine();
using (var client = await CreateClient())
{
// Optionally udpate the connectionstring details if preent
if (!string.IsNullOrWhiteSpace(connectionString))
{
var connectionParameters = new Dictionary <string, object>
{
{ "connectionString", connectionString }
};
await client.Datasets.SetAllConnectionsAsync(workspaceCollectionName, workspaceId, datasetId, connectionParameters);
}
// Get the datasources from the dataset
var datasources = await client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, datasetId);
// Reset your connection credentials
var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = username,
Password = password
}
};
if (datasources.Value.Count != 1)
{
Console.Write("Expected one datasource, updating the first");
}
// Update the datasource with the specified credentials
await client.Gateways.PatchDatasourceAsync(workspaceCollectionName, workspaceId, datasources.Value[0].GatewayId, datasources.Value[0].Id, delta);
}
}
/// <summary>
/// Updates the Power BI dataset connection info for datasets with direct query connections
/// </summary>
/// <param name="workspaceCollectionName">The Power BI workspace collection name</param>
/// <param name="workspaceId">The Power BI workspace id that contains the dataset</param>
/// <param name="datasetId">The Power BI dataset to update connection for</param>
/// <returns></returns>
static async Task UpdateConnection(string workspaceCollectionName, string workspaceId, string datasetId)
{
var chachedUsername = username;
username = userInput.EnsureParam(username, "Username", onlyFillIfEmpty: false);
if (username != chachedUsername)
{
password = userInput.EnsureParam(null, "Password", onlyFillIfEmpty: false, isPassword: true);
}
string connectionString = userInput.EnterOptionalParam("Connection String", "leave empty");
using (var client = await CreateClient())
{
// Optionally udpate the connectionstring details if preent
if (!string.IsNullOrWhiteSpace(connectionString))
{
var connectionParameters = new Dictionary <string, object>
{
{ "connectionString", connectionString }
};
await client.Datasets.SetAllConnectionsAsync(workspaceCollectionName, workspaceId, datasetId, connectionParameters);
}
// Get the datasources from the dataset
var datasources = await client.Datasets.GetGatewayDatasourcesAsync(workspaceCollectionName, workspaceId, datasetId);
// Reset your connection credentials
var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = username,
Password = password
}
};
if (datasources.Value.Count != 1)
{
Console.Write("Expected one datasource, updating the first");
}
// Update the datasource with the specified credentials
await client.Gateways.PatchDatasourceAsync(workspaceCollectionName, workspaceId, datasources.Value[0].GatewayId, datasources.Value[0].Id, delta);
}
}
public static async Task <GatewayDatasource> CreateGatewayDatasourceAsync(Gateway gateway, string server, string databaseName, UserCredentials credentials)
{
PublishDatasourceToGatewayRequest publishDatasourceRequest = new PublishDatasourceToGatewayRequest()
{
DataSourceName = databaseName,
DataSourceType = "SQL",
ConnectionDetails = "{ \"server\":\"" + server + "\"," + "\"database\":\"" + databaseName + "\"}",
CredentialDetails = GetCredentialDetails(gateway.PublicKey, credentials.userid, credentials.password)
};
using (var client = await CreateClient())
{
GatewayDatasource rtnGatewayDatasource = await client.Gateways.CreateDatasourceAsync(gateway.Id, publishDatasourceRequest);
return(rtnGatewayDatasource);
}
}
public static void UpdateConnection(string datasetId)
{
using (var client = CreatePowerBiClient())
{
// Update DataSet Connection
var connectionFormat = "Data source=tcp:{0},1433;initial catalog={1};Persist Security info=True;Encrypt=True;TrustServerCertificate=False";
var connectionString = string.Format(connectionFormat, WingtipTicketApp.Config.TenantDatabaseServer, WingtipTicketApp.Config.wingtipReporting); //, WingtipTicketApp.Config.DatabaseUser, WingtipTicketApp.Config.DatabasePassword);
var connectionParameters = new Dictionary <string, object>
{
{
"connectionString", connectionString
}
};
client.Datasets.SetAllConnections(WorkspaceCollection, WorkspaceId, datasetId, connectionParameters);
// Update DataSource Credentials
var datasources = client.Datasets.GetGatewayDatasources(WorkspaceCollection, WorkspaceId, datasetId);
var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = WingtipTicketApp.Config.DatabaseUser,
Password = WingtipTicketApp.Config.DatabasePassword
},
};
foreach (var datasource in datasources.Value)
{
// Copy over existent data
delta.ConnectionDetails = datasource.ConnectionDetails;
delta.GatewayId = datasource.GatewayId;
delta.Id = datasource.Id;
delta.DatasourceType = datasource.DatasourceType;
// Update credentials
client.Gateways.PatchDatasource(WorkspaceCollection, WorkspaceId, datasource.GatewayId, datasource.Id, delta);
}
}
}
public async Task UpdateConnectionInformation(IEnumerable <Dataset> datasets, string serverName, string databaseName, string username, string password)
{
foreach (var dataset in datasets)
{
// Optionally udpate the connectionstring details if preent
if (!string.IsNullOrWhiteSpace(serverName) && !string.IsNullOrWhiteSpace(databaseName))
{
string connectionString = String.Format("Server=tcp:{0}.database.windows.net;Database={1};Trusted_Connection=False;",
serverName,
databaseName);
var connectionParameters = new Dictionary <string, object>
{
{ "connectionString", connectionString }
};
await this._client.Datasets.SetAllConnectionsAsync(this._workspaceCollection, this._workspaceId, dataset.Id, connectionParameters);
}
// Reset your connection credentials
var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = username,
Password = password
}
};
// Get the datasources from the dataset
var datasources = await this._client.Datasets.GetGatewayDatasourcesAsync(this._workspaceCollection, this._workspaceId, dataset.Id);
foreach (var datasource in datasources.Value)
{
// Update the datasource with the specified credentials
await this._client.Gateways.PatchDatasourceAsync(this._workspaceCollection, this._workspaceId, datasource.GatewayId, datasource.Id, delta);
}
}
}
public static void UpdateConnection()
{
// Create a dev token for update
var devToken = PowerBIToken.CreateDevToken(WorkspaceCollection, WorkspaceId);
using (var client = CreatePowerBiClient(devToken))
{
// Get DataSets
var dataset = DatasetsExtensions.GetDatasets(client.Datasets, WorkspaceCollection, WorkspaceId).Value.Last();
var datasources = DatasetsExtensions.GetGatewayDatasources(client.Datasets, WorkspaceCollection, WorkspaceId, dataset.Id).Value;
// Build Credentials
var delta = new GatewayDatasource
{
CredentialType = "Basic",
BasicCredentials = new BasicCredentials
{
Username = WingtipTicketApp.Config.DatabaseUser,
Password = WingtipTicketApp.Config.DatabasePassword
}
};
// Update each DataSource
foreach (var datasource in datasources)
{
// Update the datasource with the specified credentials
GatewaysExtensions.PatchDatasource(client.Gateways, WorkspaceCollection, WorkspaceId, datasource.GatewayId, datasource.Id, delta);
}
}
}