> Using the SQL BULK INSERT command or it's equivalent.
I think you may have read the question too literally or, at least, my
interpretation of the question is different. By "bulk" think he meant
issuing a standard SQL UPDATE command that affects multiple records.
DAO's do not account for such operations, but there are plenty reasons why
you'd want to do this. If a user searches for a bunch of records and then
chooses to perform a global update on or delete off all the matching
records, it would be preferable to issue one single query to a database.
The Gateway is a good place for this kind of logic because you want to keep
your SQL all in one place. I often use the Specification pattern for
searches and bulk updates/deletes. If I have an accounts table that looks
like this:
id INT
name VARCHAR(100)
dateCreated DATETIME
dateLastUpdate DATETIME
I'll have a AccountSearchSpec object that looks like this:
ids string
name string
minDateCreated date
maxDateCreated date
Then I'll put a search() method in AccountGateway that takes an
AccountSearchSpec:
SELECT *
FROM accounts
WHERE 1 = 1
<cfif listLen(arguments.spec.getAccountIDs())>
AND <cfqueryparam value="#arguments.spec.getAccountIDs()#"
list=yes" cfsqltype="CF_SQL_INTEGER">
</cfif>
<cfif len(arguments.spec.getName())>
AND name LIKE <cfqueryparam value="%#arguments.spec.getName()#%"
cfsqltype="CF_SQL_VARCHAR">
</cfif>
<cfif isDate(arguments.spec.getMinDateCreated())>
AND dateCreated >= <cfqueryparam
value="#arguments.spec.getMaxDateCreated#"
cfsqltype="CF_SQL_TIMESTAMP">
</cfif>
<cfif isDate(arguments.spec.getMinDateCreated())>
AND dateCreated <= <cfqueryparam
value="#arguments.spec.getMinDateCreated#"
cfsqltype="CF_SQL_TIMESTAMP">
</cfif>
...and so on and so forth. The same idea can be applied to an UPDATE or
DELETE statement.
Ben Rogers
http://www.c4.net
v.508.240.0051
f.508.240.0057
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
CFCDev is supported by New Atlanta, makers of BlueDragon
http://www.newatlanta.com/products/bluedragon/index.cfm
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
