Step 3: Create Custom User Entity Data Source Provider for Authentication and User Information

Using this API a custom User Entity Data Source provider can be created.

1. Create a table named SqlUsers

   The custom entity provider fetches the user information from a table called SqlUsers.

   Script to create this table is provided below.

   GO

   /****** Object:  Table [dbo].[SqlUsers]  ******/

   SET ANSI_NULLS ON

   GO

   SET QUOTED_IDENTIFIER ON

   GO

   CREATE TABLE [dbo].[SqlUsers](

   [UserId] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_SqlUsers_Id]  DEFAULT (newid()),

    [Name] [nvarchar](100) NULL,

    [Role] [nvarchar](200) NULL,

    [Email] [nvarchar](75) NULL,

    [Password] [nchar](10) NULL,

    CONSTRAINT [PK_SqlUsers_1] PRIMARY KEY CLUSTERED

   (

   [UserId] ASC

   ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

   ) ON [PRIMARY]

2. Insert default users to SqlUsers Table

   Script to insert default users to SqlUsers Table:

   INSERT INTO [dbo].[SqlUsers]

    ([Name]

    ,[Role]

    ,[Email]

    ,[Password])

    VALUES

    ('Michelle','Admin','michelle@skelta.com','michelle'),

   ('kevin','Developer','kevin@skelta.com','kevin'),

    ('john','Developer','john@skelta.com','john'),

    ('Elaine','Product Manager','elaine@skelta.com','elaine')  

   GO
   

