Some Initial Remarks ...

by Hans-Otto Lochmann, Armin Neudert and Jochen Kirstätter

by Hans-Otto Lochmann, Armin Neudert and Jochen Kirstätter

This is the first day of the SQL-Server & ASP.NET conference. Today we also will have sessions from both tracks. All together this will be 15 sessions.

TRACK Microsoft SQL-Server

The sessions of this track share a common theme: Using the Microsoft SQL Server and its features and functionality for all kinds of Database development. Also there will be an outlook on SQL Server 2005 and it's Business Intelligence Development Studio.

This track will run for two days: On Thursday there are 9 sessions and 8 more will be on Friday. 7 will be presented in German, 10 in English. 6 Speakers will share the burden: Sebastian Flucke, Dan Jurden, Daniel LeClair, Valdis Matison, Armin Neudert, Eugen Wirsing.

An Introduction to Microsoft SQL Server 2000.

There is always a beginning at the start of everything: So also this track starts with an introduction to SQL Server: These two sessions gave a complete overview of the SQL server and its interaction with Visual FoxPro.

Early in the morning Armin Neudert had a closer look on the installation, the configuration and the daily operations of a Microsoft SQL Server 2000. He showed the most important items an administrator or a developer should do when they install and configure an SQL Server for the first time. Next, he introduced the audience to some of the agents and services as well as some of the administrative tools, which were installed by the different SQL Server editions. And in order to "confuse" the developers Microsoft brought quite a few editions of the SQL Server onto the market:

• Editions for server and enterprise installations

• Enterprise Edition
• Standard Edition



• Edition for 'special uses'

• Developer Edition (Enterprise with another EULA)
• Personal edition
• Edition for Windows CE
• Evaluation Edition

He finished his first session by showing ways to connect Visual FoxPro to an SQL Server.

In his second session Armin concentrated on the more advanced aspects of the Microsoft SQL Server 2000. First, Armin highlighted the two security concepts, which the server has - Windows Authentication and SQL Server Authentication (Mixed Mode) and compared both by their individual pros and cons.

Next, he explained the overwhelming number of possibilities to configure the authorization schemas within the SQL Server in detail. He reviewed user accounts, object rights, and database roles extensively. Also he gave some introduction to the Data Transformation Services (DTS), Full Text Search, Scheduled Tasks and the integrated replication mechanism. Finally he spoke about scheduling jobs like automatic database backups and the proper steps to restore them.

Hierarchical Data Structures with Microsoft SQL (OLAP1/4).

Today the first two of the 4 session series on OLAP will be presented.

Sebastian Flucke took care of providing his audience with a complete and thorough view of a tough topic: Online Analytical Processing or just short OLAP. It took him only four sessions to cover this complex material in such a fashion, that each attendee got a good chance to continue to study this field by himself.

In his first session Sebastian explained the different design principles of "relational" data base design schema and of multidimensional data base design schema.

The "relational" data base schema, which is used among other in Visual FoxPro, is a very effective form to organize the data in a data base. It has been designed to store and to retrieve data most efficiently (speed) and effectively (space required), which do not have a dimension, like a time dimension, i.e. the data in each Attribute (Field) has no past and no future. It only has a "now". These systems follow the principles of the "Entity Relationship Model (ERM)", which is based on the perception that the data to be stored can be described with a "relational structure". ERM among others works with Entities (Tables), Attributes (Fields) und Relations. SQL has been standardized (at least in some basic forms) as a query language. It comprises a data definition language, a data manipulation language and a data control language (security). xBase languages can also be used (see Visual FoxPro). Important for storing data free of redundancies is the normalization (3 "forms"). In order to retrieve data, which belong together "relations" can be defined.

However this data base schema quickly shows some limitations, when data is queried and the query combines data in a way, which is permissible, but is not reflected in the schema according to which the data base has been designed. The SQL statements quickly become very complex and difficult to debug.

The ERM comes completely to its limits if data is to be stored, which has "dimensions". For instance, if a time scale is involved, then the attributes (fields) can have different values depending on the instance of time, when it was stored. This is typical for data like sales volume or other commercial data, stored in what is called a DataWarehouse!

The deployment of a multidimensional or "hierarchical" data base schema can remedy such a situation. This multidimensional or "hierarchical" data base schema among others work with cubes, cells, dimensions, elements and a hierarchy. Sebastian made the participants acquaint with the concept of hierarchical data bases and how to decide which is the correct data base schema to used for a respective purpose. Additionally Sebastian explained the basic principles of the DataWarehouse structures.

