In this post, we will discuss how to execute queries using ALM OTA API. We will use C#.Net.

To execute queries in ALM we will use the following OTA API Objects

1. TDConnection – For creating Connection
2. Command – For executing Queries
3. RecordSet – For getting the results

TDConnection

Learn how to connect to ALM using TDConnection object, read this article.

Command

Once your connection to ALM is established then you can use Command object to execute a query on ALM. If you want to know more about the tables and columns in ALM then you can either use the analysis view of ALM or google search for “ALM Database reference”. You will need to get the Command object from TDConnection object.

TDAPIOLELib.Command OCommand = (TDAPIOLELib.Command)OALMConnection.Command;

After this, you will need to set the query to the Command Object

OCommand.CommandText = "Select Count(*) from bug";

RecordSet

Execute the query and get the results in the RecordSet Object.

TDAPIOLELib.Recordset recordset = OCommand.Execute()

Complete Code will look like this

//Count defects in ALM Project
        public int CountAllDefects(TDAPIOLELib.TDConnection OALMConnection)
        {
            TDAPIOLELib.Recordset ORecordSet = ExecuteQuery("Select Count(*) from Bug", OALMConnection);
            ORecordSet.First();
            return Convert.ToInt32(ORecordSet[0]);
        }

        //Find all defect values
        public Boolean GetAllDetails(TDAPIOLELib.Bug bug, TDAPIOLELib.TDConnection OALMConnection)
        {
            TDAPIOLELib.Recordset ORecordSet = ExecuteQuery("Select * from Bug where BG_BUG_ID = " + bug.ID, OALMConnection);
            ORecordSet.First();
            for (int i = 0; i < ORecordSet.RecordCount; i++)
            {
                for (int j = 0; j < ORecordSet.ColCount; j++)
                {
                    Console.WriteLine(ORecordSet.ColName[j] + "--" + ORecordSet[j]);
                }
                ORecordSet.Next();
            }
            return true;
        }

        //Executes Query
        public static TDAPIOLELib.Recordset ExecuteQuery(String QueryToExecute, TDAPIOLELib.TDConnection OALMConnection)
        {
            try
            {
                //Block the execution if its not Select Query
                if (!(QueryToExecute.Trim().ToUpper().StartsWith("SELECT")))
                    throw (new Exception("Only Select Query can be executed using this funtion"));

                TDAPIOLELib.Command OCommand = (TDAPIOLELib.Command)OALMConnection.Command;
                OCommand.CommandText = QueryToExecute;
                return (TDAPIOLELib.Recordset)OCommand.Execute();
            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message.ToString()));
            }
        }

Let me know in comments if you have any questions