EmpowerID ships with thousands of stored procedures for retrieving data in the EmpowerID database that can then be displayed to users in forms, lookups and reports as well as returned for use in EmpowerID workflows. As EmpowerID is extensible, you can create your own stored procedures to retrieve whatever data is necessary to your operations and used those stored procedures in EmpowerID lookups, reports and workflows.
This topic demonstrates this by creating a stored procedure that returns all expiring accounts and using that stored procedure in code to extend the expiration date of each account by twenty days, as well as to send an email to each account owner notifying them of the pending expiration. Doing so involves the following:
In the Procedure template, remove the sample parameter code and replace it with the below code. We will use this parameter in the class library to specify a limit for the accounts returned by the stored procedure.
@ExpirationInDays int |
In the Procedure template, remove the sample code between BEGIN and END.
At this point the CREATE PROCEDURE statement should look similar to the following:
CREATE PROCEDURE DEMO_Account_GetExpiringAccounts @ExpirationInDays int AS BEGIN END GO |
Next, add code to the statement to return all accounts not currently expired, passing in @ExpirationInDays. Passing in the parameter allows you to filter the accounts returned by the stored procedure based on their expiration date. For example, if you want to return accounts expiring in 10 days, when the procedure runs, you set the parameter to 10.
When adding code to stored procedures in this way, it is important to keep the following key points in mind:
|
The CREATE PROCEDURE statement should now look similar to the following code:
CREATE PROCEDURE DEMO_Account_GetExpiringAccounts @ExpirationInDays int AS BEGIN SELECT * FROM Account (NOLOCK) WHERE ExpiresON > GetUtCDate() and ExpiresON is not null and ExpiresOn < DateADD(Day,@ExpirationInDays, GetUtCDate()) END GO |
Sends an email to each account owner notifying them of the pending expiration
The code should look similar to the below example.
public class DemoInvokeCustomStoredProcedure { public void test() { int totalCount; E.TList<Account> accounts = Account.GetTList("Demo_Account_GetExpiringAccounts", 30, 0, int.MaxValue, out totalCount, 5); foreach(Account acc in accounts) { //Extend the expiration date by twenty days for each account returned by the stored procedure acc.ExpiresOn = DateTime.UtcNow.AddDays(20); //Update the ExpiresOn property in the external user directory acc.UpdateAccountStoreIdentityEntry(); //Send email notifying account owners if(!string.IsNullOrEmpty(acc.Email)) { double expirationInDays = acc.ExpiresOn.Value.Subtract(DateTime.UtcNow).TotalDays; string emailBody = acc.FriendlyName + ", Your account is going to expire in " + expirationInDays + " days"; string emailSubject = "Account Expiring"; TheDotNetFactory.Framework.Helpers.EmailHelper.CreateMailMessage(acc.Email, emailSubject, emailBody, true, "", ""); } } } } |
After the compilation completes, you should see an Operations Log appear with a status of Succeeded. If the compilation failed, locate and fix the errors as specified in the log and then recompile the class library.
Select an EmpowerID server as the publishing location and then click Next.
When the wizard has completed publishing, you will be promoted to restart one or more services. Restarting the services allows EmpowerID to pick up your changes as well as make the underlying assembly for the class library available to the local GAC of those services. |
You can now use the class library in workflows.
|