Analysis Services SQL 2000 (OLAP2/4).

In his second session Sebastian Flucke explained the "Analysis Services" extensions of the Microsoft SQL Server 2000. With these services hierarchical data structures for DataWarehouse projects and similar projects can be designing and can be stored onto a Microsoft SQL Server.

This session describes the conceptual background of the Analysis Services and how these concepts are used to realize these Analysis Services as an extension of the SQL Server so that hierarchical data bases can be realized with these tools on a Microsoft SQL Server.

Additionally Sebastian demonstrated, how Microsoft Analysis Services can be used to access also Non-SQL-Data-bases like Visual FoxPro, Oracle, Informix, Access, etc.

Finally Sebastian gives an overview on the possibilities and limits to build DataWarehouse systems with the Microsoft SQL Server and the interaction with Visual FoxPro data and Visual FoxPro application.

Designing SQL-Server Database.

Proper design is important to the success of any database application. In this session Daniel LeClair discussed some of the basics of database design: choosing the right data types, options for record keys, normalization (and denormalization) rules, and code formatting suggestions. These basics apply to all types of databases, so no matter what platform the database is designing for, the rules, methods and tweaks, presented in this session, would apply.

Using Microsoft SQL Server Dan presented a wealth of examples to demonstrate, how to

• choosing the right data type
• set the primary key options
• use the three normalization rules
• how to denormalize retrieved data by selecting the proper query techniques

The examples also emphasized the importance of code formatting, last not least for better debugging and later maintenance.

Dan also introduced the audience to the selection and use of the proper design tools.

Developing VFP apps using SQL Server XML Services.

In this session Dan explained all steps, including the unavoidable tweaks, to set up Microsoft SQL Server and a Web Server to provide these services. After an overview Dan presented many samples of using Template Queries to retrieve data in XML format and to call stored procedures. And finally the techniques necessary to use this technology in a VFP application to query, manipulate and save the data using XML was fully covered.

An Introduction to SQL Server 2005 aka Yukon.

Dan Jurden introduced Yukon in his session of this track. Aside from the fact, that Yukon is now officially named SQL Server 2005, Dan presented the new capabilities version 2005 will bring with it and compared and explained its differences to Microsoft SQL Server 2000.

One of the major aspects is for sure that stored procedures, triggers and user-defined functions can now be written in any .NET language. They are not restricted to T-SQL any more. Also user-defined types can be created. The new XML data type provides storage of any type of XML data including schema definitions. The data stored in these XML data types can directly be manipulated via XQuery as well. This opens the door to mix SQL and XQuery statements in order to select, insert, update and delete data, no matter whether this data is XML or not.

Actually, most of the new features, which will be shipped with SQL Server 2005 during the summer of 2005, are very impressive to any developer - especially for .NET developers.

Most likely Web Services and Reporting Services will be the top features for FoxPro developers as well as the possibility to write Stored Procedures in any .NET language.

Analysis Services in SQL 2005 and the Unified Dimensional Model.

In these two sessions Valdis Matison provides an introduction to the Unified Dimensional Model ("UDM").

End-users wishing to retrieve information directly from a data source, such as an SQL Server face a number of significant challenges. Here is a partial list of some of them:

• The contents of such data sources are often very hard to understand, as the software for these data sources are designed for use by system engineers and developers.

• Information of interest to the user is commonly distributed among multiple heterogeneous data sources, also usually rather sparingly documented if at all.
• The "standardization" SQL does prevent the suppliers of SQL Server to provide "rich" additions. It cannot be assumed that the data sources might always be a SQL Server, it can be of quite different types.
• Business rules can be anywhere, as nobody is forced to encapsulated them in the data sources.

Val first explained the basic end-user model provided by a UDM using an example and the respective scenario to demonstrate the benefits of using the UDM:

• UDM allows the user model to be greatly enriched.
• UDM provides high performance queries supporting interactive analysis, even over huge data volumes.
• UDM allows business rules to be captured in the model to support richer analysis.
• UDM supports 'closing the loop', where the user acts upon the data they see.

Val then showed different "starting points" for employing the Analysis Services in SQL 2005. Using examples, which are derived from existing applications Val gave an overview over the following areas: Key Performance Indicators, Attribute based dimensions, Security Cubes, Language Translations, and changes to MDX including scripting.

Track ASP.NET

The sessions of this track share a common theme: Using the Microsoft .NET to develop applications for the Internet and for pocket PCs - in each case also in combination along and/or as addition of the development with Visual FoxPro. Included were sessions on working with .NET Web Services in Visual FoxPro, about the differences and similarities between Visual FoxPro and VB.NET and a session about localization of VFP and VB.NET applications.

