If you've ever designed and deployed a form that will be frequently used or require significant data analysis, you have probably looked into maintaining data in a SQL database. With InfoPath 2003 and the InfoPath 2007 rich client, you get what you expect. You create a main database data connection to the SQL server and pick tables and columns that meet the requirements for submit functionality. When you open your form and click the "Run Query" button, you see the data pulled from the database as you'd expect. You then happily insert, update, or delete records and, when the time is right, hit "Submit". Luckily for you, the InfoPath client took care of maintaining the list of changes that you made while editing your form. With this list of changes intact, your updated data streaks back to the database to await the next query.
Enter InfoPath Forms Server... Here we don't get the change tracking for free, so we'll need to do some work to simulate change tracking outside of the form's execution. Basically, what we're going to try to accomplish is to use an intermediate web service that will handle querying and submitting the data from and to the target database. The web service will timestamp the data at query time and send the data to the form for editing. Then the form filling user will edit the data and click "Submit". When the data arrives back at the web service, we need to figure out what changed in the meantime. This means that we'll have to check to see if anything has changed in the database since the time when we queried the data. If it has, then the submitted data should be rejected and the user should re-query before re-applying her edits. If it hasn't, we'll diff the submitted data with the database data and submit the difference back to the database! Let's get started!
Create the Web Service and Setup the Database
Since the InfoPath data connection wizard is easiest to use when your web service is already established and available, let's start with creating the web service and setting up the database.
1) Download the attached archive and extract it somewhere on your hard drive
2) Create a new web site in Internet Information Services (IIS)NOTE: IIS must be enabled as a Windows Component through "Add or remove Windows components" in the "Control Panel")
3) Create the web service
4) Add the code and service asmx files to the project
5) Customize the web service code for your database
6) Create the database table and DML triggerThe web service includes logic to update the database table and create a DML trigger to maintain a timestamp of Last Update for each record. However, you may want to create the timestamp column and trigger yourself.
7) Build the Visual Studio solution and publish the web site
Design the InfoPath Form Template
Now that we've setup our database and constructed our web service to do the querying and submitting for us, it'll be a breeze to design an InfoPath form template based on the web service.
1) Design a new, browser-enabled form template, based on the web service that will query/submit the DataSet.
2) Set the default values for the "ID" and "QueryTime" fields
3) Insert the controls into the View.
4) Publish the form template to your InfoPath Forms Server
At this point, you have a form template that will work correctly when you open it in the InfoPath rich client. But this post is all about getting things to work correctly in the InfoPath Forms Server. So you'll need to configure your data connections to work in the browser by converting the main query and submit data connections to use Universal Data Connection (UDC) files in a Data Connection Library (DCL). Now you should be all set. The web service will query and submit the data to the database, and we'll make our best attempt at a diff of the database data against the submitted data.
From here on out, it's up to you. If you want to, for example, modify the database structure or change the way the trigger works, then you're going to need to modify the web service code. You'll also need to use "Convert Main Data Source" to update your form template whenever you modify your web service. You might also want to add support for multiple tables. All this will take some exploration of ADO.Net DataSets, but it is a reasonable exercise as long as you're comfortable writing managed code.
Forrest DillawaySoftware Design Engineer in Test
I am a newbie doing this. How do I customize the web service for my database? Which form do I go to? What do I put?
Please help.. or if you have any tutorials, please help!
Hi Pls Help.
I got " Could not open web service" error when trying to configure infopath form.
it says not enugh space is available to process the command.
Is this still valid today? There seems to be conflicting information from MS regarding the use of SOAP and XML web services to access SQL databases. i.e. Native SOAP web services being deprecated in SQL Server 2008 R2. In addition, I don't see the ASP.NET Web Service template in VS 2010 Ultimate. I want to submit InfoPath web form data in SharePoint 2010 to a separate SQL database for analytics. Is this the recommmended approach in August of 2010?
I got a question concerning Infopath2010...
Is there a way to write to SQL using Infopath WITHOUT a Sharepoint...?
I used Infopath 2003 for sending todo-list from our project managers to one of our dev teams and this did work pretty fine.
Now I was asked to connect to a SQL DB 2005 and i'm not sure if this is possible without a Sharepoint...
It seems like a TON of blogs from InfoPath 2003 and 2007 were copied into the InfoPath 2010 - but are they still relevant?
Website Helpline, Website Support +91-8010010000
Website Helpline India is a total website support company, Offer website support services under an annual contract at extremely affordable rates, We offers end-to-end website solutions which includes Domain Registration, Website Hosting, Website Design, Website Maintenance.
Website Helpline, Website Support, Website Maintenance, Website Helpline Solution, Website Helpline Service, Website Redesign Helpline, Website Supports, Website Help Support, Website Design Company, Web Based Applications, SEO Support India, Website Redesign
Call Now- +91-8010010000
Is there an update of this article that covers Infopath 2010, VS 2010 / 2012 and SQLServer 2008/2012?
thanks for the post. Its good pointer.