Macro to Generate OleDbCommand for a Stored Procedure call  
Index ‹ DotNet ‹ ADO
Author Message
Dorni50
PostPosted: Top
ADO >> Macro to Generate OleDbCommand for a Stored Procedure call After searching for just such a macro/tool, I decided to write one
myself. After writing one myself, I decided to post it for others who
might be searching as I was:

Here's the macro. Since clipboard access is not available in Visual
Studio Macros, it's based off the current selection. What I do is
select my stored procedure code in Visual Studio and run this macro.
The output is put into the Output window pane, and I copy and paste it
from there. It's already saved me hours and I only wrote it
yesterday.
Sky's the limit on customizing this thing. Just please let the world
know if you make some sort of massive improvement. It's just a Macro,
so make all the changes you want to suite your coding style, etc.

By default, if uses a connection object named "conn" and it calls
"ExecuteNonQuery". Also, as you can see, it converts the T-SQL types
that I had immediate need for to OleDbDataTypes. Add as needed. Go
to town!

(I hope word-wrappnig doesn't mess this all up...)

'-----------------------------------------------------------
' Select the entire stored procedure code and run this
Sub CraeteOleDbCommandFromSp()
Dim Win As OutputWindowPane = GetActivePane()
Dim StoredProcedure As String = DTE.ActiveDocument.Selection.Text
Dim RegExProcedure As Regex = New
Regex("procedure\s+(?<Name>[A-Za-z0-9_]+)\s+(?<Parameters>[/@A-Za-z\s/(/)0-9,]+?)[^A-Za-z0-9_]as[^A-Za-z0-9_]")

Dim M As Match = RegExProcedure.Match(StoredProcedure)
Dim spName As String = M.Groups("Name").Value
Dim spParameters As String = M.Groups("Parameters").Value

Win.Clear()

Dim RegExParameters As Regex = New
Regex("@(?<Name>[A-Za-z_0-9]+)\s+(?<Type>[A-Za-z0-9]+)\s*\(?(?<Length>[0-9]*)\)?\s*(?<Output>output|OUTPUT)?")
Dim MC As MatchCollection = RegExParameters.Matches(spParameters)

Win.OutputString("#region " & spName & " call generated by
CreateOleDbCommandFromSp macro" & vbCrLf)
Win.OutputString("OleDbCommand sp = new OleDbCommand();" & vbCrLf)
Win.OutputString("sp.Connection = conn;" & vbCrLf)
Win.OutputString("sp.CommandText = ""{? = CALL " & spName & "(")
Dim ParameterCount As Integer = MC.Count()
For Each M In MC
ParameterCount = ParameterCount - 1
If ParameterCount = 0 Then
Win.OutputString("?")
Else
Win.OutputString("?,")
End If
Next
Win.OutputString(")}"";" & vbCrLf)

Win.OutputString("sp.Parameters.Add(""@RetVal"",OleDbType.Integer);"
& vbCrLf)
Win.OutputString("sp.Parameters[""@RetVal""].Direction =
ParameterDirection.Output;" & vbCrLf)

For Each M In MC
Dim OleDbDataType As String = M.Groups("Type").Value
Select Case OleDbDataType.ToUpper()
Case "BIGINT"
OleDbDataType = "BigInt"
Case "INT"
OleDbDataType = "Integer"
Case "VARCHAR"
OleDbDataType = "VarChar"
Case "SMALLINT"
OleDbDataType = "Smallint"
Case "TINYINT"
OleDbDataType = "TinyInt"
Case "UNIQUEIDENTIFIER"
OleDbDataType = "Guid"
Case Else
OleDbDataType = M.Groups("Type").Value
End Select
Win.OutputString("sp.Parameters.Add(""@" &
M.Groups("Name").Value & """,OleDbType." & OleDbDataType)
If Not M.Groups("Length").Value = "" Then
Win.OutputString("," & M.Groups("Length").Value)
End If
Win.OutputString(");" & vbCrLf)
Next

For Each M In MC
If Not M.Groups("Output").Value = "" Then
Win.OutputString("sp.Parameters[""@" &
M.Groups("Name").Value & """].Direction = ParameterDirection.Output;"
& vbCrLf)
Else
Win.OutputString("sp.Parameters[""@" &
M.Groups("Name").Value & """].Value = " & M.Groups("Name").Value & ";"
& vbCrLf)
End If
Next

Win.OutputString("sp.Connection.Open();" & vbCrLf & "try" & vbCrLf
& "{" & vbCrLf)
Win.OutputString(vbTab & "sp.ExecuteNonQuery();" & vbCrLf)
Win.OutputString(vbTab & "if(
Convert.ToInt32(sp.Parameters[""@RetVal""].Value) != 0 )" & vbCrLf)
Win.OutputString(vbTab & vbTab & "throw new
ApplicationException(String.Format(""Execute of " & spName & " failed
with {0}"",Convert.ToInt32(sp.Parameters[""@RetVal""].Value)));" &
vbCrLf)
Win.OutputString("}" & vbCrLf & "finally" & vbCrLf & "{" & vbCrLf
& vbTab & "sp.Connection.Close();" & vbCrLf & "}" & vbCrLf)

Win.OutputString("#endregion" & vbCrLf)

End Sub

Function GetOutputWindow() As OutputWindow
Return DTE.Windows.Item(Constants.vsWindowKindOutput).Object()
End Function

Function GetActivePane() As OutputWindowPane
Return GetOutputWindow.ActivePane
End Function

DotNet419  
 
 
 
Index ‹ DotNet ‹ ADO