This article shows how to use the refresh method of the ADO Parameters collection to interrogate a stored procedure. You may already have experience using ADOX or SQL-DMO to accomplish this, but please keep in mind that the method displayed here is meant to be simple enough that anyone with ADO experience can use it. (ADOX and SQL-DMO will be addressed later.)
During my second year of development in Microsoft technologies, I constantly wished I'd known something earlier. I understood the basics but was not knowledgeable enough to draw conclusions about what could be done with them. I would see articles written for intermediate to advanced developers about writing code to generate code, and I was baffled by the specifics. Feeling discouraged, I worked through my mind-numbing tasks the same way every day. The task I remember doing the most is writing ADO interfaces to stored procedures. This process put me to sleep. The following is a simple method to keep you from falling asleep writing ADO interface code. In this article, I will show how to create the code in an automated fashion for use in VBScript connecting to MS SQL Server 7.
The refresh method is part of the command object's parameter collection. You may already be using it to make calling stored procedures easier. For example:
In this example, when you call the refresh method, ADO goes to the SQL Server and gets the information about the parameters. The advantage is that the proper values for the ADO size, direction, and precision, among others, are loaded for all the parameters of the command. You can now assign values by ordinal position and execute the command. The drawback to using the refresh method when calling a stored procedure is that two trips are made to the SQL Server each time you want to call the stored procedure. The first is when the refresh method is called. The second trip occurs when you execute the command. The information retrieved by the refresh method is enough to generate code to call the stored procedure without the refresh method.
Step 1
We first need to prompt the user to provide a small amount of information. In the following form, the user will enter a connection string, a procedure name, and decide if the code should handle the return of a recordset.
When the form is submitted, the data is saved to variables m_strConnectionString, m_strProcName, and m_blnRecordset. With this information we start the process.
'Declare and create the command object.
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
'Open the connection on the command.
cmd.ActiveConnection = m_strConnectionString
'Set the command type to stored procedure.
cmd.CommandType = 4
'Set the command text to the procedure name.
cmd.CommandText = m_strProcName
'Call refresh to retrieve the values.
cmd.Parameters.Refresh
At this point, all of the information about the parameters is loaded into the parameters collection. Next we will display our results.
Step 2
Dim blnTR1
Dim param
for each param in cmd.Parameters
If blnTR1 Then
Response.Write "<TR style=""background-color:silver;"">"
Else
Response.Write "<TR>"
End If
blnTR1 = NOT blnTR1
Response.Write "" &
"<TD align=""left""> " & param.Name & " </TD>" & _
"<TD align=""center""> " & GetParameterDirectionEnum(param.Direction) & _
" (" & param.Direction & ") </TD>" & _
"<TD align=""center""> " & GetDataTypeEnum(param.Type) & _
" (" & param.Type & ") </TD>" & _
"<TD align=""center""> " & param.Precision & " </TD>" & _
"<TD align=""center""> " & param.Size & " </TD>" & _
"<TD align=""center""> " & param.Value & " </TD>" & _
"</TR>"
next
We use the "For each .. In .. Next" construct to iterate through each parameter in the collection. From this we retrieve values for Name, Direction, Type, Precision, Size, and Value. For the Direction and Type, it helps to have a little more information. Since I didn't write the code spec on ADO, I can't look at the number 135 and know that its ADO data type is adDBTimeStamp. (Yes, even after writing these by hand countless times, I could be just blocking out bad memories.) Therefore, I feel it's necessary to translate the data types and directions into the ADO enumerations. To produce the enumerations values, we use the GetParameterDirectionEnum and GetDataTypeEnum functions declared later in our code. The results of our work are displayed in a table.
Step 3
Now that we have the knowledge about the procedure, we need to convert it into power. After writing ADO interfaces for a while, we all develop a style. Some people create a separate command and connection object, while others will use just a command object and assign the connection string to it. Regardless of your style, we can automate a good portion of it. In my example, we will create a VBScript function that will call the stored procedure. The following code executes after the refresh method was called for the tabled output above.
Declare variables to be used:
Dim blnFirstParameter 'Is this the first parameter
Dim strDeclaration 'Function declaration
Dim strCommandParameters 'Parameters for the command
Dim strOutputParameters 'Retrieving of output parameters
Dim strPrecisionParameters 'Setting of precision for Decimal and Numeric
Dim strTempParamVarName 'The variable name for the parameter
'Setup the function declaration.
'Default the Function name to the proc name
strDeclaration = "Function " & m_strProcName & "("
blnFirstParameter = True
'Add a parameter for the returned recordset if requested.
If m_blnRecordset = True Then
strDeclaration = strDeclaration & "rst"
blnFirstParameter = False
End If
We will now iterate through the parameters again. We start by chopping the "@" sign off the parameter name if it exists. The parameter name is then assigned to strTempParamVarName. This value is used as the parameter variable for the function.
If Left(param.Name,1) = "@" Then
strTempParamVarName = Mid(param.Name,2)
Else
strTempParamVarName = param.name
End If
Step 4
Next, we process everything except the return parameter. If this isn't the first parameter, add a comma to separate it from the previous one. If this is an output parameter (SQL Server types the declaration as adInputOuput (3)), then add the code for retrieving the values after the command is executed.
If NOT param.Direction = 4 Then
If NOT blnFirstParameter = True Then
strDeclaration = strDeclaration & ", "
Else
blnFirstParameter = False
End If
strDeclaration = strDeclaration & strTempParamVarName
If param.Direction = 3 Then
strOutputParameters = strOutputParameters & strTempParamVarName & _
" = cmd.Parameters(""" & param.name & """).Value" & vbCrLf
End If
End If
'Create code to attach each parameter.
strCommandParameters = strCommandParameters & _
"cmd.Parameters.Append cmd.CreateParameter(""" & param.Name _
& """, " & param.Type & ", " & param.Direction & _
", " & param.Size & ", " & strTempParamVarName & ")" & vbCrLf
The SQL Server decimal(14) land numeric(131) data types must be handled differently. The create parameter call does not include the precision value, so we create code to reference the parameter by name and set the precision values explicitly.
If param.type = 14 or param.type = 131 Then
strPrecisionParameters = strPrecisionParameters & "cmd.Parameters(""" & _
param.name & """).Precision = " & param.Precision & vbCrLf
End If
With all of the parameters processed, we exit the loop and start writing out the function. This part is very straightforward and only varies if a recordset must be returned.
Once all the information is written, you will have a text area displaying your results. Here is the output for the z_testInterrogation stored procedure without returning a recordset.
If the user selected to return a recordset, code to declare, create, open, close, and destroy it will be added. The recordset's CursorLocation property is set to adUseClient(3). If you run the statement without it, the return and output parameters will appear unchanged by the stored procedure. If you don't return a recordset and use only a command object and execute it, the output and return parameters will be returned correctly.
All of the code we just examined is very simple. It only accomplishes slightly more than the basics of writing the code to call stored procedures, however, the value we add to our toolbox by understanding it is immense. When we are new to writing ADO, we are likely to make a lot of mistakes. We may go even as far as making design mistakes that are repeated in every call. With the basis displayed here, we could modify the process to repeat for all of the procedures we need to fix, thereby correcting the flaws in only a few minutes. When we are experienced developers, the burden of fixing a project gone wrong is often assigned to us. Perhaps we were only assigned to code review a project we had nothing to do with. In these cases, we have to know the technologies inside and out. If we find the data access to be flawed, we could get the project back on track by giving the development team a tool based on the technique I described. Then the time saved can be used to solve the business rules of the application, or even go home on time! When I used it, I found it to be helpful to create both business and data-layer functions.
The example displayed above is totally void of error handling. I tend to like to know as much as possible when a problem occurs. To accomplish this, I find myself writing complex error-handling routines. Now, I just write it once in my code generator and have it reproduced everywhere.
It should be noted that the binary data types for SQL Server 7 were not discussed here. I feel that, in the current context, they would only clutter the example because they are handled far differently. Many good articles are written for working with binary data in SQL Server 7, so I don't want to just be repeating it.
ADOX and SQL-DMO
As promised, let's revisit the topics of ADOX and SQL-DMO. ADOX is the name for the new library included with ADO 2.1 The library is formally called "ActiveX Data Objects Extensions for DDL and Security," or ADOX. You may think you can do more with ADOX or SQL-DMO, and you're right. But think about what we are trying to accomplish here. Using either of these would be like using a flame thrower to destroy a small anthill. The flame thrower is a lot of fun and allows you to destroy it in a more controlled manner. You can fry it piece by piece, or destroy it in one shot, but beware of starting a forest fire! When you think about it, the flame thrower is overkill for something that can be accomplished by just crushing it with your shoe. If you have never used a flame thrower, you're probably going to need some training and a little practice before you can get the job done.
Our problem is easily solved with tools we already know, recordset and command objects. I am in no way bashing ADOX or SQL-DMO. I am quite the fan of ADOX, though the lack of implementation by the current drivers is disappointing. Both are very powerful and flexible tools to learn and add to your development tool belt. They could be used here to make this example more user friendly by retrieving a list of stored procedures for the user to choose from. Please do not infer from this article that ADOX and SQL-DMO do exactly the same thing. They have some overlap, but each has its specific uses. See the end of this article for links to more information about them.
Conclusion
So let's recap what we should be doggie-bagging from this article. First, the refresh method, which is costly to use every time you want to call a stored procedure, is great for getting all the information you need to build a basic ADO interface generator. Second, watch out for the ADO pitfalls discussed here. Decimal and numeric data types must be supplied a precision value, and opening a recordset on a command that has output parameters without using client-side cursors will cause the output parameters not to show updates. Finally, if you find that you will need more power and control in your generator, look to ADOX and SQL-DMO.
David R. K. DeLoveh is a Tech Lead for AdOutlet, which provides solutions to the media industry. He is also part owner of VisiNex Solutions, Inc. VisiNex works to provide quality Web development to small- and medium-sized businesses in the central Ohio area. David specializes in development of Web-based applications built on Microsoft technologies. He can be reached at david@deloveh.com.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
