Question

Using a customquery in Script Task that has Joins

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;

 

Like 0

Like

4 comments

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

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.

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

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