BizBert - Understanding the BizTalk Mapper: Part 10

Monday, February 18, 2008

Understanding the BizTalk Mapper: Part 10 - Database Functoids

This category contains both Database and Cross Referencing Functoids - but they all connect to a database to retrieve/update data.
Unlike all other default functoids, these functoids all call classes/methods in external assemblies - no inline C# is emitted at all. Because of this, this is the only category that emits an ExtensionObjects file listing the strong names of the external assemblies used.

Note: in this category I show some of the source code from the external assemblies as well.

For each functoid I've shown:

Whether XSLT or C# is emitted
Whether an XSLT equivalent exists
The XSLT or C# emitted by the functoid
Where C# is emitted, the equivalent XSLT to achieve the same functionality (in both XSLT v1.0 and v2.0)

Functoids covered in this category:

Database Lookup	Get Common Value
Error Return	Remove Application ID
Format Message	Set Common ID
Get Application ID	Value Extractor
Get Application Value	Common Code
Get Common ID

Database Functoids
Note: using any of the Database Functoids will cause the following to be emitted as an Extension Object xml file. This is used to tell the XSLT compiler in which assembly it can find the database/cross-referencing lookup classes. All of these functoids use classes in one of two external assemblies. For the database functoids the source code for the class/method is shown. <ExtensionObject Namespace="http://schemas.microsoft.com/BizTalk/2003/ScriptNS0" AssemblyName="Microsoft.BizTalk.BaseFunctoids, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" ClassName="Microsoft.BizTalk.BaseFunctoids.FunctoidScripts" /> <ExtensionObject Namespace="http://schemas.microsoft.com/BizTalk/2003/ScriptNS1" AssemblyName="Microsoft.BizTalk.CrossReferencing, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" ClassName="Microsoft.BizTalk.CrossReferencing.CrossReferencing" />
	Database Lookup
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v1" select="ScriptNS0:DBLookup(0 , string(@name) , string(@id) , "" , "")" /> public string DBLookup(int index, string value, string connectionString, string table, string column) { DBFunctoidHelper helper = null; bool flag = false; InitDBFunctoidHelperList(); if (!myDBFunctoidHelperList.Contains(index)) { helper = new DBFunctoidHelper(); myDBFunctoidHelperList.Add(index, helper); } else { helper = (DBFunctoidHelper) myDBFunctoidHelperList[index]; } try { if (((helper.ConnectionString == null) \|\| ((helper.ConnectionString != null) && (string.Compare(helper.ConnectionString, connectionString, StringComparison.Ordinal) != 0))) \|\| (helper.Connection.State != ConnectionState.Open)) { flag = true; helper.MapValues.Clear(); helper.Error = ""; if (helper.Connection.State == ConnectionState.Open) { helper.Connection.Close(); } helper.ConnectionString = connectionString; helper.Connection.ConnectionString = connectionString; helper.Connection.Open(); } if ((flag \|\| (string.Compare(helper.Table, table, StringComparison.Ordinal) != 0)) \|\| (((string.Compare(helper.Column, column, StringComparison.OrdinalIgnoreCase) != 0) \|\| (string.Compare(helper.Value, value, StringComparison.Ordinal) != 0)) \|\| ((helper.Error != null) && (helper.Error.Length > 0)))) { helper.Table = table; helper.Column = column; helper.Value = value; helper.MapValues.Clear(); helper.Error = ""; OleDbCommand command = new OleDbCommand("SELECT * FROM " + table + " WHERE " + column + "= ?", helper.Connection); OleDbParameter parameter = new OleDbParameter(); parameter.Value = value; command.Parameters.Add(parameter); IDataReader reader = command.ExecuteReader(); if (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { string str = reader.GetName(i).ToLower(CultureInfo.InvariantCulture); object obj2 = reader.GetValue(i); helper.MapValues[str] = obj2; } } reader.Close(); } } catch (OleDbException exception) { if (exception.Errors.Count > 0) { helper.Error = exception.Errors[0].Message; } } catch (Exception exception2) { helper.Error = exception2.Message; } finally { if (helper.Connection.State == ConnectionState.Open) { helper.Connection.Close(); } } return index.ToString(CultureInfo.InvariantCulture); }
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Error Return
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v2" select="ScriptNS0:DBErrorExtract(string($var:v1))" /> public string DBErrorExtract(int index) { string error = ""; InitDBFunctoidHelperList(); try { if (myDBFunctoidHelperList.Contains(index)) { DBFunctoidHelper helper = (DBFunctoidHelper) myDBFunctoidHelperList[index]; if (helper != null) { error = helper.Error; } } } catch (Exception) { } if (error == null) { error = ""; } return error; }
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Format Message
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v9" select="ScriptNS1:FormatMessage("" , "")" />
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Get Application ID
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v5" select="ScriptNS1:GetAppID("" , "" , "")" />
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Get Application Value
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v4" select="ScriptNS1:GetAppValue("" , "" , "")" />
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Get Common ID
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v8" select="ScriptNS1:GetCommonID("" , "" , "")" />
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Get Common Value
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v3" select="ScriptNS1:GetCommonValue("" , "" , "")" />
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Remove Application ID
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v6" select="ScriptNS1:RemoveAppID("" , "" , "")" />
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Set Common ID
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: <xsl:variable name="var:v7" select="ScriptNS1:SetCommonID("" , "" , "")" />
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Value Extractor
	Generates: XSLT calling a DLL	Has XSLT Equivalent: No
	Emitted Code: xsl:variable name="var:v2" select="ScriptNS0:DBValueExtract(string($var:v1) , string(Field/text()))" /> public string DBValueExtract(int index, string columnName) { string str = ""; InitDBFunctoidHelperList(); if (myDBFunctoidHelperList.Contains(index)) { DBFunctoidHelper helper = (DBFunctoidHelper) myDBFunctoidHelperList[index]; columnName = columnName.ToLower(CultureInfo.InvariantCulture); object obj2 = helper.MapValues[columnName]; if (obj2 != null) { str = obj2.ToString(); } } return str; }
	XSLT 1.0 Equivalent: (none)
	XSLT 2.0 Equivalent: (none)

	Common Code (this is common code used by all the database functoids)
	<xsl:variable name="var:v3" select="ScriptNS0:DBLookupShutdown()" /> public string DBLookupShutdown() { return string.Empty; } private class DBFunctoidHelper { private string column; private OleDbConnection conn = new OleDbConnection(); private string connectionString; private string error; private Hashtable mapValues = new Hashtable(); private string table; private string value; public string Column { get { return this.column; } set { this.column = value; } } public OleDbConnection Connection { get { return this.conn; } } public string ConnectionString { get { return this.connectionString; } set { this.connectionString = value; } } public string Error { get { return this.error; } set { this.error = value; } } public Hashtable MapValues { get { return this.mapValues; } } public string Table { get { return this.table; } set { this.table = value; } } public string Value { get { return this.value; } set { this.value = value; } } }

Monday, February 18, 2008 3:20:59 PM (GMT Standard Time, UTC+00:00)

Comments are closed.