Dynamic Efficient Querying of Large Datasets

Folks,

I have done a lot of work with making InfoPath live up to it's promise of providing an offline experience.  That promise becomes a lot harder to fulfill when you are querying large datasets in your form to populate one dropdown lists or more likely a series of cascading dropdowns.   The issue is that you need the whole dataset returned in order to work with it and filter it.   At least that's true in the traditional sense where you are lazy and don't want to refine it at all (describes me pretty well).    So I got pinned down by a customer the other day who said they they don't want to pull back a giant dataset, but rather want to pass parameters to a SQL Stored Procedure which then performs a much more intelligent query that only brings back the data they need.   I thought that was a swell idea, but had no idea how to pluck values out of the form and pass them as query parameters to a stored procedure.   So it turns out that someone wrote a whitepaper on just that, which I'm providing a link to right here

https://support.microsoft.com/default.aspx?scid=kb;EN-US;827007

 

Now, I don't claim to know anything about Stored Procedures, although it's on my list of things to learn in the short term. What I will tell you is that when I posted this problem to a set of internal gurus, I got some interesting responses where you could simply build a dynamic query in code that served the same end as a stored procedure. The code snippet below makes perfect sense to me and would probably be my preferred way to get this done. Check it out

 

1) Create a “query” button; use “rules and custom code” as its action, then click Edit Code

2) Customize the following code snippet to set the SQL command dynamically:

// get the value of the DOM node that we’ll use to append to the WHERE clause

currentElementID = XDocument.DOM.selectSingleNode("/mf:myFields/my:currentElementID").text;

// get the data adapter object

myDataAdapter = XDocument.DataAdapters("Revisions");

// set the SQL command property of the data adapter, providing a dynamic WHERE clause

myDataAdapter.Command = "select RevisionBy, RevisionText from Revision WHERE UIElementID = '" + currentElementID + "'";

// execute the query

myDataAdapter.Query();

Special credit goes out to Scott Heim and Alex Vaynshteyn on this one.