Step 5: Create Custom Role Entity Data Source Provider

The custom role entity provider fetches the role information from the same table "SqlUsers". The column Role in this table contains role information for a user.

The below configuration is mandatory for the Role Provider.

1. The EntityIdentifierType method should always return string as the user entity provider value type is always a string (Ex: "<Provider Instance Name>::<User Unique Identifier>").

2. The instance for User entity data source provider & Role entity data source provider should be same. The role provider does not exist by itself and is always mapped to a user provider. The users returned by the role needs to be mapped to a user provider to be used within AVEVA Work Tasks. Due to this behavior it's mandatory to have the providers having same instance name. For this sample the instance name used is "sqlproviderCustom".

3. The identifier type for Role provider is always string as the Role name itself is considered as unique identifier.

PROCEDURE

DLL REFERENCE

Workflow.NET.NET2 .dll           

 Skelta.Repository.Web.dll

System.Transactions.dll

NAMESPACE USED

System

System.Collections.Generic

System.Text

System.Xml

System.Xml.XPath

System.Transactions

System.Data

Workflow.NET

Workflow.NET.Interfaces

Skelta.Entity

System.Collections.Specialized

Workflow.NET.Storage

Skelta.Entity.RoleEntityProvider

System.IO

API Configuration

The Role Entity Data Source Provider should:

Inherit Skelta.Entity.DataSourceProviderBase class

Or

Implement Skelta.Entity.IEntityDataSourceProvider, Skelta.Core.ISkeltaAddInProvider

Implement interface Skelta.Entity.RoleEntityProvider.IRoleEntityDataSourceProvider

The only method which is accessed from IEntityDataSourceProvider for Role Provider is GetEntityItem (object entityId). Since there is no specific UI for role provider the other interface methods will be used for future release.

Skelta.Entity.RoleEntityProvider.IRoleEntityDataSourceProvider: The methods in this interface provide information related to Roles & Users related to each role.

Sample code

/// <summary>

/// Role entityt data source  provider.

/// </summary>

class RoleEntityDataSourceProvider : DataSourceProviderBase,

Skelta.Entity.RoleEntityProvider.IRoleEntityDataSourceProvider

