Creating a SQL Server User Interface with InfoPath
September 2, 2003
When Office 2003 is released about a month from now, some of the versions will contain two new applications OneNote and InfoPath. OneNote is a note taking application that may be interesting to those of you with tablet PCs. InfoPath is an XML based forms creation and entry application that looks very exciting to those of us who create applications that work with a lot of data on tablets and desktops.
InfoPath is one of several new applications on the cusp of delivery that enable the display and entry of data in a WYSIWYG forms based application without the need to write code. I am talking about:
Applications that make it easy to create data entry forms aren't new. For example, Access forms and Access Data Access Pages are both pretty easy. Adobe has long had forms data entry capabilities in Acrobat. The acquisition of Canadian company Accelio, formerly Jetforms, has enabled Adobe to add an XML based infrastructure to Acrobat 6.0.
What is different today is that the new forms based applications are enabling the creation of XML data by the application instead of in a proprietary format. They are also facilitating distribution of the forms over the web and entry of data both online and offline. That is, once created, the XML can either be saved on disk for later use or be sent to one of many possible locations for consumption.
XForms is a W3C standard for Web Forms to supersede HTML based forms on multiple platforms. The multiple platform aspect is key to the design of XForms and creators have attempted to abstract the form as much as possible from the implementation. The standard has reached the "Proposed Recommendation" stage of development. You can read more about it at: http://www.w3.org/MarkUp/Forms/
What has made the surge in XML based forms possible is the evolution of various XML standards. Among them are:
These standards each make it possible to specify one aspect or another of an application in XML rather than in code. The ultimate effect is to create the data in an XML format instead of a proprietary format so that it can easily be manipulated and transferred from application to application. InfoPath uses the XML based standards and melds them with Office technology.
To get InfoPath you'll either have to purchase it separately or have a volume license agreement for the "Professional Enterprise" edition. However, I have heard a rumor that for a limited time it may be included in the "Professional Standard" edition. Keep checking Microsoft's site for more details as the October 21st ship date approaches.
One of InfoPath's strengths is flexibility in the source of data that it can read and update. The choices include XML files, Web Services and databases. That is, as long as the database is Access or SQL Server. I suspect the list of supported databases will grow in future releases.
If InfoPath works well with SQL Server, I am hoping that it will be an easy way to create a robust GUI interface for SQL Server tables. That might save hours of writing application code. It is also a possible way to display SQL Server reports without having to have a report writer. Moreover, the resulting forms share many of Office's features such as spell checking!
This article shows how InfoPath can be used as a front end to a SQL Server database. I have kept the example deliberately simple: we will design an InfoPath form and enable it to edit the Regions table in the Northwind Traders sample database in SQL Server 2000. The table is about as simple as they get with just two columns: RegionID and RegionDescription. The information in this article is based on the Office 2003 Beta 2 version of InfoPath so there may be some changes to the product when it is officially shipped.