Console

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

Getting Started

The sample is typically located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\<language>\Commands (Console), 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.

Included with the sample is a DataTableExtension class which has a single public static method (LogToString) that takes a DataTable object and creates a nicely formatted string representation of the table that can be printed to the Console which can be useful for debugging purposes. (An example of its output can be seen at the end of this document)

Running the Sample

When run the sample sets up a connection to an instance of Relativity Server and logs in (Step 1). It then creates a collection of DataParameters that are passed to the server along with the command name to execute, after printing the result of the command a DA SQL statement is used to retrieve the new row from the Clients table (Step 2). The values in a number of the DataParameters are updated and passed to the server to update the values there using the UPDATE command (Step 3). Finally the Id value from the new row is used to delete the new row from the table (Step 4).

Examining the Code

All of the code can be found in the Program source file. The "Clients" schema table used in this sample is comprised of 10 fields, which this sample will limit to just 4 using DA SQL.

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.

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).

The remaining lines generate an SQL select statement to specifically retrieve the new record from the Clients table, specifying to retrieve the row(s) where the ClientName field matches the value from the dpClientName parameter. The ClientId that the database assigns the record is extracted from the first object in the table, and will be used in the following steps to work with that specific record.

Lastly the newly retrieved table is printed to the console so you can see that the command was successfully executed.

Console.WriteLine("STEP 2. Call command for adding a new client row ...");

DataParameter dpClientName = new DataParameter { Name = "ClientName", Value = "Jane C." };
DataParameter dpClientDiscount = new DataParameter { Name = "ClientDiscount", Value = 0.01 };
DataParameter dpClientEmail = new DataParameter { Name = "ClientEmail", Value = "test@gmail.com" };
DataParameter dpClientPassword = new DataParameter { Name = "ClientPassword", Value = "temp" };
DataParameter dpClientBirthday = new DataParameter { Name = "ClientBirthday", Value = new DateTime(1973, 6, 1) };
DataParameter dpClientPhone = new DataParameter { Name = "ClientPhone", Value = "Unknown yet" };
DataParameter dpPostalCode = new DataParameter { Name = "ClientPostalCode", Value = "???" };
DataParameter dpClientAddress = new DataParameter { Name = "ClientAddress", Value = "Somewhere in Texas..." };
DataParameter dpClientNotes = new DataParameter { Name = "ClientNotes", Value = "Added by command" };


DataParameter[] lArrayDataParametr = new DataParameter[] { dpClientName, dpClientDiscount, dpClientEmail, dpClientPassword, dpClientPhone, dpPostalCode, dpClientAddress, dpClientNotes };

RemoteCommand lRemoteCommand = new RemoteCommand();
lRemoteCommand.RemoteService = lDataService;

Int32 lResultExCommand = lRemoteCommand.Execute("Clients_INSERT", lArrayDataParametr);
Console.WriteLine(String.Format("Done with result: {0}", lResultExCommand));

Console.WriteLine("STEP 2. Call command for adding a new client row ...")

var dpClientName: DataParameter! = DataParameter("ClientName", "Jane C." )
var dpClientDiscount: DataParameter! = DataParameter("ClientDiscount", 0.01)
var dpClientEmail: DataParameter! = DataParameter("ClientEmail", "test@gmail.com")
var dpClientPassword: DataParameter! = DataParameter("ClientPassword", "temp")
var dpClientBirthday: DataParameter! = DataParameter("ClientBirthday", DateTime(1973, 6, 1))
var dpClientPhone: DataParameter! = DataParameter("ClientPhone", "Unknown yet")
var dpPostalCode: DataParameter! = DataParameter("ClientPostalCode", "???")
var dpClientAddress: DataParameter! = DataParameter("ClientAddress", "Somewhere in Texas...")
var dpClientNotes: DataParameter! = DataParameter("ClientNotes", "Added by command")
var lArrayDataParametr: DataParameter![] = ([dpClientName, dpClientDiscount, dpClientEmail, dpClientPassword, dpClientPhone, dpPostalCode, dpClientAddress, dpClientNotes] as? DataParameter![])
var lRemoteCommand: RemoteCommand! = RemoteCommand()
lRemoteCommand.RemoteService = lDataService
var lResultExCommand: Int32! = lRemoteCommand.Execute("Clients_INSERT", lArrayDataParametr)

Console.WriteLine(String.Format("Done with result: {0}", lResultExCommand))

Using the UPDATE command

As with the previous step, create an array that contains all of the parameters you wish to update. There is an additional parameter which is the ClientID we retrieved at the end of the previous step, and this the id assigned to the record by the database.

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.

