Improve Data Exchange

                    city="Tempe" state="AZ"

                    zip="85244" country="USA" />

                         mobile="480 123-5555" />

Figure 1. The customers.xml document marks up customer data, including address and phone information. The Windows service I'll discuss later in this article will use the SQLGenerator class to parse the document and create SQL statements to insert the data into a database.

Figure 2 shows a portion of the CreateSQLStatement method, which demonstrates how you can use the XmlTextReader class (located within the System.Xml namespace) to parse the customers.xml document shown in Figure 1 and generate SQL INSERT statements dynamically. (The complete code is available in the download files associated with this article.) This code contains several comments to explain what the code is doing at each step.

public SQLInfo CreateSQLStatement(string xmlPath) {

    XmlTextReader reader = null;

    //Hashtable used to hold field names and values.

    //This data will be used to construct the SQL.

    Hashtable fieldNamesValues = new Hashtable();

    //StringBuilder holds multiple SQL statements.

    StringBuilder sqlStatements = new StringBuilder();

    bool error = false;

    //Create return object for this method.

    SQLInfo sqlInfo = new SQLInfo();

    try {

      reader = new XmlTextReader(xmlPath);

      //Read through XML tokens in the stream.

      while (reader.Read()) {

         //Stop parsing if problem is encountered.  

        if (!error) {   

          if (reader.NodeType == XmlNodeType.Element) {

            //Get the name of the XML token.

            switch (reader.Name.ToLower()) {

                case "customer":   //Handle customer node.

                  //Read customerID attribute.

                  if (reader.HasAttributes) {

                    string customerID =

                      reader.GetAttribute("id");

                    if (customerID != String.Empty) {

                      fieldNamesValues.Add("CustomerID","'"

                       + customerID + "'");

                    } else {

                      //We need customerID for database,

                      //so end process if it is empty.

                       sqlInfo.Status = false;

                      sqlInfo.StatusMessage =

                       "ID attrib. empty on cust. element";

                      sqlInfo.SQL = null;

                      error = true;

                    }

                   } else {  

                    //We need customerID for database,

                    //so end process.               

                    sqlInfo.Status = false;

                    sqlInfo.StatusMessage =

                     "No attributes on customer element";

                    sqlInfo.SQL = null;

                    error = true;

                  }

                  break;

                  //Other case stmts ommitted for brevity.

            } //End switch.

          } //XmlNodeType check.

        } else {

          break;

        }

        if (reader.NodeType == XmlNodeType.EndElement) {

          if (reader.Name.ToLower() == "customer") {

            string[] FVArray =

             AddSeparator(fieldNamesValues,',');

            string fields = FVArray[0];

            string fieldVals = FVArray[1];

            sqlStatements.Append("INSERT INTO Customers ("

              + fields + ") VALUES (" + fieldVals + ");");

            //Clear out ArrayLists.

            fieldNamesValues.Clear();

          }

        }

      } //End While.

      if (!error) {  

        sqlInfo.Status = true;

        sqlInfo.StatusMessage = String.Empty;

        sqlInfo.SQL = sqlStatements.ToString();

      }

      return sqlInfo;   //Return SQLInfo object from method.

    } catch (Exception exp) {

      sqlInfo.Status = false;

      sqlInfo.StatusMessage = exp.Message +

       "\n\n" + exp.StackTrace;

      sqlInfo.SQL = null;

      return sqlInfo;

    }

    finally { if (reader != null) reader.Close(); }

}

Figure 2. The XmlTextReader class contains several methods you can use to parse an XML document's elements and attributes in order to access data. The code you see in this listing looks for specific node names in the stream of XML tokens the XmlTextReader class creates. As different nodes are found, the names and associated values are added into a HashTable object used to construct one or more SQL INSERT statements.

In addition to parsing the customers.xml document, the SQLGenerator class handles executing SQL statements it generates against a database through its ExecuteNonQuery method. This method simply wraps database connection and command-object functionality (see Figure 3).

