Refreshing Parameters Instead of typing in all of the parameter details yourself, you can get ADO to do it for you, simply by calling the Refresh method. For example, let's say we've set up usp_AddEmployee with the same parameters as before. The page we use to run it is the same as before up to here:
Then we'd call the Refresh method.
This tells ADO to ask the data store to provide the details of each parameter, and it creates the Parameters collection for you. You can then fill in the values:
Notice that we haven't had to create any parameters, including the one for the return value.
This may look like a really good shortcut, but you should be aware that it imposes a performance penalty, since ADO must query the provider for the details of the parameters for the stored procedure. Despite this it's exceedingly useful, especially if you are having trouble getting the values for your parameters correct.
In fact, you can build a little utility to be used as a development tool, which does a refresh and then constructs the Append statements, ready for you to paste into your code. It might look something like this - an ASP page called GenerateParameters.asp:
The code for this is pretty simple. The first thing to do is include the connection string, plus another file for the ADOX constants:
Then we create a form, specifying that its target will be the ASP page called PrintParameters.asp:
We then use ADOX to get a list of stored procedures from the SQL Server, and we construct a list box containing the stored procedure names:
It's a simple form, with a TEXTAREA for the connection string, and a SELECT for the stored procedures. What you won't have seen before is the ADOX bits. ADOX is the ADO Extensions for Data Definition and Security, and gives you access to the catalog (or metadata) of a data store.
We're not covering ADOX in this book, but it's quite simple. More details can be found in the ADO Programmer's Reference, also from Wrox Press. Either the 2.1 version or the 2.5 version will be OK for ADOX.
In the above example, we are using the Procedures collection, which contains a list of all stored procedures in a data store. When you press the Print Parameters button, you get the following:
The parameter lines can simply be copied from this and pasted into your code. Easy huh! The code's not very difficult either. At the very beginning we have an include file you won't have seen before. This contains several functions that convert the ADO constants (such as data type, parameter direction and so on) into string values:
Next we have a set of variables, the extraction of the user request, and the creation of the Command object:
We then use the Refresh method to automatically fill in the Parameters collection:
We can now loop through the collection, writing out a string that contains the details for creating the parameter:
The functions DataTypeDesc and ParamDirectionDesc are found in the Descriptions.asp include file.
The Descriptions.asp include file, along with the other sample files, can be found on the supporting web site, at http://webdev.wrox.co.uk/books/2610.
This is a really simple technique, and shows a good use for the Refresh method. Well, anything that saves typing has to be good, doesn't it?