How to use SELECT in OSQL

• 9 minutes to read

Here we will show how we may use the SELECT statement of OSQL to retrieve a selected amount of rows and columns.

The SELECT statement is similar to one that is used in SQL. The main difference is the differentiation of keywords.

Example 1

using SuperOffice.CRM.Data;
using SuperOffice.Data;
using SuperOffice.Data.SQL;
using SuperOffice;
using(SoSession session = SoSession.Authenticate("SAL0", ""))
{
  //Using the Select Statement to retrieve Data
  //Creating DataSets with the Tables of the Database
  ContactTableInfo newConTable = TablesInfo.GetContactTableInfo();
  //Retrieving Data from the tables into an Instance of the Select Class
  Select newSelect = S.NewSelect();
  //Choosing the columns that should be retrieved
  newSelect.ReturnFields.Add(newConTable.ContactId, newConTable.Department, newConTable.Name);
  //Restricting the rows that should be returned
  newSelect.Restriction = newConTable.ContactId.In(S.Parameter(5), S.Parameter(6));
  //Ordering the retrieved Data
  newSelect.OrderBy.SortOrder.Add(newConTable.Name, SuperOffice.Util.OrderBySortType.ASC);
  //Establishing a Database Connection
  SoConnection myConn = ConnectionFactory.GetConnection();
  //Creating and SoCommand instance and assigning the earlier created Select statement
  SoCommand myComm = myConn.CreateCommand();
  myComm.SqlCommand = newSelect;
  myConn.Open();
  //Loading the Data into the DataReader
  SoDataReader myReader = myComm.ExecuteReader();
  //Retrieving the Data from the Reader
  while (myReader.Read())
  {
    int conID = myReader.GetInt32(0);
    string conDept = myReader.GetString(1);
    string conName = myReader.GetString(2);
  }

  //Closing the Reader and Disposing the session
  myReader.Close();
}

TablesInfo

To write any OSQL statement, we have to know about the tables that we are going to pull data from. So for us to get the information on a data table, NetServer has provided us the TablesInfo class. Through this class, we can pull out information on any table in the SuperOffice database to an appropriate table object.

In the example, the following line will retrieve the contact table information.

//Retrieving Data from the tables into an Instance of the Select Class

Select object (S class)

Next, it is worth mentioning the functionality of the static S class. This class is used to create the objects for SQL types (Select, Insert, Delete), the parameter object, and different math objects.

In the example, we are developing a SELECT statement, so here we will create a Select object through the S class.

newSelect.ReturnFields.Add(newConTable.ContactId, newConTable.Department, newConTable.Name);

Build query

Now that you have all the table information you need and you have a select object, you can start building your select query.

Add the return fields as shown in the example and you can add a restriction and give the restriction parameters through the Select class.

You can sort your results by using the functionality of SuperOffice.Util.OrderBySortType class.

When you have created your query, all that is left is to execute this query on the database.

Run query

To execute this query, you have to have a connection, a command, and a reader to hold the data like any other normal SQL statement. The only difference is that all these should be NetServer provided objects like in the above example.

Reader

Finally, when you have executed the query, the data will be held in the reader and you can retrieve the data from the NetServer data reader as shown in the example.

Example 2

The above example of the select statement is very simple, so now let us look at a more complex select example.

