BizTalk has the ability to store cross reference (xref) values in the BizTalkMgmtDB. Usually, mapping functoids handle the conversion, but the following routines allow the same lookups from anywhere you can call C#.

<pre>

        /// <summary>
        /// This does the SQL lookup/conversion to the BizTalk XREF tables.
        /// </summary>
        /// <param name="appType"></param>
        /// <param name="valueXRefName"></param>
        /// <param name="strLookupValue"></param>
        /// <returns></returns>
        static string GetLookupCommonValue(string appType, string valueXRefName, string strLookupValue)
        {

            SqlConnection sqlConn = new SqlConnection(GetBizTalkDBConnectionString());

            sqlConn.Open();

            string query =
            "select commonValue from xref_ValueXRefData " +
            "   inner join xref_ValueXref on xref_ValueXref.valueXrefId = xref_ValueXRefData.valueXRefID " +
            "   inner join xref_AppType on xref_AppType.appTypeId = xref_ValueXRefData.appTypeID " +
            "   where valueXRefName = '" + valueXRefName + "'" +
            "   and appType = '" + appType + "'" +
            "   and appValue = '" + strLookupValue + "'";
            //Console.WriteLine("query=" + query);

            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = sqlConn;
            SqlDataReader rdr = cmd.ExecuteReader();

            bool gotRow = rdr.Read(); // only expecting one row - no reason to loop here
            if (!gotRow)
            {
                sqlConn.Close();
                throw new Exception("strLookupValue=" + strLookupValue + " not found.  (BizTalkMgmtDb) No value found for query=" + query);
            }
            string commonValue = (string)rdr["commonValue"];
            //Console.WriteLine("CommonValue=" + commonValue);
            sqlConn.Close();

            return commonValue;
        }

        static string GetLookupCommonValueByKeys(int valueXRefId, int appTypeId, string strLookupValue)
        {

            SqlConnection sqlConn = new SqlConnection(GetBizTalkDBConnectionString());

            sqlConn.Open();

            string query = "select commonValue from xref_ValueXRefData where valueXRefID = " + valueXRefId +
                " and appTypeID = " + appTypeId +
                " and appValue = '" + strLookupValue + "'";

            //Console.WriteLine("query=" + query);

            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = sqlConn;
            SqlDataReader rdr = cmd.ExecuteReader();

            bool gotRow = rdr.Read(); // only expecting one row - no reason to loop here
            if (!gotRow)
            {
                sqlConn.Close();
                throw new Exception("strLookupValue=" + strLookupValue + " not found.  (BizTalkMgmtDb) No value found for query=" + query);
            }
            string commonValue = (string)rdr["commonValue"];
            //Console.WriteLine("CommonValue=" + commonValue);
            sqlConn.Close();

            return commonValue;
        }

        /// <summary>
        /// xref tables are part of BizTalkMgmtDb.  Any system running BizTalk with have the connection string
        /// in the registry.  The following  common routine  finds that conn-string in the registry. 
        /// </summary>
        /// <returns></returns>
        private static string GetBizTalkDBConnectionString()
        {
            string connString =
                TFBIC.Common.BizTalk.Components.MapHelper.DynamicSQLConnectionString("BizTalkMgmtDb");
            //Console.WriteLine("ConnString=" + connString);

            // Above routine was changed so as not to include the OLEDB provider.
            //connString = connString.Replace("Provider=SQLOLEDB.1;", "");  // remove old OLEDB provider
            //Console.WriteLine("ConnString=" + connString);
            return connString;

        }
        // This was before we had two methods to get connection strings
        private static string GetBizTalkDBConnectionString2()
        {
            string connString =
                TFBIC.Common.BizTalk.Components.MapHelper.DynamicSQLConnectionStringOLEDB("BizTalkMgmtDb");
            //Console.WriteLine("ConnString=" + connString);

            connString = connString.Replace("Provider=SQLOLEDB.1;", "");  // remove old OLEDB provider
            //Console.WriteLine("ConnString=" + connString);
            return connString;

        }

</pre>