Share via

Azure Logic Apps: Implementing Message Content Enricher Patterns with Cloud Databases in Integration Account Maps (part 2)

  • Article

<<Part 1

 

1  Introduction

In part 1 of the series we looked at connecting to three Microsoft proprietary databases, which, coincidently, had their required client assembly’s resident in the GAC. In this second part we’ll look at five non- proprietary/open source databases that are hosted in Azure. Only one of these, MySQL, has its’ client assembly residing in the GAC. In this article we’ll see how we can determine what assemblies each database requires and how we can upload them and use them from an XSLT map.

This article assumes familiarity with the databases being connected to and queried, and sample test data has been created using the tutorials linked to. Note that ‘test data’ can also be created and added to the Azure databases using the Data Explorer blade provided in the Azure Portal.

2  How to Connect to Azure MySQL

As mentioned in part 1, section 2, the MySql.Data assembly exists in the GAC. Therefore, we ought to be able to add a reference to the assembly and namespaces that we need to use. Indeed, one would expect the code be very similar to that of the Azure SQL Server SqlClient.

Steps

  1. Create a new mySQL database in Azure. See here for instructions;

  2. Follow the C# sample code here and create a ‘sales’ database and ‘customers’ table. Insert some test rows;

  3. Allow access from all networks (Home > yourMySQLDb – Firewall and virtual networks);

  4. Create the XSLT MySqlClient map given below. Update the connectionString variable from the PRIMARY CONNECTION STRING (Home > yourMySQLDb – Connection String) provided in the portal. Upload the XSLT map to the Integration Account;

  5. Create a new, or edit an existing, Logic App:

    1. Add a Compose Action and copy the Person XML message from Appendix A in part 1;
    2. Add a Transform XML action. Set the Content value to the previous Compose action output. Set the Map to the value to one of the XSLT maps previously uploaded;

  6. Run the Logic App. The Logic App should fail, and the output appear like the following:

  7. The Transform XML fails with the error “An error occurred while processing map. 'Out of sync with server'”.

MySqlClient

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0"  xmlns:s0="http://AzureLogicAppsMapsDemo/Person" xmlns:ns0="http://AzureLogicAppsMapsDemo/Contact" xmlns:ScriptNS0="https://portal.azure.com/myschemas/ScriptNS0">
<xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
  <xsl:template match="/">
  <xsl:apply-templates select="/s0:Person" />
  </xsl:template>
  <xsl:template match="/s0:Person">
  <ns0:Contact>
  <Title><xsl:value-of select="Title/text()" /></Title> 
  <Forename><xsl:value-of select="Forename/text()" /></Forename> 
  <Surname><xsl:value-of select="Surname/text()" /></Surname> 
  <Phone><xsl:value-of select="ScriptNS0:GetPhoneNo(ID/text())" /></Phone> 
  </ns0:Contact>
  </xsl:template>
  <msxsl:script language="C#" implements-prefix="ScriptNS0">
  <msxsl:assembly name ="System.Data"/>
  <msxsl:assembly name ="MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
  <msxsl:using namespace="MySql.Data" />
  <msxsl:using namespace="MySql.Data.MySqlClient" />
  <![CDATA[
  public string GetPhoneNo(int id)
  {
  string connStr = "Server=xxxxxxxxxxx.mysql.database.azure.com; Port=3306; Database=sales; Uid=admin@xxxxxxxxxxx; Pwd=xxxxxxxxxxx";
  MySqlConnection conn = new MySqlConnection(connStr);
   
  conn.Open();
  
  string sql = "SELECT Phone FROM sales.customers WHERE ID=" + id.ToString();
  MySqlCommand cmd = new MySqlCommand(sql, conn);
  MySqlDataReader reader = cmd.ExecuteReader();
  
  reader.Read();
  return reader.GetString(0);
  }
  ]]>
  </msxsl:script>  
</xsl:stylesheet>

