Saturday, March 21, 2009

ADO.NET: Add Parameter to Command

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: