Stream Dataset for local SQL Server Database Query

raguyazhin

It would require quite some coding skill.

 

You can find the API sample to push data to your stream dataset in Power BI service. Then you can query your databases and send data via a http request in any language you master. Eg in this case, I test it in Powershell.

 

$SqlServer = 'yourSQLserverName';
$SqlDatabase = 'databaseName';

$SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $SqlServer, $SqlDatabase;
$SqlQuery = "SELECT * FROM streamData;";

$SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandText = $SqlQuery;
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString;
$SqlCommand.Connection = $SqlConnection;

$SqlConnection.Open();
$SqlDataReader = $SqlCommand.ExecuteReader();

##you would find your own endpoint in the Power BI service
$endpoint = "https://api.powerbi.com/beta/72f98xxxxxxx011db47/datasets/d685398b-86xxxxx47f95a9b20e4/rows?key=E5sABoNsS3uxxxxcdwr6QVpJSAgOA6juxxxxxvNYRBjtuLfIg%3D%3D"


#Fetch data and write out to files
while ($SqlDataReader.Read()) {
    $payload =  
    @{
    "product" =$SqlDataReader['product']
    "sales" =$SqlDataReader['sales']
    "datetime" =$SqlDataReader['datetime']
    } 
    Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
}


$SqlConnection.Close();
$SqlConnection.Dispose();