Question

Using a customquery in Script Task that has Joins

16:07 Feb 28, 2020

I am trying to select a specific org role from a user using a Custom Query however most examples don't have Joins in them. Can someone assist?

var sql = "select sau.Id as [RoleId] from SysUserInRole suir join SysAdminUnit sau on suir.SysRoleId = sau.id and sau.SysAdminUnitTypeValue = 6 join SysAdminUnit sau2 on suir.SysUserId = sau2.id where sau2.Id = '594c5b24-147d-421f-8083-2431f71e920d'";
 
var query = new CustomQuery(UserConnection, sql);
using (var db = UserConnection.EnsureDBConnection())
{
    using (var reader = query.ExecuteReader(db))
    {
        while (reader.Read())
        {
            var Id = reader.GetString(0);
            Set("DebugRoleID", RoleId);
        }
    }
}
 
return true;

4 comments

Ryan Farley

16:38 Feb 28, 2020

Can you explain a bit more of what you're after and what isn't working with the code above?

Michael Dorfman

16:41 Feb 28, 2020

Ryan Farley,

The query returns the GUID of the Org role of a specific person. The script keeps telling me it can't cast a GUID as a String and I cannot figure out where it is casting a GUID as a string.

Michael Dorfman

18:13 Feb 28, 2020

I resolved the issue by changing .GetString() to .GetValue. GetValue allows GUID datatypes opposed to just getting a string value.

Ryan Farley

19:15 Feb 28, 2020

Michael Dorfman,

Sounds like you have it working. You could also cast it to a Guid like this as well:

Set("DebugRoleID", new Guid(RoleId));

Or even better without the cast

Set("DebugRoleID", reader.GetGuid(0));

Using GetValue works as well, that returns the value as a generic object which gets implicitly coerced to Guid when you set it in the DebugRoleID parameter.

Ryan

Show all comments