My C# windows service hangs

[prachell]

My issue is similar to a previous post that you have answered in

Windows Service written in c# Hangs


We use only 1 service which fetches files(*.xlsx) from a folder and inserts into a table of a Database.Once the details are updated into the database, the file will be deleted by the service. The folder from which the service fetches is shared and new files get dumped then and then. (Average 5 files per day). If the content in the .xlsx file has some error or if it doesn't match with the sql constraints , it will be moved to another folder which has an error log specifying the type of error.

My issue is that my service is in Started mode but the files are not consumed into the database.


But if i RESTART the service, the files get consumed.

Pls help me with this

Code:

 
using System.IO;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Common;
 
namespace Impexp_Service
{
public partial class Service1 : ServiceBase
{
System.Timers.Timer T1 = new System.Timers.Timer();
public Service1()
{
InitializeComponent();
}
 
protected override void OnStart(string[] args)
{
///start
///
 
{
SqlConnection strconnection = new SqlConnection();
strconnection.ConnectionString = @"Data Source=XXXXX;Initial Catalog=YYYY;User ID=sa;Password=*****;";
strconnection.Open();
// To get the all files placed at the shared path
DirectoryInfo directory = new DirectoryInfo(@"D:\YY\Data\");
FileInfo[] files = directory.GetFiles("*.xlsx");
 
 
 
foreach (var f in files)
{
string path = f.FullName;
 
// TO establish connection to the excel sheet
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
 
 
 
excelConnection.Open();
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select * from [Report$]", excelConnection);
 
DbDataReader dr = cmd.ExecuteReader();
// OleDbDataReader dReader;
// dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "TableXYZ";
sqlBulk.WriteToServer(dr);
 
excelConnection.Close();
 
File.Delete(path);
 
 
 
 
// To move error files to the error folder
 
 
 
/// end
 
 
T1.Interval = 20000;
T1.Enabled = true;
T1.Start();
 
T1.Elapsed += new System.Timers.ElapsedEventHandler(T1_Elapsed);
}
}
}
 
 
void T1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
T1.Enabled = false;
try
{
SqlConnection strconnection = new SqlConnection();
strconnection.ConnectionString = @"Data Source=XXXX;Initial Catalog=YYY;User ID=conapt;Password=****;";
strconnection.Open();
// To get the all files placed at the shared path
DirectoryInfo directory = new DirectoryInfo(@"D:\YYY\Data\");
FileInfo[] files = directory.GetFiles("*.xlsx");
 
 
 
foreach (var f in files)
{
string path = f.FullName;
 
// TO establish connection to the excel sheet
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
 
try
{
excelConnection.Open();
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select * from [Report$]", excelConnection);
 
DbDataReader dr = cmd.ExecuteReader();
// OleDbDataReader dReader;
// dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "TableXYZ";
sqlBulk.WriteToServer(dr);
 
excelConnection.Close();
 
File.Delete(path);
 
 
 
}
// To move error files to the error folder
catch (Exception exp)
{
 
excelConnection.Close();
File.Move(path, Path.Combine(@"D:\YYY\error\", f.Name));
string path1 = @"D:\YYY\error\error.txt";
if (File.Exists(path1))
{
// Create a file to write to.
using (StreamWriter sw = File.AppendText(path1))
{
sw.WriteLine("File : " + path + " : " + exp.Message);
sw.Flush();
 
}
}
 
 
T1.Enabled = true;
T1.Start();
 
}
}
strconnection.Close();
 
// End of TRY 1
 
}
catch (UnauthorizedAccessException UAEx)
{
string path1 = @"D:\YYYYY\error\error.txt";
if (File.Exists(path1))
{
// Create a file to write to.
using (StreamWriter sw = File.AppendText(path1))
{
sw.WriteLine(UAEx.Message);
sw.Flush();
 
}
}
T1.Enabled = true;
T1.Start();
}
catch (PathTooLongException PathEx)
{
string path1 = @"D:\YYYY\error\error.txt";
if (File.Exists(path1))
{
// Create a file to write to.
using (StreamWriter sw = File.AppendText(path1))
{
sw.WriteLine(PathEx.Message);
sw.Flush();
 
}
}
T1.Enabled = true;
T1.Start();
}
T1.Enabled = true;
T1.Start();
 
 
}
 
protected override void OnStop()
{
}
}
}