Using C# to create SQL Select using comma-separated search terms

Using C# to create SQL Select using comma-separated search terms

  

I am trying to create a C# extension action that will allow the user to search a table using sets of strings as parameters. I have multiple inputs, one for each column to be searched on. 

In previous pages that only required one field to accept a list, I was able to use a ForEach in Service Studio and make individual calls to the database. However, this would not be preferable in this case, due the number of fields that will accept these comma-separated lists and the number of database calls that this would result in. Instead, I need to condense this into a single SQL query that will make a single call to our (external) database.

I found this solution for the purposes of dynamically creating parameters to a SQL query with IN clauses, but I am having trouble using it in the context of a RequestTransaction.


As an example:
To search on an office directory, the user needs to be able to search using a last name, multiple floors, and multiple room numbers (each field must also be allowed to be left blank).

For the following method signature:

  • GetEmployees(string lastName, string floors, string rooms)

...the user should be able to submit the following data:

  • ssLastName = "Johnson"
  • ssFloors = "2, 4, 5, 6"
  • ssRooms = "Conference Room, Corner Office, Front Desk"

Here is what I have for this so far:

public void GetEmployees(string lastName, string floors, string rooms, out EmpDirRL EmpDir) {
     List<string> floorList = floors.Split(',').ToList();
     List<string> roomList = rooms.Split(',').ToList();

     string sqlQuery = "select * from employeeDirectory";
     //ToDo: edit sqlQuery to include Where clause
     //ToDo: use method to include

     DatabaseProvider dbaProvider = DatabaseAccess.ForExternalDatabase("myDatabaseConnection");
     using (RequestTransaction trans = dbaProvider.GetRequestTransaction())
     {
          using (Command cmd = trans.CreateCommand(sqlQuery))
          {
               cmd.GetDriverCommand().CommandType = CommandType.Text;
               IDataReader reader = cmd.ExecuteReader();

               while (reader.Read())
               {
                    //EmpDir.Add(new EmpDirEntityRecord()
                    {

                         id = reader.GetInt32(reader.GetOrdinal("Id")),
                         firstName = reader.GetString(reader.GetOrdinal("FirstName")),
                         lastName = reader.GetString(reader.GetOrdinal("LastName")),
                         floor = reader.GetInt32(reader.GetOrdinal("Floor")),
                         room = reader.GetString(reader.GetOrdinal("Room"))
                    });
               }
          }
     }
}


I'm not sure how to proceed at this point. I keep getting errors like "Specified cast is not valid" or "Object reference not set to an instance of an object" and need to resolve these errors first.

Try to identify the error line.

You can use the platform log to try to find exactly where the error is.

See this post: https://www.outsystems.com/forums/discussion/6399/is-possible-to-debug-a-extension/

Hi,


why do you need to do this in an extension?


J. wrote:

Hi,


why do you need to do this in an extension?


J, 

The SQL needs to be constructed programatically so that the query can compare the Room column to each of the strings "Conference Room", "Corner Office", and "Front Desk". I was unable to accomplish this in Service Studio because I am attempting to create parameters on the fly for passing each string to the SQL statement.

Hi Cody,

The reason J is asking is because you can actually do this by using the SQL tool with "Expand Inline" Input Parameters.

By default the platform will sanitise all input parameters of the SQL tool, guaranteeing that no code injection is possible. By setting a SQL's Input Parameter Expand Inline property to Yes, you're signalling the platform that it shouldn't escape any SQL code it finds inside that input parameter, and just place it as is where you are using it in your SQL statement. This is the standard way of creating dynamic SQL statements from within OutSystems.

This requires the developer to be extra careful not to allow code injection from data provided by the user that is used in the dynamic expand inline parameters. The platform helps you there as well, making available an EncodeSQL() function you can use to do just that.

(notice how you still need to use the {Entity}.[Attribute] syntax in your expand inline parameters)

Hi Jorge,

Due to the way information is saved to this table, I also need to compare these strings to the substring of a column. For example: 

WHERE substring(name,13,10) like '%' + @LastName + '%' 

However, I am receiving the following error when I try to run this in my SQL statement:
ORA-00904: "SUBSTRING": invalid identifier

I will also need to do this for substrings that are being compared to multiple inputs like in my original post.

I apologize for what appears to be multiple subjects in a single thread, but it is the combination of these problems that is making this so difficult to implement.

Hello Cody,

The error is because your database is ORACLE and the name of this function in Oracle is SUBSTR, not SUBSTRING.

Cheers.

Hi Cody,

This all seems perfectly doable in an Advanced Query as some people already mentioned.

"I will also need to do this for substrings that are being compared to multiple inputs like in my original post."

You can achieve this by using the IN-clause with an expanded Where variable as Jorge mentioned. I don't have much experience with Oracle, but for MS-SQL it would be something like:

WHERE substring(name,13,10) IN (@LastNames)

LastNames can then hold multiple values. Please note that you need to put quotes around each search term (eg. 'Fortenberry','Martins','Jauch') 

Good luck!