using SuperOffice.CRM.Data;
using SuperOffice.Data;
using SuperOffice.Data.SQL;
using SuperOffice;
//Using the Select Statement to retrieve Data
//Creating DataSets with the Tables of the Database
ContactTableInfo newConTable = TablesInfo.GetContactTableInfo();
PersonTableInfo newPerTable = TablesInfo.GetPersonTableInfo();
EmailTableInfo newEMLTable = TablesInfo.GetEmailTableInfo();
CountryTableInfo newCouTable = TablesInfo.GetCountryTableInfo();
//Retrieving Data from a the tables in to a Instance of the Select Class
Select newSelect = S.NewSelect();
//Choosing the columns that should be retrieved
newSelect.ReturnFields.Add(newConTable.ContactId, newConTable.Department, newConTable.Name, newPerTable.Firstname, newPerTable.Lastname, newPerTable.PhonePresent, newEMLTable.EmailAddress, newEMLTable.Description, newCouTable.EnglishName);
//Restricting the rows that should be returned
newSelect.Restriction = newConTable.ContactId.In(S.Parameter(5),S.Parameter(6)).And (newPerTable.ContactId.LessThan(S.Parameter(9)));
//Joining the multiple tables
newSelect.JoinRestriction.InnerJoin(newConTable.ContactId.Equal(newPerTable.ContactId));
newSelect.JoinRestriction.LeftOuterJoin(newConTable.ContactId.Equal(newEMLTable.ContactId));
newSelect.JoinRestriction.LeftOuterJoin(newConTable.CountryId.Equal(newCouTable.CountryId));
//Ordering the retrieved Data
newSelect.OrderBy.SortOrder.Add(newConTable.Name, SuperOffice.Util.OrderBySortType.ASC);
using(SoSession mySession = SoSession.Authenticate("SAM", "sam"))
{
  if (mySession == null) return;
  //Establishing a Database Connection
  SoConnection myConn = ConnectionFactory.GetConnection();
  //Creating and SoCommand instance and assigning the earlier created Select statement
  SoCommand myComm = myConn.CreateCommand();
  myComm.SqlCommand = newSelect;
  myConn.Open();
  //Loading the Data into the DataReader
  SoDataReader myReader = myComm.ExecuteReader();
  //Retrieving the Data from the Reader
  while (myReader.Read())
  {
    string conDept = myReader.GetString(1);
    string conName = myReader.GetString(2);
    string perName = myReader.GetString(3) + " " + myReader.GetString(4);
    string perPhone = myReader.GetInt16(5).ToString();
    string conEmail = myReader.GetString(6);
    string conEmlDesc = myReader.GetString(7);
    string conCou = myReader.GetString(8);
  }
  //Closing the Reader and Disposing the session
  myReader.Close();
  mySession.Dispose();
}

Here, we retrieve contacts information from more than one table to show how we can incorporate a join to the select statement.

The Select object has all the methods to create all the join statements that exist in SQL. It is a matter of creating the type of join you want and joining the tables (in a logical way) just as in a normal SQL statement.

Create objects of tables being joined

PersonTableInfo newPerTable = TablesInfo.GetPersonTableInfo();
EmailTableInfo newEMLTable = TablesInfo.GetEmailTableInfo();
CountryTableInfo newCouTable = TablesInfo.GetCountryTableInfo();
//Retrieving Data from a the tables in to a Instance of the Select Class

Contact, Person, Email, and Country tables of the database have being used by the Select statement to retrieve the data required. With the use of the above statement, objects of these tables have being created.

newSelect.ReturnFields

To retrieve that data we make use of the Select class.

newSelect.ReturnFields.Add(newConTable.ContactId, newConTable.Department, newConTable.Name, newPerTable.Firstname, newPerTable.Lastname, newPerTable.PhonePresent, newEMLTable.EmailAddress, newEMLTable.Description, newCouTable.EnglishName);

Using the ReturnFields property of the Select class, we specify the columns that we intend to retrieve from the SELECT statement.

newSelect.Restriction = newConTable.ContactId.In(S.Parameter(5),S.Parameter(6)).And (newPerTable.ContactId.LessThan(S.Parameter(9)));

Restriction

Note

If you have more than one table, you can use those tables in the restriction statement as well.

When it comes to adding the restrictions, you can:

Use any field from any table
Include a AND statement
Include an OR statement
Include both

This works just as a normal SQL statement. So though you use the NetServer OSQL statements, the power of normal SQL is not restricted.

To restrict the number of rows returned by the query, we use the Restriction property exposed by the Select class. This statement is similar to that of a WHERE clause in SQL.

newSelect.JoinRestriction.LeftOuterJoin(newConTable.ContactId.Equal(newEMLTable.ContactId));

Join

Since we have used multiple tables to retrieve the data, we are now required to join these tables. This can be with the use of the JoinRestriction property:

newSelect.OrderBy.SortOrder.Add(newConTable.Name, SuperOffice.Util.OrderBySortType.ASC);
using(SoSession mySession = SoSession.Authenticate("SAM", "sam"))
{

This property consist of methods that are capable of acting as inner join, left outer join, and so on in SQL terms. For this, we need to have knowledge about the database structure since we need to know how the tables are connected. Here we have used the ContactId of the contact table to join ID to the person, email, and country tables.

OrderBy

The ORDERBY statement at the end is used to sort the returned rows in the ascending order of the contact’s name.

SoDataReader

Once the SELECT statement is complete, we may use the SoDataReader to retrieve the returned data.

Note

OSQL also provides Aggregate functions as well as Mathematical functions, though we have not looked at them in the above examples. These are also available through the Math and FieldExpression properties of the static class, which holds methods like Count(), Distinct(), Max(), and Min(). The only thing that restricts what data you can get out of the SuperOffice database using OSQL, is your knowledge of the database structure, which is a field of study on its own.