Best design for a tool to extract an XML file from a database, to then upload it to a server

Question

I am creating a tool (a C# Windows form) to integrate data between different systems.

The tool triggers generation of an XML file (generated by PL/SQL on the Oracle database). It then stores it in an XMLType field of the database table. I want to then extract this file from the DB and upload it to a server.

The XML file could be up to 50MB in size and contain potentially sensitive personal data.

The tool app will run on hosts remote from both database server and destination (upload) server. It will allow the user to choose different types of XML data to produce, and different destination servers to upload to.

It would also be theoretically possible to upload the file directly from the database server directly to the destination. Thus, I could create the tool to simply co-ordinate this, without transferring the file to the host that runs the tool. Having said that, I imagine I would still have to write the file to the file-system of the database server, before uploading it. This just shifts the file write-out to the DB server filesystem, from the host running the tool.

In a nutshell: File X is generated by database server A, and needs to be transferred to server B for processing. The tool will co-ordinate both the generation and file transfer of X, from A to B.

So, the tool will:

1. Trigger the Oracle database process to generate some XML via stored DB procedures. (This XML generation process is already working on Oracle)
2. Extract the newly generated XML from the DB table where it has been deposited by Oracle.
3. Transfer this XML file (using HTTPS) to another server where it will be subsequently processed.

Would it be better to extract and dump the XML to a temp file on the local machine running the tool (the middle man), then upload this local file, or could the extraction and upload be done in one go: piping the XML output from the database server directly to its destination - thus saving the need for a temp file on the "middle man"?

What would be the best approach to facilitate this, and why? Could it be done without the need for temporary XML files?

Answer 1

You can do it in one go-

• Generate the XML from C#
• Store the generated XML data in a string variable (strXMLData)

• Write the string content to the file (.xml)

  System.IO.File.WriteAllText (@"D:\New Folder\yourfile.xml", strXMLData);

Answer 2

I dunno if it's cool to answer your own question, but here goes!

The solution boiled down to this class I created. It holds the XML feed which is retrieved from Oracle, in its constructor, and allows it to be uploaded. Perhaps someone will appreciate this example.

using System;
using System.IO;
using System.Net;
using System.Text;

namespace SelfGeneratedFeedData
{
    internal class DataFeed
    {
        private OracleAccess oa;

        public string FeedContainer { get; private set; }
        public string Filename { get; private set; }
        public int Seqno { get; private set; }

        public DataFeed(OracleAccess oa, string fn, int sn)
        {
            this.oa = oa;
            this.Seqno = sn;
            this.Filename = fn;
            this.FeedContainer = Encoding.UTF8.GetString(Encoding.UTF8.GetBytes(oa.getXmlFeed(fn, sn)));
        }

        public BBServerResponse UploadFeed(Uri uri, string un, string pw)
        {
            BBServerResponse resp = new BBServerResponse("Error", HttpStatusCode.InternalServerError);

            if (!String.IsNullOrEmpty(this.FeedContainer)
                && !String.IsNullOrEmpty(un))
            {
                try
                {
                    HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);

                    req.Method = "POST";
                    req.ContentType = "text/xml";
                    // Using HTTP v1.0 seems to be important for my server.
                    req.ProtocolVersion = HttpVersion.Version10;
                    req.KeepAlive = true;
                    req.Credentials = new NetworkCredential(un, pw);

                    using (var sw = new StreamWriter(req.GetRequestStream()))
                    {
                        sw.Write(FeedContainer);
                    }


                    using (var response = req.GetResponse())
                    {
                        Console.WriteLine(((HttpWebResponse)response).StatusDescription);

                        using (var sr = new StreamReader(response.GetResponseStream()))
                        {
                            resp = new BBServerResponse(sr.ReadToEnd(), ((HttpWebResponse)response).StatusCode);
                        }

                        if (((HttpWebResponse)response).StatusCode == HttpStatusCode.OK)
                            oa.addTimeStamp(Filename, Seqno);
                    }
                    //Tidy up
                    req.Abort();
                }
                catch (Exception ex)
                {
                    resp = new BBServerResponse(ex.Message.ToString(), HttpStatusCode.InternalServerError);
                }
            }
            return resp;
        }
    }
}