Desktop

The Commands sample demonstrates executing remote schema commands from a client application to insert, update and delete a record from a table in an instance of Relativity Server. The sample also demonstrates using DA SQL to retrieve data from a schema table.

Getting Started

The sample is typically located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\<language>\Commands, though you may have installed the Data Abstract for .NET SDK and it's samples in another location.

To build it, you will of course need Visual Studio, and like all the samples provided with Data Abstract for .NET you will need to be running Relativity Server with the DASamples Domain and the Simple Schema available.

Running the Sample

When the sample starts it displays a window with an empty area and a toolbar with a combo box and 3 buttons. The empty area will display a table of data retrieved from the schema. Pressing the "Load" button will attempt to retrieve data from the "Clients" schema table at the server address specified in the combo box.

Pressing the "Add" will open a dialog box into which you can add details for a new "Client". When the "OK" button is pressed the data is passed to the server along with the name of the schema command to execute (Clients_INSERT) and the table data is freshly retrieved to show the schema contains the new data.

Pressing the "Edit" button while a row is selected will open the previous dialog with the fields containing the selected row's data. Make a change and press "OK", at which point the field data is passed to the Clients_UPDATE schema command, and the data again retrieved to demonstrate that the field(s) has correctly updated with the data.

Finally pressing the "Delete" button will will display a confirmation dialog asking if you are sure you wish to delete the currently selected row. If you click "OK" then the id of that row is passed to the Clients_DELETE schema command and the data retrieved to show that the row has been deleted.

Examining the Code

The sample is comprised of three classes; the DataModule handles all interaction with the schema and is the focus of the code below. MainForm handles the main user interface and ClientForm handles the dialog used to add a new row or edit the data of an existing row.

The "Clients" schema table used in this sample contains ten fields, and while the table in the user interface is configured to show only five of those rows, all of the field data is retrieved and is visible when editing a row.

The sample also demonstrates a use of DA SQL to retrieve the table data using a simple SQL statement.

Looking at the Commands

Before stepping through the code, we shall take a detour and use Schema Modeler to examine that commands that are used in the following sections. If you open Relativity Admin Tool, navigate to the Simple schema and press the "Open in Schema Modeler" button. This opens Schema Modeler with the selected schema. There you can see a folder titled "Commands" which contains all of the commands that are available in this schema.

Each command's name describes the table it affects and what the action is to ensure that is no confusion. Clicking the disclosure triangle beside the command name allows you to drill down into the details of the command; including the parameters that the command takes and the statement object that defines the mapping between the schema table and the database table.

In the figure below you can see that the Clients_DELETE command takes a single parameter OLD_ClientId and uses a custom SQL statement that uses that parameter to try and match against the table's Id field and then delete that table from the Agents table (The Clients schema table is mapped to the Agents database table)

Next we shall look at using the commands.

Using the INSERT command

Before we can execute the Clients_INSERT command, we need to create an array of DataParameter objects that match the parameters defined for the command in the schema. Each DataParameter is comprised of a Name that must match one of the parameter names, and a Value object that we want to set the data to. If you fail to provide one of the parameters defined in the schema then a null value will instead be inserted for that parameter in the database table. Note that as this is a new row, you don't have to worry about the ClientId field, a value will be assigned by the server when the record is inserted.

The array of parameters is passed to the Execute method of RemoteCommand, along with the name of the command we want to execute as a string; here "Clients_INSERT". The Execute method will return an integer value which indicates if the command was successfully executed (It returns a 1) or if it failed (It returns a 0).

Note that if you supply an invalid command name or parameter name then an DAException will be thrown and should be handled.

public Boolean InsertClient(String clientName, Double clientDiscount,
                                    String clientEmail, String clientPassword,
                                    DateTime clientBirthdate,
                                    String clientAddress, String clientPostalCode,
                                    String clientPhone, String clientNotes)
{
    DataParameter[] lParams = {
        new DataParameter("ClientName", clientName ),
        new DataParameter ("ClientPhone", clientPhone ),
        new DataParameter ("ClientDiscount", clientDiscount ),
        new DataParameter ("ClientEmail", clientEmail ),
        new DataParameter ("ClientPassword", clientPassword ),
        new DataParameter ("ClientNotes", clientNotes ),
        new DataParameter ("ClientPostalCode", clientPostalCode ),
        new DataParameter ("ClientAddress", clientAddress ),
        new DataParameter ("ClientBirthdate", clientBirthdate )
    };

    Int32 lCommandResult = fRemoteCommand.Execute("Clients_INSERT", lParams);
    return lCommandResult > 0;
}

method DataModule.InsertClient(
  clientName: String; clientDiscount: Double;
  clientEmail: String; clientPassword: String;
  clientBirthdate: DateTime; clientAddress: String;
  clientPostalCode: String; clientPhone: String;
  clientNotes: String): Boolean;
