title: C# SQLXMLBulkLoad4Class - followed by SQLConnection in a loop crashes
body: <p>I have a block of code that workes perfectly even in a loop.
  This block will connect, grab an xml file, and load it into the db, and if in a loop will do this for all xml files in the source directory.</p>
  
  <pre><code>[STAThread]
  static void BulkLoad(string file, string schemaFile, string tempFile, string connString) //Bulk Loads the XML into SQL - process needs to complete before any other datamanipulation
      {
              // Using the class to use SQL XML Bulk Load (version 4.0)
              SQLXMLBulkLoad4Class objBL = new SQLXMLBulkLoad4Class();
              // ConnString - from Form - format is different as it uses the SQLXMLBulkLoad Class
              //string Connstring = this.ConnString.Text;
              objBL.ConnectionString = (connString);
  
              //Error Log File if needed
              objBL.ErrorLogFile = "errorLog.xml";
              objBL.KeepIdentity = false;  
              objBL.ForceTableLock = true;
              objBL.CheckConstraints = false;
              objBL.XMLFragment = false;
              objBL.Execute((schemaFile), (tempFile)); 
      }
  </code></pre>
  
  <p>My challenge is that I then need to work on the newly imported data.  So, as part of the same loop I call other blocks for example:</p>
  
  <pre><code>public void BulkCopyNonRegulated()
      {
          // Create source connection
          // ConnStr - is the Main Database
          string sourceConnStr = this.ConnStr.Text;
          SqlConnection source = new SqlConnection(sourceConnStr);
          // Create destination connection
          // destConnStr - destination database
          string destConnStr = this.NonRegConStr.Text;
          SqlConnection destination = new SqlConnection(destConnStr);
          // Open source and destination connections.
          source.Open();
          destination.Open();
          //cmd.ExecuteNonQuery();
          // Select data from Source table
          SqlCommand cmd = new SqlCommand("SelNonRegCommodities", source);
          //cmd = new SqlCommand("SELECT * FROM Products", source);
          // Execute reader
          SqlDataReader reader = cmd.ExecuteReader();
          // Create SqlBulkCopy
          SqlBulkCopy bulkData = new SqlBulkCopy(destination);
          // Set destination table name
          bulkData.DestinationTableName = "_TEMP";
          // Write data
          bulkData.WriteToServer(reader);
          // Close objects
          bulkData.Close();
          source.Close();
          }
  </code></pre>
  
  <p>Now this block also works - well the first time through the loop.  If this block is present the first xml file will load, and be altered by this block, then when the loop goes to start on the second file it will error out at the objBL.Execute with this error:</p>
  
  <pre><code>System.Runtime.InteropServices.COMException occurred
  Message=Error connecting to the data source.
  Source=Microsoft XML Bulkload for SQL Server
  ErrorCode=-2147467259
  StackTrace:
     at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.Execute(String bstrSchemaFile, Object vDataFile)
     at DataLoader.Imports.BulkLoad(String file) in C:\MyDocs\Visual Studio 2010\Projects\PathFinder2\PathFinder\Imports.cs:line 375
  InnerException: 
  </code></pre>
  
  <p>Then, I need to drop the database, and re-attach it in order to be able to access it.
  I thought that this error was coming from a connection being kept open, but I cannot find one.  I even added:
      destination.Close();
      GC.Collect();
      GC.WaitForPendingFinalizers();</p>
  
  <p>Thinking that this was what I was missing, but no such luck.<br>
  Can anyone give me a hint on what I am overlooking here?</p>
  
  <p>Thank you.</p>