[Powershell] Query Database by using a powershell function

Here is a usefull function to query a DB from a powershell script.

1. function RunQueryOnDB ($DBServer, $DBCatalog, $DBQuery, $DBUid, $DBUidPWD)
2. {
3. $SqlServer = $DBServer
4. $SqlCatalog = $DBCatalog
5. $SqlQuery = $DBQuery
6. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
7. $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Uid = $DBUid; Pwd = $DBUidPWD"
8. $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
9. $SqlCmd.CommandText = $SqlQuery
10. $SqlCmd.Connection = $SqlConnection
11. $SQlCmd.CommandTimeout = 120
12. $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
13. $SqlAdapter.SelectCommand = $SqlCmd
14. $DSet = New-Object System.Data.DataSet
15. $SqlAdapter.Fill($DSet) | out-null
16. $SqlConnection.Close()
17. $DSet.Tables[0]
18. }

How to use it :

1. # --------- set the DB Server and Catalog
2. $DBServerName = "MyDBServer"
3. $DBName = "MyDBNAme"
4. # --------- Set the Query
5. $Query = "Select PrincipalName from MyDB"
6. # --------- Set the credentials to use to query the DB
7. $Account = "MyAccount"
8. $pwd = "MyPassword"
9. # --------- Variable initialization
10. $SCOMGrpName = "MyGroupName" 
11. # --------- Query The DB
12. $Result = RunQueryOnDB $DBServerName $DBName $Query $Account $pwd

The script wil return a table $Result that can also be re-used.

This posting is provided "AS IS" with no warranties.