Console

The DASQL console sample demonstrates using two different SQL statements to selectively retrieve data from tables in the "Simple" schema in an instance of Relativity Server. The first is a simple statement that retrieves 4 fields from the Clients table for a particular row. The second SQL statement is more complex and retrieves a new table whose fields are taken from 3 different tables, where their rows match particular criteria.

Getting Started

The sample is typically located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\<language>\DASQL (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 the sample is run it sets up a connection to the server (Step 1), and then creates a simple DA SQL statement to retrieve certain fields from the Clients table for any row where the value in the ClientId matches "20", sends it to the server and prints out the resultant table (Step 2). Finally a more complex DA SQL is created and the results from that are then printed to the console (Step 3).

Examining the Code

All of the client code can be found in the Program source file. The code for creating the connection to the server and handling logging in is covered by other samples. In this section we are focusing solely on Steps 2 & 3 creating and issuing the DA SQL statement.

A Simple DA SQL statement (Step 2)

A DA SQL statement is essentially a normal SQL statement that uses the table & field names from the schema rather than the ones in the back-end Database. There is no special class required to use DA SQL, simply put the DA SQL statement in a String object and pass it as the second argument to the FillWithDASql method of RemoteDataAdapter; the first argument should be an instance of DataTable that will be used to hold the data returned after executing the DA SQL statement and the last argument is for an optional array of DataParameter objects that would be used as arguments for the SQL statement.

Here a DA SQL statement is created that retrieves the clientid, clientname, clientphone and clientaddress fields from the clients table where the clientid is 20. It is passed along with a new instance of DataTable and a null to the FillWithDASql method of RemoteDataAdapter.

Console.WriteLine("STEP 2. Simple DASQL ...");

Int32 lClientId = 20;
String lDASql = String.Format("SELECT clientid, clientname, clientphone, clientaddress FROM clients WHERE clientid = {0}", lClientId);
Console.WriteLine("DASQL: {0}", lDASql);

DataTable lTable = new DataTable();
lDataAdapter.FillWithDASql(lTable, lDASql, null);

Console.WriteLine("STEP 2. Simple DASQL ...")

var lClientId: Int32! = 20
var lDASql: String! = String.Format("SELECT clientid, clientname, clientphone, clientaddress FROM clients WHERE clientid = {0}", lClientId)

Console.WriteLine("DASQL: {0}", lDASql)

var lTable: DataTable! = DataTable()
lDataAdapter.FillWithDASql(lTable, lDASql, nil)

A Complex DA SQL statement (Step 3)

DA SQL statements aren't limited to basic requests interacting with a single table, you can compose as complex a SQL statement as you need.

Here the statement retrieves a table where the rows are comprised of 3 fields which are assigned different names (Group, Product and Sold) from 3 different tables (OrderDetails, Products and ProductGroups) where the order Type was "2", and the order date was between "2013-01-01" and "2013-12-12" and the data is grouped together and ordered by two columns, first by column "3" (i.e. the amount sold) and then by the "Desc" column.

Console.Write("STEP 3. Complex DASQL ...\n");
String lDASqlTemplate = @"

SELECT     g.Name        AS [Group],   
p.Name        AS Product,   
sum(d.Amount) AS Sold       
FROM                                   
OrderDetails d              
INNER JOIN Orders o                    
ON o.Id = d.[Order]            
INNER JOIN Products p                  
ON p.Id = d.Product            
INNER JOIN ProductGroups g             
ON g.Id = p.[Group]            
WHERE                                  
o.Type =   {0}   -- Sale              
AND        o.Date >  '{1}'  -- 2013-01-01              
AND        o.Date <= '{2}'  -- 2013-12-12           
GROUP BY                               
g.Id,                       
g.Name,                     
p.Name                      
ORDER BY                               
3 Desc                      ";

Int32 lOrderTypeSale = 2;
String lFromDateString = "2013-01-01";
String lToDateString = "2013-12-12";
lDASql = String.Format(lDASqlTemplate, lOrderTypeSale, lFromDateString, lToDateString);

Console.Write("STEP 3. Complex DASQL ...\n")

var lDASqlTemplate: String! = "\r\n\r\nSELECT     g.Name        AS [Group],   \r\n           p.Name        AS Product,   \r\n           sum(d.Amount) AS Sold       \r\nFROM                                   \r\n           OrderDetails d              \r\nINNER JOIN Orders o                    \r\n        ON o.Id = d.[Order]            \r\nINNER JOIN Products p                  \r\n        ON p.Id = d.Product            \r\nINNER JOIN ProductGroups g             \r\n        ON g.Id = p.[Group]            \r\nWHERE                                  \r\n           o.Type =   {0}   -- Sale              \r\nAND        o.Date >  \'{1}\'  -- 2013-01-01              \r\nAND        o.Date <= \'{2}\'  -- 2013-12-12           \r\nGROUP BY                               \r\n           g.Id,                       \r\n           g.Name,                     \r\n           p.Name                      \r\nORDER BY                               \r\n           3 Desc                      "
var lOrderTypeSale: Int32! = 2
var lFromDateString: String! = "2013-01-01"
var lToDateString: String! = "2013-12-12"
lDASql = String.Format(lDASqlTemplate, lOrderTypeSale, lFromDateString, lToDateString)

Console.WriteLine("DASQL:\n {0}", lDASql)

lTable = DataTable()
lDataAdapter.FillWithDASql(lTable, lDASql, nil)

Example Output

This is an example of the output you will see when the sample is run. As each step starts the DA SQL statement is printed to the console, followed by the results of the request.

DA SQL 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
Login string is UserId="simple";Password="simple";Domain="DASamples";Schema="Simple"
Login has been successful. Going further...

STEP 2. Simple DASQL ...
DASQL: SELECT clientid, clientname, clientphone, clientaddress FROM clients WHERE clientid = 20

Table:  (1 rows from 1)
|--------------------------------------------------------------------------|
| ClientId     | ClientName        | ClientPhone       | ClientAddress     |
|--------------------------------------------------------------------------|
| 20           | Anna H. Kugler    | (817) 249-9525    | 597 Loving Acr... |
|--------------------------------------------------------------------------|


STEP 3. Complex DASQL ...
DASQL:


SELECT     g.Name        AS [Group],
           p.Name        AS Product,
           sum(d.Amount) AS Sold
FROM
           OrderDetails d
INNER JOIN Orders o
        ON o.Id = d.[Order]
INNER JOIN Products p
        ON p.Id = d.Product
INNER JOIN ProductGroups g
        ON g.Id = p.[Group]
WHERE
           o.Type =   2   -- Sale
AND        o.Date >  '2013-01-01'  -- 2013-01-01
AND        o.Date <= '2013-12-12'  -- 2013-12-12
GROUP BY
           g.Id,
           g.Name,
           p.Name
ORDER BY
           3 Desc

Table:  (17 rows from 17)
|------------------------------------------------------|
| Group             | Product           | Sold         |
|------------------------------------------------------|
| RAM               | RAM DDR3 4096Mb   | 4            |
| Monitors          | Monitor 22"; Dell | 4            |
| Motherboards      | Motherboard IN... | 2            |
| Hard-Disk Drives  | HDD Seagate Ba... | 2            |
| CPU               | CPU INTEL i5-4670 | 2            |
| Cases             | Case CM HAF 91... | 2            |
| Tablets           | Apple A1474 iP... | 2            |
| Solid State Disks | SSD Intel 240 Gb  | 1            |
| Video             | Video Asus NVi... | 1            |
| Mouses            | Mouse Logitech... | 1            |
| Monitors          | Monitor 22"; Asus | 1            |
| Notebooks         | Apple MacBook ... | 1            |
| Notebooks         | Notebook 15.6"... | 1            |
| Netbooks          | Ultrabook 13.3... | 1            |
| Tablets           | Tablet 10,1"; ... | 1            |
| Players           | TRANSCEND T.so... | 1            |
| Laser Printers    | Printer HP Las... | 1            |
|------------------------------------------------------|


DONE! Please press return to exit.