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.

ADVERTISEMENTS

Problem

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.

Solution

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.

Discussion

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:

  1. Have downloaded MySQL and installed it.
  2. Have downloaded the ODBC connector for MySQL and installed it.
  3. Have created a MySQL database, created a MySQL table, and populated a MySQL table with data.
  4. 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:

  1. On the Start menu, click Run.
  2. On the Run dialog box, type odbcad32, and click OK.
  3. On the ODBC Data Source Administrator dialog box, click the System DSN tab, and then click Add.
  4. On the Create New Data Source dialog box, select the MySQL ODBC x.x Driver, and click Finish.
  5. 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.

MySQL database connection settings on the MySQL Connector ODBC data source configuration dialog box.
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

  1. In Visual Studio, create a new Web Service project.
  2. Add a reference to the System.Data .NET assembly.
  3. Add a using statement for the System.Data and System.Data.Odbc namespaces to the code file of the Web Service.
  4. Add the following Web Method to the code file of the Web Service:

    [WebMethod]
    public DataSet GetUsers()
    {
      DataSet ds = null;

      using (OdbcConnection conn =
        new OdbcConnection("DSN=InfoPathMySQLDSN"))
      {
        // Open a connection to the MySQL database.
        conn.Open();

        // Retrieve the users from the MySQL database.
        using (OdbcCommand cmd =
          new OdbcCommand("SELECT * FROM users", conn))
        {
          OdbcDataAdapter adp = new OdbcDataAdapter(cmd);
          ds = new DataSet();
          adp.Fill(ds);
        }

        // Close the connection.
        conn.Close();
      }

      return ds;
    }

    The previous web method retrieves all of the users that are stored in the MySQL users database table.

  5. Build the project and deploy the web service.

Create an InfoPath form template to retrieve MySQL data

  1. In InfoPath, create a new Blank form template.
  2. On the Tools menu, click Data Connections.
  3. On the Data Connections dialog box, click Add.
  4. On the Data Connection Wizard, select Receive data, and click Next.
  5. On the Data Connection Wizard, select Web service, and click Next.
  6. On the Data Connection Wizard, type in the URL to the web service that retrieves data from the MySQL database, and click Next.
  7. On the Data Connection Wizard, select the GetUsers method from the list of operations, and click Next.
  8. On the Data Connection Wizard, click Next.
  9. On the Data Connection Wizard, click Finish.
  10. On the Data Connections dialog box, click Close.
  11. On the Data Source pane, select GetUsers (Secondary) from the Data source drop-down list box.
  12. 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).

    Secondary data source for the web service that retrieves data from the MySQL database.
    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).

    Repeating Section control bound to the secondary data source to the web service.
    Figure 3. Repeating Section control bound to the secondary data source to the web service.

  13. 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.

 
 Subscribe for updates via RSS or Email

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.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services