InfoPath Dev

Hi,

I'm working on a summer project creating an InfoPath 2010 form which will submit to an SQL (2008 R2) database.

The form is a web browser compatible form library form. The reasoning behind using a form library form is that it will have some custom code and that it's fairly complex, both in structure and the data it operates on.

Now I want to submit this form to SQL. The original plan was to use BCS and SharePoint external lists, but I'm starting to wonder if I closed that door when I started creating a form library form?

So, what are really my remaining options for connecting to the database? Do I have to make a web service, or can I accomplish something with code, SharePoint + BCS or some InfoPath submission feature that I've missed?

I hope someone with experience on this can find the time to reply. Thanks!

 Edit: The system is a competence mapping system. Any suggestions or recommendations as to what would be the optimal data connection approach for this form is very welcome.

Lars

Submitting to SQL is a classic InfoPath problem. Here's what was discussed in an earlier thread:

1) To use BCS with your form library, you'd create external lists for each table and/or view that you need to use, and then you'd consume these external lists as data connections for read purposes.  Then, for write purposes, you'd submit the form to the form library like usual, then fire off an SPD2010 workflow that updates the external lists using "update list item" and "create list item." Whatever you update in the external list would go to the DB, and if you need it to update a view, then you need to use a virtual table with appropriate triggers on that view in the DB so that when an update method is triggered on that external list, SQL knows what to do.  This method duplicates the data in the form library unless you delete the form with your workflow after writing the data to SQL.  Also, external lists have a default limit of 2000 items, which can be raised with a Powershell command, but if you raise it, then performance could become an issue.  Also, even though InfoPath can do parameterized queries on lists, it cannot actually send a parameterized query all the way to the DB.  Instead, it only sends it to the external list, which is on the WFE, and the WFE must then pull the data into memory before InfoPath can filter it.  So, if you have 100,000 records and only need 5 with your query, the WFE has to first pull in all 100,000, and then InfoPath pulls in 5 of them.  Not good.

2) The recommended method by the InfoPath team is to create custom web services for direct interaction with the form.  This is a security hassle with browser forms, but it can be done.  You'd need to use a Secure Store identity (group type probably) in your Secure Store service application and reference it in the Universal Data Connection that you create by converting your web service DCs in the form after creating and adding them.

3) Qdabra (owners of this forum/site) may have a solution for you that does all of this and may be worth the cost instead of writing it all yourself from scratch.  They can shred data into SQL for you with a tool they've already built and sell.