The Path to WTF is Paved with Good Intentions

Let's say that I have a stupid report that needs constant attention and maintenance because of waffling business requirements. Rather than buying a fruit bouquet for the server admin because of all the promotes I'd be doing, one solution might be to dynamically fetch the SQL for the report from a table. That way, all I would need to do is run a single UPDATE statement, COMMIT, and it's done!

Better yet, what if I wrote a generic procedure that could call any of the SQL my query table. How enterprisey!! This seems to be the general intenet of the original developer behind a web app that Patrick Magee recently inherited.

First, here's the code that is used to fetch the SQL and run it:

OleDbConnection connection = new OleDbConnection(gobjConfig.GetConnectionString());
connection.Open();
cmdText = "select query from datacommand where commandname = " + szText;
OleDbCommand command = new OleDbCommand(cmdText, connection);
object obj2 = command.ExecuteScalar();

// adds some params to his query here
cmdText = this.ParseQuery(obj2.ToString(), ref xnParameters);

// and executes it back here
OleDbDataAdapter adapter = new OleDbDataAdapter(cmdText, connection);
dataSet = new DataSet();
adapter.Fill(dataSet);
adapter.Dispose();
connection.Close();
connection.Dispose();
command.Dispose();

Not so bad. Could stand some tweaking but overall, it should work.

Well, before firing back in the comments "Where's the WTF?", consider the fact that the below is a sampling of the top 10 SQL queries that are fetched and run by the above code...out of more than 100.

Welcome to Patrick's hell.

CommandName	Query	QueryDescription
WebPortalUser	SELECT U., S.Name AS SectorName FROM webportaluser U, Sector S WHERE U.PKey = {Parameters.UserKey} AND U.SectorKey = S.Pkey	Gets a web portal user record for edit or view
TermsAndConditions	select text from termsandconditions where pkey = 1	NULL
Menus	select n.name, n.tooltip, m.packagename, m.eventname, m.targetframe, m.parameters from navigationmenu m, navigationmenuname n where m.pkey = n.navigationmenukey and m.menukey = {Parameters.MenuKey} and SectorKey = {Parameters.NavSectorKey} AND n.targetlanguage = '{Parameters.Language}' and m.accessprivileges like '%{Parameters.Privilege}%' order by itemorder	Retrieves the menus for the navigation screen
LocalUsers	select wu.pkey, wu.username, wu.firstname, wu.lastname, wu.emailaddress, wu.locked, rd.description as status from webportaluser wu, referencedata rd where wu.billinguserkey = {Parameters.BillingUserKey} and wu.privilege in('USER', 'LOCALADMIN') and wu.privilege = rd.code and rd.codetype = 'PRIVILEGE' AND wu.RecordStatus = 0 order by wu.username	Provides a list of local users for Billing User Admin
Privileges	select code, description from referencedata where codetype = 'PRIVILEGE' and data1 >= {Parameters.Level} order by sortorder	NULL
YesNo	select code, description from referencedata where codetype = 'YESNO' order by sortorder	NULL
PaymentTypes	select code, description from referencedata where codetype = 'PAYMENTTYPE' order by sortorder	NULL
PaymentMethod	SELECT *, YEAR(StartDate) AS YYStartDate, REPLACE(STR(MONTH(StartDate), 2, 0), ' ', '0') AS MMStartDate, YEAR(ExpiryDate) AS YYExpiryDate, REPLACE(STR(MONTH(ExpiryDate), 2, 0), ' ', '0') AS MMExpiryDate FROM PaymentMethod WHERE WebPortalUserKey = {Parameters.UserKey}	Gets the PaymentMethod record for a billing user.
DefaultMenu	select packagename, eventname, targetframe, parameters from navigationmenu where menukey = {Parameters.MenuKey} and defaultitem = 1	NULL
Gender	select code, description from referencedata where codetype = 'GENDER' order by sortorder	NULL

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!