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.
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:
1. 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
FROM
ApplicationParameters
WHERE
ApplicationName = @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:
- dbo.xsd
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
