UDCX files in Sharepoint Infopath and dynamic queries

Confused? Can we use dynamic queries in the infopath and UDCX combination? UDCX are meant for not writing any code to get data from database and to show it up the retrieved data on the infopath form. They allow only STATIC queries. Just straight SQL or SPROC names  and parameters to it. But, I got a requirement where I need to pass some dynamic values to the query/SPROC at runtime and gets the data and loads the data on the form. Can I achieve that with same UDCX connections and same architecture?
YES, There is a way to do this. The things to note here are:

1. Using UDCX connections, we have saved all the connection data, query data and credentials data on a single file in SharePoint list/library.
2. Query should be correct and executing without any issues [Otherwise infopath cannot download the resulted schema]. For example, you want to get the user by user name then you may created SPROC with name "GetUserByUserName" and in your UDCX file you give the query tag as "<udc:Query>EXEC "dbo.GetUserByUserName" 'DEFAULT'</udc:Query>". We know there is no record in database with the name "DEFAULT". But, this is what we have to give as default query. [This is what we will change in the c# code dynamically.]
3. Read this connection, query in the infopath c# code.
4. Change the query information in which way you want in code.
5. Execute the query.
6. Reset the UDCX connection information back to original.
7. It will automatically refresh the control data depends on the latest result set after we executed from the C# logic.

So, I believe you got complete picture of what we are going to do. This is very simple but difficult to get the idea. With this implementation I solved the big problems what I had.
To execute the below code I am assuming there is a UDCX connection file available in a SharePoint library and your infopath form is allowing c# code.

//Get the connection details by connection name 
AdoQueryConnection adoConnection = (AdoQueryConnection)DataConnections["Get_User_Details"];
if (adoConnection != null)
{
string orgCommand = adoConnection.Command; //To read original command 
int index = orgCommand.IndexOf("DEFAULT"); //Find where the keyword "DEFAULT" in the command string 
string SPROC = string.Empty;
if (index > -1)
{
try
{
SPROC = orgCommand.Substring(0, index); //Get only the SPROC name. 
adoConnection.Command = string.Format(SPROC + userName + "'"); //Append user name to the query. 
adoConnection.Execute(); //Execute the final query. This is what the command which contains actual parameter value instead of DEFAULT string. 
}
catch { }
finally
{
adoConnection.Command = orgCommand; //Should not forget to write this. We have to do this. 
}
}
}

Things to note:

1. The connection name "Get_User_Details" is the connection name from infopath form [Managed Data Connections option].
2. As we are reading from existing UDCX connection file, we are not hard-coded any of the connection strings or queries.
3. userName is the string variable which holds the user name which comes at run time. You have to write some logic to get these in your code.
4. Read the query and replace the dummy parameter values with the original values.
5. Execute the connection.
6. In finally block, we are resetting the command back to original.

That's it!!! If you are binding this information to the textbox then you should do one final thing as shown in below figure.

The checkbox in above figure "Update this value when the result of formula is recalculated" and applies to only if you use the formula.

We are done. The data now comes from database and passed the parameters to database dynamically, used UDCX connection file and did not hard-coded any of the connection, query information in code. Very clean right?

Hope you understood it well and liked it.