3. Create a dll named CustomUserEntityDataSourceProvider

   PROCEDURE

   DLL REFERENCE

   Workflow.NET.NET2

   System.Transactions.dll

   System.Web.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

   API INFORMATION

   The User Entity Data Source Provider should

   Inherit Skelta.Entity.DataSourceProviderBase class

   or

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

   Implement interface Skelta.Entity.UserEntityProvider.IUserEntityDataSourceProvider

   Implement interface Skelta.Entity.IEntityAdminDataSourceProvide.

   Skelta.Entity.DataSourceProviderBase: Base class used by the default providers, this class implements the interfaces Skelta.Entity.IEntityDataSourceProvider, Skelta.Core.ISkeltaAddInProvider. All the methods in this class are declared as virtual. The implementers should ideally implement the interfaces implemented by the base class directly. The base class is used in the sample trying to make it easier for the implementer with some of the methods already implemented with default functionality. The method which needs to be mandatorily overridden throws exception if relied on the base class. This class exposes methods for initializing the provider, fetching user information & querying users based on filters.

   Skelta.Entity.UserEntityProvider.IUserEntityDataSourceProvider: Gets called during EnterpriseConsole authentication from the Log on page. This interface provides methods which gives username and password entered in the login page of EnterpriseConsole. All the user authentication code must be implemented in these methods.

   Skelta.Entity.IEntityAdminDataSourceProvider: Required for setting the default administrator for a Repository. This administrator will have complete rights on the EC site. This administrator will be responsible for setting security and adding users to EC.

   Note: Since AVEVA Work Tasks executes activities in Transaction scope, there is a possibility for the Data Source Provider methods getting called with in a transaction. For the sample provided, there are no specific transaction scope used. The isolation level used for Transactions in AVEVA Work Tasks is read committed, so, if any other isolation level is used in the providers it could results in exceptions in Runtime. The Best practices are:

   Do not use a separate transaction for querying user information.

   Suppress the transaction if the user information is taken from an external object or source. Use the Transaction Scope option available in common functions for this.

   using (System.Transactions.TransactionScope tScope = Workflow.NET.CommonFunctions.GetSuppressedTransactionScope())
   {
   //Call to external object to get user information tScope.Complete();
   }

   SAMPLE CODE:

   namespace CustomUserEntityDataSourceProvider

   {

   /// <summary>

   /// Custom entity data source  provider which queries sql database table/view returning user information.

   /// </summary>

   public class UserEntityDataSourceProvider : DataSourceProviderBase, Skelta.Entity.UserEntityProvider.IUserEntityDataSourceProvider,

   Skelta.Entity.IEntityAdminDataSourceProvider

   {

   private string sConnectionString;

   private string sDataSourceType;

   private string sSourceTable;

   private string sCols = "";

   private Log log = null;

   private string loginProperty = "";

   
   

   public UserEntityDataSourceProvider() { }

   
   

   /// <summary>

   /// Gets called  while provider is initialized.

   /// </summary>

   /// <param name="settings">Definition XML configured for SKEntityDataSourceProvider table.</param>

   public override void Initialize(string settings)

   {

   log = new Log();

   log.FileName = "CustomUserEntityDataSourceProvider.cs";

   XmlDocument doc = new XmlDocument();

   try

   {

   doc.LoadXml(settings);

   // Get the Connection String from the definition xml file.

   XmlNode DsConnNode = doc.GetElementsByTagName("datasource").Item(0);

   sConnectionString = DsConnNode.InnerText;

   sDataSourceType = "sql server";

   sSourceTable = doc.GetElementsByTagName("SourceTable").Item(0).InnerText;

   }

   catch (Exception exp)

   {

   log.LogError(exp, "Could not read the xml data while initializing the provider");

   throw;

   }

   try

   {

   if (doc.GetElementsByTagName("loginproperty") != null)

   {

   XmlNode qxn = doc.GetElementsByTagName("loginproperty").Item(0);

   loginProperty = qxn.InnerText;

   }

   }

   catch (Exception ex)

   {

   log.LogError(ex, "Could not get loginproperty column set.");

   throw;

   }

   }

   
   

   /// <summary>

   /// Called when delayed initialization on a resource property is required.

   /// For example if there are 2 providers and the initial property collection for the resource

   /// has a property which is empty and if this property is requested a delayed initialization is

   /// triggered on the  provider based on the mapping  fields configured.

   /// </summary>

   /// <param name="filter">Filter which is internally built based on the mapping fields configured.</param>

   /// <param name="dataparams">Parameters used in the filter.</param>

   /// <returns>Entity item with the delayed initialization property  value.</returns>

   public override EntityItem GetEntityItem(string filter, object[] dataParams)

   {

   if(string.IsNullOrEmpty(sCols))

   BuildPropertyColumn();

   List<IDataParameter> dParam = new List<IDataParameter>();

   string sql = string.Empty;

   using (IDataHandler dbHandler = Workflow.NET.Storage.DataHandlerFactory.GetDataHandler(sConnectionString, sDataSourceType))

   {

   sql = "SELECT " + sCols + " FROM " + sSourceTable + " WHERE " + filter;

   try

   {

   foreach (object[] values in dataParams)

   {

   dParam.Add(dbHandler.GetParameter(values[0].ToString(), values[1]));

   }

   return GetEntityItem(sql, dParam.ToArray());

   }

   catch (Exception exp)

   {

   log.LogError(exp, "Could not fetch entity item with filter (" + filter + ").");

   throw;

   }

   }

   }

   
   

   /// <summary>

   /// Called when actor object is created or any resource details are requested.

   /// </summary>

   /// <param name="entityItemId">Guid of the resource based on the current sample.</param>

   /// <returns>User Entity Details.</returns>

   public override EntityItem GetEntityItem(object entityItemId)

   {

   if (string.IsNullOrEmpty(sCols))

   BuildPropertyColumn();

   string sql = string.Empty;

   IDataParameter dParam = null;

   using (IDataHandler dbHandler = Workflow.NET.Storage.DataHandlerFactory.GetDataHandler(sConnectionString, sDataSourceType))

   {

   try

   {

   sql = "SELECT " + sCols + " FROM " + sSourceTable + " WHERE " + EntityItemProperties.Identifier.PropertyName + " = @resourceId";

   dParam = dbHandler.GetParameter("@resourceId", entityItemId);

   return GetEntityItem(sql, dParam);

   }

   catch (Exception exp)

   {

   log.LogError(exp, "Could not fetch entity item for entity item id (" + entityItemId + ").");

   throw;

   }

   }

    

   }

   EntityItem GetEntityItem(string filter, params IDataParameter[] dataparams)

   {

   IDataReader idr = null;

   EntityItem res;

   Workflow.NET.Interfaces.IDataHandler ConnectionWrapper = null;

   using (ConnectionWrapper = Workflow.NET.Storage.DataHandlerFactory.GetDataHandler(sConnectionString, sDataSourceType))

   {

   idr = ConnectionWrapper.ExecuteReader(filter, dataparams);

   //Create a empty EntityItem object

   res = new EntityItem();

   res.Properties = EntityItemProperties.CreateDeepClone();

   string fieldname;

   EntityItemProperty rp;

   int loopcount = 0;

   
   

   while (idr.Read())

   {

   //For every record, update the properties values

   for (int i = 0; i < idr.FieldCount; i++)

   {

   fieldname = idr.GetName(i);

   if (res.Properties.ContainsKey(fieldname))

   {

   rp = ((EntityItemProperty)res.Properties[fieldname]);

   if (!rp.Values.Contains(idr.GetValue(i)) && idr.GetValue(i) != System.DBNull.Value && idr.GetValue(i).ToString() != "") //Add all unique values of a given field in Values collection

   rp.Values.Add(idr.GetValue(i));

   }

   else

   {  //if( loopcount==0 )

   log.LogWarning("Field " + fieldname + " not found in EntityItem Properties");

   }

   }

   loopcount++;

   }

   idr.Close();

   idr.Dispose();

   }

   return res;

   }

   
   

   /// <summary>

   /// Called  when  an filter is executed in  Runtime, for example when Human activity is executed.

   /// This method  also gets called when user look search is performed

   /// </summary>

   /// <param name="filter">Filter for querying the resource.</param>

   /// <param name="query"></param>

   /// <param name="FullyProcessedQuery"></param>

   /// <param name="Params"></param>

   /// <returns></returns>

   public override object[] QueryEntityItem(string filter, string query, string FullyProcessedQuery, object[] Params)

   {

   // When QueryEntityItem is called from Runtime (Engine),

   //there is an option to get the executing context details using GetEntityExecutionContext  method.

    

   Skelta.Entity.EntityExecutionContextInfo entityExecutionContextInfo =

   Skelta.Entity.EntityExecutionContext.GetEntityExecutionContext();

   //if (entityExecutionContextInfo != null)

   //{

   //entityExecutionContextInfo.ApplicationName;

   //entityExecutionContextInfo.WorkflowName;

   //entityExecutionContextInfo.ExecutionId

   //}

   IDataReader idr = null;

   Workflow.NET.Interfaces.IDataHandler ConnectionWrapper = null;

   try

   {

   using (ConnectionWrapper = Workflow.NET.Storage.DataHandlerFactory.GetDataHandler(sConnectionString, sDataSourceType))

   {

   string sql = "SELECT DISTINCT " + EntityItemProperties.Identifier.PropertyName + " FROM " + sSourceTable;

   System.Collections.ArrayList EntityItems = new System.Collections.ArrayList();

   if (FullyProcessedQuery != null && FullyProcessedQuery.Trim().Length > 0)

   {

   sql += " WHERE " + FullyProcessedQuery;

   }

   else

   {

   log.LogWarning("EntityItem filter is emtpy.");

   return EntityItems.ToArray();

   }

   
   

   System.Collections.ArrayList alParams = new System.Collections.ArrayList();

   foreach (object Param in Params)

   {

   object[] obj = (object[])Param;

   alParams.Add(ConnectionWrapper.GetParameter((string)obj[0], obj[1]));

   }

   IDataParameter[] Parameters = (IDataParameter[])alParams.ToArray(typeof(IDataParameter));

   idr = ConnectionWrapper.ExecuteReader(sql, Parameters);

   
   

   while (idr.Read())

   {

   EntityItems.Add(idr.GetValue(0));

   }

   idr.Close();

   idr.Dispose();

   return EntityItems.ToArray();

   
   

   }

   }

   catch (Exception exp)

   {

   if (entityExecutionContextInfo != null)

   log.LogError(exp, "Could not fetch entity item while querying entity item,  filter(" + filter + ").", entityExecutionContextInfo.ApplicationName, entityExecutionContextInfo.WorkflowName, entityExecutionContextInfo.ExecutionId);

   else

   log.LogError(exp, "Could not fetch entity item while querying entity item,  filter(" + filter + ").");

   throw;

   }  

   }

   void BuildPropertyColumn()

   {

   if (string.IsNullOrEmpty(sCols))

   {

   foreach (EntityItemProperty prop in EntityItemProperties)

   {

   sCols += (sCols == "" ? "" : ",") + prop.PropertyName;

   }

   }

   }

   
   

   /// <summary>

   /// Creates a clone  of the user entity provider.

   /// </summary>

   /// <returns></returns>

   public override IEntityDataSourceProvider Clone()

   {

   UserEntityDataSourceProvider customProvider = new UserEntityDataSourceProvider();

   customProvider.sCols = this.sCols;

   customProvider.Name = this.Name;

   customProvider.sConnectionString = this.sConnectionString;

   customProvider.sDataSourceType = this.sDataSourceType;

   customProvider.sSourceTable = this.sSourceTable;

   EntityItemPropertiesCollection entityItems = this.EntityItemProperties.CreateDeepClone();

   foreach (EntityItemProperty eProp in entityItems)

   {

   customProvider.EntityItemProperties.Add(eProp.PropertyName, eProp);

   }

   foreach (string key in this.DataSourceMappingFields.Keys)

   {

   customProvider.DataSourceMappingFields.Add(key, this.DataSourceMappingFields[key]);

   }

   foreach (string key in this.DataSourceProperties.Keys)

   {

   customProvider.DataSourceProperties.Add(key, this.DataSourceProperties[key]);

   }

   foreach (string key in this.RuleParts.Keys)

   {

   customProvider.RuleParts.Add(key, this.RuleParts[key]);

   }

   customProvider.IsPrimaryDataSource = this.IsPrimaryDataSource;

   customProvider.DataSourceType = this.DataSourceType;

   customProvider.loginProperty = this.loginProperty;

   customProvider.Name = this.Name;

   customProvider.CurrentEntity = this.CurrentEntity;

   customProvider.DisplayName = this.DisplayName;

   customProvider.RepositoryName = this.RepositoryName;

   customProvider.IsAuthenticationSource = this.IsAuthenticationSource;

   customProvider.ProviderPrefix = this.ProviderPrefix;

   return customProvider;

   }

   /// <summary>

   /// Converts to provider specific identifier type;

   /// </summary>

   /// <param name="identifierId">real entity id converted as string.</param>

   /// <returns>Id which  is provider specific type converted.</returns>

   public override object ConvertToIdentifierType(string identifierId)

   {

   switch (EntityItemProperties.Identifier.PropertyType)

   {

   case "string": return identifierId;

   case "int": return int.Parse(identifierId);

   case "guid": return new Guid(identifierId);

   }

   return null;

   }

   
   

   /// <summary>

   /// Return the unique identifier for the user. The default return value is user name itself.

   /// The actual unique identifier for the user could be different from the property used for login. For  the sample though the unique identifier is guid we have provided email as the login property for ease of use.

   /// </summary>

   /// <param name="userName">Value entered in the login page against user name field.</param>

   /// <param name="password">Password entered in the login page against password field.</param>

   /// <returns>Unique identifier  value. Return value type is object as the identifier type could be guid, int or string.</returns>

   public override object GetUniqueIdentifier(string userName, string password)

   {

   if (string.IsNullOrEmpty(loginProperty))

   return userName;

   string passwordColumn = GetPasswordColumnName();

   Workflow.NET.Interfaces.IDataHandler ConnectionWrapper = null;

   string sql = "";

   IDataParameter param1 = null;

   IDataParameter param2 = null;

   IDataReader idr = null;

   try

   {

   ConnectionWrapper = Workflow.NET.Storage.DataHandlerFactory.GetDataHandler(sConnectionString, sDataSourceType);

   sql = "SELECT * FROM " + sSourceTable + " WHERE " + loginProperty + "=@resourceId AND " + passwordColumn + "=@password";

   param1 = ConnectionWrapper.GetParameter("@resourceId", userName);

   param2 = ConnectionWrapper.GetParameter("@password", password);

   idr = ConnectionWrapper.ExecuteReader(sql, param1, param2);

   if (idr.Read())

   return idr[EntityItemProperties.Identifier.PropertyName];

   else

   throw new System.Exception("Unable to get unique identifier from property (" + loginProperty + "), query(" + sql + ")");

   
   

   }

   catch (Exception e)

   {

   log.LogError(e, "Could not get unique identifier for user (" + userName + ")");

   throw;

   }

   finally

   {

   if (idr != null)

   idr.Close();

   
   

   if (ConnectionWrapper != null)

   ConnectionWrapper.Dispose();

   }

   }

   //Returns the column name from property collection with purpose set as "password"

   string GetPasswordColumnName()

   {

   foreach (EntityItemProperty eProp in EntityItemProperties)

   {

   if (!string.IsNullOrEmpty(eProp.Purpose) &&

   eProp.Purpose.ToLowerInvariant() == "password")

   return eProp.PropertyName;

   
   

   }

   
   

   return "";

   }

   
   

   #region IUserEntityDataSourceProvider Members

   
   

   /// <summary>

   /// Currently this method call is not used in any  of the  default providers.

   /// It was added to provide an option in future release to perform login  for a provider

   /// based on different authentication methods available in IIS instead of performing a validation

   /// based on user name & password

   /// </summary>

   /// <param name="httpContext"></param>

   /// <returns></returns>

   bool Skelta.Entity.UserEntityProvider.IUserEntityDataSourceProvider.ValidateIISUser(System.Web.HttpContext httpContext)

   {

   return false;

   }

   /// <summary>

   /// Called when submit button event of login page is fired from EnterpriseConsole.

   /// </summary>

   /// <param name="userName">Value entered in the login page against user name field.</param>

   /// <param name="password">Password entered in the login page against password field.</param>

   /// <returns>true if user credentials are valid else false.</returns>

   bool Skelta.Entity.UserEntityProvider.IUserEntityDataSourceProvider.ValidateUser(string username, string password)

   {

   string passwordColumn = GetPasswordColumnName();

   string identifierColumn = "";

   if (string.IsNullOrEmpty(loginProperty))

   identifierColumn = EntityItemProperties.Identifier.PropertyName;

   else

   identifierColumn = loginProperty;

   
   

   if (string.IsNullOrEmpty(passwordColumn))

   return false;

   else

   {

   Workflow.NET.Interfaces.IDataHandler ConnectionWrapper = null;

   string sql = "";

   IDataParameter param1 = null;

   IDataParameter param2 = null;

   IDataReader idr = null;

   using (ConnectionWrapper = Workflow.NET.Storage.DataHandlerFactory.GetDataHandler(sConnectionString, sDataSourceType))

   {

   try

   {

   sql = "Select * From " + sSourceTable + " WHERE " + identifierColumn + "=@resourceId AND " + passwordColumn + "=@password";

   param1 = ConnectionWrapper.GetParameter("@resourceId", username);

   param2 = ConnectionWrapper.GetParameter("@password", password);

   idr = ConnectionWrapper.ExecuteReader(sql, param1, param2);

   bool returnFlag = false;

   if (idr.Read())

   returnFlag = true;

   idr.Close();

   return returnFlag;

   }

   catch (Exception e)

   {

   log.LogError(e, "Could not validate user (" + username + ")");

   //Need not throw the exception as the UI will display error message if return value is false.

   }

   }

   return false;

   }

   }

   #endregion

   
   

   #region IEntityAdminDataSourceProvider Members

   /// <summary>

   /// Return admin users unique identfiier value.

   /// This administrator will have complete rights on the EC site.

   /// This administrator will be responsible for setting security and adding users to EC

   /// </summary>

   /// <returns>Unique identifier value of administartor</returns>

   public object[] GetAdministrator()

   {

   //For the  sample we'll use the column named Role with value set as Admin.

   Workflow.NET.Interfaces.IDataHandler ConnectionWrapper = null;

   System.Collections.ArrayList EntityItems = new System.Collections.ArrayList();

   try

   {

   ConnectionWrapper = Workflow.NET.Storage.DataHandlerFactory.GetDataHandler(sConnectionString, sDataSourceType);

   string stradmin = "Admin";

   string sql = "SELECT DISTINCT " + EntityItemProperties.Identifier.PropertyName + " FROM " + sSourceTable;

   sql += " WHERE Role = '" + stradmin + "'";

   System.Collections.ArrayList alParams = new System.Collections.ArrayList();

   IDataParameter[] Parameters = (IDataParameter[])alParams.ToArray(typeof(IDataParameter));

   IDataReader idr = ConnectionWrapper.ExecuteReader(sql, Parameters);

   while (idr.Read())

   {

   EntityItems.Add(Workflow.NET.CommonFunctions.GetGuid(idr, EntityItemProperties.Identifier.PropertyName));

   }

   idr.Close();

   idr.Dispose();

   }

   catch (Exception e)

   {

   log.LogError(e, "Could not get the administrator user information.");

   throw;

   }

   finally

   {

   if (ConnectionWrapper != null)

   ConnectionWrapper.Dispose();

   }

   return EntityItems.ToArray();

   }

   #endregion

   }

   }