begin
  var lParams: array of DataParameter := [ new DataParameter('ClientName', clientName),
                                           new DataParameter('ClientPhone', clientPhone),
                                           new DataParameter('ClientDiscount', clientDiscount),
                                           new DataParameter('ClientEmail', clientEmail),
                                           new DataParameter('ClientPassword', clientPassword),
                                           new DataParameter('ClientNotes', clientNotes),
                                           new DataParameter('ClientPostalCode', clientPostalCode),
                                           new DataParameter('ClientAddress', clientAddress),
                                           new DataParameter('ClientBirthdate', clientBirthdate) ];

  var lCommandResult: Int32 := fRemoteCommand.Execute('Clients_INSERT', lParams);
  exit lCommandResult > 0
end;

public func InsertClient(clientName: String!, _ clientDiscount: Double!, _ clientEmail: String!, _ clientPassword: String!, _ clientBirthdate: DateTime!, _ clientAddress: String!, _ clientPostalCode: String!, _ clientPhone: String!, _ clientNotes: String!) -> Boolean! {

      var lParams: DataParameter![] = [DataParameter("ClientName", clientName), DataParameter("ClientPhone", clientPhone), DataParameter("ClientDiscount", clientDiscount), DataParameter("ClientEmail", clientEmail), DataParameter("ClientPassword", clientPassword), DataParameter("ClientNotes", clientNotes), DataParameter("ClientPostalCode", clientPostalCode), DataParameter("ClientAddress", clientAddress), DataParameter("ClientBirthdate", clientBirthdate)]
    var lCommandResult: Int32! = fRemoteCommand.Execute("Clients_INSERT", lParams)
    return lCommandResult > 0
}

Public Function InsertClient(clientName As String, clientDiscount As Double, clientEmail As String, clientPassword As String, clientBirthdate As DateTime, clientAddress As String, _
    clientPostalCode As String, clientPhone As String, clientNotes As String) As Boolean
    Dim lParams As DataParameter() = New DataParameter() {New DataParameter("ClientName", clientName),
                                                          New DataParameter("ClientPhone", clientPhone),
                                                          New DataParameter("ClientDiscount", clientDiscount),
                                                          New DataParameter("ClientEmail", clientEmail),
                                                          New DataParameter("ClientPassword", clientPassword),
                                                          New DataParameter("ClientNotes", clientNotes),
                                                          New DataParameter("ClientPostalCode", clientPostalCode),
                                                          New DataParameter("ClientAddress", clientAddress),
                                                          New DataParameter("ClientBirthdate", clientBirthdate)}

    Dim lCommandResult As Int32 = fRemoteCommand.Execute("Clients_INSERT", lParams)
    Return lCommandResult > 0
End Function

Using the UPDATE command

As with the previous step, create an array that contains all of the parameters you wish to update. Compared to the previous command there is an additional parameter which is the ClientID which was assigned by the server and is used here to indicate which record is to be updated.

As before the array of parameters is passed to the Execute of the RemoteCommand, this time with the command name Clients_UPDATE. A result of 1 is returned if the command was successful and a 0 if it failed.

Note that if you supply an invalid command name or parameter name then an DAException will be thrown and should be handled.

public Boolean UpdateClient(Int32 clientId, String clientName, Double clientDiscount,
                                  String clientEmail, String clientPassword,
                                  DateTime clientBirthdate,
                                  String clientAddress, String clientPostalCode,
                                  String clientPhone, String clientNotes)
{
    DataParameter[] lParams = {
        new DataParameter ("ClientName", clientName ),
        new DataParameter ("ClientPhone", clientPhone ),
        new DataParameter ("ClientDiscount", clientDiscount ),
        new DataParameter ("ClientEmail", clientEmail ),
        new DataParameter ("ClientPassword", clientPassword ),
        new DataParameter ("ClientNotes", clientNotes ),
        new DataParameter ("ClientPostalCode", clientPostalCode ),
        new DataParameter ("ClientAddress", clientAddress ),
        new DataParameter ("ClientBirthdate", clientBirthdate ),
        new DataParameter ("OLD_ClientId", clientId )
    };
    Int32 lCommandResult = fRemoteCommand.Execute("Clients_UPDATE", lParams);
    return lCommandResult > 0;
}

method DataModule.UpdateClient(
  clientId: Int32; clientName: String; clientDiscount: Double;
  clientEmail: String; clientPassword: String; clientBirthdate: DateTime;
  clientAddress: String; clientPostalCode: String;
  clientPhone: String; clientNotes: String): Boolean;
begin
  var lParams: array of DataParameter := [ new DataParameter('ClientName', clientName),
                                           new DataParameter('ClientPhone', clientPhone),
                                           new DataParameter('ClientDiscount', clientDiscount),
                                           new DataParameter('ClientEmail', clientEmail),
                                           new DataParameter('ClientPassword', clientPassword),
                                           new DataParameter('ClientNotes', clientNotes),
                                           new DataParameter('ClientPostalCode', clientPostalCode),
                                           new DataParameter('ClientAddress', clientAddress),
                                           new DataParameter('ClientBirthdate', clientBirthdate),
                                           new DataParameter('OLD_ClientId', clientId) ];

  var lCommandResult: Int32 := fRemoteCommand.Execute('Clients_UPDATE', lParams);
  exit lCommandResult > 0
