PoshCode Logo PowerShell Code Repository

New-StoredProcFunction by Steven Murawski 34 months ago
View followups from Steven Murawski | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1007"></script>download | new post

Create functions that wrap chosen stored procedures and surface their input parameters as function parameters. Output parameters are returned in a custom object with a property name for each output parameter.

  1. # New-StoredProcFunction.ps1
  2. # Steven Murawski
  3. # http://blog.usepowershell.com
  4. # 04/08/2009
  5.  
  6. # Example: ./New-StoredProcFunction.ps1 'DataSource=MySqlServer;Database=Northwind;User=AnythingButSa;Password=abc123' sp_createnewcustomer
  7. # Example 'sp_createnewcustomer | ./New-StoredProcFunction.ps1 'DataSource=MySqlServer;Database=Northwind;User=AnythingButSa;Password=abc123'
  8.  
  9. param($ConnectionString, [String[]]$StoredProc= $null)
  10. BEGIN
  11. {
  12.         if ($StoredProc.count -gt 0)
  13.         {
  14.                 $StoredProc | New-StoredProcFunction $ConnectionString
  15.         }
  16.         function Get-StoredProcText()
  17.         {
  18.                 param ($ProcName, $ConnectionString)
  19.                 $query = @'
  20. SELECT ROUTINE_DEFINITION
  21. FROM INFORMATION_SCHEMA.ROUTINES
  22. WHERE ROUTINE_BODY = 'SQL' AND ROUTINE_NAME LIKE '$_'
  23. '@
  24.                 $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
  25.                 $command = New-Object System.Data.SqlClient.SqlCommand $query,$connection
  26.                 $connection.Open()
  27.                 $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
  28.                 $dataset = New-Object System.Data.DataSet
  29.                 [void] $adapter.Fill($dataSet)
  30.                 $result = $dataSet.Tables | ForEach-Object {$_.Rows}
  31.                 $connection.Close()
  32.                
  33.                 return $Result.ROUTINE_DEFINITION
  34.         }
  35.         function Get-FunctionParameter()
  36.         {
  37.                 param ($Text)
  38.                 [regex]$EndRegex = '\)\s+AS'
  39.                 [regex]$ParamRegex = '@(?<Parameter>\w+?)\s+(?<DataType>\w+(\(\d+\))*)(,|\s+)*(?<Output>out)*'
  40.                 $ParamStart = $Text.indexof('(')
  41.                 $ParamEnd = $EndRegex.Match($text).index
  42.                 $ParamText = $Text.Substring($ParamStart, ($ParamEnd-$ParamStart))
  43.                 $RegMatches = $ParamRegex.matches($ParamText)
  44.                 foreach ($RegMatch in $RegMatches)
  45.                 {
  46.                         $Parameter = "" | Select-Object Name, DataType, IsOutput
  47.                         $Parameter.Name = $RegMatch.Groups[3].value
  48.                         $Parameter.DataType = $RegMatch.Groups[4].Value
  49.                         [bool]$Parameter.IsOutput = $RegMatch.Groups[5].Value
  50.                         $Parameter
  51.                 }
  52.         }
  53. }
  54. PROCESS
  55. {
  56.         if ($_ -ne $null)
  57.         {
  58.                 $FunctionName = $_
  59.                 $StoredProcText = Get-StoredProcText $FunctionName $ConnectionString
  60.                 $Parameters = Get-FunctionParameter $StoredProcText
  61.                
  62.                 [String[]]$InputParamNames = $Parameters | where {-not $_.IsOutput} | ForEach-Object {$_.Name}
  63.                 [String[]]$OutputParameterNames = $Parameters | Where-Object {$_.IsOutput} | ForEach-Object {$_.Name}
  64.                
  65.                 $ScriptText = ' '
  66.                
  67.                 if ($InputParamNames.count -gt 0)
  68.                 {
  69.                         $OFS = ', $'
  70.                         $ScriptText += 'param (${0})' -f $InputParamNames
  71.                         $ScriptText += "`n"
  72.                         $OFS = ', '
  73.                 }
  74.                
  75.                 $BodyTemplate = @'
  76. $connection = New-Object System.Data.SqlClient.SqlConnection('{0}')
  77. $command = New-Object System.Data.SqlClient.SqlCommand('{1}', $connection)
  78. $command.CommandType = [System.Data.CommandType]::StoredProcedure
  79.  
  80. '@
  81.                 $ScriptText += $BodyTemplate -f $ConnectionString, $FunctionName
  82.                 if ( ($Parameters -ne $null) -or ($Parameters.count -gt 1) )
  83.                 {
  84.                        
  85.                         if ($OutputParameterNames.count -gt 0)
  86.                         {
  87.                                 $ReturnText = ""
  88.                                 $CommandOutput = "" | select $OutputParameterNames
  89.                         }
  90.                         #Add the parameters    
  91.                         foreach ($param in $Parameters)
  92.                         {
  93.                                 if ($param.datatype -match '(?<type>\w+)\((?<nbr>\d+)\)')
  94.                                 {
  95.                                         $ParamTemplate = '$command.Parameters.Add("@{0}", "{1}", {2})  | out-null '
  96.                                         $ScriptText += "`n"
  97.                                         $ScriptText += $ParamTemplate -f $param.name, $matches.type, $matches.nbr
  98.                                 }
  99.                                 else
  100.                                 {
  101.                                         $ParamTemplate = '$command.Parameters.Add("@{0}", "{1}")  | out-null '
  102.                                         $ScriptText += "`n"
  103.                                         $ScriptText += $ParamTemplate -f $param.name, $param.datatype
  104.                                 }
  105.                                
  106.                                 if ($param.IsOutput)
  107.                                 {
  108.                                         $ScriptText += "`n"
  109.                                         $ScriptText += '$command.Parameters["@{0}"].Direction = [System.Data.ParameterDirection]::Output ' -f $param.Name
  110.                                         $ReturnText += "`n"
  111.                                         $ReturnText += '$CommandOutput.{0} = $command.Parameters["@{0}"].Value' -f $param.name
  112.                                 }
  113.                                 else
  114.                                 {
  115.                                         $ScriptText += "`n"
  116.                                         $ScriptText += '$command.Parameters["@{0}"].Value = ${0} ' -f $param.name
  117.                                 }
  118.                         }                              
  119.                 }
  120.                
  121.                 $ScriptText += "`n"
  122.                 $ScriptText += @'
  123. $connection.Open()  | out-null
  124. $command.ExecuteNonQuery() | out-null
  125.  
  126. '@     
  127.                 if ($OutputParameterNames.count -gt 0)
  128.                 {
  129.                         $ScriptText += $ReturnText
  130.                 }
  131.                
  132.                 $ScriptText += @'
  133.                
  134. $connection.Close() | out-null
  135. return $CommandOutput
  136. '@
  137.                
  138.                 #$ScriptText
  139.                 Set-Item -Path function:global:$FunctionName -Value $scripttext
  140.         }
  141. }

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me