This track will run only on Thursday with 8 sessions. 2 will be presented in German, 6 in English. 3 Speakers will share the burden: Rick Strahl, Les Pinter and Michael Bätge.

Localization of Applications in VFP and VB .NET

In his session Les Pinter described techniques for building multi-lingual Windows Forms applications in VFP or VB .NET, or Web Forms applications in VB .NET, using both the Resource file technology recommended by Microsoft and an alternative using database tables. He also discussed advantages and disadvantages of each technique.

Les, who speaks five languages fluently, asked the rhetorical question: The world is getting smaller: For small amount of money, you can travel to any country, have dinner there, watch a movie, sleep a few hours, and wake up on another continent. Your software can travel even more easily. When it gets there, will it be ready to go to work?

In Visual FoxPro, Steven Black's INTL utility is all you need, and I will describe briefly today. In .NET, it's the Microsoft way: The .NET documentation recommends that you use resource files. Behind each form, there's a .res file that can be used to store the strings that appear on the form, where you can put corresponding translations into other languages.

Unfortunately, that's not always the best way to do it. If you use res files, you have to make any required changes to them, which means that:

• You're responsible for the translations; if your users don't like your choice of terms, it's a technical support issue.
• Different countries that speak the same language use different words for the same thing: computadora, ordenador and PC are three Spanish translations for "computer".
• If ten or twenty screens all contain the same word, the translations for that word have to be included in all ten or twenty res files.
• Users have to wait for a new res file in order to get their screens fixed.

In this session Les provided a VB .NET methodology and a utility that allows users to translate their own screens, and a couple of classes that you can put on your base inheritable forms to prepare screens for translation and to allow users to change language on each screen. The examples presented in the session were in Visual Basic.

Les has only mentioned Steven Black's INTL utility briefly. It consists of a PRG called INTL,+ which describes a class containing methods for storing and retrieving translations and assigning them to form captions. Menu translation is accomplished using GENMENUX and calls to a pair of functions that ship with INTL. INTL is my model for the application presented in this article, and my tribute to Steven's amazing programming skills.

Allowing users to maintain their own translations of their screens solves some serious maintenance headaches, and uses available talent. You can use a local MDB or a set of FoxPro tables for the translation subsystem, or integrate it with your SQL databases. Either way, it's a powerful tool to add to your toolkit, and will open up new worlds for your software.

Accessing and sharing data with .Net Web Services

Rick Strahl demonstrated how to access .Net Web Services from Visual FoxPro and exchange data in a variety of ways. This session focused on how to pass and update shared data between client and server applications efficiently utilizing the ADO.Net data model and Web Service architecture.

Using Web Services from Visual FoxPro is not difficult, but dealing with Data or Complex objects is not quite as straightforward as it could be. Rick started with a basic review of creating a .Net Web Service and calling it from Visual FoxPro. The he went on to more advanced examples that demonstrated passing DataSets from .Net to VFP forth and back and how to process them with the XMLAdapter class in Visual FoxPro.

Accessing Web Services from Visual FoxPro is nothing new. But effectively leveraging Web Services and interacting with them especially when dealing with .Net requires a more complete understanding of the mechanics and the tools available to Visual FoxPro developers, than merely creating the service and firing off method calls.

Using the SOAP Toolkit from VFP is straight forward and calling .Net Services is no different than calling any other service. But things get sticky when running not simple 'Hello World' type examples that returns little more than simple types. The issues involved in Rick's session dealt with the fact that the SOAP Toolkit (and most other tools SOAP Tools available to Visual FoxPro) are woefully underequipped to effectively pass and translate complex types.

In his session Rick specifically showed how to pass DataSets and objects between Visual FoxPro and a .Net Web Service and demonstrates how the simple call model falls quickly apart when using complex types and datasets. It's not difficult to work around these issues, but Rick pointed out that a Visual FoxPro developer has to realize that there will be extra work and likely some manual XML parsing to make this happen.

In his session Rick kept everything pretty simple from an application point of view to demonstrate the Infrastructure mechanics, but he assured his attendees that the same principles are also working with more complex data scenarios that involve multiple tables or hierarchical objects. All examples have been base on Visual FoxPro 8 (or later), Microsoft .Net 1.1, Microsoft IIS and SQL Server or MSDE with the Pubs Sample Database installed.

Porting a VFP Web application to ASP.NET

