Paradigm Patterns

This blog is complete rubbish. Who knew the blogger release was going to fubar my blog so badly? Sigh... Well. I did kind of ignore this for about the last 4 years so it's my fault. I don't know if I have the energy to try repair my previous posts, or if I should just delete them all and start fresh?

I came across this code today. This is the ultimate anti-pattern for ASP.NET and jQuery. The idea here is that when a user clicks on the cdbDriverConsent checkbox; if the customer account is "inProbation", the "probationBox" will slide down with further instructions. The problem is, when looking at strictly the markup. There is absolutely no way of knowing that this is what is happening. The markup looks like this: ' ToolTip="I attest" /> ... In the code behind the OnItemDatabind function grabs the checkbox and assigns a jQuery click handler: protected void dgDIR_OnItemDataBound(object sender, DataGridItemEventArgs e) { if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { ... if (IsOnProbation()) { ...

To follow is an example of an anonymous procedure using a nested case statement in SQL. The cursor declaration is 'ad hoc' for use in Toad. BEGIN OPEN :pResultSet FOR SELECT pc.cdbcustomerid, CASE WHEN (pc.typeid = 1 OR pc.typeid = 2) THEN (CASE WHEN ( pc.motor_passenger_carrier IS NULL OR pc.motor_passenger_carrier = 'Not Specified') OR ( pc.inter_intra_state IS NULL OR pc.inter_intra_state = 'Not Specified') OR ( pc.discovered_psp_via IS NULL OR pc.discovered_psp_via = 'No Information Provided') THEN 'No' ELSE 'Yes...

Conditional expressions in where clause: procedure get_demo_by_carrier_scope (pIsInterstateCarrier in char,pResultSet out sys_refcursor) AS BEGIN OPEN pResultSet FOR SELECT pc.dba_name, pc.cdbcustomerid, pt.type_name, pc.inter_intra_state FROM psp_customer pc JOIN psp_customer_types pt ON pt.typeid = pc.typeid WHERE (pIsInterstateCarrier ='A' OR pc.inter_intra_state = CASE WHEN pIsInterstateCarrier = 'Y' THEN 'Interstate' WHEN pIsInterstateCarrier = 'N' THEN 'Intrastate' END) AND (PC.TYPEID = 1 or pc.typeid = 2); -- MC and IAH only END get_demo_by_carrier_scope;;

I recently added an ad hoc column to a heavily used query with paging and sorting. To get the sorting to work on the new aliased column it was necessary to pull the "row_number() over" clause in to an outer query. I just wanted to document how this was done for future reference. SELECT * FROM (SELECT my_table.*, ROW_NUMBER () OVER ( ORDER BY UPPER ( DECODE ( pSortDir, 'asc', DECODE (pSortOrder, 'HasDemographicData', has_demographic_data pc.legal_name))), UPPER ( DECODE ( pSortDir, 'desc', DECODE (pSortOrder, 'HasDemographicData', has_demographic_data ...

Prefix your cursor variable with a colon ":" begin event_api.get_activity_data(1, null,'2013', null, null, null,:pResultSet); end; Toad will prompt you for the type. Select CURSOR And voila! You will have the cursor dumped into a data grid within Toad!

I recently had a requirement to track changes to fields with certain records. What I needed to track was the id of the record that changed, the field that changed, the old and new values and who and when it was changed. Credit must go to the "Ask Tom" forum for the basic idea. My modification to thie original idea was to track changes at the record level rather than the table level. So I am able to access changes to a specific record with any given entity. Here's a link to the original inspiration: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055 In Oracle, a trigger gives you acces to two variable ":old" and ":new"; :new and :old are like bind variables to the trigger, they are not 'regular' variables. you cannot dynamically access them, only 'statically'. The challenge then is to write a trigger that statically references the new/old values. First of all we need a table to track chan...

Recently we had the need to pull user profile information independent of the Microsoft provider framework. The property values are stored in a table that contains a "PropertyNames" field with meta information on the start position and length of the property value being stored. This self-describing storage method is pretty slick but attempting to use the built in wrapped function for retrieving the property values proved to be more challenging than simply writing our own parser. There are two SQL functions. They are used in this way: SELECT p.USERID, m.EMAIL, GETPROFILEELEMENT('FirstName', PropertyNames, PropertyValuesString) AS FirstName, GETPROFILEELEMENT('LastName', PropertyNames, PropertyValuesString) AS LastName FROM ora_aspnet_profile p, ora_aspnet_membership m WHERE p.USERID = m.USERID Here are the functions: CREATE OR REPLACE FUNCTION ASPNET_DB_USER."GETPROFILEELEMENT" ( fieldName IN VARCHAR2, ...

Here is a more efficient approach to using a cursor in Oracle: FOR myCursor_rec IN myCursor LOOP ... END LOOP; And then from within the loop you can access elements in the cursor record like this: myCursor_rec.vAuthSeq;

SET SERVEROUTPUT ON; declare cursor myCursor is (select auth_code, lastname, dob, driverlicensenumber, driverlicensestate from snap_driver_record where auth_date >= sysdate - 13 and auth_sequence=0); vAuthCode snap_driver_record.auth_code%type; vLastName snap_driver_record.lastname%type; vDob snap_driver_record.dob%type; vLicenseNumber snap_driver_record.driverlicensenumber%type; vLicenseState snap_driver_record.driverlicensestate%type; vCrashCount integer; vInspCount integer; vCrashTot integer; vInspTot integer; vDirCount integer; begin select count(*) into vDirCount from snap_driver_record where auth_date >= sysdate - 13 and auth_sequence=0; open myCursor; loop fetch myCursor into vAuthCode, vLastName, vDob, vLicenseNumber, vLicenseState; EXIT WHEN myCursor%NOTFOUND; -- get crash record count select count(*) into vCrashCount from crash_driver cd ...

Have you ever wanted to sort an ICollection? It took me awhile to figure this one out so I thought I should blog it. I originally posted this quite awhile ago. Since then I have discovered a much easier way to sort a collection. Here's an update on sorting collections using LINQ. Much simpler: var orderedList = customer.Users .OrderBy(x => x.UserType) .ToList(); And here is my original post on the subject. You decide which one looks easier. private IList<EventReqFormSection> GetSections() { ICollection<EventReqFormSection> sections = EventReqFormSection.GetByEventReqFormId(_businessObject.Id, ObjectManager); // sort by Display Sequence List<EventReqFormSection> sortedValues = new List<EventReqFormSection>(sections); sortedValues.Sort(new EventReqFormSection.DisplaySequenceSort()); return sortedValues; } The above function retrieves an ICollection puts it into a List object and calls the sort method usin...

This just seemed like a good idea. I will use this as a scratch pad for things that I discover throughout my daily struggles in code wrangling.