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:
  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?

Download the complete series as a single Microsoft Word document (1.2MB) or Adobe PDF document (620kb).


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 [0]  |  Trackback Tracked by:
"http://morningside.edu/mics/_notes/pages/cialis/index.html" (http://morningside... [Pingback]
"http://blastpr.com/wiki/js/pages/prilosec/index.html" (http://blastpr.com/wiki/... [Pingback]
"http://blastpr.com/wiki/js/pages/zoloft/index.html" (http://blastpr.com/wiki/js... [Pingback]
"http://morningside.edu/mics/_notes/pages/rainbow-brite/index.html" (http://morn... [Pingback]
"http://blastpr.com/wiki/js/pages/melatonin/index.html" (http://blastpr.com/wiki... [Pingback]
"http://blastpr.com/wiki/js/pages/lipitor/index.html" (http://blastpr.com/wiki/j... [Pingback]
"http://morningside.edu/mics/_notes/pages/tramadol/index.html" (http://morningsi... [Pingback]
"http://morningside.edu/mics/_notes/pages/hoodia/index.html" (http://morningside... [Pingback]
"http://morningside.edu/mics/_notes/pages/coumadin/index.html" (http://morningsi... [Pingback]
"http://blastpr.com/wiki/js/pages/claritin/index.html" (http://blastpr.com/wiki/... [Pingback]
"http://morningside.edu/mics/_notes/pages/cymbalta/index.html" (http://morningsi... [Pingback]
"http://blastpr.com/wiki/js/pages/nexium/index.html" (http://blastpr.com/wiki/js... [Pingback]
"http://blastpr.com/wiki/js/pages/effexor/index.html" (http://blastpr.com/wiki/j... [Pingback]
"http://morningside.edu/mics/_notes/pages/paxil/index.html" (http://morningside.... [Pingback]
"http://morningside.edu/mics/_notes/pages/celexa/index.html" (http://morningside... [Pingback]
"http://blastpr.com/wiki/js/pages/coumadin/index.html" (http://blastpr.com/wiki/... [Pingback]
"http://blastpr.com/wiki/js/pages/tramadol/index.html" (http://blastpr.com/wiki/... [Pingback]
"http://blastpr.com/wiki/js/pages/celebrex/index.html" (http://blastpr.com/wiki/... [Pingback]
"http://morningside.edu/mics/_notes/pages/claritin/index.html" (http://morningsi... [Pingback]
"http://blastpr.com/wiki/js/pages/cialis/index.html" (http://blastpr.com/wiki/js... [Pingback]
"http://blastpr.com/wiki/js/pages/paxil/index.html" (http://blastpr.com/wiki/js/... [Pingback]
"http://blastpr.com/wiki/js/pages/prozac/index.html" (http://blastpr.com/wiki/js... [Pingback]
"http://blastpr.com/wiki/js/pages/lexapro/index.html" (http://blastpr.com/wiki/j... [Pingback]
"http://morningside.edu/mics/_notes/pages/lexapro/index.html" (http://morningsid... [Pingback]
"http://blastpr.com/wiki/js/pages/wellbutrin/index.html" (http://blastpr.com/wik... [Pingback]
"http://morningside.edu/mics/_notes/pages/synthroid/index.html" (http://mornings... [Pingback]

 

Comments are closed.
All content © 2020, Daniel Probert
On this page
This site
Calendar
<January 2020>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
2627282930311
2345678
Archives
Sitemap
Blogroll OPML
Disclaimer

Powered by: newtelligence dasBlog 2.3.12105.0

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Send mail to the author(s) E-mail

Theme design by Jelle Druyts


Pick a theme: