By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,596 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

My C# windows service hangs

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. using System.IO;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.ComponentModel;
  6. using System.Data;
  7. using System.Diagnostics;
  8. using System.Linq;
  9. using System.ServiceProcess;
  10. using System.Text;
  11. using System.Data.SqlClient;
  12. using System.Data.OleDb;
  13. using System.Data.Common;
  14.  
  15. namespace Impexp_Service
  16. {
  17. public partial class Service1 : ServiceBase
  18. {
  19. System.Timers.Timer T1 = new System.Timers.Timer();
  20. public Service1()
  21. {
  22. InitializeComponent();
  23. }
  24.  
  25. protected override void OnStart(string[] args)
  26. {
  27. ///start
  28. ///
  29.  
  30. {
  31. SqlConnection strconnection = new SqlConnection();
  32. strconnection.ConnectionString = @"Data Source=XXXXX;Initial Catalog=YYYY;User ID=sa;Password=*****;";
  33. strconnection.Open();
  34. // To get the all files placed at the shared path
  35. DirectoryInfo directory = new DirectoryInfo(@"D:\YY\Data\");
  36. FileInfo[] files = directory.GetFiles("*.xlsx");
  37.  
  38.  
  39.  
  40. foreach (var f in files)
  41. {
  42. string path = f.FullName;
  43.  
  44. // TO establish connection to the excel sheet
  45. string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
  46. //Create Connection to Excel work book
  47. OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
  48.  
  49.  
  50.  
  51. excelConnection.Open();
  52. //Create OleDbCommand to fetch data from Excel
  53. OleDbCommand cmd = new OleDbCommand("Select * from [Report$]", excelConnection);
  54.  
  55. DbDataReader dr = cmd.ExecuteReader();
  56. // OleDbDataReader dReader;
  57. // dReader = cmd.ExecuteReader();
  58. SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection);
  59. //Give your Destination table name
  60. sqlBulk.DestinationTableName = "TableXYZ";
  61. sqlBulk.WriteToServer(dr);
  62.  
  63. excelConnection.Close();
  64.  
  65. File.Delete(path);
  66.  
  67.  
  68.  
  69.  
  70. // To move error files to the error folder
  71.  
  72.  
  73.  
  74. /// end
  75.  
  76.  
  77. T1.Interval = 20000;
  78. T1.Enabled = true;
  79. T1.Start();
  80.  
  81. T1.Elapsed += new System.Timers.ElapsedEventHandler(T1_Elapsed);
  82. }
  83. }
  84. }
  85.  
  86.  
  87. void T1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
  88. {
  89. T1.Enabled = false;
  90. try
  91. {
  92. SqlConnection strconnection = new SqlConnection();
  93. strconnection.ConnectionString = @"Data Source=XXXX;Initial Catalog=YYY;User ID=conapt;Password=****;";
  94. strconnection.Open();
  95. // To get the all files placed at the shared path
  96. DirectoryInfo directory = new DirectoryInfo(@"D:\YYY\Data\");
  97. FileInfo[] files = directory.GetFiles("*.xlsx");
  98.  
  99.  
  100.  
  101. foreach (var f in files)
  102. {
  103. string path = f.FullName;
  104.  
  105. // TO establish connection to the excel sheet
  106. string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
  107. //Create Connection to Excel work book
  108. OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
  109.  
  110. try
  111. {
  112. excelConnection.Open();
  113. //Create OleDbCommand to fetch data from Excel
  114. OleDbCommand cmd = new OleDbCommand("Select * from [Report$]", excelConnection);
  115.  
  116. DbDataReader dr = cmd.ExecuteReader();
  117. // OleDbDataReader dReader;
  118. // dReader = cmd.ExecuteReader();
  119. SqlBulkCopy sqlBulk = new SqlBulkCopy(strconnection);
  120. //Give your Destination table name
  121. sqlBulk.DestinationTableName = "TableXYZ";
  122. sqlBulk.WriteToServer(dr);
  123.  
  124. excelConnection.Close();
  125.  
  126. File.Delete(path);
  127.  
  128.  
  129.  
  130. }
  131. // To move error files to the error folder
  132. catch (Exception exp)
  133. {
  134.  
  135. excelConnection.Close();
  136. File.Move(path, Path.Combine(@"D:\YYY\error\", f.Name));
  137. string path1 = @"D:\YYY\error\error.txt";
  138. if (File.Exists(path1))
  139. {
  140. // Create a file to write to.
  141. using (StreamWriter sw = File.AppendText(path1))
  142. {
  143. sw.WriteLine("File : " + path + " : " + exp.Message);
  144. sw.Flush();
  145.  
  146. }
  147. }
  148.  
  149.  
  150. T1.Enabled = true;
  151. T1.Start();
  152.  
  153. }
  154. }
  155. strconnection.Close();
  156.  
  157. // End of TRY 1
  158.  
  159. }
  160. catch (UnauthorizedAccessException UAEx)
  161. {
  162. string path1 = @"D:\YYYYY\error\error.txt";
  163. if (File.Exists(path1))
  164. {
  165. // Create a file to write to.
  166. using (StreamWriter sw = File.AppendText(path1))
  167. {
  168. sw.WriteLine(UAEx.Message);
  169. sw.Flush();
  170.  
  171. }
  172. }
  173. T1.Enabled = true;
  174. T1.Start();
  175. }
  176. catch (PathTooLongException PathEx)
  177. {
  178. string path1 = @"D:\YYYY\error\error.txt";
  179. if (File.Exists(path1))
  180. {
  181. // Create a file to write to.
  182. using (StreamWriter sw = File.AppendText(path1))
  183. {
  184. sw.WriteLine(PathEx.Message);
  185. sw.Flush();
  186.  
  187. }
  188. }
  189. T1.Enabled = true;
  190. T1.Start();
  191. }
  192. T1.Enabled = true;
  193. T1.Start();
  194.  
  195.  
  196. }
  197.  
  198. protected override void OnStop()
  199. {
  200. }
  201. }
  202.  
Mar 31 '14 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.