Extracting Metadata - Extracting Metadata from Databases Such As SQL Server
(Page 4 of 22 )
Databases are a great source of metadata; they’re arguably the most important metadata source you’ll use. But if you can create an XSD for any ADO.NET source, why not just build an XSD and translate it? After all, Visual Studio .NET uses the XSD to describe database structures to build the strongly typed DataSet. Well, the flexibility of XSD is great, but it doesn’t have the richness of a modern database such as SQL Server, particularly if you generate the XSD through the .NET inference engine. It’s hard to include details such as defaults, constraints, and extended properties when you’re working with an inferred schema. You get more information if you programmatically extract it from the database.
TIP Use SQL-92 techniques when your metadata source is a SQL-92 compliant database such as Microsoft SQL Server or Oracle.
NOTE: To extract data from a database, you have to first include the data in the database. Okay, that may sound pretty obvious, but many people don’t maintain default values, constraints, and sometimes even relations within their database. If you currently avoid storing this information in your database server, reconsider. SQL Server and most other backends are good at managing data for you and allow a good deal of metadata in a single location. But, if you don’t want to store the metadata in your database, you can store it in separate, manually created freeform XML files. |
SQL Server offers three different ways to access information on the database structure as follows:
- You can access the system tables directly. But, that’s really uncool because Microsoft might change them and invalidate your extraction tool.
- You could use the sp_help procedures that are so handy in Query Analyzer. But they’re designed for people to read and have a lot of quirks and incon sistencies. Besides, they apply only to SQL Server.
- The best way to extract database structures is the information schema views defined as part of the SQL-92 standard. The “Working with SQL-92 Databases (SQL Server)” section has more details about extracting from these views.
SQL Server, Oracle, and other key databases conform to SQL-92, but others such as Access don’t. If you need to extract data from a non-SQL-92 source, you can fall back on the XSD approach, use tools such as .NET’s GetOLEDBSchemaTable method, or internal queries to create DataSets that contain the system information.
Entering Metadata Manually
It probably doesn’t surprise you that some of the metadata isn’t in your database or anywhere else outside the brains of your group. This metadata might be tightly associated with database columns or objects, and in that case you can include additional information as extended properties of your SQL Server database. But if there isn’t such an association, you’re working with a database that doesn’t support extended properties, you think extended properties in SQL Server are awkward to use, or your Database Administrator (DBA) resembles an underfed grizzly bear, then the solution is to manually enter the information as freeform metadata.
TIP: Use manually entered freeform metadata when the information isn’t already contained in a translatable source. This includes information extracted from your requirements and information about UI functionality.
A logical use of freeform metadata is adding captions, ToolTips, and validation to the metadata for your database (assuming you aren’t using extended properties to hold this information). Another place you can use freeform metadata is defining UI items. You don’t want to redo things the Visual Studio’s designers do well, but there are a ton of things you do in your user interface that don’t need visual tools. For example, you could create a manual freeform metadata file that defines role-authorized menu structures.
In the “Creating Freeform Metadata” section, you’ll learn more about designing freeform metadata and merging it into your monolithic metadata file.
Extracting Metadata from Web Services via WSDL
Is it possible to write a book today without talking about Web Services? For better or worse, its coverage in this book is limited to this section. That’s because I think you can extend what you learn from other metadata sources and apply it to Web Services. Web Services are basically a universal messaging system on major steroids. The messaging interface of the Web Service is described using WSDL. WSDLs are metadata. That’s their purpose. They describe the service, the data structures that go back and forth, the methods that are called, and the bigger picture stuff about how the service behaves. WSDLs are XML, making them great metadata.
TIP Use WSDL as a metadata source when you’re retrieving data via Web Services and the Web Services aren’t part of the application you’re also generating.
Why would you want to do code generation from WSDLs? If this was a presentation, I could see a number of people waving their hands frantically and the rest with blank stares. There are at least three reasons to do this as follows:
- Creating client-side proxies
- Creating Web Services from a standard or industry-wide WSDL
- Creating code to interact with Web Services
.NET creates client-side proxies for you (either via Add Web Reference or via WSDL.EXE), so you’ll only need to do this yourself if the .NET tool comes up short or you have an unusual reason such as wanting to generate a standard set of client proxies for different platforms for testing.
It isn’t clear how the vision of industry-wide WSDLs will mature. There will probably be industry-wide WSDLs that you’ll support, but most of your work may be extending these standard WSDLs to add value to your products. Implementing these standardized WSDLs will generally involve building entry points into your Web Service with handcrafted code backing them up. In some cases, you might be able to generate your entire Web Service.
If you know the structure of a Web Service, you know the structure of its proxy, and you can write code that interacts with it. Examples of how you might use this are a default Windows application that lets you interact with a Web Service, or doing scatter/gather to a custom user interface. You also might create WSDLs or create Web Service wrappers for middle tier or other data.
 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 Metadata from External Sources >>
More Database Articles
More By Apress Publishing
/ 18 
