TSimpleDataSet.Params Property

Contains parameter values that are sent to the provider.

Pascal

property Params: TParams;

C++

__property TParams Params;

Description

Use Params to specify parameter values that the provider should pass to a source dataset. If the client dataset uses an internal provider, Params represents the values for any parameters used by the SQL statement specified by the CommandText property. If the client dataset uses an external provider that is associated with a dataset that represents a query or stored procedure, these parameter values supply input parameter values for that query or stored procedure and return any output parameters of a stored procedure. If the provider is associated with a TTable or TSQLTable component, Params limits the records that are sent in data packets. After specifying the parameters (at design time or runtime), they are sent to the provider automatically when the client dataset fetches data or executes a query or stored procedure using its Execute method.

To send parameter values to a query or stored procedure, for every parameter on the query or stored procedure for which the client dataset should send a value, add a parameter object (TParam) to Params. Assign each parameter object the same Name, DataType, and ParamType as the corresponding parameter on the query or stored procedure. These values are automatically encoded and sent to the provider when the client dataset fetches data.

To send parameter values to a TTable or TSQLTable, add a parameter object for each field in the table that is used to limit the values sent in data packets. Assign each parameter object the same Name and DataType as the corresponding field component on the table. These parameter values are automatically sent when the client dataset fetches data, and the records returned in data packets from the provider only contain records that match the assigned value on the corresponding fields. You can achieve the same affect by applying a filter to the field, but using Params is more efficient because the data need not be included in data packets.

Note: To initialize Params so that it reflects the parameters of a provider's associated query or stored procedure, call the FetchParams method.

Warning: The parameters are only applied to the provider when a query or stored procedure is run the first time the client dataset fetches records. To change parameter values and force a query or stored procedure to be rerun with new values, use the Execute method.

C++ Examples:

Copy Code

/*
  This example saves params from a ClientDataSet, alters them
  and restores them.
*/
TParams *SavedParams;

void __fastcall TForm1::SaveParamsClick(TObject *Sender)
{
  // Save the parameters for the TDataSet
  SavedParams->Assign(CDS->Params);
}

void __fastcall TForm1::AlterParamsClick(TObject *Sender)
{
  CDS->Params->CreateParam(ftString, "stringParam", ptInput);  // you can also edit the params in the CDS dynamically
  CDS->Params->CreateParam(ftSmallint, "smallIntParam", ptInput);
  CDS->Params->CreateParam(ftInteger, "intParam", ptInput);
  CDS->Params->CreateParam(ftWord, "wordParam", ptInput);
  CDS->Params->CreateParam(ftBoolean, "boolParam", ptInput);
  CDS->Params->CreateParam(ftFloat, "floatParam", ptInput);
  CDS->Params->CreateParam(ftCurrency, "currencyParam", ptInput);
  CDS->Params->CreateParam(ftBCD, "bcdParam", ptInput);
  CDS->Params->CreateParam(ftDate, "dateParam", ptInput);
  CDS->Params->CreateParam(ftTime, "timeParam", ptInput);
  CDS->Params->CreateParam(ftDateTime, "dateTimeParam", ptInput);
  // Call FetchParams to ensure parameters reflect server metadata
  CDS->FetchParams();
//  with CDS->Params do
  for (int I = 0; I < CDS->Params->Count; I++)
  {
    AnsiString ListItem = ListBox1->Items->Strings[I];
    switch (CDS->Params->Items[I]->DataType)
    {
      case ftString:
        CDS->Params->Items[I]->AsString = ListItem; break;
      case ftSmallint:
        CDS->Params->Items[I]->AsSmallInt = StrToIntDef(ListItem, 0); break;
      case ftInteger:
        CDS->Params->Items[I]->AsInteger = StrToIntDef(ListItem, 0); break;
      case ftWord:
        CDS->Params->Items[I]->AsWord = StrToIntDef(ListItem, 0); break;
      case ftBoolean:
        CDS->Params->Items[I]->AsBoolean = (ListItem == "True"); break;
      case ftFloat:
        CDS->Params->Items[I]->AsFloat = StrToFloat(ListItem); break;
      case ftCurrency:
        CDS->Params->Items[I]->AsCurrency = double(StrToFloat(ListItem)); break;
      case ftBCD:
        CDS->Params->Items[I]->AsBCD = StrToCurr(ListItem); break;
      case ftDate:
        CDS->Params->Items[I]->AsDate = StrToDate(ListItem); break;
      case ftTime:
        CDS->Params->Items[I]->AsTime = StrToTime(ListItem); break;
      case ftDateTime:
        CDS->Params->Items[I]->AsDateTime = StrToDateTime(ListItem); break;
    }
  }
}

void __fastcall TForm1::RestoreParamsClick(TObject *Sender)
{
  // Restore the parameters to TDataSet
  CDS->Params->AssignValues(SavedParams);
}

void __fastcall TForm1::FormCreate(TObject *Sender)
{
  // Initialize SavedParams
  SavedParams = new TParams;
  ListBox1->Items->Add("Here is a string");
  ListBox1->Items->Add("4");
  ListBox1->Items->Add("3456789");
  ListBox1->Items->Add("34");
  ListBox1->Items->Add("0");
  ListBox1->Items->Add("12.56");
  ListBox1->Items->Add("4.23");
  ListBox1->Items->Add("99.5");
  ListBox1->Items->Add("4/12/53");
  ListBox1->Items->Add("7:45:00");
  ListBox1->Items->Add("4/12/53 7:45:00");
}

