The New and Improved Data Explorer

The Data Explorer introduced in VFP 9.0 allows developers to work with different types of data from diverse data sources independent of specific projects. The Sedna update extends this already powerful and productive tool.

The Sedna release of the Data Explorer adds new features and corrects some recognized bugs. Each of the enhancements and improvements came from suggestions made by the Fox Community, problems submitted to Microsoft, and at least one bug fix came directly from code blogged with the correction.

The changes discussed in this article are based on the version shipped in the Sedna October 2006 Community Tech Preview (CTP) and some additions made just after the CTP release. You can download the Sedna components from Microsoft’s Visual FoxPro Web site (http://msdn.microsoft.com/vfoxpro/). It is important to note that at the time this article was written, Microsoft has not finalized the features to be included in the gold release.

Data Explorer Main Form

One change you immediately notice is the buttons on the top of the main form seen side-by-side in Figure 1 with the newer version on the right. The toolbar buttons of the Data Explorer now mimic the functionality as well as the look and feel of the Server Explorer inside of Visual Studio .NET. You can use the new Refresh button to update the contents of the tree view. This feature is available using the Data Explorer shortcut menu even in the original version. In addition, you’ll use the Refresh button when you make changes to the schemas in a database, or you have multiple copies of the Data Explorer open and make changes to the connections in another instance and you want to update the current instance of the Data Explorer. You can optionally include the icons and make the toolbar buttons hot tracking. These two settings are available on the Options dialog box and discussed later in this article.

Microsoft enhanced the main form so the treeview nodes no long collapse when you return to the Data Explorer window from the Options dialog box. This saves time when you make code changes to the various extensions like the shortcut menu, drag and drop functionality, or the query add-ins. You now jump directly to the node you are using to test the changes instead of having to drill down every time you return to the Data Explorer.

The original version of the Data Explorer allows you to sort objects for each connection. Turning on this setting will sort all the tables, views, and stored procedures by name. Unfortunately, this setting also sorts the column names by name. Most developers want the tables, views, and stored procedures sorted, but prefer the column names in natural column order. The Sedna Data Explorer allows you to choose how you want the column names ordered for VFP and SQL Server connections separate from the object sorting setting. This is one of my favorite enhancements. Microsoft enhanced the property dialog boxes for these connections (Figure 2) so you can make your preferences more granularly than the original version of the Data Explorer.

Shortcut Menu

You’ll find most of the Data Explorer functionality on the shortcut menu for the treeview. Sedna has several new features that don’t automatically show up on the menu if you’ve previously used the Data Explorer because the functionality for the shortcut menu is stored in the metadata: DataExplorer.DBF in the HOME(7) folder. The new functionality is stored inside the DataExplorer.APP file internally in a DBF file. If you want the new menu functionality and want to retain your existing connections and extensions you have added or downloaded you need to use the Options dialog box to update your DataExplorer.DBF metadata.

From the Data Explorer toolbar click Options and then find the button called Restore to Default. You might hesitate thinking that this option might set your Data Explorer back to the factory settings, and indeed this is possible, but you can also retain the connection settings and the extensions you have added to the menu, to the Run Query dialog box, and the drag and drop functionality. Click Restore to Default to start the update process. You’ll see this message: “Do you want to maintain connections and customizations that were done by you or a third-party vendor?” Click yes if you want to save your changes and get the new enhancements. Click no if you really want to reset your installation to factory settings including the new functionality distributed by Microsoft in the Sedna release. Note that if you made any enhancements to the code included in the original Data Explorer features, it is possible the Data Explorer will reset it. See the sidebar, Update Shortcut Menu Can Overwrite Your Code Changes, for more details and recommendations.

SQL ShowPlan

Another feature that Sedna adds to the shortcut menu will display the SQL ShowPlan (Figure 3) for local views. Besides the actual ShowPlan details, the results include:

• The ShowPlan level passed to the SYS(3054) function
• SQL-SELECT code stored in the local view
• How long the query ran in seconds
• Number of records returned from the query
• View parameter(s) and their data types
• Optional messages to indicate how slow the query ran based on your threshold preferences
• Date and time the analysis was completed

One aspect of the ShowPlan output you can customize in the code is the threshold of when a view is considered moderately slow, just plain slow, super slow, or critically slow. Each of these thresholds can differ between developers. You can change these #DEFINEs to meet different localization requirements.

Visual FoxPro determines the ShowPlan for a view when it opens or requeries a view. One of the tricky parts of writing generic code to open a view is to deal with the possibility of view parameters. The script code for this feature looks at the view parameter list for the view and places empty values based on the data type of the view parameter if this is set. If you haven’t added the view parameters to the parameter list in the View Designer, in your view script code (if you are avoiding the View Designer), or by your favorite view editor, VFP will prompt you to enter in the value when the view is opened. This will artificially slow down the performance of the view, but will not affect the ShowPlan details.

By: Rick Schummer

Rick Schummer is the president and lead geek at White Light Computing, Inc. headquartered in southeast Michigan, USA. He prides himself in guiding his customer’s information technology investment toward success. He is a co-author of Visual FoxPro Best Practices for the Next Ten Years, What’s New in Nine: Visual FoxPro’s Latest Hits, Deploying Visual FoxPro Solutions, MegaFox: 1002 Things You Wanted To Know About Extending Visual FoxPro, and 1001 Things You Always Wanted to Know About Visual FoxPro. He is regular presenter at user groups in North America and has enjoyed presenting at GLGDW, Essential Fox, VFE DevCon, Southwest Fox, German DevCon and Advisor DevCon conferences. You can find all of his developer tools at his company Web site: http://whitelightcomputing.com.

raschummer@whitelightcomputing.com