You've probably heard a lot about ASP.NET over the last year or so and you're wondering: Is .Net all that it's cracked up to be and is there a real benefit? In this session Rick Strahl was not giving an answer to this question but Rick demonstrated to the attendees what is involved in taking a Visual FoxPro application and move it to .Net. The final result is a good comparison case that can showcase the strengths and weaknesses of each platform.

ASP.NET has become a very popular solution for building Web Applications on the Microsoft platform and Visual FoxPro developers are eyeing the platform as a potential platform for their Web applications. While ASP.NET can be used in conjunction with Visual FoxPro via COM interop, the mechanisms involved in this endeavor, are neither optimal nor a good fit for the application development process due to the limitations of COM objects hosted inside of IIS.

The Application, which Rick converted to ASP.NET was a Web Store application including a Fat Client interface originally created with West Wind Web Connection and a set of business objects. It was ported to .Net including a business object framework. Rick showed the application implementation, the framework design, the time to build and deploy, including performance comparisons and other related issues. Rick's aim was to remove some of the prejudices that have built up against both VFP and .NET and look at what's realistically involved.

Rick pointed out that ASP. Net is the only way to go. If a developer wants to stick with Visual FoxPro as his/hers development language this can certainly be done. The tools to build Web applications are available and are quite capable to build any kind of Web application. Rick pointed out that ASP. Net provides a model for building Web applications that is simply more elegant and more efficient to provide stable and scalable platform to run Web applications on.

Introduction to software development for Pocket PCs

First of all Michael Bätge gave an overview on possible uses of Pocket PCs in real world scenarios. Then he explained the architecture of the hardware built into a Pocket PCs and what to consider, when developing software for these device - for example the small amount of memory and the small display resolution compared with regular PCs.

After that he lectured about some of the several existing software development environments and languages that are available. He shortly explained the advantages and disadvantages of these tools and gave some hints, why some are more common than others. Then he explained databases, which are available for Pocket PCs.

Michael outlined several possible ways to design application using Microsoft .NET technology. He explained in detail how to build web-based applications with the help of ASP.NET Mobile Controls and how to build client-based applications using .the NET Compact Framework. The first is only then a viable way, if the device has a constant access to a Web Server. The second option applies if an application is to be designed that should run directly and only on the device.

The .NET Compact Framework

In his second session Michael Bätge presented his way and his view on how to develop smart applications on smart devices applications with the .NET Compact Framework ("CF") and SQL Server CE edition.

Michel told the audience that Visual Studio 2003 contains the Smart Device Extensions, which is needed to build applications with the .NET Compact Framework. He included two demos showing how easy it is to access data stored in a SQL Server CE database: this task could be accomplished with six or seven lines of code. He showed the application working on his handheld device, which displayed data in a live grid control on his Pocket PC. These data originated from his notebook.

Michel continued to show several ways, how to work with data coming from a Visual FoxPro database running in a mobile application. Here he explained two web-based solutions. Both used ASP.NET Mobile Controls. One solution retrieves the data via a VFP COM object. The other solution used data, which it directly received from a Visual FoxPro database using the VFP OLE DB provider. The solution, which was developed, used the .NET CF, consumed data from a VFP Web Service.

After that he gave an overview of future technologies: .NET Compact Framework 2.0, SQL Server 2005 Mobile Edition and "Magneto", codename the future Windows Mobile Edition.

VFP and VB .NET Side-By-Side: More similar than different - or - More different than similar?

This is the second day of the SQL-Server & ASP.NET conference. Today we only will have sessions from the SQL Server track. All together this will be 7 sessions.

TRACK Microsoft SQL-Server - continued

Data Import and Data Presentation (OLAP3/4).

This track starts with the continuation of Sebastian's sessions on OLAP.

A DataWarehouse requires pre- and post-processing in order to put data into it or to retrieve data from it. In this session Sebastian describes these processes and their technical realization. The knowledge of these processes and the tools involved are vital to use DataWarehouse to store and present data.

In the first part of this session the capabilities of the Data Transformation Services (DTS) have been explained. These DTS comes with the Microsoft SQL Server and their purpose is to acquire data from various data sources and to store them into a DataWarehouse residing on a SQL Server. Sebastian used realistic examples to demonstrate the use of DTS with special emphasis on accessing Visual FoxPro data.

In the second part this session Sebastian introduced the tools, which can be used to present the data of DataWarehouse. The tools also come with the Microsoft SQL Server. Sebastian demonstrated how to use these tools to present the data of a DataWarehouse, which reside on a Microsoft SQL Server, to the end user in a way, which at the same time is technically clear and optically impressive. In this context Sebastian also demonstrated the use of various frontend tools and the Report Service of the Microsoft SQL Server.

