Extracting Metadata - Modifying Mappings
(Page 17 of 22 )
Sometimes the information in the database schema isn’t exactly the information you want to use. Maybe you need to use a different column name or a narrower type in the user interface. There are several ways to provide mapping. You can programmatically modify the structure, or you can use freeform data to override existing attribute values and rely on the merge mechanisms allowing later XML files to override data in earlier files. Chapter 6 shows a simplified ORM solution targeted at indirect mappings. If you’re just changing a caption or type, overriding data with freeform metadata may be sufficient.
Extracting Stored Procedures Schemas You can extract the stored procedure and parameter information from the ROUTINES and PARAMETERS information schema views. What’s much harder to figure out is the intent of each stored procedure and what recordsets the stored procedure returns. The returned recordsets are important because these are often used to create business objects. Stored procedures are preferable to creating SQL statements as strings in your .NET code—also called dynamic or inline SQL. Inline SQL has significant problems, including security vulnerabilities, nonoptimal performance, and limited opportunities for reuse.
Determining Stored Procedure Intent
Knowing the intent of each stored procedure tells you how you’ll use it in code generation. Will it define a business object because it’s a select stored procedure? Will it be part of an updating strategy? Should it be totally ignored during code generation? You probably have some combination of nine kinds of stored procedures in your database as follows:
- Simple select stored procedures that retrieve a single root record
- Set-based select stored procedures to retrieve multiple records
- Insert stored procedures
- Update stored procedures
- Delete stored procedures
- Process stored procedures
- Administrative stored procedures
- Reporting stored procedures (select stored procedures used differently)
- Miscellaneous stored procedures
Although there isn’t any difference in how you call them, I think it’s helpful to differentiate between simple select procedures that return a single record and procedures that return multiple records. Note that this distinction is on the number of root records returned, not on the number of recordsets. Simple select stored procedures will often return associated child collections. Depending on the amount of preprocessing that has to be done, you can sometimes gain considerable performance benefits returning these multiple recordsets together. I’ll differentiate these two types of stored procedures by calling them select and setselect stored procedures.
You’re probably familiar with retrieve, insert, update, and delete stored procedures. You may also have process stored procedures that make changes to the database or retrieve scattered information that can’t easily be considered a select and don’t map to underlying tables. Most databases have some administrative stored procedures that are used for importing data, updating structures such as maintaining an internal calendar, and so on. Code generation can generally ignore these utility and internal stored procedures. Reporting stored procedures aren’t much different from select stored procedures, except that they’re more likely to be denormalized and will almost always be read-only on the client. Few databases survive without a few stored procedures that are unique and strange to the history of that database. These are sometimes temporary or intended as temporary, and no one was quite sure they could delete them.
TIP Use your initials as a prefix for temporary stored procedures to clarify who is responsible for either deleting them or marking them as a permanent part of your database.
To generate code effectively, you have to know into which category each stored procedure falls. You can do this with either extended properties or an approach called careful naming discussed later in this chapter. Careful naming formalizes the stored procedure’s purpose within its name, allowing you to grab it during metadata extraction. You might be able to parse the body of simple stored procedures to guess what the stored procedure does, but I advise against that approach because it gets too ugly too quickly with nontrivial stored procedures.
 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 Stored Procedure Recordsets >>
More Database Articles
More By Apress Publishing
/ 17 
