I had this case again today, where I got “Root element is missing”. The code was working, I made some changes, and boom, an existing code path started blowing up. It was actually a trace routine, where I had the following code:

   xmlDoc = msgWhatever;
   //then I passed as a parameter:  
   CallCSharpMethod(xmlDoc.OuterXml)... 

So here is the code I added, above the section that bombed:

   // Avoid compile erorr "Use of unconstructed message 'msgCanonicalErr'
   // in the common error handler after the end of the big Decide Block
   xmlDoc.LoadXml("<Dummy />");
   msgCanonicalErr = xmlDoc;

The simple fix was to create another xmlDoc variable with another name:

   // Avoid compile erorr "Use of unconstructed message 'msgCanonicalErr'
   // in the common error handler after the end of the big Decide Block
   xmlDocInitializer.LoadXml("&lt;Dummy /&gt;");
   msgCanonicalErr = xmlDocInitializer;

I think another solution would have been to reset the xmlDocument variable, but I preferred the above approach:

   xmlDoc = new System.XmlDocument(); 

Sometimes, you might need to get the SQL Server Name of the SQL Server where the BizTalkMgmtDB

The C# code below, uses the registry to get that server name, and also builds a connection screen to some database on that server (where you pass the Database Name as a parameter). MgmtDBServer (in the rk.GetValue below), referes to the BizTalkMgtmDB.

        public static string DynamicSQLConnectionStringOLEDB(string DBName)
        {
            try
            {
                Microsoft.Win32.RegistryKey rk = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\BizTalk Server\\3.0\\Administration", false);
                string ConnString = 
                  "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + 
                  DBName + ";Data Source=" + rk.GetValue("MgmtDBServer").ToString();
                return ConnString;
            }
            catch
            {
                return "";
            }
        }

Here’s one example how this could be used. Suppose you have a database that contains lookup tables, and that database is on the same SQL server as the BizTalkMgmtDB database. The Scripting Functoid below calls the C# to get the connection string, and becomes the second parameter to the Database Lookup Functoid.

GetSQLServerForBizTalkFunction

The scripting functoid is setup as shown in the picture below. First, make sure you reference the .net assembly created from the C# code above. Then select that in “Script Assembly”, and pick the class in “Script Class”.

ScriptingFunctoidSQLServer

Related blogs:

Keith Lim

MSDN Restoring the BizTalk Configuration Database

Xref Database Routines

 

There was a typo below, the first time is using LINQ, the second one is using Directory.GetFiles (then getting the time on each file within a loop). Big Difference, right? The last one is 41 times slower. The difference was not really using LINQ, but using the DirectoryInfo.GetFiles instead of the combination of Directory.GetFiles and then using File.GetCreationTime inside the loop.

DirInfo_SpeedUp_Stats_3

I was using the third method, and was getting so bored and tired of waiting on it to run when dealing with a file with about 5000 files in it, out of which about 450 matched my file mask. I knew it shouldn’t take that long to enumerate through a directory. So I did some searching and found this question on Stackoverflow.

 

The code below will benchmark all three methods:
1) LINQ (which uses DirectoryInfo.GetFiles)
2) DirectoryInfo.GetFiles
3) Directory.GetFiles and File.GetCreationTime

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO; 

namespace DirGetFilesSpeedTest
{
    class Program
    {
        static void Main(string[] args)
        {

            DateTime somedate = DateTime.Now.Subtract(new TimeSpan(24, 0, 0));
            string folderName = @"\\MyServer\Biztalk_Messages\Archive\856_EDI";

            DateTime startDateTime = DateTime.Now; 
            Console.WriteLine("Start LINQ query at " + DateTime.Now);
            DirectoryInfo dirInfo = new DirectoryInfo(folderName); 
            int countMatch = 0;
            var filterFiles = from file in dirInfo.GetFiles()
                              where file.CreationTime &gt; somedate
                              select file;
            DateTime stopDateTime = DateTime.Now; 
            TimeSpan tsElapsed  = stopDateTime.Subtract(startDateTime);
            foreach (var x in filterFiles)
            {
                countMatch++; 
            }


            Console.WriteLine("CountMatching Files=" + countMatch);
            Console.WriteLine("Stop LINQ query at " + stopDateTime + " Duration = " + tsElapsed.TotalSeconds);

            startDateTime = DateTime.Now;
            FileInfo[] fileInfoArray = dirInfo.GetFiles("*.*");
            countMatch = 0;
            foreach (FileInfo fileInfo in fileInfoArray)
            {
                DateTime fileDateTime = fileInfo.CreationTime; 
                TimeSpan ts1 = DateTime.Now.Subtract(fileDateTime);
                if (ts1.TotalSeconds &lt; 24 * 60 * 60)
                {
                    countMatch++;
                }
            }

            Console.WriteLine("CountMatching Files=" + countMatch);
            Console.WriteLine("Stop DirInfo loop  " + stopDateTime + " Duration = " + tsElapsed.TotalSeconds);

            
            startDateTime = DateTime.Now;
            string[] filenameArray = Directory.GetFiles(folderName, "*.*");
            countMatch = 0; 
            foreach (string filename in filenameArray)
            {
                DateTime fileDateTime = File.GetCreationTime(filename);
                TimeSpan ts1 = DateTime.Now.Subtract(fileDateTime);
                if (ts1.TotalSeconds &lt; 24 * 60 * 60)
                {
                    countMatch++; 
                }
            }


            Console.WriteLine("CountMatching Files=" + countMatch);
            stopDateTime = DateTime.Now;
            tsElapsed = stopDateTime.Subtract(startDateTime);
            Console.WriteLine("Stop LINQ query at " + stopDateTime + " Duration = " + tsElapsed.TotalSeconds);
             


            Console.WriteLine("\n\nPress enter to end:"); 
            Console.ReadLine(); 

        }
    }
}

