For one of our customers I had to come up with a solution for the changing variables and different environment parameters.
For the changing send/receive location we use Deployment Framework for BizTalk, but there are some parameters that cannot be changed during runtime.
One of the proposals was too place the environment variables in the different config-files used by BizTalk. Yes BizTalk uses different config-files, we all know the config-files for 32-bit and 64-bit host instances. But not everyone knows that de isolated hosts and WCF use different ones.
This means that you have to maintain at least four files per server and we all know how quickly people forget things. This and the fact that altering config-files isn’t a best practice, we opted for the use of a database. The data can be changed at runtime and we can make an identical database with different data per environment that all the servers for that environment can access.
The following screenshot describes the table I’m using for this explanation.
By including the application name as a column, we can reuse the same table, stored procedures and the generated schemas by the WCF-SQL adapter. This also eliminates the possibility of identical parameter names by different applications. Depending on the expected records, you can index the table. We put an index on the ApplicationName together with ParameterName.
For retrieving the data, we’re using the WCF-SQL adapter that calls one of two stored procedures. There are 2 stored procedures:
- usp_GetParameterValueByName: it takes the application and parameter name as an input value and returns the value of that parameter
USE [EbtsServices]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetParameterValueByName]
@applicationName nvarchar(10),
@parameterName nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT ParameterValue
FROM ApplicationParameters
WHERE ApplicationName = @applicationName
AND ParameterName = @parameterName;
END
2. usp_GetParametersByApplicationName: it takes the application name as an input value and returns all the parameters associated with the application
USE [EbtsServices]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetParametersByApplicationName]
@applicationName nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT ParameterName , ParameterValue
FROMApplicationParameters
WHEREApplicationName = @applicationName;
END
Don’t forget to grant the correct BizTalk groups execute (with grant) permissions on the stored procedures. Using WCF-SQL to generate the schemas results in the following three schemas:
The first thing to do is to promote the ApplicationName en ParameterName element in the dbo.xsd so that we can alter them inside an orchestration.
This is a response message when using usp_GetParametersByApplication :
As you can see, the response consists out of – depending on the number of parameters in the table – multiple StoredProcedureResultSet0 records, which have a ParameterName and ParameterValue element.
Because BizTalk doesn’t let you promote elements that can occur multiple times, we had to use XPath expression to extract the value.
On the internet there are a lot of blogs that suggest that you count the number of repeated records and then loop x time to extract the parameter value by using the index in the XPath. This approach has some disadvantages:
- you need to add three extra shapes to you orchestration (one expression to determine the count, one loop and one expression insight the loop the extract the values)
- It has an impact on performance because it needs to loop over all the StoredProcedureResultSet0, check if the parameter name is the one you expect and then extract the value, even if you don’t use them all.
So I searched for another solution. Just like with an collection it is possible to replace the index by a key value, in this case the parameter name. First I tried to figure out the correct xpath by trail and error. Eventually I went looking for an interesting tool. The lucky winner is Altova’s xml spy.
I opened the response message and started to experiment until I found the correct xpath expression:
string(/*[local-name()=’usp_GetAllParametersByApplicationNameResponse’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’]/*[local-name()=’StoredProcedureResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’]/*[local-name()=’StoredProcedureResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName’] /*[local-name()=’ParameterName’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName’ and .=’{ParameterName}’] /../*[local-name()=’ParameterValue’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName’])”))
Now I just replace {ParameterName} with the parameter name (as written in the database) where I want to get the corresponding parameter value form. And then I had to place the expression inside string(), to get the value as a System.String object.
Still this wasn’t good enough for BizTalk, I got an object of type Microsoft.XLANGs.Core.Part+ArrayBasedXmlNodeList, not exactly what I needed. The solution is to use System.Convert.ToString.
The full command to retrieve the parameter value in an orchestration, parameterValue is of type System.String.
parameterValue = System.Convert.ToString(xpath(parametersResponse.Body , “string(/*[local-name()=’usp_GetAllParametersByApplicationNameResponse’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’]/*[local-name()=’StoredProcedureResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’]/*[local-name()=’StoredProcedureResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName’] /*[local-name()=’ParameterName’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName’ and .=’{ParameterName}’] /../*[local-name()=’ParameterValue’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName’])”));
When using usp_GetParameterValueByName the xpath expression is slightly different:
/*[local-name()=’usp_GetParameterValueByNameResponse’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’]/*[local-name()=’StoredProcedureResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’]/*[local-name()=’StoredProcedureResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/sp_GetParameterValueByName’]/*[local-name()=’ParameterValue’])”))
It is still necessary to put the expression inside of string() and do a conversion to System.String.
Author: Martijn