This is known issue with the version of the MySqlClient that we are referencing (see here) and causes us a problem as version 6.5.4.0 is the only version installed. One work around to this is to upload a newer version of the assembly to the Integration Account assemblies’ blade and reference this new version in the XSLT.

  1. Download the MSql.Data nuget package 8.0.12;

  2. Upload the MySql.Data.dll assembly to the Integration Account assemblies’ blade;

  3. Modify the script to reference the uploaded assembly:

    • <msxsl:assembly name ="MySql.Data, Version=8.0.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>

  4. Run the Logic App. The Logic App should fail, and the output appear like the following:

  5. The Transform XML fails with the error “You must add a reference to assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'”.

This second error is due to the newly uploaded MySql.Data assembly referencing version 4 of the System.Data assembly. If we check our GAC list from part 1, section 2, a version 4 does exist along with version 2. But, by not specify the fully qualified name in the msxsl:assembly, it is version 2 that gets loaded. Edit the XSLT to reference the correct version:

  1. Modify the script to reference the uploaded assembly:

    • <msxsl:assembly name ="System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

  2. Run the Logic App. The output should appear like the following:

Points to Note

Querying a MySQL database has been the most problematic so far, but the workaround was straightforward. There are a couple of key points here:

  1. We have learnt that the Integration Account assemblies are not ‘just’ for custom assemblies;
  2. We can upload third part ones too. Secondly, they can be used to upload a different version of an assembly if it is required;
  3. Where more than one version exists, we need to ensure the correct version of an assembly is referenced i.e. use the full assembly name and not a short name.

3  How to Connect to Azure PostgreSQL

Following on from MySQL we now turn to PostgreSQL. From the assembly list in part 1, section 2 there is no sign of any PostgreSQL client assemblies, but what about if we use the same mechanism as MySQL and upload the necessary client assemblies? The first thing we need to do is determine what assemblies are required, and for this we’ll need to disassemble the Npgsql client assembly.

Steps

  1. Download the Npgsql nuget package 4.03;

  2. Download and install ILSpy (available here);

  3. Open the Npgsql assembly in ILSpy and examine the references;

  4. Cross-references the assembly and version with the GAC list from part 1, section 2:

  5. Comparing the two lists reveals that there are two missing assemblies: System.Runtime.CompilerServices.Unsafe.dll & System.Threading.Tasks.Extensions.dll;

  6. Upload the Npgsql.dll and the two assemblies in step 5 to the Integration Account assemblies’ blade;

  7. Create a new Postgre database in Azure. See here for instructions;

  8. Follow the example script here and create a database with a ‘customers’ table that includes an ‘ID’ and a ‘PhoneNumber’ column. Insert some test rows;

  9. Allow access from all networks (Home > yourPostgreDB– Firewall and virtual networks);

  10. Create the XSLT Npgsql map given below. Update the connection string variables from the PRIMARY CONNECTION STRING (Home > yourMySQLDb – Connection String) provided in the portal. Upload the XSLT map to the Integration Account;

  11. Create a new, or edit an existing, Logic App:

    1. Add a Compose Action and copy the Person XML message from Appendix A in part 1;
    2. Add a Transform XML action. Set the Content value to the previous Compose action output. Set the Map to the value to one of the XSLT maps previously uploaded;

  12. Run the Logic App. The output should appear like the following:

  13. The phone element should be populated with the phone number from the test data created in step 8;

 