{

  bool _IsConnectionValid = false;

  string _ConnectionString;

  string _ApplicationName;

  private Log logger = new Log();

#region IRoleEntityDataSourceProvider Members

  /// <summary>

  /// Repository  name.

  /// </summary>

  public string ApplicationName

{

  get

{

  return this._ApplicationName;

}

  set

{

  this._ApplicationName = value;

}

}

  /// <summary>

  /// Connection string for role provider

  /// </summary>

  public string ConnectionString

{

  get

{

  return this._ConnectionString;

}

  set

{

  this._ConnectionString = value;

}

}

  /// <summary>

  /// The identifier type value  should always be string. It's always string as the type for user entity provider will always be string.

  /// </summary>

  public string EntityIdentifierType

{

  get

{

  return "string";

}

}

  /// <summary>

  /// Return all the roles for the  provider, Get called from Role look up provider (Queue, Security)

  /// </summary>

  /// <returns>Collection of roles as string array</returns>

  public string[] GetAllRoles()

{

  // When search is performed  on role lookup , the context  information  of search can be got using ther object

Skelta.Repository.Web.Lookup.RoleExecutionContextInfo searchContextInfo

= Skelta.Repository.Web.Lookup.RoleExecutionContext.GetRoleExecutionContext();

  string[] StrRolesArray = { };

StringCollection StrRoleCollection = new StringCollection();

System.Data.IDataReader idrRoles;

  try

{

  if (_IsConnectionValid)

{

  // Modify the query as per your table details

  string StrSQl = "";

  if (searchContextInfo == null)

StrSQl = "SELECT DISTINCT Role FROM  SqlUsers WHERE Role is not NULL ORDER BY Role";

  else

StrSQl = "SELECT DISTINCT Role FROM  SqlUsers WHERE Role is not NULL AND Role like '%" + searchContextInfo.RoleSearchString + "%' ORDER BY Role";

IDataHandler dbHandler = DataHandlerFactory.GetDataHandler(this.ConnectionString, "sql server");

  using (dbHandler)

{

idrRoles = dbHandler.ExecuteReader(StrSQl);

  while (idrRoles.Read())

{

StrRoleCollection.Add(idrRoles.GetString(0));

}

idrRoles.Close();

StrRolesArray = new string[StrRoleCollection.Count];

StrRoleCollection.CopyTo(StrRolesArray, 0);

}

}

}

  catch (Exception ex)

{

logger.LogError(ex, "@@Error while getting the SkeltaRoleProvider GetAllRoles");

  throw;

}

  return StrRolesArray;

}

  /// <summary>

  /// Get the roles associated to a user. Called from Queue or security for getting the users associated to a role  after role selection

  /// </summary>

  /// <param name="resource">resource </param>

  /// <returns>Arrays of Roles</returns>

  public string[] GetRolesForUser(Resource resource)

{

StringCollection StrRoleForUserCollection = new StringCollection();

System.Data.IDataReader idrRolesForUser;

  string[] StrRolesForUserArray = { };

  string StrSql = "";

StrSql = "  SELECT Role";

StrSql += " FROM SqlUsers ";

StrSql += " WHERE  UserId =@UserId";

  try

{

  if (_IsConnectionValid)

{

IDataHandler dbHandler = DataHandlerFactory.GetDataHandler(this.ConnectionString, "sql server");

  using (dbHandler)

{

System.Data.IDataParameter iparmUserId = dbHandler.GetParameter("@UserId", resource.Properties.Identifier.Value);

idrRolesForUser = dbHandler.ExecuteReader(StrSql, iparmUserId);

  while (idrRolesForUser.Read())

{

StrRoleForUserCollection.Add(idrRolesForUser.GetString(0));

}

idrRolesForUser.Close();

}

StrRolesForUserArray = new string[StrRoleForUserCollection.Count];

StrRoleForUserCollection.CopyTo(StrRolesForUserArray, 0);

}

}

  catch (Exception ex)

{

logger.LogError(ex, "@@SkeltaRoleProvider@GetRolesForUser");

  throw;

}

  return StrRolesForUserArray;

}

  /// <summary>

  /// Collection  of  unique users id's which  belongs to the Role. The Keys for the colleciton hold the unique identifier of the user & the value  could be name or any  custom value.

  /// </summary>

  /// <param name="roleName"></param>

  /// <returns></returns>

  public Dictionary<object, string> GetUsersInRole(string roleName)

{

  string StrSql = string.Empty;

Dictionary<object, string> StrUsersInRoleArray = new Dictionary<object, string>();

System.Data.IDataReader idrUsersInRole;

  try

{

  if (_IsConnectionValid)

{

StrSql = "  SELECT UserId , Name";

StrSql += " FROM SqlUsers ";

StrSql += " WHERE Role=@Role";

IDataHandler dbHandler = DataHandlerFactory.GetDataHandler(this.ConnectionString, "sql server");

  using (dbHandler)

{

System.Data.IDataParameter iparmUserId = dbHandler.GetParameter("@Role", roleName);

idrUsersInRole = dbHandler.ExecuteReader(StrSql, iparmUserId);

  while (idrUsersInRole.Read())

{

StrUsersInRoleArray.Add(idrUsersInRole.GetGuid(0), idrUsersInRole.GetString(1));

}

idrUsersInRole.Close();

}

}

}

  catch (Exception ex)

{

logger.LogError(ex, "@@SkeltaRoleProvider@GetUsersInRole");

  throw;

}

  return StrUsersInRoleArray;

}

  /// <summary>

  /// Checks if the user belongs to a specific role.

  /// </summary>

  /// <param name="resource">User object.</param>

  /// <param name="roleName">Role name.</param>

  /// <returns></returns>

  public bool IsUserInRole(Resource resource, string roleName)

{

  string StrSql = string.Empty;

  bool IsUserInRole = false;

System.Data.IDataReader idrUserInRole;

  try

{

  if (_IsConnectionValid)

{

StrSql = " SELECT * FROM SqlUsers";

StrSql += " WHERE  UserId = @UserId AND Role = @Role";

IDataHandler dbHandler = DataHandlerFactory.GetDataHandler(this.ConnectionString, "sql server");

  using (dbHandler)

{

System.Data.IDataParameter iparmRolesAppid = dbHandler.GetParameter("@UserId", resource.Properties.Identifier.Value);

System.Data.IDataParameter iparmUserId = dbHandler.GetParameter("@Role", roleName);

idrUserInRole = dbHandler.ExecuteReader(StrSql, iparmRolesAppid, iparmUserId);

  while (idrUserInRole.Read())

{

IsUserInRole = true;

  break;

}

idrUserInRole.Close();

}

}

}

  catch (Exception ex)

{

logger.LogError(ex, "@@SkeltaRoleProvider@IsUserInRole");

IsUserInRole = true;

}

  return IsUserInRole;

}

  void IRoleEntityDataSourceProvider.Initialize(string definition)

{

  if (!_IsConnectionValid)

Initialize(definition);

}

  /// <summary>

  /// Checks if a role exists in the provider.

  /// </summary>

  /// <param name="roleName">Role name.</param>

  /// <returns></returns>

  public bool RoleExists(string roleName)

{

  bool CheckRoleStatus = false;

  try

{

  if (_IsConnectionValid)

{

  string StrRoleName = roleName;

  string StrSQl = "SELECT distinct role FROM SqlUsers WHERE Role =@Role";

IDataHandler dbHandler = DataHandlerFactory.GetDataHandler(this.ConnectionString, "sql server");

System.Data.IDataReader idrRoles;

  if (!string.IsNullOrEmpty(StrRoleName))

{

  using (dbHandler)

{

System.Data.IDataParameter iparmRole = dbHandler.GetParameter("@Role", roleName);

idrRoles = dbHandler.ExecuteReader(StrSQl, iparmRole);

  while (idrRoles.Read())

{

CheckRoleStatus = true;

}

idrRoles.Close();

}

}

}

}

  catch

{

CheckRoleStatus = true;

}

  return CheckRoleStatus;

}

#endregion

#region "DataSourceProviderBase"

  /// <summary>

  /// Loads the configuration info

  /// </summary>

  /// <param name="definitionXml"></param>

  public override void Initialize(string definitionXml)

{

  try

{

XmlDocument xDoc;

xDoc = new XmlDocument();

  try

{

xDoc.Load(new StringReader(definitionXml));

}

  catch (System.Exception ex)

{

logger.LogError(ex, "Could not open the document ()");

  throw ex;

}

  //Get connection string

  try

{

XmlNode xnc = xDoc.GetElementsByTagName("datasource").Item(0);

_ConnectionString = xnc.InnerText;

_IsConnectionValid = true;

}

  catch (System.Exception ex)

{

logger.LogError(ex, "Cannot get required information.");

  throw ex;

}

}

  catch (Exception ex)

{

logger.LogError(ex, "@@SkeltaMembershipDataProvider@Initialize");

  throw ex;

}

}

  /// <summary>

  /// GetEntityItem is the only interface method of IEntityDataSourceProvider which will be used. This method call is initiated  after Role is selected from UI and the virtual id for the role gets created.

  /// This method does not get called while listing different roles for the provider.

  /// </summary>

  /// <param name="entityId">Id of the EntityItem</param>

  /// <returns></returns>

  public override EntityItem GetEntityItem(object entityId)

{

  string Identifier = EntityItemProperties.Identifier.PropertyName;

  //It's role provider, so return single property value

EntityItemPropertiesCollection tempResProperties = EntityItemProperties.CreateDeepClone();

EntityItem entityItem = new EntityItem();

  //We need to consider role itself as unique identifier.

tempResProperties[Identifier].Values.Add(entityId);

  if (!string.IsNullOrEmpty(ProviderPrefix))

entityId = entityId.ToString().Replace(ProviderPrefix, "");

tempResProperties["RoleName"].Values.Add(entityId);

entityItem.Properties = tempResProperties;

  return entityItem;

  //It's mandatory for the role provider to perisist the RoleName in db, if  a property named RoleName is not

  //specfified or coded to be perissted in DB then the below code can be used to add or set the same

  //if (!tempResProperties.ContainsKey("RoleName"))

  //{

  //  EntityItemProperty roleProperty = new EntityItemProperty();

  //  roleProperty.PropertyName = "RoleName";

  //  roleProperty.PropertyDisplayName = "Role";

  //  roleProperty.PersistColumn = "RoleName";

  //  roleProperty.Persist = true;

  //  tempResProperties.Add(roleProperty.PropertyName, roleProperty);

  //}

  //else

  //{

  //  tempResProperties["RoleName"].PersistColumn = "RoleName";

  //  tempResProperties["RoleName"].Persist = true;

  //}

}

  public override IEntityDataSourceProvider Clone()

{

  //Create a shallow clone of the instance

RoleEntityDataSourceProvider roleProvider = new RoleEntityDataSourceProvider();

roleProvider = this;

  return roleProvider;

}

#endregion

}

