Using InfoPath 2010 with SQL Server 2008
Get an overview of the use of InfoPath 2010 with SQL Server 2008 databases.
There are two ways you can use InfoPath 2010 with SQL Server: 1. To retrieve data from a SQL Server database, and 2. To submit data to a SQL Server database through InfoPath data connections.
Where submitting data is concerned, you can choose to submit the values of form fields to separate columns in a SQL Server database table or you can choose to submit an entire InfoPath form to one column in a SQL Server database table.
The support for XML in SQL Server 2008 has been greatly improved compared to that in SQL Server 2005. Whereas SQL Server 2005 had some limitations regarding a few features and elements in XML schemas, which required you to perform workarounds if you wanted to create typed XML columns that were based on the XSD of InfoPath forms, those limitations are absent from SQL Server 2008.
Therefore, it is much easier to use the XML data type on columns together with XML schema collections to create typed XML table columns in which you can store one or different types of InfoPath forms. You can read more about implementing XML in SQL Server 2008 R2 on MSDN.
While you can directly connect InfoPath 2010 to SQL Server to retrieve and submit data, the most common way to use InfoPath 2010 with SQL Server is to create a web service that can serve as an intermediary to retrieve and submit data to SQL Server from within an InfoPath form.
This web service would be similar to the web service you would have to create to retrieve data from a MySQL database or to submit data to a MySQL database with the main difference being that you would have to use classes from the System.Data.SqlClient namespace instead to perform CRUD (create-update-delete) actions on the SQL Server database.
Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.