-
Notifications
You must be signed in to change notification settings - Fork 0
/
ExecuteQueryResults.ps1
71 lines (61 loc) · 2.42 KB
/
ExecuteQueryResults.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<#
.SYNOPSIS
Execute the Query Statements, Store procedure with parameter and returns a result.
.DESCRIPTION
Execute the Query Statements, Store procedure with parameter and returns a results either multiple tables or single table based on the statement.
.PARAMETER Name*
Required. Name should be either Store Procedure or you can directly pass the SQL query statement.
.PARAMETER Parameters
Optional. Parameters is array object. Default value is blank array object.
.PARAMETER Procedure
Optional. If $false Execute the Procedure else Execute the Query Statement. Default value is $true.
.PARAMETER Stage
Optional. If $false create a connection for the Production database. Default value is $true.
.OUTPUTS
System.DataTables
.EXAMPLE
ExecuteQueryResults -Name "GetUserList" -Parameters @{objname="value"} -Procedure $true
#>
function ExecuteQueryResults() {
[cmdletbinding()]
Param (
[Parameter(Mandatory = $true, Position = 0)]
$Name,
$Parameters = @{ },
$Procedure = $true,
$Stage = $true
)
Begin {
$sqlConnection = Open-SqlConnection $Stage
}
Process {
try {
[System.Data.SqlClient.SqlCommand]
$sqlCommand = New-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 30
$sqlCommand.Connection = $sqlConnection;
foreach ($p in $Parameters.Keys) {
[Void] $sqlCommand.Parameters.AddWithValue("@$p", $Parameters[$p])
}
$sqlCommand.CommandText = $Name
if ($IsProcedure) {
$sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
}
$sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sqlCommand)
$dataSet = New-Object System.Data.DataSet
$sqlDataAdapter.Fill($dataSet) | Out-Null
return $dataSet.Tables
}
catch [System.Data.SqlClient.SqlException] {
Write-Exception -ErrorObj $_ -Message "Throws an '[System.Data.SqlClient.SqlException]' in 'ExecuteNonQuery'" -Stop $true
}
catch {
Write-Exception -ErrorObj $_ -Message "Throws an SqlException in 'ExecuteNonQuery'" -Stop $true
}
finally {
Close-SqlConnection -Connection $sqlConnection
}
}
End {
}
}