Integrating InfoPath with SQL Server 2005
I discovered InfoPath only a few months ago and started wondering how it could be used in enterprise offices. InfoPath does a great job of building forms for publishing XML data quickly. It not only makes the task of building CRUD (create/retrieve/update/delete) applications for Web-services really easy but with the introduction of the .NET extensions for InfoPath, it is possible to build really diverse InfoPath application. So how do you build desktop productivity applications in InfoPath that offer asychronous execution and reliability that traditional web-services over HTTP do not offer?
Consider an enterprise that processes forms at the client and ships XML data to a server for background processing. Typical applications would be in order-processing or inventory systems. The data would have to be delivered reliably. The execution should be asynchronous since the server maybe busy processing earlier orders or the client may not have a connection with the server (eg> a mobile client). See figure 1.
With SQL Server 2005's Service Broker feature, the task can be accomplished quite easily. The clients would require Infopath, the .NET Framework and SQL Server 2005 Express (free version which comes with Service Broker). The server would need to run a standard of enterprise version of SQL Server 2005. InfoPath can submit form data to SQL Server using the InfoPath 2003 Toolkit for Visual Studio .NET and ADO.NET. The hand-on lab on writing business logic using managed code shows how to write managed code behind an InfoPath form. Figure 2 illustrates how an InfoPath form can interact with Service Broker.
You can write code behind the form that handles the form submit event and connects to the local SQL Server instance to begin a conversation with the remote service and send the XML data as a message.
The service running on the message processing database server will queue the requests arriving from the clients and process them as they arrive. The service program running on the server as either a standalone application or an internally activated stored proc could process the request and send a request back on the same conversation (Figure 3). The client would need some application that receives the response and notifies the user, maybe using a task-bar popup.
The service program running asychronously could further interact with other systems such as web-services and e-mail in order to process the request. One scenario is depicted in figure 4.
Using InfoPath 2003, Visual Studio .NET 2003 and SQL Server 2005 Beta 2 it is possible to build such interesting asynchronous office productivity applications today. I'll try to post a sample soon.