How to get data from a MySQL database into InfoPath using a Web Service
This article shows you how to use a Microsoft .NET Web Service to retrieve data from a MySQL database and display the data in an InfoPath form.
You would like to retrieve data that is stored in a MySQL database and display this within an InfoPath form, but InfoPath does not allow you to connect to a MySQL database.
Use a Microsoft .NET Web Service to connect to and retrieve data from the MySQL database and then use the web service as a data connection in InfoPath.
This article uses a MySQL database named InfoPathDB, which contains a table named users. The table has 3 varchar columns: firstname, lastname, and email.
Before you begin, you should:
- Have downloaded MySQL and installed it.
- Have downloaded the ODBC connector for MySQL and installed it.
- Have created a MySQL database, created a MySQL table, and populated a MySQL table with data.
- Know how to create a .NET web service that retrieves data from a database.
Create a DSN to connect to the MySQL database
To create a DSN that can be called from within the web service:
- On the Start menu, click Run.
- On the Run dialog box, type odbcad32, and click OK.
- On the ODBC Data Source Administrator dialog box, click the System DSN tab, and then click Add.
- On the Create New Data Source dialog box, select the MySQL ODBC x.x Driver, and click Finish.
- On the MySQL Connector/ODBC Data Source Configuration dialog box, enter the required information to connect to your MySQL database, click Test to test whether the connection is working, and then click OK.
Figure 1. MySQL database connection settings on the MySQL Connector ODBC data source configuration dialog box.
Create a .NET web service to retrieve data from the MySQL database
- In Visual Studio, create a new Web Service project.
- Add a reference to the System.Data .NET assembly.
- Add a using statement for the System.Data and System.Data.Odbc namespaces to the code file of the Web Service.
Add the following Web Method to the code file of the Web Service:
public DataSet GetUsers()
DataSet ds = null;
using (OdbcConnection conn =
// Open a connection to the MySQL database.
// Retrieve the users from the MySQL database.
using (OdbcCommand cmd =
new OdbcCommand("SELECT * FROM users", conn))
OdbcDataAdapter adp = new OdbcDataAdapter(cmd);
ds = new DataSet();
// Close the connection.
The previous web method retrieves all of the users that are stored in the MySQL users database table.
- Build the project and deploy the web service.
Create an InfoPath form template to retrieve MySQL data
- In InfoPath, create a new Blank form template.
- On the Tools menu, click Data Connections.
- On the Data Connections dialog box, click Add.
- On the Data Connection Wizard, select Receive data, and click Next.
- On the Data Connection Wizard, select Web service, and click Next.
- On the Data Connection Wizard, type in the URL to the web service that retrieves data from the MySQL database, and click Next.
- On the Data Connection Wizard, select the GetUsers method from the list of operations, and click Next.
- On the Data Connection Wizard, click Next.
- On the Data Connection Wizard, click Finish.
- On the Data Connections dialog box, click Close.
- On the Data Source pane, select GetUsers (Secondary) from the Data source drop-down list box.
On the Data Source pane, expand all of the nodes under dataFields, and then drag-and-drop the Table node to the InfoPath form template (see Figure 2).
Figure 2. Secondary data source for the web service that retrieves data from the MySQL database.
When you drop the Table node on the InfoPath form template, select Repeating Section with Controls from the context menu (see Figure 3).
Figure 3. Repeating Section control bound to the secondary data source to the web service.
- Save your work and Preview the InfoPath form.
You should now have a fully functional InfoPath form so that when you open the form, the web service will retrieve data from the MySQL database and display the records in repeating sections on the InfoPath form.
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.
Related InfoPath Articles:
- How to submit data from an InfoPath form to a MySQL database using a Web Service
- Retrieve an InfoPath form from SQL Server and display it in a new instance of InfoPath
- 2 Ways to store InfoPath data in SQL Server
- Submit/retrieve an entire InfoPath form to/from SQL Server
- Submit an InfoPath form to a web service and then retrieve return values