Desktop
The DASQL desktop sample demonstrates using a DA SQL statement to selectively retrieve data from tables in the "Simple" schema in an instance of Relativity Server.
Getting Started
The sample is typically located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\<language>\DASQL, 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
This sample allows you to see the results of running a pre-defined complex DA SQL statement that retrieves a table where the rows are comprised of 3 fields which are assigned different names (Group, Product and Sold) from 4 different tables (OrderDetails, Orders, 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.
When you first run the sample you will see an empty area where the table data will be displayed and a toolbar which contains a combo box and a button. When pressed the "DASQL Report" button will execute the DA SQL statement on the server specified in the combo box, the retrieved results will then be used to populate the table. The combo box contains the address of the server to connect to, which by default points to an instance of Relativity Server running on the local machine. If you enter a different address, then that will be used when you press the "DASQL Report" button.
Examining the Code
The sample is built around 2 classes, DataModule which handles the interaction with the server including creating and sending the DA SQL statement. MainForm handles creating the user interface and the user interactions.
The code for creating the connection to the server and handling logging in is covered by other samples. In this section we shall focus solely on creating and issuing the DA SQL statement which can be found in the DataModule class.
NOTE If the table name, or the field names do not exist or if there is a mistake in the SQL statement then a RemObjects.SDK.Types.ServerException exception will be raised and should be handled appropriately.
Creating and Issuing a Complex DA SQL statement
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. DA SQL statements aren't limited to basic requests, you can compose as complex a request as you need and there are no special classes required to use DA SQL, simply put the statement in a String object and pass it to the FillWithDASql method of RemoteDataAdapter.
In this particular sample a statement is created that will retrieve a new table where the rows are comprised of 3 fields which are assigned different names (Group, Product and Sold) from 4 different tables (OrderDetails, Orders, 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.
The DA SQL statement is passed as the second argument to the as the second argument to the FillWithDASql method of RemoteDataAdapter; the first argument is an instance of DataTable that will be used to store the retrieved data and the last argument is for an optional array of DataParameter objects that would be used as arguments for the SQL statement.
public void GetDASQLReport()
{
int ORDER_TYPE_SALE = 2;
String DATE_FROM = "2013-01-01";
String DATE_TO = "2013-12-12";
String DASQLTemplate = @"
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}
AND o.Date > '{1}'
AND o.Date <= '{2}'
GROUP BY
g.Id,
g.Name,
p.Name
ORDER BY
3 Desc";
String DASQL = String.Format(DASQLTemplate, ORDER_TYPE_SALE, DATE_FROM, DATE_TO);
this.reportTable.Clear();
remoteDataAdapter.FillWithDASql(this.reportTable, DASQL, null);
}
method DataModule.GetDASQLReport;
begin
var ORDER_TYPE_SALE: System.Int32 := 2;
var DATE_FROM: String := '2013-01-01';
var DATE_TO: String := '2013-12-12';
var DASQLTemplate: String := "
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}
AND o.Date > '{1}'
AND o.Date <= '{2}'
GROUP BY
g.Id,
g.Name,
p.Name
ORDER BY
3 Desc";
var DASQL: String := String.Format(DASQLTemplate, ORDER_TYPE_SALE, DATE_FROM, DATE_TO);
self.reportTable.Clear();
remoteDataAdapter.FillWithDASql(self.reportTable, DASQL, nil)
end;
public func GetDASQLReport() -> ()! {
var ORDER_TYPE_SALE: Int32 = 2
var DATE_FROM: String! = "2013-01-01"
var DATE_TO: String! = "2013-12-12"
var DASQLTemplate: String! = "\r\n SELECT g.Name AS [Group], \r\n p.Name AS Product, \r\n sum(d.Amount) AS Sold \r\n FROM \r\n OrderDetails d \r\n INNER JOIN Orders o \r\n ON o.Id = d.[Order] \r\n INNER JOIN Products p \r\n ON p.Id = d.Product \r\n INNER JOIN ProductGroups g \r\n ON g.Id = p.[Group] \r\n WHERE \r\n o.Type = {0} \r\n AND o.Date > \'{1}\' \r\n AND o.Date <= \'{2}\' \r\n GROUP BY \r\n g.Id, \r\n g.Name, \r\n p.Name \r\n ORDER BY \r\n 3 Desc"
var DASQL: String! = String.Format(DASQLTemplate, ORDER_TYPE_SALE, DATE_FROM, DATE_TO)
self.reportTable.Clear()
remoteDataAdapter.FillWithDASql(self.reportTable, DASQL, nil)
}
Public Sub GetDASQLReport()
Dim ORDER_TYPE_SALE As Integer = 2
Dim DATE_FROM As String = "2013-01-01"
Dim DATE_TO As String = "2013-12-12"
Dim DASQLTemplate As String = "" &
"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} " &
"AND o.Date > '{1}' " &
"AND o.Date <= '{2}' " &
"GROUP BY " &
" g.Id, " &
" g.Name, " &
" p.Name " &
"ORDER BY " &
"3 Desc"
Dim DASQL As String = String.Format(DASQLTemplate, ORDER_TYPE_SALE, DATE_FROM, DATE_TO)
Me.reportTable.Clear()
remoteDataAdapter.FillWithDASql(Me.reportTable, DASQL, Nothing)
End Sub