• Configure Custom Role Entity Data Source Provider

Configuring custom role entity data source provider involves entries in two tables of the Repository

• Table : SKAddInProviders , Object  : Skelta.Core.AddInProvider

• Table : SKEntityDataSourceProvider, Object : Skelta.Entity.DataSourceProvider

I. Adding Assembly information in  SKAddInProvider table

API for Adding Role provider in SKAddInProvider table: Create an entry in SKAddInProviders for the role provider.

ApplicationObject applicationObject = new ApplicationObject("Sample Repository");

 Skelta.Core.AddInProvider addInProvider = new Skelta.Core.AddInProvider(applicationObject);

 addInProvider.Type = "Role";

 addInProvider.Name = "customsqlroleprovider";

#warning : Make sure to change the assembly  path

 addInProvider.Description = "Sample Custom Role Entity Data Source Provider";

 addInProvider.ClassName = "CustomUserEntityDataSourceProvider.RoleEntityDataSourceProvider";

 addInProvider.AssemblyNameWithAbsoluteOrRelativePath = @"M:\Michelle\CustomUserEntityDataSourceProvider\CustomUserEntityDataSourceProvider\bin\Debug\CustomUserEntityDataSourceProvider.dll";

 addInProvider.IsGacAssembly = false;

 addInProvider.IsGlobal = true;

 addInProvider.IsDefault = true; // The properties IsGacAssembly, IsGlobal, IsDefault  is not currently in use. The value used in sample should be used "as is"

 addInProvider.Save();