To show the record was correctly updated, we again use FillWithDASql of RemoteDataAdapter to retrieve a table with only the specific record. The SQL SELECT statement is slightly different this time around, using the lClientId.

Console.WriteLine("STEP 3. Call command for modification client row ...");

dpClientName.Value = "Jane Colins";
dpClientDiscount.Value = 0.05;
dpClientPhone.Value = "(095) 636-19-63";
dpClientNotes.Value = "Updated by command";
dpClientEmail.Value = "janec@gmail.com";
dpClientPassword.Value = "djCkU";
dpClientAddress.Value = "4935 Oliver Street Weatherford, TX 76086";
DataParameter dpOldClientId = new DataParameter { Name = "OLD_ClientId", Value = lClientID };

lArrayDataParametr = new DataParameter[] { dpClientName, dpClientDiscount, dpClientPhone, dpClientNotes, dpClientEmail, dpClientPassword, dpClientAddress, dpOldClientId };

lResultExCommand = lRemoteCommand.Execute("Clients_UPDATE", lArrayDataParametr);
Console.WriteLine(String.Format("Done with result: {0}", lResultExCommand));

Console.WriteLine("STEP 3. Call command for modification client row ...")

dpClientName.Value = "Jane Colins"
dpClientDiscount.Value = 0.05
dpClientPhone.Value = "(095) 636-19-63"
dpClientNotes.Value = "Updated by command"
dpClientEmail.Value = "janec@gmail.com"
dpClientPassword.Value = "djCkU"
dpClientAddress.Value = "4935 Oliver Street Weatherford, TX 76086"
var dpOldClientId: DataParameter! = DataParameter("OLD_ClientId", lClientID)
lArrayDataParametr = ([dpClientName, dpClientDiscount, dpClientPhone, dpClientNotes, dpClientEmail, dpClientPassword, dpClientAddress, dpOldClientId] as? DataParameter![])
lResultExCommand = lRemoteCommand.Execute("Clients_UPDATE", lArrayDataParametr)
Console.WriteLine(String.Format("Done with result: {0}", lResultExCommand))

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 only the previously retrieved lClientID value.

This time, when FillWithDASql is called, the table returned will be empty as the row has been deleted.

Console.WriteLine("STEP 4. Call command for removing client row ...");
lArrayDataParametr = new DataParameter[] { dpOldClientId };
lResultExCommand = lRemoteCommand.Execute("Clients_DELETE", lArrayDataParametr);

Console.WriteLine("STEP 4. Call command for removing client row ...")

lArrayDataParametr = ([dpOldClientId] as? DataParameter![])
lResultExCommand = lRemoteCommand.Execute("Clients_DELETE", lArrayDataParametr)

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 it 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 3 times to (try and) retrieve the newly added record from the Clients table.

dasql = String.Format("SELECT ClientId, ClientName, ClientPhone, ClientNotes FROM clients WHERE clientid = {0}", lClientID);
lDataAdapter.FillWithDASql(lClientsData, dasql, null);

dasql = String.Format("SELECT ClientId, ClientName, ClientPhone, ClientNotes FROM clients WHERE clientid = {0}", lClientID)
lDataAdapter.FillWithDASql(lClientsData, dasql, nil)

Example Output

This is an example of the output you will see when the sample is run. You can see in Step 2 the table contains only the newly added row (thanks to using DA SQL to selectively retrieve data). Then in Step 3 that the values for the ClientName, ClientPhone and ClientNotes fields have been updated. Finally as the row is deleted in Step 4, the DA SQL statement fails to find a matching row, so the resultant table is empty.

Commands sample has been started.
Target URL is http://localhost:7099/bin
RO SDK layer is configured.
RO DataAbstract layer is configured.
STEP 1: Login to DataService
User Id="simple";Password="simple";Domain="DASamples";Schema="Simple"
Login has been successful. Going further...

STEP 2. Call command for adding a new client row ...
Done with result: 1

Table: Clients (1 rows from 1)
|--------------------------------------------------------------------------|
| ClientId     | ClientName        | ClientPhone       | ClientNotes       |
|--------------------------------------------------------------------------|
| 26           | Jane C.           | Unknown yet       | Added by command  |
|--------------------------------------------------------------------------|


STEP 3. Call command for modification client row ...
Done with result: 1

Table: Clients (1 rows from 1)
|--------------------------------------------------------------------------|
| ClientId     | ClientName        | ClientPhone       | ClientNotes       |
|--------------------------------------------------------------------------|
| 26           | Jane Colins       | (095) 636-19-63   | Updated by com... |
|--------------------------------------------------------------------------|


STEP 4. Call command for removing client row ...
Done with result: 1
Nothing to log. Rows collection is empty.
DONE! Please press return to exit.