# Database

Provides the ability to synchronize matters from a SQL Server database to Sharepoint.

# Installation

Copy the Epona.Database.dll from the Plugins\Database directory to the site provisioning service directory. Restart the service to activate the plugin.

# Configuration

Start the configurator and browse to CustomSettings to configure the database plugin.
Configurator->Settings->Plugins

One or more jobs can be defined.
Configurator->Settings->Plugins->Database

# IntervalInMinutes

Each job will be start every X minutes, configured via the IntervalInMinutes parameter. If a @LastRunDateTime parameter is available in the commandtext, the value from the configuration will be used and updated with the current datetime.

# ADAL Authentication (Azure Active Directory Authentication Library)

For the use of ADAL authentication make sure you have adal.dll installed in the server.

Ensure you actually have ADAL installed (the existence of C:\Windows\system32\adal.dll is plenty)
Paste this into a .reg file:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSADALSQL]
"TargetDir"="C:\Windows\System32\adal.dll"

Double-click the .reg file to install that value

# ConnectionString

Specify a full connectionstring.

Sql authentication
server=sql;database=aderant;user id=cms;password=cms
If ConnectionString has a password property, and that password has a semicolon character (😉 then the password must be under quotation marks.
Windows authentication
server=sql;database=aderant;Trusted_Connection=True
Upon connecting to the database server:
- the current windows user is used when runnning in console mode
- the service account is used when running as a service

or specify the name of the connectionstring that is specified in the Epona.SiteProvisioning.exe.config. Use this option when a specific provider is necessary (other than the SqlClient). See http://www.connectionstrings.com (opens new window) for samples.

If the connection string has a "password" (case insensitive) key-value pair, the plugin will encrypt the password. The password is stored in a separated setting and the password is removed from the connection string. The password can be modified via the separate setting. When entering the uncrypted password via this setting, the password will be automatically encrypted. The plugin will reconstruct the connection string with the decrypted password when needed to establish the database connection.

# Connectionstring ProviderName

It's also possible to dynamically use not default ADO.NET providers. Follow these steps:

search on https://www.nuget.org/ (opens new window) for the provider and click on Download Package
rename the nupkg file to zip and extra the files
browser to <zip>/lib/net45 / 46 / 48, unlock the dll files and copy the dll to the site provisioning directory
specify the name of the dll in the providername setting

If you get a "type X not found exception", update the providername setting with the correct name of DbFactoryType type in the format <dll name>;<type name>

# CommandText

Specify the sql commandtext or a full path to a file that contains the sql commandtext. Use the parameter @LastRunDateTime to inject the last run date from the configuration.

See the /Plugins/Database/Sql directory for examples.

Configurator->Settings->Plugins->Database->Job

--declare @LastRunDateTime datetime
--select @LastRunDateTime = GETDATE() - 300
select
c.CLIENT_CODE as ClientCode,
c.CLIENT_NAME as ClientName,
m.MATTER_CODE as MatterCode,
m.LONG_MATT_NAME as MatterName,
d.DEPT_NAME as 'Matter.Department',
o.OFFC_DESC as 'Matter.Office',
m.OPEN_DATE as 'Matter.OpenDate',
m.CLOSE_DATE as 'Matter.CloseDate',
rtk.EMAIL as 'Matter.RTK',
btk.EMAIL as 'Matter.BTK',
cl.MATT_CLASS_DESC as 'Matter.MattClass',
mt.MATT_TYPE_DESC as 'Matter.MatterType',
st.STATUS_DESC as 'Matter.Status'
from
hbm_matter m
inner join hbm_client c  on c.CLIENT_UNO = m.CLIENT_UNO
left join HBL_OFFICE o on o.OFFC_CODE = m.OFFC
left join HBL_DEPT d on d.DEPT_CODE = m.DEPT
left join HBM_PERSNL rtk on m.RESP_EMPL_UNO = rtk.EMPL_UNO
left join HBM_PERSNL btk on m.BILL_EMPL_UNO = btk.EMPL_UNO
left join HBL_MATT_CLASS cl on m.MATT_CLASS_CODE = cl.MATT_CLASS_CODE
left join HBL_MATT_TYPE mt on mt.MATT_TYPE_CODE = m.MATT_TYPE_CODE
left join HBL_STATUS_MATT st on st.STATUS_CODE = m.STATUS_CODE
where (c.LAST_MODIFIED > @LastRunDateTime OR  m.LAST_MODIFIED > @LastRunDateTime) 
--and st.STATUS_DESC = 'Open'

# Mapping

The column names returned by the query are mapped on the clientmatter handler. If the column name starts with Client. (client dot) or Client__ (double underscore) or Matter. (matter dot) / Matter__ (double unscore) the name / value is added to the client or matter properties (without the prefix). Unknown properties are added to the matterproperties with a __ and are only used for dynamic fields.

# Test Mapping

Used for verification of the database mapping to the Site provisioning JSON structure.

Default value: 'False'

When 'True', it will save the JSON files to a subfolder called Test in your monitor directory. This prevents Site provisioning from processing them.

It also won't update the last run datetime in Config/Database/{Configuration name}_{Database name}.json.

For example: ..../Monitor/{Configuration name}/Test.

← Sisjuri Visma →