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:

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

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);
   var
      Parameter: Borland.Data.Common.BdpParameter;
   begin
      Parameter := BdpParameter.Create(Name, &Type, Size);
      Parameter.Value := Value;
      Parameter.Direction := ParameterDirection.Input;
      Parameter.SourceColumn := Name;
      Command.Parameters.Add(Parameter);
      (*$IFDEF DEBUG*)
      context.trace.Write('Parameter ' + Parameter.ParameterName +
          ' = [' + Parameter.Value.ToString + ']')
      (*$ENDIF*)
   end;

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.