end;

public func UpdateClient(clientId: Int32!, _ clientName: String!, _ clientDiscount: Double!, _ clientEmail: String!, _ clientPassword: String!, _ clientBirthdate: DateTime!, _ clientAddress: String!, _ clientPostalCode: String!, _ clientPhone: String!, _ clientNotes: String!) -> Boolean! {

      var lParams: DataParameter![] = [DataParameter("ClientName", clientName), DataParameter("ClientPhone", clientPhone), DataParameter("ClientDiscount", clientDiscount), DataParameter("ClientEmail", clientEmail), DataParameter("ClientPassword", clientPassword), DataParameter("ClientNotes", clientNotes), DataParameter("ClientPostalCode", clientPostalCode), DataParameter("ClientAddress", clientAddress), DataParameter("ClientBirthdate", clientBirthdate), DataParameter("OLD_ClientId", clientId)]
    var lCommandResult: Int32! = fRemoteCommand.Execute("Clients_UPDATE", lParams)
    return lCommandResult > 0
}

Public Function UpdateClient(clientId As Int32, clientName As String, clientDiscount As Double, clientEmail As String, clientPassword As String, clientBirthdate As DateTime, _
            clientAddress As String, clientPostalCode As String, clientPhone As String, clientNotes As String) As Boolean
    Dim lParams As DataParameter() = New DataParameter() {New DataParameter("ClientName", clientName),
                                                          New DataParameter("ClientPhone", clientPhone),
                                                          New DataParameter("ClientDiscount", clientDiscount),
                                                          New DataParameter("ClientEmail", clientEmail),
                                                          New DataParameter("ClientPassword", clientPassword),
                                                          New DataParameter("ClientNotes", clientNotes),
                                                          New DataParameter("ClientPostalCode", clientPostalCode),
                                                          New DataParameter("ClientAddress", clientAddress),
                                                          New DataParameter("ClientBirthdate", clientBirthdate),
                                                          New DataParameter("OLD_ClientId", clientId)}
    Dim lCommandResult As Int32 = fRemoteCommand.Execute("Clients_UPDATE", lParams)
    Return lCommandResult > 0
End Function

Using the REMOVE command

To delete a record from the Clients table, call the Execute method passing the name of the delete command (Clients_DELETE) as well as an array that contains the ClientId of the selected row. This value will be used by the sql in the command to delete the row.

public Boolean DeleteClient(Int32 clientId)
{
    DataParameter[] lParams = { new DataParameter("OLD_ClientId", clientId) };
    Int32 lCommandResult = fRemoteCommand.Execute("Clients_DELETE", lParams);
    return lCommandResult > 0;
}

method DataModule.DeleteClient(clientId: Int32): Boolean;
begin
  var lParams: array of DataParameter := [ new DataParameter('OLD_ClientId', clientId) ];
  var lCommandResult: Int32 := fRemoteCommand.Execute('Clients_DELETE', lParams);
  exit lCommandResult > 0
end;

public func DeleteClient(clientId: Int32!) -> Boolean! {

  var lParams: DataParameter![] = [DataParameter("OLD_ClientId", clientId)]
    var lCommandResult: Int32! = fRemoteCommand.Execute("Clients_DELETE", lParams)
    return lCommandResult > 0
}

Public Function DeleteClient(clientId As Int32) As Boolean
    Dim lParams As DataParameter() = New DataParameter() {New DataParameter("OLD_ClientId", clientId)}
    Dim lCommandResult As Int32 = fRemoteCommand.Execute("Clients_DELETE", lParams)
    Return lCommandResult > 0
End Function

Using DA SQL to retrieve data

Data Abstract provides a technology called DA SQL that allows you to execute an SQL statement against the schema data rather than directly against the backend table.

To use DA SQL create a string that contains the SQL clause you wish to use. Then call the FillWithDASql passing in the DataTable that will contain the table data as the first argument, then the DA SQL statement as the second argument, and finally an optional array of DataParameters that would be needed for arguments to the DA SQL statement.

This sample uses DA SQL to retrieve all of the rows from the "Clients" table to demonstrate that the changes made with a command have succeeded.

public void SelectClients()
{
    String lDASQL = "SELECT * FROM Clients";
    this.clientsTable.Clear();
    remoteDataAdapter.FillWithDASql(this.clientsTable, lDASQL);
}

method DataModule.SelectClients();
begin
  var lDASQL: String := 'SELECT * FROM Clients';
  self.clientsTable.Clear();
  remoteDataAdapter.FillWithDASql(self.clientsTable, lDASQL, nil)
end;

public func SelectClients() {

    var lDASQL: String! = "SELECT * FROM Clients"
    self.clientsTable.Clear()
    remoteDataAdapter.FillWithDASql(self.clientsTable, lDASQL)
}

Public Sub SelectClients()
    Dim lDASQL As String = "SELECT * FROM Clients"
    Me.clientsTable.Clear()
    remoteDataAdapter.FillWithDASql(Me.clientsTable, lDASQL, Nothing)
End Sub