If you've done 20 InfoPath applications using SQL, you probably don't care about the limitations:
-
Data isn't saved as XML - rich data types (images, rich text, files, etc.) don't roundtrip, can't refer to a signed "document" later since the data doesn't exist in one XML piece in SQL
-
Data locking and permissions cannot be controlled - users can overwrite one another's data, users can see all of the data (can't be locked out based on user roles, active directory group membership, etc.)
-
Changes to your SQL database schema will invalidate previously saved XML files (if you have them on a local computer) and force application downtime (while the changes to the server are made)
-
Browser forms don't submit to SQL (due to double hop authentication issues)
-
SQL rows cannot be dynamically queried based on parameters in your InfoPath form (you can't populate a dropdown based on a server-side filter)
-
Etc.
That's a list of the main reasons why you should consider a Web service. Web services can also scale to handle larger and more complex forms. Web services aren't a panacea. In general, most organizations make the mistake of creating one Web service per form (XSN). InfoPathDev and Qdabra Software (which pays for this site) advocate data-driven Web services for the following reasons:
-
One Web service suite for all forms - XML to SQL mapping is defined in a data file that can be updated out-of-band, not interrupting the application uptime
-
Less IT maintenance due to less proliferation of Web services and code
-
XML is saved in database enabling 100% roundtrip fidelity for all rich data types
We built the first (and to date the only) data-driven Web service for XML forms back in 2004. In addition to the above features, we've added many more in the intervening years, including:
-
Document level permissions - control who reads and writes what document based on data in the form
-
Extensive tool set, for example:
-
Admin Tool - create XML to SQL mappings
-
Document Migration - easily move thousands of forms and templates from one server to another
-
Document Deployer - create automated packages to deploy forms, templates and mappings
-
Bulk Editer - edit multiple XML in one step
-
Bulk Assignment - assign multiple documents in one step
-
Query Builder - build data-driven queries that run on the server for best performance
-
Report Builder - build complex reports based on XML data and export to Excel - no database mapping required
-
Client Dashboard that synchs with Web server (or SharePoint)
-
SharePoint integration - faster submits and queries when working with SharePoint
-
Cloud-based instances - ready-to-deploy Amazon cloud instances make it easy to bring a new Web service and backend online
-
... other stuff that I forget....
Perhaps the most important thing is it's a *very* inexpensive solution - we built it to help accelerate the many InfoPath Consulting projects we do for small, medium and large enterprises. We offer free trials and low cost server-based licensing to the InfoPath community. We guarantee it will save you time/money and solve the issues that come up with SQL integration, but it will also make your InfoPath life a lot easier in other ways.
OK, didn't mean to make this a total sales pitch, but as you can see I feel strongly about Qdabra's DBXL Web service suite. I understand if you aren't interested - you've already had success with your approach and I respect that. :)
Microsoft has training on how to create Web services. Qdabra also provides online and in-person training to help you create Web services, but DBXL's a better value since it's data-driven and can handle all of your XSNs and scale as you change them in the future. Lots of documentation on this site and on Qdabra.com.
Let me know if you want a free fifteen minute phone call to further discuss how you can get the most value out of InfoPath with your database.
Merry Christmas,