ColdFusion 9 cfgrid with ORM: Part 2 - Full CRUD Grid
Previously I blogged on getting a normal cfgrid running with ColdFusion. This post will look at expanding that to a full CRUD grid using ORM.
In addition to ColdFusion, I am also using jQuery and jqModalspecifically to display the modal boxes. Main reason being that I was itching to do some jQuery stuff.
Code follows.The first thing to be done is add some extra buttons to our paging toolbar for the Add, Edit, Delete functions. Ext JS makes this easy for us. The function gridSmash() is called on page load and it makes our adjustments to the grid.
1 function gridSmash()
2 {
3 grid = ColdFusion.Grid.getGridObject('usersgrid');
4 cols = grid.getColumnModel();
5 configA = cols.config;
6 //This will add the Row Numbering to our grid
7 newcolumn = new Ext.grid.RowNumberer();
8 temp = configA.splice(0,0,newcolumn);
9 bbar = grid.getBottomToolbar();
10
11 //Add buttons to the paging toolbar
12 bbar.add('-', {
13 pressed: false,
14 enableToggle:false,
15 text: 'Add',
16 icon:'css/add.png',
17 cls: 'x-btn-text-icon',
18 handler:addShow
19 },
20 '-',{
21 pressed: false,
22 enableToggle:false,
23 text: 'Edit',
24 icon:'css/edit.png',
25 cls: 'x-btn-text-icon',
26 handler:editShow
27 },
28 '-',{
29 pressed: false,
30 enableToggle:false,
31 text: 'Delete',
32 icon:'css/delete.png',
33 cls: 'x-btn-text-icon',
34 handler:deleteShow
35 }
36 );
37 bbar.doLayout();
38 }
2 {
3 grid = ColdFusion.Grid.getGridObject('usersgrid');
4 cols = grid.getColumnModel();
5 configA = cols.config;
6 //This will add the Row Numbering to our grid
7 newcolumn = new Ext.grid.RowNumberer();
8 temp = configA.splice(0,0,newcolumn);
9 bbar = grid.getBottomToolbar();
10
11 //Add buttons to the paging toolbar
12 bbar.add('-', {
13 pressed: false,
14 enableToggle:false,
15 text: 'Add',
16 icon:'css/add.png',
17 cls: 'x-btn-text-icon',
18 handler:addShow
19 },
20 '-',{
21 pressed: false,
22 enableToggle:false,
23 text: 'Edit',
24 icon:'css/edit.png',
25 cls: 'x-btn-text-icon',
26 handler:editShow
27 },
28 '-',{
29 pressed: false,
30 enableToggle:false,
31 text: 'Delete',
32 icon:'css/delete.png',
33 cls: 'x-btn-text-icon',
34 handler:deleteShow
35 }
36 );
37 bbar.doLayout();
38 }
1 <cfgrid title="Users" name="usersgrid" pagesize="25" format="html" colheaderbold="true" colheaderfont="Verdana" autowidth="true" deletebutton="true" colheaderfontsize="90%" align="middle" colheaderalign="center" collapsible="true" selectOnLoad="false" font="Verdana" fontsize="90%" striperows="true" striperowcolor="##F0FAFF"
2 bind="cfc:Logic.UsersAccess.getUsers({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})" selectMode="edit" onchange="cfc:Logic.UsersAccess.editUser({cfgridaction},{cfgridrow},{cfgridchanged})">
3
4 <cfgridcolumn name="FirstName" header="First Name" width="150">
5 <cfgridcolumn name="LastName" header="Last Name" width="150">
6 <cfgridcolumn name="UserName" header="User Name" width="150">
7 <cfgridcolumn name="DisplayName" header="Display Name" width="150">
8
9 </cfgrid>
The other important part is our UsersAccess.cfc. We add functions to take care of our add, edit and delete functions.
2 bind="cfc:Logic.UsersAccess.getUsers({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})" selectMode="edit" onchange="cfc:Logic.UsersAccess.editUser({cfgridaction},{cfgridrow},{cfgridchanged})">
3
4 <cfgridcolumn name="FirstName" header="First Name" width="150">
5 <cfgridcolumn name="LastName" header="Last Name" width="150">
6 <cfgridcolumn name="UserName" header="User Name" width="150">
7 <cfgridcolumn name="DisplayName" header="Display Name" width="150">
8
9 </cfgrid>
1 <cfcomponent>
2
3 <cffunction name="getUsers" access="remote" returntype="any" returnformat="JSON">
4 <cfargument name="page" required="yes">
5 <cfargument name="pageSize" required="yes">
6 <cfargument name="gridsortcolumn" required="yes">
7 <cfargument name="gridsortdirection" required="yes" default="asc">
8
9 <cfif arguments.gridsortcolumn EQ "">
10 <cfset sortColumn = "ID">
11 <cfelse>
12 <cfset sortColumn = arguments.gridsortcolumn>
13 </cfif>
14 <cfset sort = lCase(Arguments.gridsortdirection)>
15 <cfset objORMUsers = EntityLoad("Users",{},"#sortColumn# #sort#")>
16 <cfset selORMUsers = EntityToQuery(objORMUsers)>
17
18 <cfreturn queryconvertforgrid(selORMUsers,Arguments.page,Arguments.pageSize)/>
19
20 </cffunction>
21
22 <!--- Add User to the system. Create a new Entity of User, store our information
23 and its done. --->
24 <cffunction name="addUser" access="remote">
25 <cfargument name="stcForm" type="struct" required="true">
26
27 <cfset newUser = EntityNew("Users")>
28 <cfset newUser.setFirstName("#Arguments.stcForm.FirstName#")>
29 <cfset newUser.setLastName("#Arguments.stcForm.LastName#")>
30 <cfset newUser.setUserName("#Arguments.stcForm.UserName#")>
31 <cfset newUser.setDisplayName("#Arguments.stcForm.DisplayName#")>
32 <cfset EntitySave(newUser)>
33
34 </cffunction>
35
36 <!--- Delete the selected User. We use the EntityLoad function to return the single object by ID, and then
37 Delete it --->
38 <cffunction name="delUser" access="remote">
39 <cfargument name="delID" type="numeric" required="true">
40
41 <cfset delUser = EntityLoad("Users",Arguments.delID,true)>
42 <cfset EntityDelete(delUser)>
43
44 </cffunction>
45
46 <cffunction name="editUser" access="remote">
47 <cfargument name="gridaction" required="yes">
48 <cfargument name="gridrow" required="yes" type="Struct">
49 <cfargument name="gridchanged" required="yes" type="Struct">
50
51 <!--- Get the Primary key for record changed, get the column changed and get the new Value --->
52 <cfset var intRecordID = arguments.gridrow.ID>
53 <cfset var colList = StructKeyList(arguments.gridrow)>
54 <cfset var col = StructKeyList(arguments.gridchanged)>
55 <cfset var newValue = StructFind(arguments.gridchanged,"#col#")>
56
57 <!--- Load the entity object for the record we need to update --->
58 <cfset objUser = EntityLoad("Users",intRecordID,true)>
59 <!--- Call the corresponding upd function for the column defined in this cfc --->
60 <cfinvoke method="upd#col#" objUser="#objUser#" value="#newValue#">
61
62 </cffunction>
63
64 <!--- Each of the upd functions will update a corresponding database field when called.
65 We pass in the User Object that and the accessor functions will do the magic for us --->
66 <cffunction name="updFirstName" access="private">
67 <cfargument name="objUser" type="Any" required="yes">
68 <cfargument name="value" type="String" required="yes">
69
70 <cfset objUser.setFirstName(Arguments.value)>
71
72 </cffunction>
73
74 <cffunction name="updLastName" access="private">
75 <cfargument name="objUser" type="Any" required="yes">
76 <cfargument name="value" type="String" required="yes">
77
78 <cfset objUser.setLastName(Arguments.value)>
79
80 </cffunction>
81
82 <cffunction name="updUserName" access="private">
83 <cfargument name="objUser" type="Any" required="yes">
84 <cfargument name="value" type="String" required="yes">
85
86 <cfset objUser.setUserName(Arguments.value)>
87
88 </cffunction>
89
90 <cffunction name="updDisplayName" access="private">
91 <cfargument name="objUser" type="Any" required="yes">
92 <cfargument name="value" type="String" required="yes">
93
94 <cfset objUser.setDisplayName(Arguments.value)>
95
96 </cffunction>
97
98 </cfcomponent>
2
3 <cffunction name="getUsers" access="remote" returntype="any" returnformat="JSON">
4 <cfargument name="page" required="yes">
5 <cfargument name="pageSize" required="yes">
6 <cfargument name="gridsortcolumn" required="yes">
7 <cfargument name="gridsortdirection" required="yes" default="asc">
8
9 <cfif arguments.gridsortcolumn EQ "">
10 <cfset sortColumn = "ID">
11 <cfelse>
12 <cfset sortColumn = arguments.gridsortcolumn>
13 </cfif>
14 <cfset sort = lCase(Arguments.gridsortdirection)>
15 <cfset objORMUsers = EntityLoad("Users",{},"#sortColumn# #sort#")>
16 <cfset selORMUsers = EntityToQuery(objORMUsers)>
17
18 <cfreturn queryconvertforgrid(selORMUsers,Arguments.page,Arguments.pageSize)/>
19
20 </cffunction>
21
22 <!--- Add User to the system. Create a new Entity of User, store our information
23 and its done. --->
24 <cffunction name="addUser" access="remote">
25 <cfargument name="stcForm" type="struct" required="true">
26
27 <cfset newUser = EntityNew("Users")>
28 <cfset newUser.setFirstName("#Arguments.stcForm.FirstName#")>
29 <cfset newUser.setLastName("#Arguments.stcForm.LastName#")>
30 <cfset newUser.setUserName("#Arguments.stcForm.UserName#")>
31 <cfset newUser.setDisplayName("#Arguments.stcForm.DisplayName#")>
32 <cfset EntitySave(newUser)>
33
34 </cffunction>
35
36 <!--- Delete the selected User. We use the EntityLoad function to return the single object by ID, and then
37 Delete it --->
38 <cffunction name="delUser" access="remote">
39 <cfargument name="delID" type="numeric" required="true">
40
41 <cfset delUser = EntityLoad("Users",Arguments.delID,true)>
42 <cfset EntityDelete(delUser)>
43
44 </cffunction>
45
46 <cffunction name="editUser" access="remote">
47 <cfargument name="gridaction" required="yes">
48 <cfargument name="gridrow" required="yes" type="Struct">
49 <cfargument name="gridchanged" required="yes" type="Struct">
50
51 <!--- Get the Primary key for record changed, get the column changed and get the new Value --->
52 <cfset var intRecordID = arguments.gridrow.ID>
53 <cfset var colList = StructKeyList(arguments.gridrow)>
54 <cfset var col = StructKeyList(arguments.gridchanged)>
55 <cfset var newValue = StructFind(arguments.gridchanged,"#col#")>
56
57 <!--- Load the entity object for the record we need to update --->
58 <cfset objUser = EntityLoad("Users",intRecordID,true)>
59 <!--- Call the corresponding upd function for the column defined in this cfc --->
60 <cfinvoke method="upd#col#" objUser="#objUser#" value="#newValue#">
61
62 </cffunction>
63
64 <!--- Each of the upd functions will update a corresponding database field when called.
65 We pass in the User Object that and the accessor functions will do the magic for us --->
66 <cffunction name="updFirstName" access="private">
67 <cfargument name="objUser" type="Any" required="yes">
68 <cfargument name="value" type="String" required="yes">
69
70 <cfset objUser.setFirstName(Arguments.value)>
71
72 </cffunction>
73
74 <cffunction name="updLastName" access="private">
75 <cfargument name="objUser" type="Any" required="yes">
76 <cfargument name="value" type="String" required="yes">
77
78 <cfset objUser.setLastName(Arguments.value)>
79
80 </cffunction>
81
82 <cffunction name="updUserName" access="private">
83 <cfargument name="objUser" type="Any" required="yes">
84 <cfargument name="value" type="String" required="yes">
85
86 <cfset objUser.setUserName(Arguments.value)>
87
88 </cffunction>
89
90 <cffunction name="updDisplayName" access="private">
91 <cfargument name="objUser" type="Any" required="yes">
92 <cfargument name="value" type="String" required="yes">
93
94 <cfset objUser.setDisplayName(Arguments.value)>
95
96 </cffunction>
97
98 </cfcomponent>
A lot to digest here.
Appreciate it.
I apologize for getting your blog post confused with Ray Camden's.
Here are the SQL Server commands needed to create the test data:
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERS]') AND type in (N'U'))
DROP TABLE USERS
GO
CREATE TABLE USERS(
ID INT IDENTITY PRIMARY KEY,
UserAccountingCode VARCHAR(255) DEFAULT '',
FirstName VARCHAR(255) DEFAULT '',
LastName VARCHAR(255) DEFAULT '',
Phone VARCHAR(12) DEFAULT '',
Fax VARCHAR(255) DEFAULT '',
Email VARCHAR(255) DEFAULT '',
UserName VARCHAR(255) DEFAULT '',
Password VARCHAR(255) DEFAULT '',
DisplayName VARCHAR(255) DEFAULT ''
)
DECLARE @I INT
SET @I=1
WHILE @I < 5000 BEGIN
INSERT INTO USERS(FirstName,LastName,UserName,DisplayName) VALUES('User FN ' + CONVERT(VARCHAR(255),@I),'User LN ' + CONVERT(VARCHAR(255),@I),'User Name ' + CONVERT(VARCHAR(255),@I),'Display Name ' + CONVERT(VARCHAR(255),@I))
SET @I = @I + 1
END
SELECT * FROM USERS
Thanks for posting what you've done! This looks very interesting!
I remember this being a great demo and wanted to share it with some people, but neither of your demo links seem to work right now.
Garrett Johnson worked out how to get cfGrid working and together we posted it at:
http://www.cfmzengarden.com/jQueryZenGarden/001/
There's a link in the upper-right corner to download the source code.
@Phillip, thanks for the link!!
At what point are all changes to the grid entities saved? It looks like your update actions set the properties of the ORM objects, but entitySave() is never called for them. Is the idea that there would be a seperate process that saves all the changes by clicking a Submit button on the CFGrid page, or am I missing something?