public SQLInfo ExecuteNonQuery(string sql) {

  string connStr =

   ConfigurationSettings.AppSettings["DBConnectionString"];

  SqlConnection conn = null;

  SQLInfo info = new SQLInfo();

  try {

    conn = new SqlConnection(connStr);

    SqlCommand cmd = new SqlCommand(sql,conn);

    conn.Open();

    cmd.ExecuteNonQuery();

    info.Status = true;

  }

  catch (Exception exp) {

    info.Status = false;

    info.StatusMessage = exp.Message;

  }

  finally {

    if (conn.State != ConnectionState.Closed) conn.Close();

  }

  return info;

}

Figure 3. The ExecuteNonQuery method handles executing SQL statements against a database. It wraps the SqlConnection and SqlCommand managed-provider classes.

Now that you've seen how XML documents can be parsed and moved into a database, I'll explain the class responsible for watching the xmlFeeds folder and calling the SQLGenerator class's methods.

Create a Wrapper Class

You can use the .NET platform in several ways to watch for changes to directories or files. Fortunately, the .NET platform provides a built-in class named FileSystemWatcher that handles these types of operations automatically with little effort on your part. When XML documents are dropped into the folder being watched, you can use the FileSystemWatcher class to detect the file and trigger the parsing process you saw in the previous section. To use the FileSystemWatcher class this way, either you assign the directory and filename you want to watch to the FileSystemWatcher class's Path or Filter properties, respectively, or you pass these values into one of its overloaded constructors:

public FileSystemWatcher(

   string path,     //the directory to watch

   string filter   //the file (or files) to watch for

);

Because the directory- and file-watching capabilities are called from within a Windows service, a custom class named XmlFileWatcher is created to handle these tasks. Here is this class' shell:

public class XmlFileWatcher {

    FileSystemWatcher watcher = null;

    string _directory;

    string _filter;

    public XmlFileWatcher(string directory,string filter) {

        _directory = directory;   //the directory to watch

        _filter = filter; //The file(s) to watch for

    }

}

The XmlFileWatcher class contains a method named StartXmlFileWatcher, which handles instantiating the FileSystemWatcher class, identifying the directory and file to watch, and hooking up events (such as the Created event) to event handlers. The Created event is fired any time a file is created in the folder the class is watching. FileSystemWatcher exposes several other events that aren't used in this application, such as Changed, Deleted, and Renamed. Figure 4 shows the code for the StartXmlFileWatcher method, as well as the event-handler method for the Created event.

public void StartXmlFileWatcher() {

   //WriteToLog() method available in article's

   //downloadable code.

   WriteToLog("Starting XmlImporter file watcher.");

   //If a directory is not specified, exit program.

   if (_directory == String.Empty || _directory == null) {

      WriteToLog("XmlImporter: No directory specified " +

      "to watch. Update the application config file");

      throw new Exception("No directory specified");

   }

   //Create a new FileSystemWatcher.

   watcher = new FileSystemWatcher(_directory,_filter);

   //Watch for changes in LastAccess and LastWrite times,

   //and the renaming of files or directories.

   watcher.NotifyFilter = NotifyFilters.LastAccess |

      NotifyFilters.LastWrite

      | NotifyFilters.FileName | NotifyFilters.DirectoryName;

   //Add event handler to see if a new file is created in

   //the directory being watched. The

   //FileSystemEventHandler delegate is used to hook up

   //Created to a method named File_OnChanged.

   watcher.Created +=

    new FileSystemEventHandler(File_OnChanged);

   //Begin watching for XML file...enable events.

   watcher.EnableRaisingEvents = true;

}

//Event Handler for Changed event. This will be called

//if a file named customers.xml is created in the

//directory being monitored.

public void File_OnChanged(object source,

 FileSystemEventArgs e) {

   //Pause briefly to allow FileSystemWatcher file handle

   //to be released.

   System.Threading.Thread.Sleep(1000);

   //Get path of file that was created.

   string filePath = e.FullPath;

   //Call XML import object to move XML into db.

   SQLGenerator gen = new SQLGenerator();

   SQLInfo info = gen.CreateSQLStatement(filePath);

   if (info.Status) { //Keep going if SQL generated OK.

      WriteToLog(filePath + " parsed successfully!");

      //Execute SQL statement against database.

      SQLInfo dbInfo = gen.ExecuteNonQuery(info.SQL);

      if (dbInfo.Status) {

         WriteToLog(filePath + " data updated " +

                 "successfully in database!");

      } else {

         WriteToLog(filePath + " not updated in db " +

                 "successfully.   Error: " +

                  dbInfo.StatusMessage);

      }

   } else {

      WriteToLog(filePath + " not parsed successfully. " +

       "Error: " + info.StatusMessage);

   }

   //Rename the file so we can refer to it if needed

   //in the future.

   File.Move(filePath,filePath + "." +

      Guid.NewGuid().ToString() + ".old");

}

