Sample-SQL-Actions – SQL through SMA

<#
   Sample-SQL-Actions demonstrates how to create a SQL database connection
   and use this connection to execute commands on the database.

   Before running this workflow, you must create a Management Service (Windows
   Azure Pack) variable named 'SQL_Connectionstring' with the connectionstring to the SQL server.
   Example: "Server=SQL1;Database=TestDB;User ID=SQLuser;Password=Passw0rd;"
#>

workflow Sample-SQL-Actions
{
   
$SQL_Connectionstring = Get-AutomationVariable -Name 'SQL_Connectionstring'

inlinescript {
    $tablename = "TestTable"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $Using:SQL_Connectionstring

    #Open the SQL connection
    Try{
        $connection.Open()
       }
    Catch{
    Write-Error "Cannot open connection to SQL server with connectionstring $Using:SQL_Connectionstring"
         }

    #Create table if not exists in database
    $createtable = "if not exists (select * from sysobjects where name='$tablename') create table $tablename(Name Char(25),Website Char(25))"
    $Command = New-object system.Data.SqlClient.SqlCommand($createtable,$connection)
    $Command.ExecuteNonQuery()

    #Insert content in table
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = "INSERT INTO $tablename(name,Website) VALUES ('Darryl','www.DarrylvanderPeijl.nl');"
    $Command.ExecuteNonQuery()

    #Update content in database
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = "UPDATE $tablename set Name = 'Darryl van der Peijl' WHERE Name = 'Darryl'"
    $Command.ExecuteNonQuery()


    #Delete all content in table
    #$Command = New-Object System.Data.SQLClient.SQLCommand
    #$Command.Connection = $connection
    #$Command.CommandText =     "delete from $tablename;"
    #$Command.ExecuteNonQuery()


    #Close the SQL connection
    $connection.Close()
    }
}