Pronunciation / adjective
Bringing something good and unforseen.
    Skip Navigation Links > Code Repository
   

Macro - Auto-Generate SQL Parameters



 
Return

Macro - Auto-Generate SQL Parameters

Would you like to auto-generate your SQL parameters just by highlighting a list of variables?

Here you go:

To use the macro below copy it into your macro file. Then highlight one or more private variables and double click the "CreateSqlParameters" macro in your macro list.

The naming convention used for your private variables should use a leading underscore.
  1. Private _MyString As String
  2. Private _MyInt As Int32

  1. Public Sub CreateSqlParameters()
  2. Dim TS As TextSelection = GetTextSelection()
  3. Dim ParameterDefinitionInsertion As New System.Text.StringBuilder
  4. Dim Line As String
  5. Dim Lines() As String = TS.Text.Split(vbNewLine)
  6. Dim ReturnedText As String
  7. Dim LineCount, LineLoop As System.Int32
  8. LineCount = Lines.GetUpperBound(0)
  9. For LineLoop = 0 To LineCount
  10. Line = Lines.GetValue(LineLoop)
  11. ReturnedText = GetSqlParameterDefinitionInsertion(Lines.GetValue(LineLoop))
  12. ParameterDefinitionInsertion.Append(ReturnedText)
  13. ParameterDefinitionInsertion.Append(InsertLine(LineLoop, LineCount, ReturnedText))
  14. Next
  15. TS.EndOfLine()
  16. TS.LineDown(False, 1)
  17. TS.EndOfLine()
  18. TS.NewLine(2)
  19. TS.StartOfLine()
  20. TS.Insert("Dim SqlCommand As New SqlClient.SqlCommand")
  21. TS.NewLine(2)
  22. TS.StartOfLine()
  23. TS.Insert(ParameterDefinitionInsertion.ToString)
  24. End Sub
  25. Private Function GetSqlParameterDefinitionInsertion(ByVal text As String) As String
  26. Dim Words() As String = text.Trim.Split()
  27. ' Check if the line is a comment line.
  28. If Words(0).IndexOf("'") = 0 Then
  29. '---This is a comment line include it.
  30. Return text
  31. Else
  32. If Words.Length > 3 Then
  33. '---This is a variable declaration
  34. Return "SqlCommand.Parameters.Add(""@" & Words(1).Trim("_") & """, SqlDbType, 0).Value = " & Words(1)
  35. End If
  36. End If
  37. Return ""
  38. End Function
  39. Private Function InsertLine(ByVal lineLoop As Integer, ByVal lineCount As Integer, ByVal returnedText As String) As String
  40. If (lineLoop < lineCount) And (returnedText > "") Then
  41. Return vbNewLine
  42. End If
  43. Return ""
  44. End Function
  45. Private Function GetTextSelection() As EnvDTE.TextSelection
  46. Return DTE.ActiveDocument.Selection
  47. End Function


Return
Microsoft Certified Professional   © 2017 Fortunate.  All rights reserved.
contact: justin@aboutfortunate.com