Dynamic SQL Data connection to InfoPath

There is no possible out of the box way to setup a dynamic SQL data connection to InfoPath form. When we use SQL table as a secondary data source in InfoPath, there is no option to filter data in SQL before loading into InfoPath.

Using InfoPath connection wizard, connection you setup is a plain select statement on table you select. If your backend SQL table has thousands of rows of data, all of it needs to be loaded first to form before you can filter.

But you can write custom C#/VB code on button click event to add the filter string to the connection you created. Here is sample code to do that.Dynamic SQL Data connection to Infopath

I have created a connection named ‘People’ to pull data from my People table in database. Using the code behind, I am adding modifying the SQL query, which is a plain Select statement and adding a filter value. I am getting the value for filter from a field in the form, in this case it is UserName. This code should be added on button click event.