API programming, e.g. with VFP (OLAP4/4).

The Analysis Services, which come with the Microsoft SQL Server, offer a great wealth of possibilities to store and retrieve data in hierarchical data structures.

In his fourth and last session in this topic Sebastian showed, how the API interface can be used to manipulate these hierarchical data structures and the data contained in them. This includes administrative tasks using the Decision Support Objects as well as data access via ADO MD using the query language MDX. Sebastian demonstrated their use in very appropriate code examples written in VFP and VBA. All these examples, which are included in the session notes, can be put directly into operation by each of the attendees.

Introduction to SQL Server Reporting Services.

In this session Dan Jurden introduced the SQL Server Reporting Services to the audience. SQL Server Reporting Services is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports.

This session Dan covered the integrated architecture of SQL Server Reporting Services, Report Authoring, Report Management and Report Delivery.

Build SQL Server into your App with DMO.

In this session Daniel LeClair showed, that, for someone familiar with using Visual FoxPro, it is not a complicated task to use Microsoft SQL Server. VFP connects to an existing database inside an instance of SQL Server via ODBC or OLE DB in most cases. So, manipulating data is straight forward with these technologies. But what to do if you have to do even additional tasks, like creating a daily backup job for the application's database, creating and configuring database access accounts, or tweaking settings of user roles inside your SQL database? Well, SQL Distributed Management Objects (DMO) are at your fingertips to realize these kinds of tasks.

Acknowledgements

Well, very important: He did it! Yes, he did it! Again! With the precision of an experienced expert, Brent Speedie again not only found lots of our typos, he also cared for our grammar and for the proper use of words, idioms, and phrases. Thank you, Brent!

There is no event like this one with continuous working center of gravity holding all the centrifugal forces, like registering attendees, checking for the correct reservations, forgetting starting time (he/she knows, who is meant), forgetting papers (see left) and so on: These genius, who master the art of omnipresence and omnimemory (freshly invented!), were Tina Flieher-Ojen, Andy Sicenica and Sven Wilke.

The reporters also would like to extend their thanks to Michel Fournier and Evan Delay. Michel enabled UT and he invented this type of reporting. Evan took care of the proper placements of the announcement in UT. Above all, both showed remarkable patience with us because we constantly missed the deadlines. Thank you, Michel! Thank you, Evan!

Hans-Otto Lochmann, Dr. Lochmann Consulting Gmbh   In the early 1970-s I spend my first professional years writing software for process control systems, using assembler and FORTRAN. I got my first PC in 1980 and bought dBase II. A couple of projects later dBase III Plus annoyed me with its "abnormalities" (nice word). In 1989 someone pointed me to FoxPro, which since then is my favorite development tool. Since January 1993 I am a member of the German user group. The people who take care of FoxPro have my deepest admiration, because they always have been able to place VFP at the top of the trade and they seem to be determined to keep it that way. Besides VFP I also use VB/VBA and Office Automation in my projects. Today I earn my living as partner of a very successful recruitment organization in Germany (http://www.baumann-ag.com).

Armin Neudert, TMN Systemberatung GmbH   Armin is co-owner and one of two managing directors of TMN Systemberatung GmbH located in Ilsfeld (near Stuttgart, Germany that’s where the Mercedes and the Porsches come from). Armin’s first experiences with FoxPro have been with FoxPro for Windows 2.6. Over the years he worked in large projects where he got experience with MS SQL Server, UML, Multi Tier Architecture, COM+, Visual Basic, etc. Armin uses Visual FoxPro as his main development tool. Since 1994 he is a Regional Director for the German FoxPro UserGroup dFPUG. He arranges monthly meetings in the area of Stuttgart, where he often does sessions, keeps people up to date with the latest information and keeps the FoxPro community together. Armin is also a speaker at other user group meetings in Germany and at the German FoxPro conference in Frankfurt. Armin is also a Microsoft Certified Professional for Visual FoxPro.

Jochen Kirstätter, Kheops Gmbh   Jochen Kirstaetter, aka JoKi, he has been doing various jobs in internet and desktop programming since 1995, initially using Perl, PHP and MySQL, soon he also used VFP, Sybase and MS SQL for his project; recently he also included AFP; presently he works for Kheops, mainly as a member of teams working on client applications; his scope of skills now includes a thor-ough knowledge of xCase, of Visual UML and of the Wise Installer. Besides that he recently became chairman of a local branch of dFPUG in Speyer (a German city). Through these activities he is well known in the German community.