Database script for adding Role provider in SKAddInProviders table

INSERT INTO SKAddInProviders VALUES (newid(),'Role','customsqlroleprovider','Sample Custom Role Entity Data Source Provider','CustomUserEntityDataSourceProvider.RoleEntityDataSourceProvider','M:\Michelle\CustomUserEntityDataSourceProvider\CustomUserEntityDataSourceProvider\bin\Debug\CustomUserEntityDataSourceProvider.dll',null,0,1,1,getutcdate())

Note: Assembly  path should be changed.

Verification:

The above code creates an entry in SKAddInProviders table with type as Role. The entry in Repository DB can be verified in database by running the query select * from SKAddInProviders Where Name='customsqlroleprovider '

II. Creating an Entry in SKEntityDataSourceProvider table

API for updating the List Role Provider with Custom Role Provider.

Update the role entry created for the list with custom role provider in SKEntityDataSourceProvider table.

Skelta.Entity.Entity entity = Skelta.Entity.Entity.GetEntity(applicationObject, "RoleEntity");

Skelta.Entity.DataSourceProvider roleEntitySourceProvider = null;

roleEntitySourceProvider = new Skelta.Entity.DataSourceProvider(applicationObject, "skeltalist");

 //Role entity definition

 string  roleProviderDefinitionXML = "";

 roleProviderDefinitionXML += "<dataproviders>";

 roleProviderDefinitionXML += "<provider name=\"customsqlprovider\" type=\"Custom Role Provider\">";

 roleProviderDefinitionXML += "<properties>";

 roleProviderDefinitionXML += "<property name=\"Role\" type=\"string\" purpose=\"identifier\"/>";

 roleProviderDefinitionXML += "<property name=\"RoleName\" type=\"string\" persist=\"true\" persistcolumn=\"rolename\"/>";

 roleProviderDefinitionXML += "</properties>";

roleProviderDefinitionXML += "<datasource>server=localhost;database=SkeltaUsers;User Id=sa;pwd=XXXX;</datasource>";