Figure 4. The Windows service calls the StartXmlFileWatcher method. The service uses the StartXmlFileWatcher method to create a new FileSystemWatcher class, specify the directory and file to watch, and handle events thrown by the FileSystemWatcher class, such as the Created event.

Now that you've seen how you can use .NET to watch for XML documents and how you can handle events to trigger the parsing of the XML documents, I'll show you how to create a Windows service that leverages the SQLGenerator and XmlFileWatcher classes.

Create a Windows Service Class

Using .NET to create a Windows service is surprisingly simple, especially if you have access to Visual Studio .NET. Even if you don't have VS .NET installed, you can create a Windows service relatively quickly in a simple editor such as Notepad.

I named the Windows service class created for this article XmlImporter. It inherits functionality from a base class named ServiceBase, located within the System.ServiceProcess namespace. XmlImporter contains a main entry point for the service, the Main method; an overridden Dispose method used to clean up the service; and an overridden OnStart method called when the service is started. You also can override the OnStop, OnPause, OnContinue, and OnShutDown methods if you choose.

I chose to use VS .NET to create the XmlImporter Windows service class because it creates the different members I mentioned automatically. It does this by creating a new Windows service VS .NET project. This is the code VS .NET added into the Main method:

// The main entry point for the process.

static void Main() {

  System.ServiceProcess.ServiceBase[] ServicesToRun;

      //Identify XmlImporter class as service to run.

      //Multiple services could be identified here if needed

  ServicesToRun =

        new System.ServiceProcess.ServiceBase[]

         {new XmlImporter()};

      //Run XmlImporter service by calling Run() method

  System.ServiceProcess.ServiceBase.Run(ServicesToRun);

}

After the Main method is executed and the service is started through the Control Panel's Services manager (see your system's help file for information on starting and stopping services), the OnStart method is called. The XmlImporter service uses this method to instantiate the XmlFileWatcher class I mentioned earlier, which handles watching a given directory for XML file drops. Here's the OnStart method:

protected override void OnStart(string[] args) {

 //Get directory and file information from config file.

 string directory =

  ConfigurationSettings.AppSettings

    ["XmlWatchDirectory"];

 string file =

  ConfigurationSettings.AppSettings

    ["XmlWatchDirectoryFile"];

 //Fire up the XmlFileWatcher by passing in the

 //directory and file to watch for.

 watcher = new XmlFileWatcher(directory,file);

 watcher.StartXmlFileWatcher();

}

Notice that the directory and file to watch are located within an application-configuration file. This allows different directories and files to be watched without having to recompile and redeploy the Windows service.

After creating a Windows service class, an installer class that inherits from System.Configuration.Install.Installer also must be created to help with installing resources associated with the service. This class handles setting up important details such as the username and password (the security context) under which the service runs and the name by which the service will appear in Windows. For this application, the service name will be XmlImportService. If you need the service to run immediately after a reboot, you can specify that the service should run under the local System account. Because the XmlImporter service needs to run constantly, its installer (named XmlImportServiceProjectInstaller) is coded to run under this account.

Figure 5 shows the complete installer code the XmlImporter Windows service uses.

[RunInstaller(true)]