I got this error: ORA-01843 Oracle date format picture ends before converting entire input string

The variable fileDateTime was a defined as a normal C# DateTime.

Code Failed with ORA=01843

                    commSQL.CommandText = "UPDATE MY_TAB " + 
                        " SET DATE_FILE_PROCESSED = TO_DATE('" + fileDateTime + "', 'MM/DD/YYYY HH:MI:SS')" +  
                        " WHERE DATE_FILE_PROCESSED IS NULL AND CUST_PO_NO = '" + PONum + "'";  

Above generated this SQL Update – Notice the “AM” on the end of the date time:

UPDATE MY_TAB  SET DATE_FILE_PROCESSED = TO_DATE('9/17/2015 8:01:51 AM', 'MM/DD/YYYY HH:MI:SS') 
WHERE DATE_FILE_PROCESSED IS NULL AND CUST_PO_NO = '118686'

Corrected Code Works

Note that I changed Oracle date from HH to HH24.

                    commSQL.CommandText = "UPDATE MY_TAB " + 
                        " SET DATE_FILE_PROCESSED = TO_DATE('" + fileDateTime.ToString("MM/dd/yyyy HH:mm:ss") + "', 'MM/DD/YYYY HH24:MI:SS')" +  
                        " WHERE DATE_FILE_PROCESSED IS NULL AND CUST_PO_NO = '" + PONum + "'";  

UPDATE MY_TAB  SET DATE_FILE_PROCESSED = TO_DATE('09/17/2015 08:01:53', 'MM/DD/YYYY HH24:MI:SS') 
WHERE DATE_FILE_PROCESSED IS NULL AND CUST_PO_NO = '118690'

References: Microsoft Dater Formats   Oracle TO_DATE function

 

 

I’m in the process of writing a C# program that will validate field sizes (and maybe data types) against a SQL table. I need to traverse through each element and get the element value, the next step will be to validate it each element value.

The code below will “walk the tree” structure of the XML. Attributes are ignored in this program. The variable “indent” is used to indent each level of the XML as it is being processed. This program has a method TraverXmlNode that recursively calls itself to read the XML through unlimited levels of depth.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;

namespace FieldSizeChecker 
{
    class Program
    {
        static void Main(string[] args)
        {
            string xmlFilename = "CanonicalSample1.xml";
            Console.WriteLine("\n\nStart XML Parser");
            ValidateXMLFieldValues(xmlFilename, objTable); 

            Console.WriteLine("\n\nPress enter to continue:");
            Console.ReadLine(); 

        }

        static void ValidateXMLFieldValues(string xmlFilename, SQLParserLibrary.Table objTable)
        {
            XmlDocument xmlDoc = new XmlDocument(); 
            xmlDoc.Load(xmlFilename);
            int depth = 0; 
            TraverseXMLNode(xmlDoc.DocumentElement, depth); 
        }

        
        static void TraverseXMLNode(XmlNode xmlNode, int depth)
        {
            //Loop through results
            depth = depth + 1;
            string indent = new String(' ', depth);
            if (xmlNode.NodeType == XmlNodeType.Element)
            {
                XmlElement element = (XmlElement)xmlNode;
                Console.WriteLine(indent + " elementName=" + element.Name + " Value=" + getElementTextValue(element))
                    ;
            }


            foreach (XmlNode childNode in xmlNode.ChildNodes)
            {
                TraverseXMLNode(childNode, depth);
            }

        }

        static string getElementTextValue(XmlElement element)
        {
            XmlNode firstChildNode = element.FirstChild; 
            if (firstChildNode != null)
            {
                return firstChildNode.Value;
            }
            else
            {
                return ""; 
            }
        }

    }

}