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!