Npgsql

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureLogicAppsMapsDemo/Person" xmlns:ns0="http://AzureLogicAppsMapsDemo/Contact" xmlns:ScriptNS0="https://portal.azure.com/myschemas/ScriptNS0">
  <xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
  <xsl:template match="/">
  <xsl:apply-templates select="/s0:Person" />
  </xsl:template>
  <xsl:template match="/s0:Person">
  <ns0:Contact>
  <Title><xsl:value-of select="Title/text()" /></Title> 
  <Forename><xsl:value-of select="Forename/text()" /></Forename> 
  <Surname><xsl:value-of select="Surname/text()" /></Surname> 
  <Phone><xsl:value-of select="ScriptNS0:GetPhoneNo(ID/text())" /></Phone> 
  </ns0:Contact>
  </xsl:template>
  <msxsl:script language="C#" implements-prefix="ScriptNS0">
  <msxsl:assembly name ="System.Data"/>
  <msxsl:assembly name ="System.ValueTuple"/>
  <msxsl:assembly name ="System.Threading.Tasks.Extensions, Version=4.2.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51"/>
  <msxsl:assembly name ="System.Runtime.CompilerServices.Unsafe, Version=4.0.4.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  <msxsl:assembly name ="Npgsql, Version=4.0.3.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
  <msxsl:using namespace="Npgsql" />
  <![CDATA[
  public string GetPhoneNo(int id)
  {
  // Specify connection options and open a connection
  string Host = "xxxxxxxxxxx.postgres.database.azure.com";
  string User = "admin@xxxxxxxxxx";
  string DBname = "mydemopgsqldb";
  string Password = "xxxxxxxxx";
  string Port = "5432";
   
  string connString = String.Format("Server={0}; User Id={1}; Database={2}; Port={3}; Password={4}; SslMode=Require", Host, User, DBname, Port, Password);  
  var conn = new NpgsqlConnection(connString);
  conn.Open();
  
  // Define a query
  var command = conn.CreateCommand();
  command.CommandText = "SELECT phoneNumber FROM customers WHERE id=" + id.ToString();
  var dr = command.ExecuteReader();
  dr.Read();
  return dr.GetString(0);
  }
  ]]>
  </msxsl:script>  
</xsl:stylesheet>

Points to Note

We can upload both new assemblies and referenced assemblies to the Integration Account assemblies’ blade. All assemblies get loaded into the same AppDomain and can be referenced by the XSLT map. By disassembling the DB client assembly, we can cross reference the assemblies required by the client with those in the AppDomain and GAC list to determine which assemblies need to be uploaded.

Note that when comparing version numbers only the major and minor numbers need to be considered. For example, the version number of the System.ValueTuple assembly is 4.0.3.0 but the version in the GAC list is 4.0.0.0. The version number is represented as a four-part string with the format:  <major version>.<minor version>.<build number>.<revision>.

 

4  How to Connect to Cosmos DB Cassandra

As with connecting to Gremlin, connecting to Cassandra will follow the same pattern of downloading and examining the client assembly. The latest driver is CassandraCSharpDriver 3.6.0. If we examine the client we see there is a dependency on Microsoft.Extensions.Logging (>= 1.0.0) and Microsoft.Extensions.Logging.Abstractions (>= 1.0.0) and in our ‘map’ environment we have both of these assemblies with a version of 1.1.0.0. However, this is not the full story

Steps

  1. Create a new CosmosDB Cassandra database in Azure. See here for instructions;

  2. Follow the C# example and create a ‘customer’ table;

  3. Allow access from all networks (Home > yourCosmosDBCassandra – Firewall and virtual networks);

  4. Download the CassandraCSharpDriver nuget package and examine the Cassandra.dll assembly in ILSpy;

  5. Cross-references the assembly versions with those found in the assembly list in part 1, section 2. There should a dependency on LZ4.dll;

  6. Upload the required assemblies (Cassandra.dll & LZ4.dll) to the Integration Account Assemblies blade;

  7. Create the XSLT CassandraCSharpDriver map given below. Update the connection string variables from the PRIMARY CONNECTION STRING (Home > yourCosmosDBCassandra – Connection String) provided in the portal. Upload the XSLT map to the Integration Account;

  8. Create a new, or edit an existing, Logic App:

    1. Add a Compose Action and copy the Person XML message from Appendix A in part 1;
    2. Add a Transform XML action. Set the Content value to the previous Compose action output. Set the Map to the value to one of the XSLT maps previously uploaded;

  9. Run the Logic App. The output should appear like the following:

  10. The phone element should be populated with the phone number from the test data created in step 2;

 

