Create User Entity Data Source Provider

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

Note: The Repository for which you are going to create the User Entity Data Source Provider, should have Authentication provider as "AVEVA Work Tasks List".

• 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]

• 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

• 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 & 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.

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 identifier 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 administrator</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

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

2. Table : SKEntity, Object : Skelta.Entity.Entity

3. 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.

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

  • 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.

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 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 & 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 same as the name column value inserted in SKAddInProviders table.

Note: Also, ensure that the query given below updates the row for the AVEVA Work Tasks 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'