roleProviderDefinitionXML += "</provider>";

 roleProviderDefinitionXML += "</dataproviders>";

roleEntitySourceProvider.DefinitionXml = roleProviderDefinitionXML;

//Change the role provider name.

roleEntitySourceProvider.Provider = "customsqlroleprovider";

roleEntitySourceProvider.InstanceName = "sqlproviderCustom"; // Instance Name should be same as the instance name set for the user provider.

roleEntitySourceProvider.DisplayName = "Custom Role Provider";

// adding an entry in SKEntityDataSourceProvider fro Role provider

roleEntitySourceProvider.Entity = entity;

roleEntitySourceProvider.Save();

Database script for updating the Skelta list Role Provider with Custom Role Provider.

Make sure to update the connection string information in the definition XML with appropriate value. The provider column value should be same as the name column value inserted in SKAddInProviders table.

Note: Also, ensure that the query given below updates the row for the List Role Entity Data Source Provider and not the User Entity Data Source Provider.

//For Windows Authentication

Update SKEntityDataSourceProvider

Set

DefinitionXML =' <dataproviders><provider name="customsqlprovider" type="Custom Role Provider"><properties><property name="Role" type="string" purpose="identifier"/><property name="RoleName" type="string" persist="true" persistcolumn="rolename"/></properties>

<datasource>server=localhost; database=SkeltaUsers;Integrated Security=true;</datasource></provider></dataproviders>',

DisplayName='Custom Role Provider',Provider='customsqlroleprovider', InstanceName='sqlproviderCustom' Where InstanceName='skeltalist' and EntityId=(Select Id from SKEntity where Name='RoleEntity')

//SQL SERVER Authentication

Update SKEntityDataSourceProvider

Set

DefinitionXML =' <dataproviders><provider name="customsqlprovider" type="Custom Role Provider"><properties><property name="Role" type="string" purpose="identifier"/><property name="RoleName" type="string" persist="true" persistcolumn="rolename"/></properties>

<datasource>server=localhost; database=SkeltaUsers;User  Id=sa;pwd=XXXX;</datasource></provider></dataproviders>',

DisplayName='Custom Role Provider',Provider='customsqlroleprovider',

InstanceName='sqlproviderCustom' Where InstanceName='skeltalist' and EntityId=(Select Id from SKEntity where Name='RoleEntity')

Note: Connection string should be changed.

The provider name value customsqlroleprovider should be same as the AddInProvider Name column value.

Verification:

The above code creates an entry in SKEntityDataSourceProvider table. The entry in Repository DB can be verified in database by running the query select * from SKEntityDataSourceProvider Where DisplayName='Custom Role Provider

Verification:

Now we should be able to access role & respective user information from the role provider. Sample code to check role information is listed below.

AddInProviderCollection addInProviderCollection = new AddInProviderCollection(applicationObject);

Dictionary<string, ISkeltaAddInProvider> addinProviders = addInProviderCollection.GetProvidersForType(true, "Role");

ISkeltaRoleProvider iSkeltaRoleProvider;

//Addin provider added with  name as RoleProvider is the entity  provider and should be  used to access the role entity data source provider.

 iSkeltaRoleProvider = (ISkeltaRoleProvider)addinProviders["RoleProvider"];

 iSkeltaRoleProvider.ApplicationName = "Sample Repository";

 string[] rolesCollection = iSkeltaRoleProvider.GetAllRoles();

 Dictionary<object,string> usersInRole = iSkeltaRoleProvider.GetUsersInRole(rolesCollection[0]);

The above code displays all the roles configured.

Code below creates an entry in SkVirtualRole table.

//For each role, a virtual role id gets created which is used internally for reference  with in AVEVA Work Tasks.

Skelta.Core.VirtualRole virtualRole = new VirtualRole(applicationObject);

 Guid virtualRoleId = virtualRole.GetVirtualRoleId(rolesCollection[0]);

The script below can be used to check the entries in respective tables.

select * from SKAddInProviders Where Name='customsqlprovider'

select * from SKEntityDataSourceProvider Where DisplayName='Custom Role Provider'

select * from SKVirtualRole where ResourceHandler='Sample Repository'