求基于Power BI Push Dataset从SQL Server实时推送数据的示例代码

Got it, let's walk through this step by step—using Power BI's Push Datasets to send real-time data from SQL Server is totally achievable, and I'll give you concrete code and setup instructions to make it work.

Before writing any code, you need to set up the destination dataset in Power BI:

• Go to your Power BI workspace, click New > Dataset > API
• Define your table schema to match the data you want to pull from SQL Server (e.g., if you're pushing sales data, include columns like SaleID, ProductName, SaleAmount, SaleDateTime)
• Once created, note down two key values from the dataset's settings:
  • Group ID (your workspace ID)
  • Dataset ID
  • You'll also need the table name from your dataset (e.g., Sales)

To push data via the API, you need an Azure AD app registration for authentication:

1. Go to Azure Portal > App Registrations > New Registration
2. Name your app, set the redirect URI to https://login.microsoftonline.com/common/oauth2/nativeclient (for desktop apps)
3. Under Certificates & Secrets, create a new client secret and save it (you won't see it again!)
4. Note down your Tenant ID, Client ID, and the client secret you just created

Here's a quick helper to fetch an access token for the Power BI API:

using System.Net.Http;
using System.Threading.Tasks;
using System.Collections.Generic;

public async Task<string> GetPowerBIAccessToken(string tenantId, string clientId, string clientSecret)
{
    var tokenEndpoint = $"https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token";
    using var client = new HttpClient();
    
    var formData = new Dictionary<string, string>
    {
        ["grant_type"] = "client_credentials",
        ["client_id"] = clientId,
        ["client_secret"] = clientSecret,
        ["scope"] = "https://analysis.windows.net/powerbi/api/.default"
    };
    
    var response = await client.PostAsync(tokenEndpoint, new FormUrlEncodedContent(formData));
    response.EnsureSuccessStatusCode();
    
    var tokenData = await response.Content.ReadFromJsonAsync<Dictionary<string, object>>();
    return tokenData["access_token"].ToString();
}

Now, combine SQL Server data retrieval with the Power BI push API. Below is a complete example that pulls the latest sales data from SQL Server and pushes it to your Push Dataset:

using System;
using System.Data.SqlClient;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using System.Collections.Generic;
using Newtonsoft.Json;

// Match this class to your Power BI table schema
public class SalesData
{
    public int SaleID { get; set; }
    public string ProductName { get; set; }
    public decimal SaleAmount { get; set; }
    public DateTime SaleDateTime { get; set; }
}

public class PowerBiPushService
{
    private readonly string _sqlConnString;
    private readonly string _groupId;
    private readonly string _datasetId;
    private readonly string _tableName;
    private readonly string _tenantId;
    private readonly string _clientId;
    private readonly string _clientSecret;

    public PowerBiPushService(string sqlConnString, string groupId, string datasetId, string tableName, string tenantId, string clientId, string clientSecret)
    {
        _sqlConnString = sqlConnString;
        _groupId = groupId;
        _datasetId = datasetId;
        _tableName = tableName;
        _tenantId = tenantId;
        _clientId = clientId;
        _clientSecret = clientSecret;
    }

    // Fetch latest data from SQL Server (adjust query to fit your use case)
    private async Task<List<SalesData>> GetLatestSalesData()
    {
        var salesList = new List<SalesData>();
        // Example: Pull data added in the last hour
        var query = @"SELECT SaleID, ProductName, SaleAmount, SaleDateTime 
                      FROM Sales 
                      WHERE SaleDateTime >= DATEADD(HOUR, -1, GETDATE())";

        using var conn = new SqlConnection(_sqlConnString);
        await conn.OpenAsync();
        using var cmd = new SqlCommand(query, conn);
        using var reader = await cmd.ExecuteReaderAsync();
        
        while (await reader.ReadAsync())
        {
            salesList.Add(new SalesData
            {
                SaleID = reader.GetInt32(0),
                ProductName = reader.GetString(1),
                SaleAmount = reader.GetDecimal(2),
                SaleDateTime = reader.GetDateTime(3)
            });
        }
        return salesList;
    }

    // Push data to Power BI Push Dataset
    public async Task PushDataToPowerBi()
    {
        var accessToken = await GetPowerBIAccessToken(_tenantId, _clientId, _clientSecret);
        var salesData = await GetLatestSalesData();

        if (salesData.Count == 0)
        {
            Console.WriteLine("No new data to push.");
            return;
        }

        // Format payload to match Power BI's required structure
        var payload = new { rows = salesData };
        var jsonPayload = JsonConvert.SerializeObject(payload);

        var apiUrl = $"https://api.powerbi.com/v1.0/myorg/groups/{_groupId}/datasets/{_datasetId}/tables/{_tableName}/rows";
        
        using var client = new HttpClient();
        client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", accessToken);
        
        var response = await client.PostAsync(apiUrl, new StringContent(jsonPayload, Encoding.UTF8, "application/json"));
        
        if (response.IsSuccessStatusCode)
        {
            Console.WriteLine($"Successfully pushed {salesData.Count} rows to Power BI.");
        }
        else
        {
            var errorDetails = await response.Content.ReadAsStringAsync();
            Console.WriteLine($"Push failed: {response.StatusCode} - {errorDetails}");
        }
    }

    // Reuse the token fetch helper from Step 2
    private async Task<string> GetPowerBIAccessToken(string tenantId, string clientId, string clientSecret)
    {
        var tokenEndpoint = $"https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token";
        using var client = new HttpClient();
        
        var formData = new Dictionary<string, string>
        {
            ["grant_type"] = "client_credentials",
            ["client_id"] = clientId,
            ["client_secret"] = clientSecret,
            ["scope"] = "https://analysis.windows.net/powerbi/api/.default"
        };
        
        var response = await client.PostAsync(tokenEndpoint, new FormUrlEncodedContent(formData));
        response.EnsureSuccessStatusCode();
        
        var tokenData = await response.Content.ReadFromJsonAsync<Dictionary<string, object>>();
        return tokenData["access_token"].ToString();
    }
}

// Example usage
class Program
{
    static async Task Main(string[] args)
    {
        var service = new PowerBiPushService(
            sqlConnString: "Server=YOUR_SQL_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PWD;",
            groupId: "YOUR_POWERBI_WORKSPACE_ID",
            datasetId: "YOUR_POWERBI_DATASET_ID",
            tableName: "Sales",
            tenantId: "YOUR_AZURE_TENANT_ID",
            clientId: "YOUR_AZURE_APP_CLIENT_ID",
            clientSecret: "YOUR_AZURE_APP_CLIENT_SECRET"
        );
        
        await service.PushDataToPowerBi();
    }
}

• Real-Time Trigger: To make this truly real-time, you can either:
  • Set up a recurring task (e.g., Windows Task Scheduler or Azure Functions) to run the code every X minutes/seconds
  • Use a SQL Server trigger to run the push code whenever new data is inserted (be cautious with triggers to avoid performance bottlenecks)
• Batch Sizes: Power BI recommends pushing batches of 10,000 rows or fewer per API call to avoid throttling
• Error Handling: Add retry logic (use libraries like Polly) to handle temporary API failures
• Security: Store credentials (SQL Server, Azure AD) in a secure vault (e.g., Azure Key Vault) instead of hardcoding them
• Dataset Limits: Push Datasets have row limits—check Power BI's official docs for current limits and consider purging old data if needed

内容的提问来源于stack exchange，提问作者Ravi