CODEWITHSUNDEEP
powershell
Eric
Eric

Reputation: 941

Powershell SQL Query to variables

Trying to use SQL results as variables. This will return the results to the host, but I would like to use each value as a variable. 

$job = "22940"
$SQLServer = "my-sql" #use Server\Instance for named SQL instances! 
$SQLDBName = "MyDatabase"
$SqlQuery = "DECLARE @Job VARCHAR(5);
SET @Job = '$job'
SELECT        dbo.Job.Job, dbo.Job.Customer, 
              dbo.Job.Note_Text, 
              dbo.User_Values.Date1 AS 'Setup Date', 
              dbo.User_Values.Date2 AS 'Due Date'
FROM            dbo.Job INNER JOIN
                         dbo.User_Values ON dbo.Job.User_Values = dbo.User_Values.User_Values
WHERE        (dbo.Job.Job = @Job) AND (dbo.Job.User_Values = dbo.User_Values.User_Values)"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

clear

$DataSet.Tables[0]

How can I put each column into a variable that I can use for something else?

Upvotes: 0

Views: 7457

Answers (2)

Ed Callahan
Ed Callahan

Reputation: 189

I work with data tables directly. You should also look into handling parameters correctly. It helps with handling special characters in the variable value. And, it protects from SQL Injection Attacks:

$Sql = 'select top 100 first_name, Last_name from person where last_name=@lastname'
$Database = 'XXX'
$Server = 'XXX'

$LastName = 'Jones'

$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$Connection.Open()

$cmd = new-object system.Data.SqlClient.SqlCommand($Sql, $Connection)

$null = $cmd.Parameters.AddWithValue('@lastname', $LastName)

$DataTable = New-Object System.Data.DataTable
$SqlDataReader = $cmd.ExecuteReader()
$DataTable.Load($SqlDataReader)

$Connection.Close()
$Connection = $null

$DataTable.Rows.Count

foreach ($r in $DataTable.Rows)
{
    $fn = $r.first_name
    $ln = $r.last_name    

    "$fn $ln"
}

Upvotes: 2

Eric
Eric

Reputation: 941

Not sure if this is really the correct way but it does give me the results I am looking for. Looked through some of my notes on working with CSV files and I added the foreach at the end. The above comments from @notjustme and @jcoehoorn got me looking in the right direction. Thanks

$job = "22940"
$SQLServer = "my-sql" #use Server\Instance for named SQL instances! 
$SQLDBName = "MyDatabase"
$SqlQuery = "DECLARE @Job VARCHAR(5);
SET @Job = '$job'
SELECT        dbo.Job.Job, dbo.Job.Customer, 
              dbo.Job.Note_Text, 
              dbo.User_Values.Date1 AS 'Setup Date', 
              dbo.User_Values.Date2 AS 'Due Date'
FROM            dbo.Job INNER JOIN
                         dbo.User_Values ON dbo.Job.User_Values = dbo.User_Values.User_Values
WHERE        (dbo.Job.Job = @Job) AND (dbo.Job.User_Values = dbo.User_Values.User_Values)"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

clear

foreach($Job in $DataSet.Tables[0])
     {
     $MyJobNumber = $Job.Job
     $MyCustomer = $Job.Customer
     $MyNotes = $Job.Note_Text
     $MySetupDate = $Job.Setup_Date
     $MyDueDate = $Job.Due_Date
     }

Upvotes: 0

Related Questions