DrakeFox: First I rewrote most of a class I wrote last week. Basically a hackish helper library to translate a tagged SQL statement accepted through a jQuery XmlHttpRequest, validating it and helping translate the tags to the actual table data fields, validating the input. It feels like an ugly hack, but short of having to rewrite my data-access layer or switching to an ORM for accepting complex seardh queries, I don't know how. Any good advice here?
Wishbone: Depends. What exactly is it you're trying to do? It sounds like you want to use an XML based syntax to make queries against a database by translating the XML into SQL, is that more or less correct?
Not quite. More I've got one .dll project for data access (Data classes and classes for fetching/saving) another project for a service doing monitoring of external data and updating the database as needed (through the .dll) and a third project using the same .dll to make a HTML/javascript user interface.
Should've likely gone with an ORM for it, though I still have distrust that an ORM is going to be optimal for some of the discovery code I'm doing. So instead I've written my own data layer.
Specific example was a quick-fetch option on the user interface. Enter a value hit submit. Server code will check if it's a pure int value, and if so treat it like a reference to a specific item returning that item. Otherwise it'll try to fetch a piece of equipment from the database where name = @input or serial = @input
So my options for that latter bit was to either update the data access .dll with a method for this specific use case, or to first make one query for name, then one separate query for serial, join the two results, removing any objects which might otherwise show up twice.
Also foresaw this might be an issue if I want to make more complex queries later. So my stupid idea was to allow the javascript or server handler to send a string array. First string being semi-SQL, the rest being the string values of the parameters. So in this case [ "[NAME=1] or [SERIAL=1]", inputField.value ], basically a tag being alias for the field you want, what kind of condition (= > < or so on) and the index of the array which contains the referenced value.
The helper would pick out the tags, parse the condition and check the value is actually present, then defer to a Data layer implemented interface to have it translate what the field means in context to that class' sql queries, and handle parsing and conversion of the string data to check it's valid for the given field.
When all tags are parsed the output is something to plop into a where clause with the proper table names, comparison and parameters in place so the data layer class can use that to build it's query.
The thought being sanitation of input happens in the class which knows the data storage and a class specifically built for sanitizing and generating SQL conditions, and it'd allow the user interface to create rather complex queries without me having to update the data layer every time I hit an edge case.
And I realise this is a completely horrible waste of time and breach of good coding practices. But short of going with an ORM tossing all the work on the synchronization service and pretty much letting the UI layer make the data queries (through the ORM) I know no other good pattern to solve an issue like this.