Delphi Examples:

Copy Code

{
  This example uses the FetchParams method to sync the params
  with the server.
}
procedure TForm1.SaveParamsClick(Sender: TObject);
begin
  { Save the parameters for the TDataSet }
  SavedParams.Assign(CDS.Params);
end;

procedure TForm1.AlterParamsClick(Sender: TObject);
var
  I : Integer;
  ListItem: string;
begin
  CDS.Params.CreateParam(ftString, 'stringParam', ptInput);  // you can also edit the params in the CDS dynamically
  CDS.Params.CreateParam(ftSmallInt, 'smallIntParam', ptInput);
  CDS.Params.CreateParam(ftInteger, 'intParam', ptInput);
  CDS.Params.CreateParam(ftWord, 'wordParam', ptInput);
  CDS.Params.CreateParam(ftBoolean, 'boolParam', ptInput);
  CDS.Params.CreateParam(ftFloat, 'floatParam', ptInput);
  CDS.Params.CreateParam(ftCurrency, 'currencyParam', ptInput);
  CDS.Params.CreateParam(ftBCD, 'bcdParam', ptInput);
  CDS.Params.CreateParam(ftDate, 'dateParam', ptInput);
  CDS.Params.CreateParam(ftTime, 'timeParam', ptInput);
  CDS.Params.CreateParam(ftDateTime, 'dateTimeParam', ptInput);
  { Call FetchParams to ensure parameters reflect server metadata }
  CDS.FetchParams;
  with CDS.Params do
  begin
    for I := 0 to Count - 1 do
    begin
      ListItem := ListBox1.Items[I];
      case Items[I].DataType of
        ftString:
          Items[I].AsString := ListItem;
        ftSmallInt:
          Items[I].AsSmallInt := StrToIntDef(ListItem, 0);
        ftInteger:
          Items[I].AsInteger := StrToIntDef(ListItem, 0);
        ftWord:
          Items[I].AsWord := StrToIntDef(ListItem, 0);
        ftBoolean:
          begin
            if ListItem = 'True' then
              Items[I].AsBoolean := True
            else
              Items[I].AsBoolean := False;
          end;
        ftFloat:
          Items[I].AsFloat := StrToFloat(ListItem);
        ftCurrency:
          Items[I].AsCurrency := StrToFloat(ListItem);
        ftBCD:
          Items[I].AsBCD := StrToCurr(ListItem);
        ftDate:
          Items[I].AsDate := StrToDate(ListItem);
        ftTime:
          Items[I].AsTime := StrToTime(ListItem);
        ftDateTime:
          Items[I].AsDateTime := StrToDateTime(ListItem);
      end;
    end;
  end;
end;

procedure TForm1.RestoreParamsClick(Sender: TObject);
begin
  { Restore the parameters to TDataSet }
  CDS.Params.AssignValues(SavedParams);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  { Initialize SavedParams }
  SavedParams := TParams.Create;
  ListBox1.Items.Add('Here is a string');
  ListBox1.Items.Add('4');
  ListBox1.Items.Add('3456789');
  ListBox1.Items.Add('34');
  ListBox1.Items.Add('0');
  ListBox1.Items.Add('12.56');
  ListBox1.Items.Add('4.23');
  ListBox1.Items.Add('99.5');
  ListBox1.Items.Add('4/12/53');
  ListBox1.Items.Add('7:45:00');
  ListBox1.Items.Add('4/12/53 7:45:00');
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  SavedParams.Free;
end;

Copy Code

{
This example fills in the parameters of a client dataset
from the entries of a list box.
} 
procedure TForm1.Button4Click(Sender: TObject);
var
  I: Integer;
  ListItem: string;
begin
  { Call FetchParams to ensure parameters reflect server metadata }
  ClientDataSet1.FetchParams;
  with ClientDataSet1.Params do
  begin
    for I := 0 to Count - 1 do
    begin
      ListItem := ListBox1.Items[I];
      case Items[I].DataType of
        ftString:
          Items[I].AsString := ListItem;
        ftSmallInt:
          Items[I].AsSmallInt := StrToIntDef(ListItem, 0);
        ftInteger:
          Items[I].AsInteger := StrToIntDef(ListItem, 0);
        ftWord:
          Items[I].AsWord := StrToIntDef(ListItem, 0);
        ftBoolean:
          begin
            if ListItem = 'True' then
              Items[I].AsBoolean := True
            else
              Items[I].AsBoolean := False;
          end;
        ftFloat:
          Items[I].AsFloat := StrToFloat(ListItem);
        ftCurrency:
          Items[I].AsCurrency := StrToFloat(ListItem);
        ftBCD:
          Items[I].AsBCD := StrToCurr(ListItem);
        ftDate:
          Items[I].AsDate := StrToDate(ListItem);
        ftTime:
          Items[I].AsTime := StrToTime(ListItem);
        ftDateTime:
          Items[I].AsDateTime := StrToDateTime(ListItem);
      end;
    end;
  end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  ListBox1.Items.Add('Here is a string');
  ListBox1.Items.Add('4');
  ListBox1.Items.Add('3456789');
  ListBox1.Items.Add('34');
  ListBox1.Items.Add('0');
  ListBox1.Items.Add('12.56');
  ListBox1.Items.Add('4.23');
  ListBox1.Items.Add('99.5');
  ListBox1.Items.Add('4/12/53');
  ListBox1.Items.Add('7:45:00');
  ListBox1.Items.Add('4/12/53 7:45:00');

end;