CassandraCSharpDriver

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureLogicAppsMapsDemo/Person" xmlns:ns0="http://AzureLogicAppsMapsDemo/Contact" xmlns:ScriptNS0="https://portal.azure.com/myschemas/ScriptNS0">
  <xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
  <xsl:template match="/">
  <xsl:apply-templates select="/s0:Person" />
  </xsl:template>
  <xsl:template match="/s0:Person">
  <ns0:Contact>
  <Title><xsl:value-of select="Title/text()" /></Title> 
  <Forename><xsl:value-of select="Forename/text()" /></Forename> 
  <Surname><xsl:value-of select="Surname/text()" /></Surname> 
  <Phone><xsl:value-of select="ScriptNS0:GetPhoneNo(ID/text())" /></Phone> 
  </ns0:Contact>
  </xsl:template>
  <msxsl:script language="C#" implements-prefix="ScriptNS0">
  <msxsl:assembly name ="System.Core"/>
  <msxsl:assembly name ="System.Data"/>
  <msxsl:assembly name ="System.Linq"/>
  <msxsl:assembly name ="Cassandra, Version=3.1.0.0, Culture=neutral, PublicKeyToken=10b231fbfc8c4b4d"/>
  <msxsl:assembly name ="LZ4, Version=1.0.15.93, Culture=neutral, PublicKeyToken=62e1b5ec1eec9bdd"/>
  <msxsl:using namespace="System.Linq" />
  <msxsl:using namespace="System.Net.Security" />
  <msxsl:using namespace="System.Security.Authentication" />
  <msxsl:using namespace="System.Security.Cryptography.X509Certificates" />
  <msxsl:using namespace="Cassandra" />
  <![CDATA[
  public string GetPhoneNo(int id)
  {
  var options = new Cassandra.SSLOptions(SslProtocols.Tls12, true, ValidateServerCertificate);
  options.SetHostNameResolver((ipAddress) => "xxxxxxxxxxxxxx.cassandra.cosmosdb.azure.com");
   Cluster cluster = Cluster.Builder()
  .WithCredentials("xxxxxxxxxxxxxxx", "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==")
  .WithPort(10350)
  .AddContactPoint("xxxxxxxxxxxxxxx.cassandra.cosmosdb.azure.com")
  .WithSSL(options)
  .Build();
  
  ISession session = cluster.Connect("demokeyspace");
  
  var rs = session.Execute("SELECT * FROM person WHERE userid=" + id.ToString());
  string phone = rs.GetRows().First().GetValue<string>("phonenumber");
  return phone;
  }
  
  public static bool ValidateServerCertificate(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
  { 
  if (sslPolicyErrors == SslPolicyErrors.None) return true; 
   
  // Do not allow this client to communicate with unauthenticated servers. 
  return false; 
  }
  ]]>
  </msxsl:script>  
</xsl:stylesheet>

 

Points to Note

Querying a Cassandra database has proved to be straightforward by simply uploading the two required assemblies (Cassandra and LZ4) to the Integration Account Assemblies’ blade. Note that client requires us to provide a callback function to validate the server certificate, for more details see here.

 

5  How to Connect to Cosmos DB Gremlin

The techniques that have been learnt connecting to MySQL and PostgreSQL can now be used when connecting to the remaining three Cosmos databases. The general process will be to download the client, disassemble it and determine what assemblies exist/do not exist in the ‘map’ environment, and then upload the required assemblies to the Integration Account Assemblies’ blade.

Steps

  1. Create a new CosmosDB Gremlin database in Azure. See here for instructions;

  2. Follow the C# example to insert data into the database. Remove the last dictionary entry ‘DropVertex’;

  3. Allow access from all networks (Home > yourCosmosDBGremlin – Firewall and virtual networks);

  4. Download the Gremlin.NET nuget package and examine the Gremlin.Net.dll assembly in ILSpy;

  5. Cross-references the assembly versions with those found in the assembly list in part 1, section 2. There should a dependency on Newtonsoft.Json.dll version 11.0, but the ‘map’ environment only versions 6.0, 9.0 &10.0 installed;

  6. Upload the required assemblies (Gremlin.Net.dll & Newtonsoft.Json.dll) to the Integration Account Assemblies blade;

  7. Create the XSLT Gremlin.NET map given below. Update the connection string variables from the PRIMARY CONNECTION STRING (Home > yourCosmosDBGremlin – Connection String) provided in the portal. Upload the XSLT map to the Integration Account;

  8. Create a new, or edit an existing, Logic App:

    1. a.  Add a Compose Action and copy the Person XML message from Appendix A in part 1;
    2. b.  Add a Transform XML action. Set the Content value to the previous Compose action output. Set the Map to the value to one of the XSLT maps previously uploaded;

  9. Run the Logic App. The output should appear like the following:

  10. The Notes element should be populated with the note that Thomas knows mary.

Gremlin.Net

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureLogicAppsMapsDemo/Person" xmlns:ns0="http://AzureLogicAppsMapsDemo/Contact" xmlns:ScriptNS0="https://portal.azure.com/myschemas/ScriptNS0">
  <xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
  <xsl:template match="/">
  <xsl:apply-templates select="/s0:Person" />
  </xsl:template>
  <xsl:template match="/s0:Person">
  <ns0:Contact>
  <Title><xsl:value-of select="Title/text()" /></Title> 
  <Forename><xsl:value-of select="Forename/text()" /></Forename> 
  <Surname><xsl:value-of select="Surname/text()" /></Surname> 
  <Notes><xsl:value-of select="ScriptNS0:GetNotes(Forename/text())" /></Notes>
  </ns0:Contact>
  </xsl:template>
  <msxsl:script language="C#" implements-prefix="ScriptNS0">
  <msxsl:assembly name ="netstandard"/>
  <msxsl:assembly name ="System.Core"/>
  <msxsl:assembly name ="Gremlin.Net, Version=3.4.0.0, Culture=neutral, PublicKeyToken=d2035e9aa387a711"/>
  <msxsl:assembly name ="Newtonsoft.Json, Version=11.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed"/>
  <msxsl:using namespace="Gremlin.Net.Driver" />
  <msxsl:using namespace="Gremlin.Net.Structure.IO.GraphSON" />
  <msxsl:using namespace="Newtonsoft.Json" />
  <msxsl:using namespace="Newtonsoft.Json.Linq" />
  <![CDATA[
  public string GetNotes(string name)
  { 
  string hostname = "xxxxxxxxx.gremlin.cosmosdb.azure.com";
  int port = 443;
  string authKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==";
  string database = "graphdb";
  string collection = "Persons";
   
  var gremlinServer = new GremlinServer(hostname, port, enableSsl: true, username: "/dbs/" + database + "/colls/" + collection, password: authKey);
  var gremlinClient = new GremlinClient(gremlinServer, new GraphSON2Reader(), new GraphSON2Writer(), GremlinClient.GraphSON2MimeType);  
  var task = gremlinClient.SubmitAsync<dynamic>("g.V('" + name + "').out('knows').hasLabel('person')");
  
  task.Wait();
  
  if (task.Result.Count != 0)
  {
  JArray ja = JArray.Parse(JsonConvert.SerializeObject(task.Result));
   
  string knowsWho = ja.SelectToken("[0].properties.firstName[0].value").Value<string>();
  return "Knows " + knowsWho;
  }
  else
  {
  return string.Empty;  
  }
  }
  ]]>
  </msxsl:script>  
</xsl:stylesheet>

Points to Note

In a similar way to how we were able to upload a more recent version of the MySqlClient assembly, in this example we upload a new version of the Newtonsoft.Json assembly. Also notice the task.Wait() method call. This is because we cannot use the async modifier and await operator in the XSLT C# script. Any attempt to use them will result in an exeception, XslTransformException: Extension function parameters or return values which have Clr type 'Task`1' are not supported.

Another common method of connecting to a Gremlin DB is to use Microsoft.Azure.Graphs. This client assembly extends the Microsoft.Azure.Documents.Client, which was use in part 1 to connect to Cosmos DB Core, to support the Gremlin traversal language (see here). However, using this client causes several problems as its dependencies are hard coded to older versions of assemblies. Recently the Azure Cosmos DB Team announced that Microsoft.Azure.Graphs has been deprecated and that Gremlin.Net is the recommended client (see here).

 

6  How to Connect to Cosmos DB MongoDB

If we follow the same procedure as in the previous two sections for the MongoDB client, one thing that sticks out when the assemblies are examined in ILSpy is that none of them are strong-named. This causes an issue at run-time when the assemblies are resolved, and an exception is thrown when trying to execute the map. Further, if we do try to sign the individual assemblies (e.g. see here) , the references in one of the assemblies – one which references the other two assemblies – will no longer be valid, and we receive a file not found error. The solution is to merge the three unsigned assemblies into a single signed assembly, and upload this new assembly to the Integration Account, along with two other dependency assemblies.

Steps

  1. Create a new CosmosDB MongoDB database in Azure. See here for instructions;

  2. Follow the C# example (see here) and create a some tasks;

  3. Allow access from all networks (Home > yourCosmosDBMongoDB – Firewall and virtual networks);

  4. Download the MongoDB.Driver nuget package and examine the MongoDB.Driver, MongoDB.Driver.Core and MongoDB.Bson assemblies in ILSpy;

  5. Cross-references the assembly versions with those found in the assembly list in part 1, section 2. There should two assembly differences: DnsClient & System.Buffers;

  6. Download the ILMerge tool (see here);

  7. Copy the three assemblies in the same folder and then open a command prompt. Ensure you can execute the SN.exe and ILMerge.exe applications;

  8. Run the following commands:

    1. SN.exe -k MongoDBClient.snk
    2. ILMerge.exe /keyfile:MongoDBClient.snk /out:MongoDBClient.dll MongoDB.Driver.dll MongoDB.Driver.Core.dll MongoDB.Bson.dll /targetplatform:"v4, C:\Windows\Microsoft.NET\Framework\v4.0.30319"

  9. This should produce a new assembly MongoDBClient.dll;

  10. Upload the three assemblies MongoDBClient.dll, DnsClient & System.Buffers to the Integration Account Assemblies blade;

  11.  Create the XSLT MongoDB map given below. Update the connection string variables from the PRIMARY CONNECTION STRING (Home > yourCosmosDBMongoDB – Connection String) provided in the portal. Upload the XSLT map to the Integration Account;

  12. Create a new, or edit an existing, Logic App:

    1. a.  Add a Compose Action and copy the Person XML message from Appendix A in part 1;
    2. b.  Add a Transform XML action. Set the Content value to the previous Compose action output. Set the Map to the value to one of the XSLT maps previously uploaded;

  13. Run the Logic App. The output should appear like the following:

  14. The Notes element should be populated with the tasks from the test data created in step 2;

 

MongoDB.Driver

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"  exclude-result-prefixes="msxsl s0 ScriptNS0" version="1.0" xmlns:s0="http://AzureLogicAppsMapsDemo/Person" xmlns:ns0="http://AzureLogicAppsMapsDemo/Contact" xmlns:ScriptNS0="https://portal.azure.com/myschemas/ScriptNS0">
  <xsl:output omit-xml-declaration="yes" method="xml" indent="yes" version="1.0" />
  <xsl:template match="/">
  <xsl:apply-templates select="/s0:Person" />
  </xsl:template>
  <xsl:template match="/s0:Person">
  <ns0:Contact>
  <Title>
  <xsl:value-of select="Title/text()" />
  </Title>
  <Forename>
  <xsl:value-of select="Forename/text()" />
  </Forename>
  <Surname>
  <xsl:value-of select="Surname/text()" />
  </Surname>
  <Notes>
  <xsl:value-of select="ScriptNS0:GetTasks()" />
  </Notes>
  </ns0:Contact>
  </xsl:template>
  <msxsl:script language="C#" implements-prefix="ScriptNS0">
  <msxsl:assembly name="System.Buffers, Version=4.0.1.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51"/>
  <msxsl:assembly name="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
  <msxsl:assembly name="DnsClient, Version=1.0.7.0, Culture=neutral, PublicKeyToken=4574bb5573c51424"/>
  <msxsl:assembly name="MongoDBClient, Version=2.7.0.0, Culture=neutral, PublicKeyToken=9f8575fec07bcb94"/>
  <msxsl:using namespace="System.Linq" />
  <msxsl:using namespace="System.Security.Authentication" />
  <msxsl:using namespace="MongoDB.Bson" />
  <msxsl:using namespace="MongoDB.Bson.Serialization.Attributes" />
  <msxsl:using namespace="MongoDB.Bson.Serialization.IdGenerators" />
  <msxsl:using namespace="MongoDB.Driver" />
  <![CDATA[
  public string GetTasks()
  {
  string userName = "demomongo";
  string host = "demomongodb.documents.azure.com";
  string password = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==";
  string dbName = "Tasks";
  string collectionName = "TasksList";
  
  MongoIdentity identity = new MongoInternalIdentity(dbName, userName);
  MongoIdentityEvidence evidence = new PasswordEvidence(password);
  MongoClientSettings settings = new MongoClientSettings()
  {
  Server = new MongoServerAddress(host, 10255),
  UseSsl = true,
  SslSettings = new SslSettings()
  {
  EnabledSslProtocols = SslProtocols.Tls12
   },
  Credential = new MongoCredential("SCRAM-SHA-1", identity, evidence)
  };
  
  MongoClient client = new MongoClient(settings);
  var database = client.GetDatabase(dbName);
  var todoTaskCollection = database.GetCollection<MyTask>(collectionName);
  var list = todoTaskCollection.Find(new BsonDocument()).ToList();
  
  string tasks = string.Join(",", list.Select(mt => mt.Name));
  return tasks;
  }
   
  public class MyTask
  {
   [BsonId(IdGenerator = typeof(CombGuidGenerator))]
  public Guid Id { get; set; }
  [BsonElement("Name")]
  public string Name { get; set; }
  [BsonElement("Category")]
  public string Category { get; set; }
  [BsonElement("Date")]
  public DateTime Date { get; set; }
  [BsonElement("CreatedDate")]
  public DateTime CreatedDate { get; set; }
  }
   
  ]]>
  </msxsl:script>
</xsl:stylesheet>

 

Points to Note

Querying the Cosmos DB MongoDB has proven to the most intricate, but by understanding precisely what assemblies are required and how to merge and use unsigned assemblies we are able to reference them from the XSLT map.

Also, the System.Buffer assembly did not appear in the assembly list returned by the script in part 1, section 2. However, it does actually exist in the folder:

D:\Program Files (x86)\SiteExtensions\Functions\1.0.11959\bin\System.Buffers.dll.

Therefore, an alternative to loading the assembly to the Integration Account, for example if the limit on the number of uploaded assemblies is reached, is to directly reference this assembly in the XSLT using the href attribute, i.e.:

  <msxsl:assembly href=" D:\Program Files (x86)\SiteExtensions\Functions\1.0.11959\bin\System.Buffers.dll"/>

 

7  Summary

In this article we have been able to connect and query the remaining three Cosmos DB databases and two databases offered as an Azure service. By design, each example, starting from the first one in part 1 the connecting to and querying of each database has gotten progressively more difficult. But by understanding the existing functionality and components installed on the ‘map’ environment, and by examining how the client assembly’s function, we ‘ve been able successfully connect to each database.

In part 1 of the series we looked at connecting to three Microsoft proprietary databases, which, coincidently, had their required client assembly’s resident in the GAC. In this second part we’ll look at five non- proprietary/open source databases that are hosted in Azure. Only one of these, MySQL, has its’ client assembly residing in the GAC. In this article we’ll see how we can determine what assemblies each database requires and how we can upload them and use them from an XSLT map.

This article assumes familiarity with the databases being connected to and queried, and sample test data has been created using the tutorials linked to. Note that ‘test data’ can also be created and added to the Azure databases using the Data Explorer blade provided in the Azure Portal.

>>Part 3