Understanding the BizTalk Mapper: Part 10 – Database Functoids

by | Feb 18, 2008 | BizTalk Community Blogs via Syndication | 0 comments

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:

  1. Whether XSLT or C# is emitted

  2. Whether an XSLT equivalent exists

  3. The XSLT or C# emitted by the functoid

  4. 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  

Note:

This is the tenth in a series of 13 posts about the BizTalk Mapper.
The other posts in this series are (links will become active as the posts become active):
Understanding
the BizTalk Mapper: Part 1 – Introduction

Understanding
the BizTalk Mapper: Part 2 – Functoids Overview

Understanding
the BizTalk Mapper: Part 3 – String Functoids

Understanding
the BizTalk Mapper: Part 4 – Mathematical Functoids

Understanding
the BizTalk Mapper: Part 5 – Logical Functoids

Understanding
the BizTalk Mapper: Part 6 – Date/Time Functoids

Understanding
the BizTalk Mapper: Part 7 – Conversion Functoids

Understanding
the BizTalk Mapper: Part 8 – Scientific Functoids

Understanding
the BizTalk Mapper: Part 9 – Cumulative Functoids

Understanding the BizTalk Mapper: Part 10 – Database Functoids

Understanding the BizTalk Mapper: Part 11 – Advanced Functoids
Understanding the BizTalk Mapper: Part 12 – Performance and Maintainability
Understanding the BizTalk Mapper: Part 13 – Is the Mapper the best choice for Transformation
in BizTalk?

When all of the above have been posted, the entire series will be downloadable as
a single Microsoft Word document, or Adobe PDF file.

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;

       
}

    }

}

Submit a Comment