Compression of XML file, FTP upload and SQL Server SqlBulkInsert



This article describes one approach to upload data from a backend database to a frontend SQL database hosted on a web server using
FtpWebRequest, GZipStream and SqlBulkInsert classes in .NET. The solution consists of two different parts; one application running on one machine; for fetching the data from the backend database, compressing it and then sending the data to the server using FtpWebRequest.  This could be a console application running on AT commands. The other application, running on the receiving server is in this case a Windows Service. This handles new files posted to the server, importing them using SqlBulkInsert to the SQL server table.

The basic idea is that the SQL statement fetching the data from the back end database meets a corresponding SQL Server datatable "column wise" and the datatable in the xml file needs to have the same name as the SQL Server table.

Fetching data and writing the XML file

We use a simple table, just illustrating the creation of the XML file being compressed and sent to the server:

// The current execution path is the location for the files
string appDir = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
appDir = appDir.Replace("file:\\", "");


string c = "Data Source=the_local_SQL_server;Initial Catalog=the_db_itself;Integrated Security=SSPI;";

using (SqlConnection conn = new SqlConnection(c))
{
   string cmd = "SELECT customerID, customerName from customers as customers";
   SqlDataAdapter daImport = new SqlDataAdapter(cmd, conn);

   using (DataSet ds = DataSet())

   {
       sqlConn.Open();

       daImport.Fill(ds, "customers");

       sqlConn.Close();
       ds.Tables[0].WriteXml(appDir + "\\" + ds.Tables[0].TableName + ".xml");
   }
}

This SQL statement meets a table having two columns; customerID and customerName on the SQL Server.
Okay, now we have the customer.xml file in the current application folder, and now we want to compress it and send it using
FtpWebRequest.

First, compress the file

foreach (string filePath in Directory.GetFiles(appDir, "*.xml"))

{
   FileInfo f = new FileInfo(filePath);
   //Rename the remote file so that we know it's a compressed file

   string shortfilename = f.FileName.Replace(".xml", ".gzipxml");

   //Read the original xml file into a filestream
   using (FileStream fsOrg = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))

   {
      //Create a new filestream for the compressed file

      using (FileStream fsNew = new FileStream(appDir + "\\" + shortfilename, FileMode.Create, FileAccess.ReadWrite))

      {

         byte[] buffer = new byte[fsOrg.Length];
         int count = fsOrg.Read(buffer, 0, buffer.Length);
         fsOrg.Close();
         //Create a gzipstream from the new filestream
         using (GZipStream fsZip = new GZipStream(fsNew, CompressionMode.Compress))
         {
            fsZip.Write(buffer, 0, buffer.Length
            fsZip.Close();
         }
      }

   }

   //Delete the original xml file

   File.Delete(filePath);

}

The directory now contains of a compressed XML file named customers with extension gzipxml.

Create the request:

FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://www.microsoft.com/imp/tmp/" + remoteFileName);

request.KeepAlive = false;
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential("bill", "gates");


This code snippet creates the request against the remote FTP server and as we normally don't use anonymous access, we fabricate the
NetworkCredential with user name and password for a famous person.

Writing the
FileStream to the server

using (FileStream fs = new FileStream(appDir + "\\" + shortfilename, FileMode.Open, FileAccess.Read, FileShare.Read))
{
   byte[] fileContents = new byte[fs.Length];
   fs.Read(fileContents, 0, fileContents.Length);
   request.ContentLength = fileContents.Length;


   using
(Stream requestStream = request.GetRequestStream())

   {

      requestStream.Write(fileContents, 0, fileContents.Length);
      requestStream.Close();

   }

   fs.Close();

   fileContents = null;
}

FtpWebResponse response = (FtpWebResponse)request.GetResponse();

This places the file in the tmp folder as stated above and we want to move it from tmp to the import folder, making it available for insertion into SQL server table customers.

request = null;

request = (FtpWebRequest)WebRequest.Create("ftp://www.microsoft.com/imp/" + shortfilename);

request.KeepAlive = false;

request.Method = System.Net.WebRequestMethods.Ftp.Rename;

request.Credentials = new NetworkCredential("bill", "gates");

request.RenameTo = @"..\" + shortfilename;

response = (FtpWebResponse)request.GetResponse();

Okay, that's it. Now we placed to compressed xml (gzipxml) file in the import folder on the FTP server.

Decompressing and inserting the xml data into SQL Server table.

The application on the server is a Windows service that in its OnStart method starts a timer which has an
ElapsedEventHandler checking the import folder for new files.

private
System.Timers.Timer tmrCheckForFiles;

protected override void OnStart(string[] args)
{
   // Start looking for files
  
tmrCheckForFiles = new System.Timers.Timer(30000);
   tmrCheckForFiles.Elapsed += new
   System.Timers.ElapsedEventHandler(checkForFiles);
   tmrCheckForFiles.Start();

}

The method checkForFiles looks in the import folder and enumerates thru all of the files, decompressing them if they are zipped and inserts them to the corresponding SQL Server datatable.

First, we need to decompress the gzipxml files.

foreach (string f in Directory.GetFiles(localPath))

{

   if (f.IndexOf(".gzipxml") != -1)

   {
      FileInfo fi = new FileInfo(f);
      string new_file = fi.Filename.Replace(".xml",".gzipxml");

      using (FileStream fsOrg = new FileStream(f, FileMode.Open, FileAccess.Read, FileShare.Read))

      {

         using (FileStream fsNew = new FileStream(localPath + "\\" + new_file, FileMode.Create, FileAccess.Write, FileShare.Read))

         {

            using (GZipStream fsZip = new GZipStream(fsOrg, CompressionMode.Decompress, true))

            {

                const int buffersize = 4096;

                byte[] buffer = new byte[buffersize];

                int count = 0;

                while (true)

                {

                   count = fsZip.Read(buffer, 0, buffersize);

                   if (count != 0)

                   {

                      fsNew.Write(buffer, 0, count);

                   }

                   if (count != buffersize)

                   { break; }

                }

                fsZip.Close();

            }

            fsNew.Close();

            fsOrg.Close();

         }

      }
      File.Delete(f);
   }


Now we have the xml file back to its original format, ready for insertion to SQL Server table.

Reading the xml file and inserting it

foreach
(string xml_file in Directory.GetFiles(localPath))

{
   using (System.Data.DataSet dsImport = new DataSet())

   {
      string c = "here goes your connectionstring to your SQL Server";

      // Read in the file to memory

      dsImport.ReadXml(xml_file);
      // This method truncates the receiving table before insertion
      // This is left out to shorten the example.

      clearTable(dsImport.Tables[0].TableName);

 

      if (dsImport.Tables[0].Rows.Count != 0)
      {
         System.Data.SqlClient.SqlBulkCopy sqlBulk = new
        
System.Data.SqlClient.SqlBulkCopy(c,
         SqlBulkCopyOptions.TableLock);
         // Just make sure that the datatable in the xml file
         // has the same name as the receiving table

         sqlBulk.DestinationTableName = dsImport.Tables[0].TableName;

         sqlBulk.WriteToServer(dsImport.Tables[0]);

         sqlBulk.Close();
      }

      // Delete the xml file
     
File.Delete(FileName);
}


That's it!

Please comment and give your thoughts and ideas to improve this solution, thanks!