Delphi Clinic C++Builder Gate Training & Consultancy Delphi Notes Weblog Dr.Bob's Webshop
Bob Swart (aka Drs.Bob) Dr.Bob's Delphi Clinics Dr.Bob's Delphi Courseware Manuals
View Bob Swart's profile on LinkedIn Drs.Bob's Delphi Notes
These are the voyages using Delphi Enterprise (and Architect). Its mission: to explore strange, new worlds. To design and build new applications. To boldly go...

ADO.NET: Add Parameter to Command

Author: Bob Swart
Posted: 1/25/2007 9:57:18 AM (GMT+1)

In one of the discussion lists that I follow, someone was asking about a way to add named parameters to SQL queries using ADO.NET. He was currently doing something along the following lines:

Command: BdpCommand;
Command := BdpCommand.Create('SELECT * FROM TABLE WHERE ID=?',
Connection, Transaction);
Command.Parameters.Add('', DbType.&String).Value := '7';
Command.ExecuteReader; // etc

He felt this was strange to him because it forced him to use "?" marks for parameters (difficult to maintain) and it forces him to refer to those parameters by POSITION (difficult to maintain).
In the Win32 world we would have used named parameters and that's really easy to maintain.

I'm always using a special routine that I wrote to add parameters to a parameterised query.
This is what I would do:
    Command := BdpCommand.Create('SELECT * FROM TAB WHERE ID = ?',
Connection, Transaction);
// add parameters, assume ID of type String, length 15
AddParameterToCommand(Command, 'ID', BdpType.&String, 15, '42');

With the following support routine:
  procedure AddParameterToCommand(var Command: BdpCommand;
const Name: String;
&Type: BdpType; Size: Integer; Value: TObject);
Parameter: Borland.Data.Common.BdpParameter;
Parameter := BdpParameter.Create(Name, &Type, Size);
Parameter.Value := Value;
Parameter.Direction := ParameterDirection.Input;
Parameter.SourceColumn := Name;
context.trace.Write('Parameter ' + Parameter.ParameterName +
' = [' + Parameter.Value.ToString + ']')


As you can see, the connection to the SourceColumn will ensure that you can even call AddParameterToCommand in the "wrong" order (of parameters) is you wish.

Described and used in more detail in my ASP.NET courseware manuals by the way.


No Comments, yet.

New Comment (max. 2048 characters, no HTML):


This webpage © 2005-2017 by Bob Swart (aka Dr.Bob - All Rights Reserved.