Extracting Metadata - Understanding the Tool Architecture
(Page 14 of 22 )
The SQL-92 standard features provide most of the metadata you’ll want and work with a number of database backends. Each backend provides additional metadata opportunities. That makes a base/derived class pattern a good choice for the metadata extraction tool. A base class named ExtractMetadata gives access to the SQL-92 standard features. The SQLExtractMetadata class derives from this and provides Microsoft SQL Server–specific features. Figure 2-3 illustrates this relationship. The Oracle box is gray because I haven’t supplied an Oracle class.
The derived class provides the housekeeping duties of accessing the back-end as well as backend provider-specific extensions. Housekeeping duties include connection management and accessing the data through a DataAdapter. Because these tasks are handled by the derived classes, the base class can’t stand on its own so is defined as an abstract (MustInherit) class. You’ll need to build a derived class to at least perform this housekeeping functionality for any other backend database you use. You may want to add other features specific to your backend, similar to the way extended properties are added in the SQL Server derived class.
Because I don’t know what you’re going to do with derived classes you may create, I maximize the flexibility by making everything in the base class Overridable (virtual). I don’t suggest you do this to most classes. In general, you want Overridable to indicate the ways you anticipate derived classes using your base class. In this case, I cheat and make everything overridable.
NOTE: You do incur a small performance penalty every time you access an Overridable member. This performance hit is inconsequential when you need to allow the member to be overridden. However, doing this for members that you don’t expect to be overridden just wastes processing cycles. |
TIP: It doesn’t matter if you waste processing cycles during code generation.
Understanding Security
When you’re creating metadata, you need permission to read from the system tables. The easiest way to do this is to create one or more logins for code generation that are different from the logins you’ll use at runtime. From the programmer’s perspective, the simplest approach is Windows authentication. The downside of this approach is that programmers may wind up testing the application with different rights than end users will have. One solution is to have multiple logins and use the Windows feature Run As when running code generation. Another solution is to provide a login to the code generation and use username/password authentication.
The tools on the Web site use Windows authentication because that’s the most appropriate approach for code I’m asking you to run against your server. You can change that in the connection string in the SQLExtractMetadata class. This connection string is in the class, rather than App.config, because it’s concatenated at runtime with the server and database provided by the harness. To run the samples, you’ll need to have access to Northwind via a Windows-authenticated account or make changes to the connection string and provide a user login screen.
TIP: Differentiate between permissions for code generation and application testing, especially if your code generation writes to your database. Use separate accounts for code generation and application testing.
Code generation may also need to make changes to your databases. You might do this either because you’re creating stored procedures or because you’re creating and running scripts to build tables and/or import data. In these cases, you’ll need additional database rights. You’ll need to fine-tune your code generation login to have the required permissions.
TIP: You can mess up working with code generation in basically the same ways you can mess up in Enterprise Manager or Query Analyzer. You can run any script using the harness introduced in Chapter 3, including ones that overwrite stored procedures, add tables, drop tables, and so on. Just as you can “toast” your database with manual tools, you can toast it (faster and more efficiently) with code generation, so you’ll want to use backups, test databases, scripts, caution, and other good habits.
 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: Working with Information Schema Views >>
More Database Articles
More By Apress Publishing
/ 18 
