Extracting Metadata - Retrieving Stored Procedure Recordsets
(Page 18 of 22 )
It’s tricky to retrieve the structure of recordsets returned by stored procedures. It’s not available from information schemas or from the system tables. To retrieve this information, you have to actually run the stored procedure and look at the output. If you look at Microsoft SQL Profiler, you’ll see that Visual Studio uses this approach to come up with the return values for Server Explorer.
SQL Server includes a SET FMTONLY ON switch, instructing it to return empty recordsets with no data. This works in most cases, primarily failing if you have temporary tables and a few other scenarios. It’s wise to also set the database to read-only prior to calling the stored procedure to minimize side effects from running these stored procedures or start and roll back a transaction.
The remaining problem is what parameters you’re going to use when calling the stored procedure. In the SQL-92 metadata extraction tool, I solve this by passing default values for the type, such as zero from numeric values. For this to work, obviously your procedure can’t blow up when default values are passed. I had one DBA include a consistently named optional parameter in all stored procedures that indicated whether I was extracting metadata. This Boolean parameter worked well, only requiring that one way or another the stored procedure return empty recordsets matching the runtime structure. The DBA could figure out internally the best way to do this. A similar approach would be to use a null value for one or more key parameters to indicate you’re in metadata extraction mode. If you can’t use these approaches, you’ll have to work out a scheme for storing the appropriate parameter values. You could store these in a separate file, store them in extended properties, or parse them from the opening comments of the stored procedure itself. Regardless of how you do it, this will be hard to maintain, so limit the number of parameters you need to assign values for code generation where possible.
Some stored procedures vary the structure of the recordsets they create based on parameter values. From a code generation point of view, not having predictable columns is disastrous. My first choice for a solution is to rewrite these stored procedures, perhaps having the stored procedures return empty data rather than omit columns when there are no values. My second choice is to create wrapper stored procedures for all the different variations. This way your code generation can access only the wrapper stored procedures, each of which returns consistent recordsets. If neither of these solutions works and your stored procedures need to return a variable structure, you’ll have to determine how that variability will affect your business objects.
In some cases, such as pivot tables, the recordsets produced by stored procedures is inherently variable. These stored procedures aren’t going to provide you with meaningful recordset metadata you can use to generate business objects. Your goal will be just to avoid misleading objects being built and develop other strategies, such as untyped DataSets for this kind of data.
Understanding ADO.NET Structures and Logical Tables Running the stored procedures results in a set of ADO.NET DataTables. If you run the stored procedure with the MissingSchemaAction property set to AddWithKey, you’ll get a recordset that includes the column names, sizes, and types. You can access this information through normal ADO.NET techniques.
That’s nice information, but these returned columns almost always map to an underlying table and column. Previous sections showed how to extract even richer information from the underlying columns. To connect this information with the stored procedure recordset, you’ll have to determine the mapping between the stored procedure and its underlying tables.
Each returned recordsets is a logical table. Logical tables often map directly to physical database tables. They won’t map directly if the logical table consists of joined tables.
Inferring the Mappings The mapping between the logical and physical tables can be explicit through extended properties or freeform data. But that’s often not needed because the mappings can be inferred. These inference rules will almost always get you the right column. When they don’t, you can override with manual mapping in freeform metadata. As part of evaluating your database for metadata collection, evaluate where you have potentially ambiguous fields.
Selecting stored procedures that return a single recordset mapped to a single table aren't too hard. Because I love well-organized databases with names I can predict, I like the careful naming approach discussed later in the “Introducing Careful Naming” section to determine the physical table for selects that return a single recordset. For example, the stored procedure that retrieves data from the Customer table might be named acc_Customer_Select. Alternatively, you can use extended properties or freeform XML. You can apply extended properties to stored procedures and their parameters but not to returned recordsets or columns.
Things get a lot trickier when the stored procedure returns multiple record-sets or a recordset represents a table join. In the first case, you don’t know much about each recordset, and in the second case, individual columns in the recordset are mapped to different tables. To map multiple recordsets to their underlying tables, you can extend careful naming to columns by having a column name that’s the name of the table followed by ID, and using that column name to determine the physical table. This is probably how you already name your primary keys, so it’s an easy rule to implement; however, you also have to place this column in a predictable location, usually the first occurrence of a field ending with ID. You’ll also have to ensure that the recordset column names are the same as the underlying table’s column names. This solution works if there’s a single table mapped to each stored procedure recordset. Alternatively, you could store a comma-delimited list of table names as a stored procedure extended property.
When a recordset is comprised of joined underlying recordsets, you can gather a set of tentative recordsets by grabbing any column whose name is a primary key to another table. You can then walk these tables looking for each column name. Another approach is to prefix all of the column names with the corresponding table name and an underscore. This is the least ambiguous approach. It can be tedious to maintain by hand, but it’s a breeze if you’re generating the stored procedures. Yank these prefixing table names off the column name before exposing the data in a business object, which is also easy to do with code generation. Regardless of your overall approach, you need to use the concatenated table name approach when you have two columns that have the same name in different underlying tables with different metadata.
Working with SQL Server–Specific Metadata Most of the information in your SQL Server database structure is available through the SQL-92 information schemas. Some of the information, specifically the autoincrementing identity columns and extended properties, are SQL Server–specific extensions and not included in the SQL-92 standard or the information schemas. In the metadata extraction tool, these values are retrieved in the derived SQLExtractMetadata class.
 This is from Code Generation in Microsoft .NET, by Kathleen Dollard (Apress, ISBN 1590591372). Check it out at your favorite bookstore today. Buy this book now. |
Next: Retrieving Identity Columns >>
More Database Articles
More By Apress Publishing
/ 17 
