To handle this, I've created two classes: One for the object itself and another for reporting. For example, with employees, you will want a list, perhaps adding hours worked, salary, pay, etc....
Andy -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Kairoh! Sent: Monday, December 08, 2003 6:43 AM To: [EMAIL PROTECTED] Subject: [CFCDev] Wanted: OOP Best Practice for CFC's with database interaction Hi everyone, I have build a simple employee CFC - shown below - that interacts with a database. This CFC uses a utilities CFC for handling zero-length strings and preparing datefields for a database insert or update. The CFC does the job as expected. So far so good. But there are two questions bothering me: 1. From an OOP perspective an employee.cfc should not contain a listEmployees method. How could I solve this? Sometimes you need a list of employees. How should this be done in a OOP best practice example? Two CFC's? A design pattern? 2. I have the idea my implementation for accessing, inserting and updating could be done in a much cleaner fashion. Does anyone has a suggestion how to improve it. In summary, can anyone of you provide me with a best practice example for building an OOP-based CFC with integrated database interaction that does listings too. I have read Hal's book, but it does not discuss database access implementations. Ben's book has a nice example of OOP and database access but the example is based on a CFC/CFM combination. I am planning to use the CFC as part of a RIA application(CFC/SWF combination). All suggestions, links, book titles, articles are welcome. This is my first time on this list. (Don't shoot me, I have searched the archives but could not find the answer to my question). Vinny <cfcomponent displayname="Employee dataprocessing services" hint="This component provides employee information services"> <cffunction name="listEmployees" access="remote" output="no" returntype="query" displayname="List employees" hint="Returns a list of employees"> <!--- Optional keyword argument ---> <cfargument name="Keyword" type="string" required="false" hint="Optional search criteria; if not given, all employees are returned."> <!--- Get the employees information ---> <cfset var q_EmployeesInfo=""> <cfquery name="q_EmployeesInfo" datasource="#APPLICATION.DataSource#"> SELECT Employees.EmployeeID, Offices.OfficeName,Employees.FirstName FROM Employees, Offices WHERE Employees.Office = Offices.OfficeID <!--- If a keyword has been specified ---> <cfif IsDefined("ARGUMENTS.Keyword")> AND FirstName LIKE '%#ARGUMENTS.Keyword#%' OR LastName LIKE '%#ARGUMENTS.Keyword#%') </cfif> ORDER BY Employees.LastName </cfquery> <!--- Finally, return the populated employee query. ---> <cfreturn q_EmployeesInfo> </cffunction> <cffunction name="getEmployee" access="remote" output="no" returntype="struct" displayname="Get employee information" hint="Returns detailed information about the specified employee"> <cfargument name="EmployeeID" type="numeric" required="true" hint="ID specifying the particular employee to get"> <!--- Get the employee information ---> <cfset var q_EmployeeInfo=""> <cfquery name="q_EmployeeInfo" datasource="#APPLICATION.DataSource#"> SELECT * FROM Employees, Offices WHERE Employees.Office = Offices.OfficeID AND EmployeeID = <cfqueryparamvalue="#ARGUMENTS.EmployeeID#" cfsqltype="cf_sql_integer"> </cfquery> <!--- If we do not find a record for the employee, the object ID is invalid. Throw an exception to notify the caller ---> <cfif not q_EmployeeInfo.RecordCount> <cfthrow message="ERROR_UNKNOWN_USER"> </cfif> <!--- Create a structure for the employee information. ---> <cfset st_Employee = StructNew()> <!--- Populate the employee structure with employee information.---> <cfset st_Employee.EmployeeID = q_EmployeeInfo.EmployeeID> <cfset st_Employee.OfficeID = q_EmployeeInfo.OfficeID> <cfset st_Employee.FirstName = q_EmployeeInfo.FirstName> <cfset st_Employee.Login = q_EmployeeInfo.Login> <cfset st_Employee.Password = q_EmployeeInfo.Password> <cfset st_Employee.LastName = q_EmployeeInfo.LastName> <cfset st_Employee.BirthDate = q_EmployeeInfo.BirthDate> <cfset st_Employee.Email = q_EmployeeInfo.Email> <cfset st_Employee.Remarks = q_EmployeeInfo.Remarks> <cfset st_Employee.Picture = q_EmployeeInfo.Picture> <!--- Finally, return the populated employee structure. ---> <cfreturn st_Employee> </cffunction> <cffunction name="createEmployee" access="remote" output="no" returntype="struct" displayname="Insert new employee" hint="Inserts a new employee in the system"> <cfargument name="Office" type="numeric" required="false" default="" hint="The employee's office"> <cfargument name="Login" type="string" required="true" hint="The user's login"> <cfargument name="Password" type="string" required="true" hint="The user's password"> <cfargument name="LastName" type="string" required="false" default="" hint="The user's last name"> <cfargument name="BirthDate" type="date" required="false" default="" hint="The user's birthdate"> <cfargument name="Email" type="string" required="false" default="" hint="The user's email"> <cfargument name="Remarks" type="string" required="false" default="" hint="Remarks concerning the user"> <cfargument name="Picture" type="string" required="false" default="" hint="The user's photo"> <!--- Create a structure for the employee information. ---> <cfset st_Employee = StructNew()> <cfset st_Employee.Login = " "> <!--- Before we add the employee, we need to make sure that the employee's login does not already exist. If the employee does exist, we'll throw an exception. ---> <cfif IsDefined("ARGUMENTS.Login")> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Login"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Login#"> </cfinvoke> </cfif> <cfquery name="EmployeeCheck" datasource="#APPLICATION.DataSource#"> SELECT * FROM Employees WHERE Login = <cfqueryparam value="#st_Employee.Login#" cfsqltype="cf_sql_varchar"> </cfquery> <cfif EmployeeCheck.RecordCount> <cfthrow message="ERROR_USER_EXISTS"> </cfif> <cftransaction action="begin"/> <cfset st_Employee.Office = " "> <cfset st_Employee.Login = " "> <cfset st_Employee.Password = " "> <cfset st_Employee.LastName = " "> <cfset st_Employee.BirthDate = " "> <cfset st_Employee.Email = " "> <cfset st_Employee.Remarks = " "> <cfset st_Employee.Picture = " "> <!--- Add the information from the arguments if they exist. ---> <cfif IsDefined("ARGUMENTS.Office")> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Office"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Office#"> </cfinvoke> </cfif> <cfif IsDefined("ARGUMENTS.Login")> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.login"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Login#"> </cfinvoke> </cfif> <cfif IsDefined("ARGUMENTS.Password")> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.password"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Password#"> </cfinvoke> </cfif> <cfif IsDefined("ARGUMENTS.LastName")> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.LastName"> <cfinvokeargument name="TextField" value="#ARGUMENTS.LastName#"> </cfinvoke> </cfif> <cfif IsDefined("ARGUMENTS.BirthDate")> <cfinvoke component="common" method="prepareDateField" returnvariable="st_Employee.BirthDate"> <cfinvokeargument name="DateString" value="#ARGUMENTS.BirthDate#"> </cfinvoke> </cfif> <cfif IsDefined("ARGUMENTS.Email")> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.email"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Email#"> </cfinvoke> </cfif> <cfif IsDefined("ARGUMENTS.Remarks")> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Remarks"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Remarks#"> </cfinvoke> </cfif> <cfif IsDefined("ARGUMENTS.Picture")> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Picture"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Picture#"> </cfinvoke> </cfif> <!--- Insert a employee info record into the database. ---> <cfquery datasource="#APPLICATION.DataSource#"> INSERT INTO Employees (Office, Login, Password, LastName, BirthDate, Email, Remarks, Picture) VALUES (<cfqueryparam value="#st_Employee.Office#" cfsqltype="cf_sql_integer">, <cfqueryparam value="#st_Employee.Login#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#st_Employee.Password#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#st_Employee.LastName#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#st_Employee.BirthDate#" cfsqltype="cf_sql_date">, <cfqueryparam value="#st_Employee.Email#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#st_Employee.Remarks#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#st_Employee.Picture#" cfsqltype="cf_sql_varchar">) </cfquery> <!--- Commit the database changes. ---> <cftransaction action="commit"/> <!--- Get the new ID. ---> <cfquery name="q_returnQuery" datasource="#APPLICATION.DataSource#"> SELECT @@identity AS newId </cfquery> <!--- Finally, return the new ID. ---> <cfreturn getEmployee(q_returnQuery.newId)> </cffunction> <cffunction name="editEmployee" access="remote" output="no" returntype="struct" displayname="Update employee information" hint="Updates employee information in the system"> <cfargument name="EmployeeID" type="numeric" required="true" hint="The unique object ID for the user"> <cfargument name="Office" type="numeric" required="false" hint="The employee's office"> <cfargument name="Login" type="string" required="true" hint="The user's login"> <cfargument name="Password" type="string" required="true" hint="The user's password"> <cfargument name="LastName" type="string" required="false" hint="The user's last name"> <cfargument name="BirthDate" type="date" required="false" hint="The user's birthdate"> <cfargument name="Email" type="string" required="false" hint="The user's email"> <cfargument name="Remarks" type="string" required="false" hint="Remarks concerning the user"> <cfargument name="Picture" type="string" required="false" hint="The user's photo"> <!--- Create a structure for the employee's information. ---> <cfset st_Employee = StructNew()> <cfset st_Employee.EmployeeID = ARGUMENTS.EmployeeID> <!--- Prepare the user info text fields for updating the database ---> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Office"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Office#"> </cfinvoke> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Login"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Login#"> </cfinvoke> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Password"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Password#"> </cfinvoke> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.LastName"> <cfinvokeargument name="TextField" value="#ARGUMENTS.LastName#"> </cfinvoke> <cfinvoke component="common" method="prepareDateField" returnvariable="st_Employee.BirthDate"> <cfinvokeargument name="DateString" value="#ARGUMENTS.BirthDate#"> </cfinvoke> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Email"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Email#"> </cfinvoke> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Remarks"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Remarks#"> </cfinvoke> <cfinvoke component="common" method="prepareTextField" returnvariable="st_Employee.Picture"> <cfinvokeargument name="TextField" value="#ARGUMENTS.Picture#"> </cfinvoke> <!--- Update the employee's info ---> <cftransaction action="begin"/> <cfquery datasource="#APPLICATION.DataSource#"> UPDATE Employees SET Office = <cfqueryparam value="#st_Employee.Office#" cfsqltype="cf_sql_integer">, Login = <cfqueryparam value="#st_Employee.Login#" cfsqltype="cf_sql_varchar">, Password = <cfqueryparam value="#st_Employee.Password#" cfsqltype="cf_sql_varchar">, LastName = <cfqueryparam value="#st_Employee.LastName#" cfsqltype="cf_sql_varchar">, BirthDate = <cfqueryparam value="#st_Employee.BirthDate#" cfsqltype="cf_sql_date">, Email = <cfqueryparam value="#st_Employee.Email#" cfsqltype="cf_sql_varchar">, Remarks = <cfqueryparam value="#st_Employee.Remarks#" cfsqltype="cf_sql_varchar">, Picture = <cfqueryparam value="#st_Employee.Picture#" cfsqltype="cf_sql_varchar"> WHERE EmployeeID = #st_Employee.EmployeeID# </cfquery> <!--- Commit the database changes. ---> <cftransaction action="commit"/> <!--- Finally, return the userdata. ---> <cfreturn getEmployee(st_Employee.EmployeeID)> </cffunction> <cffunction name="deleteEmployee" access="remote" output="no" returntype="numeric" displayname="Delete employee information" hint="Deletes all information for the specified employee"> <cfargument name="EmployeeID" type="numeric" required="true" hint="Unique object ID specifying the particular employee to delete"> <cftransaction action="begin"/> <!--- Delete the item from tbEmployees ---> <cfquery datasource="#APPLICATION.DataSource#"> DELETE FROM Employees WHERE EmployeeID = #ARGUMENTS.EmployeeID# </cfquery> <!--- Commit the database changes. ---> <cftransaction action="commit"/> <!--- Finally, return the deleted employee. ---> <cfreturn ARGUMENTS.EmployeeID> </cffunction> </cfcomponent> ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