public class XmlImportServiceProjectInstaller :

  System.Configuration.Install.Installer {

    private System.ServiceProcess.ServiceProcessInstaller

      serviceProcessInstaller;

    private System.ServiceProcess.ServiceInstaller

      serviceInstaller;

    private System.ComponentModel.Container components = null;

    public XmlImportServiceProjectInstaller()  {

        InitializeComponent();

    }

    private void InitializeComponent() {

        this.serviceProcessInstaller = new

          System.ServiceProcess.ServiceProcessInstaller();

        this.serviceInstaller = new

          System.ServiceProcess.ServiceInstaller();

         //Specify that service will run under local System

        //account.

        this.serviceProcessInstaller.Account =

          System.ServiceProcess.ServiceAccount.LocalSystem;

        this.serviceProcessInstaller.Password = null;

        this.serviceProcessInstaller.Username = null;

        //Establish service name. This will show up in the

        //Services manager in the Control Panel.

        this.serviceInstaller.ServiceName =

         "XmlImportService";

        //Set service start-up type to Automatic.

        this.serviceInstaller.StartType =

          System.ServiceProcess.ServiceStartMode.Automatic;

        this.Installers.AddRange(

          new System.Configuration.Install.Installer[] {

               this.serviceProcessInstaller,

                 this.serviceInstaller});

    }

}

Figure 5. The XmlImportServiceProjectInstaller class is necessary to run the XmlImporter class as a Windows service. XmlImportServiceProjectInstaller contains functionality that allows a username, password, and service name to be set so you can install and run the service. The service name given to this application is XmlImportService.

Install the Windows Service

After compiling the SQLGenerator, XmlFileWatcher, XmlImporter, and XmlImportServiceProjectInstaller classes, an .exe file is generated. You cannot install the XmlImporter service directly into Windows by executing this file, however, even though you created an installer class for it. Instead, you must create a custom installer project (see the .NET SDK for details on doing this in VS .NET) or use a command-line utility named InstallUtil.exe. Using this utility is extremely straightforward. For example, to install a Windows service named yourWindowsService.exe, type this on the command line:

installutil yourWindowsService.exe

Uninstalling the service involves using the /u switch:

installutil /u yourWindowsService.exe

After you run this utility to install the XmlImporter service, it shows up in the Windows Services manager (see Figure 6). After you start the service, files dropped automatically into the xmlFeeds folder are processed using the SQLGenerator class discussed earlier.

Figure 6. After the XmlImporter service class is compiled and installed using InstallUtil.exe, it shows up in the Windows Service manager where you can start, stop, or pause it as needed.

Windows services provide an excellent way to encapsulate code for long-running applications that must run even if a user isn't logged into the system. In this article, you learned how several classes can be aggregated to create a Windows service that watches a folder for newly created XML files, parses those files, creates dynamic SQL statements, and executes the statements against a database. By writing this type of service, XML data exchange between different systems can become less labor-intensive.

The files referenced in this article are available for download.

Dan Wahlin received Microsoft's Most Valuable Professional award in the ASP.NET category. He is president of Wahlin Consulting and founded the XML for ASP.NET Developers Web site (http://www.XMLforASP.NET), which focuses on using XML and Web Services in Microsoft's .NET platform. He also is a corporate trainer and speaker, and he teaches XML and ASP.NET training courses around the United States. Dan co-authored Professional Windows DNA (Wrox) and wrote XML for ASP.NET Developers (Sams). E-mail Dan at mailto:[email protected].

What is a Windows Service?

The term Windows service was introduced with the release of the .NET platform and replaces the older term NT service, which you might have heard in the past. A Windows service is quite useful in situations where you need a program to run for a long time and start up automatically when a computer is rebooted (even if a user has not logged into the system).

Before .NET, creating Windows services was an involved process and required a labor-intensive language such as C++. By using the framework classes .NET provides, you now can create Windows services using intrinsic languages such as C# or VB .NET.

Examples of Windows services include performance-monitoring applications, servers, or XML document-parsing services. To see other types of services, locate the Services icon in your computer's Control Panel (Start | Settings | Control Panel | Administrative Tools | Services on Windows 2000) and view the different services available.

The Windows service I'll introduce you to in the main article watches a specific folder, named xmlFeeds, for XML documents that are dropped into the folder (through FTP, copy and paste, XCopy, etc.). The FileSystemWatcher class in the System.IO namespace watches the folder, and when XML files are dropped into the folder, FileSystemWatcher instantiates a custom class that uses the XmlTextReader class to parse the XML data.

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.