• Article
Stored Procedure Wizard in Visual Basic Boosts Productivity
Ken Spencer
Download the code for this article:Serving0101.exe (82KB)
T

his month I'll take a look at one way you can quickly improve your productivity as a developer. Developers frequently find themselves repeating the same tasks over and over again. Recently, I asked an audience of developers how many find themselves writing essentially the same ActiveX® Data Objects (ADO) code over and over just to extract something from a database. Most of the audience could relate to that experience.
      To help streamline the creation of applications that use ADO and stored procedures, Microsoft created the ADO Stored Procedure Add-In for Visual Basic®. It writes the ADO code necessary to call a stored procedure that you have selected. This is helpful because creating calls to stored procedures in ADO usually requires setting up an ADO Command object and the appropriate Parameters collection. The add-in does a nice job of creating the ADO code, but what happens when you need to work with your own objects to handle the database operations?
      FMStocks, the Fitch & Mather demo, serves as a good example (see https://www.fmstocks.com). It has the ADOHelper class, which contains the various Run functions that do the work for you. You simply need to set up the proper calls to use this packaged code. But even using the Run functions requires you to know the various parameters for each stored procedure, plus the type and size of the parameters, so I thought there must be an easier way to use database objects.
      I decided to create a new add-in that would automate the use of the FMStocks-style Run functions, using the ADO Stored Procedure Add-In as the foundation. The add-in can help developers in two ways. First, it can allow you to select a stored procedure to execute. Second, it can allow you to select the method to use from the database object and provide a way to set various options for the method call. Then the add-in can dump the correct code into the current cursor location in the Visual Basic editor, and you can call the right method and stored procedure in a matter of seconds.

Modifying the Sample Add-in

      Let's walk through the steps for modifying the sample add-in to work with a database object. After downloading the add-in (which is downloadable from the Fitch & Mather site), I copied all of its files into a new folder. Then I renamed the project SprocWizardMP.vbp. Next, I opened it in Visual Basic and changed the project name to SprocWizardMP, and the project's description to Stored Procedure MP Generator Wizard.
      At this point, I opened frmAddin and began modifying the user interface. Figure 1 shows the completed form. The textbox in the upper-left has its text value set to GetDSN(), representing the function I use to return a DSN string. This return value is passed as the last parameter to each of the Run methods, thus allowing the use of a unique DSN for each method call. The GetDSN function must be defined in the module or class that calls the Run method. You can enter a DSN string there, or use the GetDSN function to return the DSN string.

Figure 1 The ADO Stored Procedure Add-in
Figure 1The ADO Stored Procedure Add-in

      The listbox on the right side of the form contains the method names from the database object. They are entered as a static list. You should modify this list to contain any functions that you plan to use with your objects.
      The cboSproc combobox is dynamically loaded with the stored procedure names by the LoadSprocList procedure (more on this in a minute). The cboDSNForSproc combobox contains the DSNs that are used to connect to the database containing the stored procedures in cboSproc. This control is loaded from the LoadDSNList procedure. The DSNs are contained in DataAccess.txt under c:\database. Its contents are:
  Accounts DSN=Accounts
Pubs DSN=Pubs
SessionData  DSN=SessionData
The first entry on each line is the DSN name that will go in the combobox. The second entry, which starts with DSN=, is the actual DSN. You can use a DSN name like this sample does, or you can use a complete connection string.
      The first row of checkboxes on the form control the code that is generated by the add-in. You can select which features to use on a case-by-case basis as you use the add-in. For instance, some stored procedures return recordsets, so the Returns Rows box should be checked. For stored procedures that only return a value and not rows, the Include Return Parameter box, not the Returns Rows box, should be checked.
      Checking the Include Variable Declarations box will cause Dim statements to be generated for the recordset and return variable. The default variable name used for the recordset is rs; it's shown in the first textbox on the last row. The second textbox contains the default variable name used as a reference to the database object. You can override the defaults at runtime.
      The row of buttons across the bottom of the dialog control the add-in's action. The Apply button generates code at the current cursor location. The second button, Preview, generates code and places it in a preview form. The Load Sprocs button loads the stored procedures combobox, while the Load DSNs button loads the DSN combobox. The stored procedures are automatically loaded when a DSN is selected. The DSNs are automatically loaded when the first add-in form loads. These two buttons are implemented for those times when either the DSNs or the stored procedures need to be reloaded without reloading the add-in. For instance, if the database has new stored procedures, you can click the Load Sprocs button to load them.

Digging into the Code

      The code in the GenADOCode function does almost all of the work in this add-in. This function creates the text to insert into the Visual Basic editor and returns it as a return value. The text is stored in three variables as it is created. StrHeader contains the header lines for the code block, strCode contains the actual code, and strFooter contains the footer lines.
      The first section of code dimensions the variables for the procedure. Next, the code defines the header and footer strings:
  strHeader = "' Sproc Wizard Code Generation START - " & _
    vbCrLf & vbCrLf
strFooter = vbCrLf & "'Sproc Wizard Code Generation END â€"" _
    & vbCrLf
Notice the use of vbCrLf here. This outputs a carriage return/line feed sequence to the code window when the code is inserted into the editor. These characters are used to provide clean formatting of the code.
      The next few lines of code define the ADO connection properties and open the connection to the database containing the stored procedures:
  ' set connection properties and open
cn.CursorLocation = adUseClient
cn.ConnectionString = gStrSprocDSN
cn.Open
      Once the connection is open, the code sets up a Command object to use the stored procedure in the cboSproc combobox. Then the Refresh method of the Parameters object is executed to retrieve the stored procedure's parameters:
  Set cmd.ActiveConnection = cn
cmd.CommandText = cboSproc.Text
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
      The next line of code sets the params variable as a reference to the Parameters collection. This variable will be used later in this function to retrieve the parameter details:
  Set params = cmd.Parameters
      The code in Figure 2 begins to construct the code details and store them in the strCode variable. At this point, the code stored in strCode should look familiar to you. It's the same type of code that you would use with any database object. The following code adds the stored procedure name to strCode:
  strCode = strCode & Chr(34) & cboSproc.Text & Chr(34) & ","
The bParams variable is set to False and used in the For loop to stop the output of the Array function after the first parameter.
  bParams = False
      The code in Figure 3 is the For Each loop that processes the Parameters collection. Inside the loop, each parameter is inspected by checking certain properties, such as Type or Name. Then the mp statement for each parameter is added to strCode. The Select Case param.Type statement may require modifying if you need to use other data types.
      After the For loop concludes, the code completes the construction of strCode by adding a closing parenthesis if any parameters were output. It adds the DSN statement if necessary, and finally closes the connection, completing strCode and setting the return value to strCode:
  If bParams Then
strCode = strCode & ")"
End If
If chkIncludeDSN.Value Then
strCode = strCode & ", _" & vbCrLf & "  " & _
    txtConnectionString.Text & ")" & vbCrLf
Else
strCode = strCode & ")" & vbCrLf
End If
cn.Close
GenADOCode = strHeader & strCode & strFooter
      The mp (MakeParameter) function that is referenced for each parameter comes from FMStocks and is used to construct a parameter array for each parameter. The function's code is:
  Public Function mp(ByVal PName As String, _    
    ByVal PType As ADODB.DataTypeEnum, _
    ByVal PSize As Long, ByVal PValue As Variant)
    mp = Array(PName, PType, PSize, PValue)
End Function
Along with the DSN function, this function must be included in any project that uses the output of this add-in. The easiest way to include mp is to use the Helpers.bas file from FMStocks 2000 (see https://msdn.microsoft.com/library/techart/fm2kintro.htm).

More Code

      The add-in is wired to the Visual Basic 6.0 object model and the add-in connection by creating references (as shown in Figure 4) and by declaring the two public variables shown here:
  Public VBInstance As VBIDE.VBE
Public Connect As Connect
These two declarations are located in the General area of frmAddIn. This makes the references available to the entire add-in. The references used in the project are shown in Figure 4.

Figure 4 Creating References
Figure 4Creating References

      The add-in only interacts with the Visual Basic IDE in one placeâ€"the code window. After you have selected a method and stored procedure, click the Apply button to insert the proper code into the current cursor location in the code window. When you click Apply, the cmdApply_Click event executes. The first four lines of the procedure define the variables it uses:
  Dim lngStartLine As Long
Dim lngStartCol As Long
Dim lngEndLine As Long
Dim lngEndCol As Long
      The next line executes the GetSelection method to return information about the current selection in the code window. The add-in is looking for the current line so that it can then insert the code in the proper place. The current line is returned in the lngStartLine variable:
  VBInstance.ActiveCodePane.CodeModule.CodePane.GetSelection _
lngStartLine, lngStartCol, lngEndLine, lngEndCol
      The following line does the work of generating the code and inserting it in the code window:
  VBInstance.ActiveCodePane.CodeModul.InsertLines _
lngStartLine, GenADOCode()
You should notice that GenADOCode is the last parameter of the InsertLines method.
      If the developer clicks the Preview button instead of the Apply button, the code is generated and sent to the Preview form by the cmdPreview_Click event.

Using the Add-in

      Using the add-in is simple. Select it from the Add-In menu, choose the DSN that points to the correct database, select the stored procedure to use, select the options you need, then click Apply. The code will be inserted directly into the editor at the cursor location.
      The following code is from a test class I used with the add-in:
  Dim oDB As BestISVDBUtil.DBHelper
Set oDB = CreateObject("BestISVDBUtil.DBHelper")

' Sproc Wizard Code Generation START ---------
Dim rs As ADODB.Recordset
Dim vReturn As Variant
Set rs = oDB.RunSPReturnRS("GetUserByLastName", _
Array(mp("@lastnamemask", adVarChar, 30, lastnamemask)), _
GetDSN())
' Sproc Wizard Code Generation END ---------


Set tester2 = rs
The first two lines and the last line were added by me. The other lines, starting and ending with the comment lines, were inserted by the add-in. I added the line continuation character on the Set rs line for readability purposes.
      Also, notice the call to mp and GetDSN on the Set rs line. As I mentioned earlier, you will need to place these two functions in your code.

Conclusion

      Creating add-ins like this one are a sure way to improve your productivity as a developer. Add-ins can not only shorten the time it takes to build your applications, but because they generate the same code each time, they can also decrease the amount of debugging that you are required to do.
      There are many other types of add-ins that you can create. For instance, you could create an add-in that allows you to browse the Web while you are developing in Visual Basic. You could also create add-ins that automate the use of other business objects in your collection. In a future column, I will dig into another add-in that you can use to simplify your life.
Ken Spencer works for the 32X Tech Corporation (https://www.32X.com), which produces a line of high-quality developer courseware. Ken also spends much of his time consulting or teaching private courses.

From the January 2001 issue of MSDN Magazine