The DataImport Example

---

At this point we are ready to concentrate our attention on a practical example that will be useful to demonstrate what we have seen thus far. The DataImport example, included here, is a typical application that waits for files to arrive in a specific directory before importing them into a SQL Server database. The code for this application, as with the rest of the code in this book, can be found at the Apress web site. Below we outline the classes that will be used in this example:

• FileSystemWatcher: This allows developers to specify the directory to monitor and to raise an event when something changes (for example, a new file is created or removed). This class is contained in the System.IO namespace of the .NET Framework class library.

• TextWriterTraceListener: This implements our own tracing functionality.

• Thread: This, which you've seen many times before, allows us to start a new thread to import data into the database.

• Many classes from the SqlClient namespace necessary to manage the SQL Server database connection and update.

The first release of the DataImport application contains some logical errors that you will discover using tracing functionality. In that way you can have a good example about log (trace) files and their importance.

To learn more about the ADO.NET classes, please refer to Professional ADO.NET Programming (ISBN 1-86100-527-X), or ADO.NET Programmer's Reference (ISBN 1-86100-558-X).

The Code

Let's start analyzing the code of the DataImport example:

    using System;    using System.IO;    using System.Data;    using System.Data.SqlClient;    using System.Threading;    using System.Diagnostics;    namespace DataImport1    {      class DataImport      { 

First of all, we referenced all the necessary namespaces to use the FileSystemWatcher, Thread, and SQL Server classes:

    public static BooleanSwitch bs;    [STAThread]    static void Main(string[] args)    {    // Remove the default listener    Trace.Listeners.RemoveAt(0);    // Create and add the new listener    bs = new BooleanSwitch("DISwitch", "DataImport switch");    Trace.Listeners.Add(new TextWriterTraceListener(        new FileStream(@"C:\DataImport.log", FileMode.OpenOrCreate))); 

Then the code removes the default listener and creates a new TextWriterTraceListener object that points to C:\DataImport.log:

    // Create a FileSystemWatcher object used to monitor    // the specified directory    FileSystemWatcher fsw = new FileSystemWatcher();    // Set the path to watch and specify the file    // extension to monitor for    fsw.Path = @"C:\temp";    fsw.Filter = "*.xml";    // No need to go into subdirs    fsw.IncludeSubdirectories = false;    // Add the handler to manage the raised event    // when a new file is created    fsw.Created += new FileSystemEventHandler(OnFileCreated);    // Enable the object to raise the event    fsw.EnableRaisingEvents = true; 

Here the code creates a FileSystemWatcher object used to monitor the C:\temp directory specified in the Path property. The Filter property is useful to filter through each file within the directory looking for just the ones with the specified file extension. The IncludeSubdirectories property determines whether to extend the file monitoring to subdirectories. Next, we want to receive file creation events so we have to specify the Created event provided by the FileSystemWatcher class. Using the FileSystemEventHandler we can specify the event handler that will be called when a new XML file is created in the target directory. Finally, the code enables the FileSystemWatcher object to raise events.

    try    {      // Call the waitforchanged method in      // an infinite loop. When the event is raised      // the OnFileCreated will be contacted.      WaitForChangedResult res;      while(true)      {        res = fsw.WaitForChanged(WatcherChangeTypes.Created);        Trace.WriteLineIf(bs.Enabled, DateTime.Now +              " - Found: " + res.Name + " file");      }    } 

The above code implements an infinite loop, which waits for the file creation event to be raised. The WaitForChangedResult object will contain information about the file created. For example, the code uses the Name property to trace the name of the discovered file.

    catch (Exception e)    {      Trace.WriteLineIf(bs.Enabled, DateTime.Now +          " - An exception occurred while waiting for file: ");      Trace.Indent();      Trace.WriteLineIf(bs.Enabled, DateTime.Now + " - " + e.ToString());      Trace.Unindent();    }    finally    {      fsw.EnableRaisingEvents = false;      Trace.WriteLineIf(bs.Enabled, DateTime.Now +          " - Directory monitoring stopped");      Trace.Close();    } 

The above Main() method ends by tracing some useful messages and any exceptions. The OnFileCreated() static method is detailed below:

    private static void OnFileCreated(Object source,                                      FileSystemEventArgs e)    {      try      {        // Create a new object from the        // ImportData class to process the        // incoming file        ImportData id = new ImportData();        id.m strFileName = e.FullPath;    // Create and start the thread    Thread t = new Thread(new ThreadStart(id.Import));    t.Name = "DataImportThread";    t.Start(); 

Inside the OnFileCreated event handler a new thread will be started. This thread will use the Import() method of the custom ImportData class to import the XML file into the database. Since at this point we know the full path of the discovered file (the FileSystemEventArgs parameter contains this information) and since we need it even in the ImportData class, we can use the m_strFileName variable provided by the class:

    catch    {      Trace.WriteLineIf(bs.Enabled, DateTime.Now +      " - An exception occurred while queuing file : "); Trace.Indent();      Trace.WriteLineIf(bs.Enabled, DateTime.Now + " - " + e.ToString());      Trace.Unindent();    }    finally    {      Trace.Flush();    }    class ImportData    {      // Path and filename of the retrieved file      public string m strFileName = null;      public void Import()      {        // Declare Sql objects to contact the database        SqlConnection dbConn = new            SqlConnection("server=.;database=pubs;uid=sa;pwd=");        SqlDataAdapter da = new SqlDataAdapter(              "SELECT * FROM authors", dbConn);        DataSet ds = new DataSet();        SqlCommandBuilder sa = new SqlCommandBuilder(da); 

Inside the Import() method, the code starts by creating and setting all the necessary classes to contact the authors table within the SQL Server pubs database. The SqlConnection object allows us to specify database connection parameters. The SqlDataAdapter object connects to the database using the connection object executing the SQL statement specified as the first parameter. Finally, the SqlCommandBuilder examines the SQL statement specified in the SqlDataAdapter constructor, creating INSERT, MODIFY, and DELETE statements automatically. They are needed when we use the Update() method exposed by the SqlDataAdapter class to physically change the database with new information:

    try    {      Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +          " - Filling the DataSet.");      // Fill a dataset with data within the authors table      da.Fill(ds); 

Here the Fill() method from the SqlDataAdapter class is used to fill the DataSet object specified in its parameter, with the results of the SQL query specified earlier. The DataSet is an in-memory representation of the database data and so it will be formatted as the authors table and filled with every record contained in the table:

    // Read the XML file filling another dataset    DataSet dsMerge = new DataSet();    Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +        " - Reading XML file.");    dsMerge.ReadXml(m strFileName, XmlReadMode.InferSchema);    Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +        " - DataSet filled with data."); 

Here the code uses the discovered file to fill another DataSet object. This time the ReadXml() method has been used. The power of the DataSet object is just right in front of you. You can manage data provided by both database and XML document in exactly the same way. The DataSet object maintains an XML data representation of the records within itself:

    // Update the database tracing the    // total time needed to conclude the operation    DateTime time;    time = DateTime.Now;    Trace.WriteLineIf(DataImport.bs.Enabled, time +        " - Updating database.");    da.Update(dsMerge);    DateTime time2;    time2 = DateTime.Now;    Trace.WriteLineIf(DataImport.bs.Enabled, time2 +              " - Database updated successfully.");    Trace.Indent();    Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +        " - Total TIME: " + time2.Subtract(time) + " second/s");    Trace.Unindent(); 

Finally, the code uses the Update() method provided by the SqlDataAdapter class to write new records to the authors table. Note the tracing information used in this snippet of code; this provides detailed information by adding performance messages. The DateTime class has been used to retrieve the total time in seconds needed to update the database:

    catch (SqlException sex)    {      Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +          " - A SQL exception occurred during file processing: ");      Trace.Indent();      Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now          + " - " + sex.ToString());      Trace.Unindent();    }    catch (Exception ex)    {      Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +          " - A general exception occurred during file processing: ");      Trace.Indent();      Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +          " - " + ex.ToString());      Trace.Unindent();    }    finally    {      Trace.Flush();    } 

Then, after writing the code for catching and dealing with any exceptions that may occur, the code is complete.

Testing the Application

To test the application you have to follow these steps:

• Create a C:\temp directory to contain the XML file

• Run the DataImport application

• Copy the authors.xml file into the C:\temp directory

As a final result you should find the DataImport.log file in the C:\directory having content similar to this:

 01/05/2002 12:23:01 - Found: authors.xml file 01/05/2002 12:23:01 - Filling the DataSet. 01/05/2002 12:23:02 - Reading XML file. 01/05/2002 12:23:02 - DataSet filled with data. 01/05/2002 12:23:02 - Updating database. 01/05/2002 12:23:02 - Database updated successfully. 01/05/2002 12:23:03 - Total TIME: 0 second/s 

The authors.xml file is not that large so the total time is less than one second.

Logical Errors

All seems to be working well, but obviously, everything hasn't been accounted for. So far, we have tested our application with a very small file size, so when the application receives the file creation event and opens the file, the process that copies it into the directory finishes its task of closing the file. What happens when you receive a huge file? Well, when the thread tries to access the XML file and fill the DataSet object, it receives an access-denied error caused by attempting to open a file already in use by the copier task. Try to test the application again by copying the huge_authors.xml file instead. Since you have used tracing messages, you may find the following error in the log file:

 4/14/2002 1:29:00 PM - Found: huge_authors.xml file 4/14/2002 1:29:00 PM - Filling the DataSet. 4/14/2002 1:29:00 PM - Reading XML file. 4/14/2002 1:29:00 PM - A general exception occurred during file processing:    4/14/2002 1:29:00 PM - System.IO.IOException: The process cannot access the file "C:\temp\huge_authors.xml" because it is being used by another process.   at System.IO.__Error.WnIOError(Int32 errorCode, String str)   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, Boolean useAsync, String msgPath, Boolean bFromProxy)   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)   at System.Xml.XmlDownloadManager.GetStream(Uri uri, (Credentials credentials)   at System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn)   at System.Xml.XmlTextReader.CreateScanner()   at System.Xml.XmlTextReader.Init()   at System.Xml.XmlTextReader.Read()   at System.Xml.XmlReader.MoveToContent()   at System.Data.DataSet.ReadXml(XmlReader reader, XmlReadMode mode)   at System.Data.DataSet.ReadXml(String fileName, XmlReadMode mode) 

This is a kind of error that the debugger often fails to catch because the time used to launch it and the time to step through the code is often sufficient to copy the file. It may also not occur on your machine. It depends on the speed of your disk access and the amount of memory you have (so how much the application is slowed down).

The error message suggests a possible solution that you should add to the application to resolve the error. Before calling the ReadXml() method, you should try to open the file with exclusive access. If an error occurs, then you can suspend the thread for few seconds, trying again when the file can be processed. Let's see how the code changes in DataImport2, by adding the GetFileAccess() method:

    private bool GetFileAccess()    {      Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +          " - Trying to get exclusive access to the "          + m strFileName + " file.");      try      {        FileStream fs = File.Open(m strFileName,                                  FileMode.Append,                                  FileAccess.Write,                                  FileShare.None);        fs.Close();        Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +            " - Access to the " + m strFileName + " file allowed.");        return true;      }      catch      {        Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +            " - Access denied to the " + m strFileName + " file.");        return false;      }    } 

The GetFileAccess() method has been added in order to return a Boolean value indicating whether you can have exclusive access to the file or not. The method simply tries to open the file with the share access property set to None:

    public void Import()    {      // Declare Sql objects to contact the database      SqlConnection dbConn = new          SqlConnection("server=.;database=pubs;uid=sa;pwd=");      SqlDataAdapter da = new SqlDataAdapter(          "SELECT * FROM authors", dbConn);      DataSet ds = new DataSet();      SqlCommandBuilder sa = new SqlCommandBuilder(da);      try      {        while (GetFileAccess() == false)        {          Thread.Sleep(5000);          Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +              " - Slept 5 seconds... Try to access to the "              + m_strFileName + " file, again.");        }        Trace.WriteLineIf(DataImport.bs.Enabled, DateTime.Now +            " - Filling the DataSet.");        // Fill a dataset with data within the        // authors table        da.Fill(ds); 

The Import() method provided by the ImportData class will try to get exclusive access to the file. If the file is still opened by the copier task, the thread will be suspended for five seconds. So, the GetFileAccess() method will be called until the source file can be opened.

We have seen practically how the tracing functionalities can be useful to understand the application behavior during run-time execution.

---