05-21-2018 01:20 PM
Can someone provide me a sample script that will read a database table and populate a set of workflow properties? I am looking for a way to populate a unity form with information from a SQL database. My thought was to read the database, populate some properties and then push the properties to the Unity Form fields.
05-22-2018 05:13 AM
I've found myself having to do this fairly often lately for some bits of custom workflow logic, so here's an example of a script I pieced together from other posts here on the community. This example accepts an input value from a property, allows you to use that in a T-SQL command, then places the result into an output property. To get this to work, you'll have to make sure you have a connection string setup for your database. To do that, go to the Unity Scripts tab in Studio and click "Connection Strings". For this example, I'm using a connection to the OnBase db that I've setup ahead of time named "ONBASE_DB_CONNECTION"
// Skeleton generated by Hyland Unity Editor
namespace DBToProperty
{
using System;
using System.Text;
using Hyland.Unity;
using Hyland.Unity.CodeAnalysis;
using Hyland.Unity.Workflow;
using System.Data;
using System.Data.Common;
using System.Linq;
/// <summary>
/// This script will take an imput value from propInput, and query the database to get a return value to place into propOutput
/// </summary>
public class DBToProperty : Hyland.Unity.IWorkflowScript
{
#region IWorkflowScript
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
string PROP_INPUT = "propInput";
string PROP_OUTPUT = "propOutput";
string ONBASE_DB_CONNECTION = "ONBASE_DB_CONNECTION";
string inputValue = string.Empty;
string result = string.Empty;
DbConnection dbConn;
using(dbConn = app.Configuration.GetConnection(ONBASE_DB_CONNECTION))
{
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Fetching value from database: {0}", dbConn.Database));
dbConn.Open();
DbCommand command = dbConn.CreateCommand();
command.Connection = dbConn;
try
{
inputValue = GetRequiredProperty(PROP_INPUT, args);
//Get the attachment names from the tables
command.CommandText =
"<TSQL Command Here>";
app.Diagnostics.WriteIf(
Diagnostics.DiagnosticsLevel.Verbose,
string.Format("Executing Query: {0}", command.CommandText));
DbDataReader reader = command.ExecuteReader();
while(reader.Read()) {
result = reader.GetString(0);
}
reader.Close();
if(result.Length == 0){
throw new ApplicationException("No value was retrieved from database!");
}
app.Diagnostics.WriteIf(
Diagnostics.DiagnosticsLevel.Verbose,
string.Format("Result = {0}", result));
args.SessionPropertyBag.Set(PROP_OUTPUT, result);
}
catch (Exception e) {
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Error, string.Format("An exception of type {0} was encountered while accessing the DB during Unity script execution.", e.GetType()));
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Error, string.Format("Exception message: {0}", e.Message));
}
finally {
dbConn.Close();
}
} //end using for DB Connection
}
/// <summary>
/// Gets a property value and throws an excpetion if the property does not exist
/// </summary>
private string GetRequiredProperty(string propertyName, Hyland.Unity.WorkflowEventArgs args)
{
string propertyValue = "";
if (!args.SessionPropertyBag.TryGetValue(propertyName, out propertyValue))
{
string errorMessage = string.Format("Failed to get a value for the '{0}' workflow property", propertyName);
throw new ApplicationException(errorMessage);
}
return propertyValue;
}
#endregion
}
}
05-22-2018 05:04 AM
Hello. Rather than writing a script to do this, I think a better way would be to create an External Dataset in Configuration for a Keyword on the form and put the SQL in the dataset.
If you have multiple fields to populate, an Autofill will work to populate them after the first value is selected.
05-22-2018 06:52 AM
05-22-2018 07:24 AM
05-22-2018 07:35 AM
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.