Configure Custom User Entity Data Source Provider

Configuring custom user entity data source provider involves entries in three tables of the Repository.

Table : SKAddInProviders , Object  : Skelta.Core.AddInProvider

Table : SKEntity, Object : Skelta.Entity.Entity

Table : SKEntityDataSourceProvider, Object : Skelta.Entity.DataSourceProvider

 

Add the assembly information of the user entity custom provider created, in the SKAddInProviders table

Provider type must be "UserDSProvider" and name should be any unique name. The newly added provider name must be again updated in the SKEntityDataSourceProvider table.

You can add an entry to SKAddInProviders table by using API or Database script. We have given both methods below, use any one of them and verify it using the query given in the verification topic

Using API

Skelta.Core.AddInProvider addInProvider = new Skelta.Core.AddInProvider(

new ApplicationObject("Sample Repository"));

addInProvider.Type = "UserDSProvider";

addInProvider.Name = "customsqlprovider";

#warning : Make sure to change the assembly  path

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

addInProvider.ClassName =  "CustomUserEntityDataSourceProvider.UserEntityDataSourceProvider";

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();

Using Script

INSERT INTO SKAddInProviders VALUES (newid(),'UserDSProvider','customsqlprovider',

'Sample Custom User Entity Data Source Provider','CustomUserEntityDataSourceProvider.

UserEntityDataSourceProvider','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 'UserDSProvider'.The entry in Repository DB can be verified in database by running the query select * from SKAddInProviders Where Name='customsqlprovider'

Adding entry to SKEntity and SKEntityDataSourceProvider

Steps:

Update the DefinitionXml column of SKEntity table with appropriate resource XML data for the newly created custom user entity provider that has been updated in the SKAddInProviders table.

Update the DefinitionXml column, Provider, Instance Name, Display Name for default entry of the List in SKEntityDataSourceProvider table with appropriate custom user entity data source provider entries.

 To do the above steps, you can use API or Database script, both methods are given below, use any one of  them.

 The definition XML that has been used in the code,to update the DefinitionXml column of  SKEntity and SKEntityDataSourceProvider  table are given below for your reference.

Custom User Entity Data Source Provider Definition XML

<dataproviders>

<provider name="customsqlprovider" type="Custom Sql Provider">

<properties>

<property name="UserId" type="guid"  purpose="identifier" />

<property name="Name" type="string"  purpose="name"/>

<property name="Email"  type="string"  purpose="email"/>

<property name="Password" type="string"  purpose="password"/>

</properties>

<datasource>server=localhost;database=SkeltaUsers;uid=sa;pwd= XXXX</datasource>

<SourceTable>SqlUsers</SourceTable>

<loginproperty>Email</loginproperty>

</provider>

</dataproviders>

"provider" node has attribute name value set as customsqlprovider as this should match the Name column entry in SKAddInProvider.

As described in earlier sections, the purpose attributes identifier, name & email are mandatory.

Custom purpose named password is used to identify the column containing password info for authentication.

"datasource" node contains connection string information.

"SourceTable" node contains the table or view which contains user details.

 "loginproperty" node provides information of the column which will be used as user name while logging in to the repository.

Custom Entity Definition XML

<entity type="user">

<properties>

<property name="UserId" type="string"  purpose="identifier" />

<property name="Name" type="string"  purpose="name"/>

<property name="Email"  type="string"  purpose="email"/>

</properties>

</entity>%%$#@@!!!*&

Contains properties which are mapped to user entity definition XML properties.

As described in earlier sections, the purpose attributes identifier, name and email are mandatory.

Using API for steps 1 & 2

Skelta.Core.ApplicationObject applicationObject = new Skelta.Core.ApplicationObject("Sample Repository");

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

 string entityDefinitionXML  = "<entity type=\"user\">";

 entityDefinitionXML += "<properties>";

 entityDefinitionXML += "<property name=\"UserId\" type=\"string\"  purpose=\"identifier\" />";

 entityDefinitionXML += "<property name=\"Name\" type=\"string\"  purpose=\"name\"/>";

 entityDefinitionXML += "<property name=\"Email\"  type=\"string\"  purpose=\"email\"/>";

 entityDefinitionXML += "</properties>";

 entityDefinitionXML += "</entity>%%$#@@!!!*&";

 //Update default entity definition xml with matching xml for new  custom user entity data source provider

entity.DefinitionXml = entityDefinitionXML;

entity.Save();

 Skelta.Entity.DataSourceProviderCollection entityDataSourceProvider = null;

entityDataSourceProvider = new Skelta.Entity.DataSourceProviderCollection(applicationObject,entity.Id);

 //Entity user xml need to be changed based on the custom User table

#warning : Make sure to change the connection string

 string entityDataSourceProviderDefinitionXML = "<dataproviders>";

 entityDataSourceProviderDefinitionXML += "<provider name=\"customsqlprovider\" type=\"Custom Sql Provider\">";

 entityDataSourceProviderDefinitionXML += "<properties>";

 entityDataSourceProviderDefinitionXML += "<property name=\"UserId\" type=\"guid\"  purpose=\"identifier\"/>";

 entityDataSourceProviderDefinitionXML += "<property name=\"Name\" type=\"string\"  purpose=\"name\"/>";

 entityDataSourceProviderDefinitionXML += "<property name=\"Email\"  type=\"string\"  purpose=\"email\"/>";

 entityDataSourceProviderDefinitionXML += "<property name=\"Password\" type=\"string\"  purpose=\"password\"/>";

 entityDataSourceProviderDefinitionXML += "</properties>";

 entityDataSourceProviderDefinitionXML += "<datasource>server=localhost;database=SkeltaUsers;uid=sa;pwd=XXXX</datasource>";

 entityDataSourceProviderDefinitionXML += "<SourceTable>SqlUsers</SourceTable>";

 entityDataSourceProviderDefinitionXML += "<loginproperty>Email</loginproperty>";

 entityDataSourceProviderDefinitionXML += "</provider>";

 entityDataSourceProviderDefinitionXML += "</dataproviders>";

 entityDataSourceProvider .Items[0].DefinitionXml = entityDataSourceProviderDefinitionXML;

 //Custom provider name which is added in addinprovider table

 entityDataSourceProvider .Items[0].Provider = "customsqlprovider";

 entityDataSourceProvider .Items[0].InstanceName = "sqlproviderCustom";

 entityDataSourceProvider .Items[0].DisplayName = "Custom Provider";

 entityDataSourceProvider .Items[0].Entity = entity;

 // Update skelta list entry  with the custom entry in SKEntityDataSourceProvider for user provider

entityDataSourceProvider.Items[0].Save();

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

 

Using Table Entry for steps 1 & 2:

The definition XML for entity should be matching the properties for entity data source provider.

Update SKEntity Set DefinitionXML = '<entity type="user"><properties><property name="UserId" type="string"  purpose="identifier" /><property name="Name" type="string"  purpose="name"/><property name="Email"  type="string"  purpose="email"/></properties></entity>%%$#@@!!!*&%%$#@@!!!*&'

Where Name = 'UserEntity'

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

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

Update SKEntityDataSourceProvider Set DefinitionXML ='<dataproviders><provider name="customsqlprovider" type="Custom Sql Provider"><properties><property name="UserId" type="guid"  purpose="identifier"/><property name="Name" type="string"  purpose="name"/><property name="Email"  type="string"  purpose="email"/><property name="Password" type="string"  purpose="password"/></properties><datasource>server=localhost;

database=SkeltaUsers;uid=sa;pwd=XXX</datasource><SourceTable>SqlUsers</SourceTable>

<loginproperty>Email</loginproperty></provider></dataproviders>',

DisplayName='Custom Provider', Provider='customsqlprovider', InstanceName='sqlproviderCustom' Where DisplayName ='Skelta List' and EntityId=(Select Id from SKEntity where Name='UserEntity')

Note: Connection string should be changed.

Verification

The above code creates an entry in SKEntity table. The entry in Repository DB can be verified in database by running the query select * from SKEntity Where Name='UserEntity'and Application='Sample Repository'

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 Provider'

 

Verify whether the Custom User Entity Data Source Provider is configured correctly or not

Verification:

The above code updates the entries required for configuring Custom User Entity Data Source Provider. Now, we should be able to see the provider in Repository list page. The previous entry of "List" will be updated with "Custom Provider".

If you get a message "No Provider Available" in the repository list page, cross check the Logger Console for a detailed exception for the cause.

Check the calls made to the custom user entity data source provider.

If there is no exception then we could also create the Actor object API to check the calls made to the custom user entity data source provider.

DLL REFERENCE

Skelta.HWS

Workflow.NET.NET2

The parameters passed to the Actor object are application object & real user id. Real user id can be formed by prefixing provider instance name and special characters "::" to the unique identifier value of user. We have used UserId column value for Kevin.

Sample code

Skelta.HWS.Actor actor = new Actor(new ApplicationObject("Sample Repository"), "sqlproviderCustom::c6a7170a-6a35-4f79-8688-150d0ac8fe15");

object identifierValue = actor.Resource.Properties.Identifier.Value;

string Name = actor.Resource.Properties.Name.Value.ToString();

string Email = actor.Resource.Properties.Email.Value.ToString();

The above code also creates an entry to SKVirtualActor table, this can be checked using query. select * from SKVirtualActor Where Name='Kevin' .