Hi Ruben,

For your use case, if the "Select" query is something you could otherwise attain by using the built-in tools such as Aggregates or Advanced SQL Queries, instead of adding the query to your C# extension somehow I would try to "pass" the result set of the Aggregate/Advanced SQL Query to the action you're creating in the extension. 

In general, this means adding an input parameter to your extension action with the desired result (e.g. Primitive variable, List of Records, Text to receive a JSON, etc.) so that you can access/manipulate them in your extension code more easily without having to worry about the database connection overhead. Basically passing the output of the Aggregate/Advanced SQL Query to one (or many) of the input parameters of your action. For most scenarios, I think this should result in more manageable, maintainable code as well.

Hopefully that helps!

Hi Ruben,

We have an extension that accesses data from an OutSystems database. We created a small class called SqlHelper:

    public class SqlHelper
    {
        private static SqlCommand GetSqlCommand()
        {
            var dbaProvider = DatabaseAccess.ForRunningApplication();
            var trans = dbaProvider.GetRequestTransaction();
            return (SqlCommand)(trans.CreateCommand().GetDriverCommand());
        }

        public static string SqlCommandExecute(string sqlCommand)
	{
            SqlCommand command = SqlHelper.GetSqlCommand();

            command.CommandText = sqlCommand;
            command.CommandType = CommandType.Text;
            command.CommandTimeout = 15;

            return command.ExecuteScalar().ToString();
        }
    }

And we invoke it to query data like this:

var result = SqlHelper.SqlCommandExecute($"SELECT Value FROM DYNAMIC_SETTING WHERE Label = '{dynamicSettingName}'");

I haven't written that code myself, so I'm not sure how it works exactly, but no doubt you can google the details.

Can we use logical table name in the query or we need to use physical name of the table ? also will this depend on the environment like personal or production ?