SQL and sending emails with Powershell

Run SQL Command

Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM Table" -ConnectionString "YourConnectionString"

Invoke commands in a script file and save the output in a text file

Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" | Out-File -FilePath "C:\ScriptFolder\TestSqlCmd.rpt"

Invoke sql script and write the results into CSV file.

Invoke-Sqlcmd -InputFile "C:\Users\Mehmet\Desktop\Users.sql" -ConnectionString "connection-string" | export-csv "C:\Users\Mehmet\Desktop\SqlResults.csv" 

Invoke sql script and write the results into JSON file.

Invoke-Sqlcmd -InputFile "C:\Users\Mehmet\Desktop\Users.sql" -ConnectionString "connection-string" | ConvertTo-Json | export-csv "C:\Users\Mehmet\Desktop\SqlResults.csv" 

Invoke a script and pass in variable values from a string

C:\> $StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
C:\> Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2" -Variable $StringArray

Send Mail

$PSEmailServer = "smtp-mail-server"
$mypassword = ConvertTo-SecureString "your-password" -AsPlainText -Force
$mycredential = New-Object System.Management.Automation.PSCredential ("your-username", $secpasswd)

Send-MailMessage -From "from@mehmet.es" -To "to@mehmet.es" -Subject "Test email" -Body "This is a test email" -Credential $mycredential

A scenario: Retrieve data from a database and send it as a CSV file.

$CsvFileName = "./Users.csv"
$SqlScriptFile = "Users.sql"
$ConnectionString = "your-connection-string"
$SmtpAddress = "your-smtp"
$UserName = "username"
$Password = "password"
$FromEmail = "from"
$ToEmail = "to"
$EmailSubject = "your-subject"
$EmailBody = "hi there"

$PSEmailServer = $SmtpAddress
$mypassword = ConvertTo-SecureString $Password -AsPlainText -Force
$mycredential = New-Object System.Management.Automation.PSCredential ($UserName, $mypassword)

# Run sql command and create the file
Invoke-Sqlcmd -InputFile $SqlScriptFile  -ConnectionString $ConnectionString  | export-csv $CsvFileName 

# Send the file 
Send-MailMessage -From $FromEmail -To $ToEmail -Subject $EmailSubject -Body $EmailBody -Credential $mycredential -Attachments $CsvFileName

# Remove the file
Remove-Item $CsvFileName

"DONE!"

Note: Invoke-Sqlcmd does not work on Azure WebJobs, it is not installed.

#powershell #powershell-send-email #powershell-sql-commands #powershell-sql-to-csv

Simple self-join with LINQ and SQL

Let’s say we have a table in which we store the various test results of a persons.

PersonId TestId Result
1 TOEFL 5
1 IELTS 10
2 TOEFL 10
2 IELTS 6
3 TOEFL 2
4 IELTS 4

And assuming that it has been queried and stored in a list of objects.

results= db.Query<PersonResult>(SELECT * FROM Results).ToList();

(This is a Dapper query)

To get the result of one person in one row, we can do a self-join with LINQ as following:

var modifiedList = from r1 in theList.Where(x => x.TestId == "IELTS").ToList()
                   join r2 in theList.Where(x => x.TestId == "TOEFL").ToList()
                   r1.PersonId equals r2.PersonId 
                   select new Person
                           {
                                   PersonId= r1.PersonId
                                   TestId = r1.TestId,
                                   ResultToefl= r2.Result
                                   ResultIelts = r1.Resul
                            }

Or another way, I think better is to do is with SQL in the server:

SELECT 
t1.PersonId, t1.Result as TOEFLResult, t2.Items AS IELTSResult
FROM Results t1, Results t2
WHERE t1.PersonId= t2.PersonId
AND t1.TestId = 'TOEFL' and t2.TestID='IELTS'
ORDER BY t1.CompletionDate DESC

#linq #sql #self-join #combine-two-rows-sql #merge-two-row-sql-linq #merge-rows

Query rows between two times


select * from log_table
where logtime > = to_date('2015-06-01 14:12:00', 'YYYY-MM-DD HH24:MI:SS')
  AND logtime < = to_date('2015-06-30 14:20:00', 'YYYY-MM-DD HH24:MI:SS') 
  ORDER BY NO