I have a FileSystemWatcher, that moves excel data to a database when ever an excel sheet is made. But it keeps closing on the second iteration, and I can't understand why. Can someone please help. Here is the code.

private void StartWatcher()
        {
            // Create a new FileSystemWatcher and set its properties.
            FileSystemWatcher watcher = new FileSystemWatcher();
            watcher.Path = path;
            /* 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;
            // Only watch text files.
            watcher.Filter = "*.xls";

            // Add event handler.
            watcher.Created += new FileSystemEventHandler(OnChanged);

            // Begin watching.
            watcher.EnableRaisingEvents = true;
        }
private void OnChanged(object source, FileSystemEventArgs e)
        {
            // Specify what is done when a file is changed, created, or deleted.
            // Console.WriteLine("File: " + e.FullPath + " " + e.ChangeType);
            StreamWriter te = new StreamWriter("fileList.txt");
            te.WriteLine(e.FullPath.ToString());
            te.Close();
            CreateIncidents(mX, dX);

           // txtOutput.AppendText(e.Name);
        }
void CreateIncidents(IncidentsObjectsDataContext crt, IncidentsObjectsDataContext art)
        {
            string addr, crx;
            StreamReader kt = new StreamReader("fileList.txt");
            string xl = kt.ReadLine();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(xl, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
            int index = 0;
            int rowIndex = 18;
            DateTime dt5 = DateTime.FromOADate(0.0);
            string temp5;

            while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)
            {
                temp5 = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                //Converts OADatetime to DateTime
                addr = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                crx = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);

                try
                {
                    Double rogue = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2;
                    dt5 = DateTime.FromOADate(Convert.ToDouble(rogue));

                    addr = addr.Replace("BLK", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty);
                    crx = crx.Replace("BLK", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty);

                    incident newInc = new incident();
                    newInc.Agency = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                    newInc.Intersection = addr + "& " + crx;
                    newInc.CallTime = dt5;
                    newInc.IncidentType = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                    dX.incidents.InsertOnSubmit(newInc);
                    dX.SubmitChanges();
                    index++;
                    rowIndex = 18 + index;
                }
                catch
                {
                    try
                    {
                        Double rogue = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2;
                        dt5 = DateTime.FromOADate(Convert.ToDouble(rogue)); joinedResp newJR = new joinedResp();
                        newJR.Agency = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                        newJR.Address = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                        newJR.CrossStreet = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
                        newJR.IncidentType = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                        mX.joinedResps.InsertOnSubmit(newJR);
                        mX.SubmitChanges();
                        index++;
                        rowIndex = 18 + index;
                    }
                    catch
                    {
                        index++;
                        rowIndex = 18 + index;
                    }
                }
            }
          //  workBook = null;
          //  app = null;
          //  workBook.Close();
          //  app.Quit();
        }
public void button1_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog fd = new FolderBrowserDialog();
            fd.ShowDialog();
            path = fd.SelectedPath;
            StartWatcher();
        }

Couple of quick questions to clarify here if possible...

  1. When you say it's closing after 2nd iteration are you talking about the FileSystemWatcher, the DB connection or something else?
  2. Define iterations in this case... are we talking about the button1_Click event which triggers the StartWatcher() procedure?

I'm asking only because as I'm seeing it right now the following is happening (based on the code provided).

  • button1 is clicked
  • folder path is selected
  • StartWatcher is called
  • watcher is set to monitor selected path for *.xls file changes
  • streamwriter is created on file change to write the changed files to a list
  • incident event generated for changed file list
  • Excel workbook opened based on filename in list
  • Somewhere in code that is not provided above a database is updated with information from the excel sheet

Throughout this I'm not seeing anything that calls for the FileSystemWatcher to close, however, is it possible that it is closing from a default timeout period being reached?

Also, as the DB connection information was not provided above I am not sure how the connection opening/closing is being handled or how the information is specifically being passed based on the dX and mX variables indicated above so I can't comment on whether or not there might be an issue there at all.

Don't know if this will help at all but more information on the specifics of what is happening and where would probably help solve this :)

It detects the first file created then processes it. Thens go back to watching. When the next is detected it closes the app(window form) before doing any work. No error returned just closed.

Below is the full program, so that u can understand how it works.

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System;
using System.Data.Linq.Mapping;
using System.Collections;
using System.Runtime.Serialization.Formatters.Binary;
using System.ComponentModel.Composition;
using Microsoft.Office.Interop.Excel;
using System.Security.Permissions;
using System.Diagnostics;
using System.IO;

namespace pSQL
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            LiveWorker.WorkerReportsProgress = true;
        }
        string path = null;
        const string cnStr =
            @"Data Source = (local)\MSSQLSERVER;Initial Catalog =IncidentsDb;" +
            "Integrated Security =True";
        string pathDir = null;
        IncidentsObjectsDataContext mX = new IncidentsObjectsDataContext();
        IncidentsObjectsDataContext dX = new IncidentsObjectsDataContext();
        List<string> exlList = new List<string>();
        [PermissionSet(SecurityAction.Demand, Name = "FullTrust")]

        void CreateIncidents(IncidentsObjectsDataContext crt, IncidentsObjectsDataContext art)
        {
            string addr, crx;
            StreamReader kt = new StreamReader("fileList.txt");
            string xl = kt.ReadLine();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(xl, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
            int index = 0;
            int rowIndex = 18;
            DateTime dt5 = DateTime.FromOADate(0.0);
            string temp5;

            while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)
            {
                temp5 = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                //Converts OADatetime to DateTime
                addr = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                crx = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);

                try
                {
                    Double rogue = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2;
                    dt5 = DateTime.FromOADate(Convert.ToDouble(rogue));

                    addr = addr.Replace("BLK", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty);
                    crx = crx.Replace("BLK", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty);

                    incident newInc = new incident();
                    newInc.Agency = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                    newInc.Intersection = addr + "& " + crx;
                    newInc.CallTime = dt5;
                    newInc.IncidentType = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                    dX.incidents.InsertOnSubmit(newInc);
                    dX.SubmitChanges();
                    index++;
                    rowIndex = 18 + index;
                }
                catch
                {
                    try
                    {
                        Double rogue = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2;
                        dt5 = DateTime.FromOADate(Convert.ToDouble(rogue)); joinedResp newJR = new joinedResp();
                        newJR.Agency = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                        newJR.Address = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                        newJR.CrossStreet = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
                        newJR.IncidentType = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                        mX.joinedResps.InsertOnSubmit(newJR);
                        mX.SubmitChanges();
                        index++;
                        rowIndex = 18 + index;
                    }
                    catch
                    {
                        index++;
                        rowIndex = 18 + index;
                    }
                }
            }
          //  workBook = null;
          //  app = null;
          //  workBook.Close();
          //  app.Quit();
        }

        public void SDirBtn_Click(object sender, EventArgs e)
        {
            exlList.Clear();
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            fbd.ShowDialog();
            pathDir = fbd.SelectedPath; DirectoryInfo di = new DirectoryInfo(pathDir);
            FileInfo[] rgFiles = di.GetFiles("*.xls");
            foreach (FileInfo fi in rgFiles)
            {
                exlList.Add(fi.FullName.ToString());
            }
        }

        public void mExcelBtn_Click(object sender, EventArgs e)
        {
            bulkWorker.RunWorkerAsync();
        }

        public void tabPage2_Click(object sender, EventArgs e)
        {

        }

        


        public void button1_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog fd = new FolderBrowserDialog();
            fd.ShowDialog();
            path = fd.SelectedPath;
            LiveWorker.RunWorkerAsync();
        }

        public void button2_Click(object sender, EventArgs e)
        {
            LiveWorker.CancelAsync();
            MessageBox.Show(" You Have Stopped the Process\n press GoLive! button to start again", "Process Ended");
        }
        

        public void LiveWorker_DoWork(object sender, DoWorkEventArgs e)
        {

            FileSystemWatcher watcher = new FileSystemWatcher();
            // Create a new FileSystemWatcher and set its properties.
            watcher.Path = path;
            /* Watch for changes in LastAccess and LastWrite times, and the renaming of files or directories. */
            watcher.NotifyFilter = NotifyFilters.LastWrite;
            // Only watch text files.
            watcher.Filter = "*.xls";
            // Add event handler.
            watcher.Created += new FileSystemEventHandler(OnChanged);
            // Begin watching.
            watcher.EnableRaisingEvents = true;
        }

        private void LiveWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Cancelled)
            {
                MessageBox.Show("The task has been cancelled");
            }
            else if (e.Error != null)
            {
                MessageBox.Show("Error. Details: " + (e.Error as Exception).ToString());
            }
            else
            {
                MessageBox.Show("The task has been completed. Results: " + e.Result.ToString());
            }
        }

        public void LiveWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
        }

        public void bulkWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            Stopwatch s = new Stopwatch();
            s.Start();
            string addr, crx;
            foreach (string xl in exlList)
            {

                if (bulkWorker.CancellationPending)
                {
                    e.Cancel = true;
                    break;
                }
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(xl, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
                int index = 0;
                int rowIndex = 18;
                DateTime dt5 = DateTime.FromOADate(0.0);
                string temp5;
                while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)
                {
                    temp5 = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                    //Converts OADatetime to DateTime
                    addr = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                    crx = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
                    try
                    {
                        Double rogue = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2;
                        dt5 = DateTime.FromOADate(Convert.ToDouble(rogue));

                        addr = addr.Replace("BLK", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty);
                        crx = crx.Replace("BLK", string.Empty).Replace("(", string.Empty).Replace(")", string.Empty);

                        incident newInc = new incident();
                        newInc.Agency = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                        newInc.Intersection = addr + "& " + crx;
                        newInc.CallTime = dt5;
                        newInc.IncidentType = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                        dX.incidents.InsertOnSubmit(newInc);
                        dX.SubmitChanges();
                        index++;
                        rowIndex = 18 + index;
                    }


                    catch
                    {
                        try
                        {
                            Double rogue = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2;
                            dt5 = DateTime.FromOADate(Convert.ToDouble(rogue)); joinedResp newJR = new joinedResp();
                            newJR.Agency = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                            newJR.Address = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                            newJR.CrossStreet = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
                            newJR.IncidentType = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                            mX.joinedResps.InsertOnSubmit(newJR);
                            mX.SubmitChanges();
                            index++;
                            rowIndex = 18 + index;
                        }
                        catch
                        {
                            index++;
                            rowIndex = 18 + index;
                        }
                    }
                }

                workBook.Close();
                app.Quit();
            }
            s.Stop();
            MessageBox.Show("Task completed in " + s.Elapsed);
            s.Reset();
        }

        public void button3_Click(object sender, EventArgs e)
        {
            bulkWorker.CancelAsync();
            MessageBox.Show("You have cancelled all proccesses", "Work Stopped");
        }

        private void StartWatcher()
        {
            // Create a new FileSystemWatcher and set its properties.
            FileSystemWatcher watcher = new FileSystemWatcher();
            watcher.Path = path;

            /* 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;

            // Only watch text files.
            watcher.Filter = "*.xls";

            // Add event handler.
            watcher.Created += new FileSystemEventHandler(OnChanged);

            // Begin watching.
            watcher.EnableRaisingEvents = true;
        }

        private void OnChanged(object source, FileSystemEventArgs e)
        {
            // Specify what is done when a file is changed, created, or deleted.
            MessageBox.Show("File: " + e.FullPath + " " + e.ChangeType);
            StreamWriter te = new StreamWriter("fileList.txt");
            te.WriteLine(e.FullPath.ToString());
            te.Close();
            CreateIncidents(mX, dX);
        }
    }
}

I got it working, still not sure what the problem was. Here it is below

{
            FolderBrowserDialog fd = new FolderBrowserDialog();
            fd.ShowDialog();
            fileWatcher.Path = fd.SelectedPath;
            /* Watch for changes in LastAccess and LastWrite times, and
               the renaming of files or directories. */
            /* Watch for changes in LastAccess and LastWrite times, and
           the renaming of files or directories. */
            fileWatcher.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite
               | NotifyFilters.FileName | NotifyFilters.DirectoryName;
            fileWatcher.Filter = "*.xls";
            fileWatcher.Changed += new FileSystemEventHandler(OnChanged);
            fileWatcher.Created += new FileSystemEventHandler(OnChanged);
            fileWatcher.Deleted += new FileSystemEventHandler(OnChanged);
            fileWatcher.Renamed += new RenamedEventHandler(OnChanged);
            fileWatcher.IncludeSubdirectories = false;
            fileWatcher.EnableRaisingEvents = true;
        }
private void OnChanged(object sender, System.IO.FileSystemEventArgs e)
        {
            txtOutput.BeginUpdate();
            txtOutput.Items.Add(e.ChangeType + ": " + e.FullPath);
            txtOutput.EndUpdate();
            exlList.Clear();
            exlList.Add(e.FullPath);
            CreateIncidents(mX, dX);
        }
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.