My take on database access

If you've done any reading on DAOs, gateways and data access with ColdFusion you'll no doubt have come across the fact that there are more than a few ways to do it. Here's how I'm doing it.

I like my code to be useful, so an object that can do a number of different things is gonna make me happy.

What I found lacking in some DAOs I've seen out there, is that the WHERE clauses are restricted to acting on the primary key only. So for SELECTs, UPDATEs and DELETEs you'd have to know the primary key, which you usually do, but you may have to obtain this if you don't.

The same is true with INSERTs. I want my INSERTs to contain any columns.

So what I set out to create, was a CRUD object that could:

• INSERT any combination of columns
• SELECT any columns, with anything in the WHERE clause and ORDER BY any columns
• UPDATE any columns based on any WHERE clause columns (I stopped short of doing this as you'll see below)
• DELETE any row based on any columns in the WHERE clause

This is what I came up with:

<cfcomponent displayname="UserDAO">      <cfset VARIABLES.dsn = "">        <cffunction name="init">      <cfargument name="dsn">      <cfset VARIABLES.dsn = ARGUMENTS.dsn>      <cfreturn THIS>    </cffunction>        <cffunction name="create">            <cfset var separator = "">      <cfset var q = "">            <cfquery name="q" datasource="#VARIABLES.DSN#">        INSERT INTO [user] (          <cfif StructKeyExists(ARGUMENTS, "userID")>#separator#userID<cfset separator = ","></cfif>          <cfif StructKeyExists(ARGUMENTS, "firstName")>#separator#firstName<cfset separator = ","></cfif>          <cfif StructKeyExists(ARGUMENTS, "lastName")>#separator#lastName<cfset separator = ","></cfif>        ) VALUES (          <cfset separator = "">          <cfif StructKeyExists(ARGUMENTS, "userID")>#separator#<cfqueryparam... value="#ARGUMENTS.userID#"><cfset separator = ","></cfif>          <cfif StructKeyExists(ARGUMENTS, "firstName")>#separator#<cfqueryparam... value="#ARGUMENTS.firstName#"><cfset separator = ","></cfif>          <cfif StructKeyExists(ARGUMENTS, "lastName")>#separator#<cfqueryparam... value="#ARGUMENTS.lastName#"><cfset separator = ","></cfif>        )        SELECT SCOPE_IDENTITY() userID      </cfquery>            <cfreturn q.userID>        </cffunction>        <cffunction name="read">          <cfargument name="columnList" default="*">      <cfargument name="orderByList" default="">            <cfset var q = "">            <cfquery name="q" datasource="#VARIABLES.dsn#">        SELECT #ARGUMENTS.columnList#        FROM [user]        WHERE 1 = 1        <cfif StructKeyExists(ARGUMENTS, "userID")>AND userID = <cfqueryparam... value="#ARGUMENTS.userID#"></cfif>        <cfif StructKeyExists(ARGUMENTS, "firstName")>AND firstName = <cfqueryparam... value="#ARGUMENTS.firstName#"></cfif>        <cfif StructKeyExists(ARGUMENTS, "lastName")>AND lastName = <cfqueryparam... value="#ARGUMENTS.lastName#"></cfif>        <cfif ARGUMENTS.orderByList NEQ "">          ORDER BY #ARGUMENTS.orderByList#        </cfif>      </cfquery>            <cfreturn q>        </cffunction>        <cffunction name="update">            <cfargument name="userID" required="true">            <cfset var separator = "">            <cfquery name="q" datasource="#VARIABLES.DSN#">        UPDATE [user] SET        <cfif StructKeyExists(ARGUMENTS, "firstName")>#separator#firstName = <cfqueryparam... value="#ARGUMENTS.firstName#"><cfset separator = ","></cfif>        <cfif StructKeyExists(ARGUMENTS, "lastName")>#separator#lastName = <cfqueryparam... value="#ARGUMENTS.lastName#"><cfset separator = ","></cfif>        WHERE userID = <cfqueryparam... value="#ARGUMENTS.userID#">      </cfquery>          </cffunction>        <cffunction name="delete">            <cfset var q = "">            <cfquery name="q" datasource="#VARIABLES.dsn#">        DELETE FROM [user]        WHERE 1 = 1        <cfif StructKeyExists(ARGUMENTS, "userID")>AND userID = <cfqueryparam... value="#ARGUMENTS.userID#"></cfif>        <cfif StructKeyExists(ARGUMENTS, "firstName")>AND firstName = <cfqueryparam... value="#ARGUMENTS.firstName#"></cfif>        <cfif StructKeyExists(ARGUMENTS, "lastName")>AND lastName = <cfqueryparam... value="#ARGUMENTS.lastName#"></cfif>      </cfquery>            <cfreturn true>          </cffunction>      </cfcomponent>

I've stripped out some attributes and comments for the sake of this post. For the most part this is the code I'm using on my latest project and it has been working like a charm.

Inserting a row:

   <cfset userDAO = CreateObject("component", "cfc.UserDAO").init(APPLICATION.DSN)>   <cfset userDAO.create(firstName = "Adrian", lastName = "Lynch")>  

Selecting all rows where first name is Adrian:

   <cfset adrians = userDAO.read(firstName = "Adrian")>  

Updating:

   <cfset userDAO.update(firstName = "Author", userID = 1)>  

Deleting Adrians:

   <cfset userDAO.delete(firstName = "Arthur")>  

Or making a mistake and deleting all rows!

   <cfset adrians = userDAO.delete()>  

All this is auto generated with a tool I use. Inspired by Illudium PU-36 but wanting it to work across multiple tables I created my own version, but that's a post for another time. Here's a screen shot.

I'd love to hear people's thoughts on the above as I quite like it.