Guest User

Untitled

a guest
Jul 31st, 2018
104
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.62 KB | None | 0 0
  1. reader only pulling one row in while loop C#
  2. using System;
  3. using System.Collections.Generic;
  4. using System.ComponentModel;
  5. using System.Data;
  6. using System.Diagnostics;
  7. using System.Linq;
  8. using System.ServiceProcess;
  9. using System.Text;
  10. using System.Timers;
  11. using MySql.Data.MySqlClient;
  12. using FAXCOMLib;
  13. using FAXCOMEXLib;
  14.  
  15. namespace ProcessFaxes
  16. {
  17. public partial class Service1 : ServiceBase
  18. {
  19. public Service1()
  20. {
  21. InitializeComponent();
  22. }
  23. public static Timer timer = new Timer();
  24.  
  25. protected override void OnStart(string[] args)
  26. {
  27.  
  28. timer.Elapsed += new ElapsedEventHandler(Tick);
  29. timer.Interval = 600000; // every 10 minutes
  30. timer.Enabled = true;
  31. // Console.ReadLine();
  32. }
  33.  
  34. protected override void OnStop()
  35. {
  36.  
  37. }
  38.  
  39. public static void Tick(object source, ElapsedEventArgs e)
  40. {
  41. string connString = "Server=localhost;Port=3306;Database=communications;Uid=root;password=pass;";
  42. MySqlConnection conn = new MySqlConnection(connString);
  43. MySqlCommand command = conn.CreateCommand();
  44.  
  45. MySqlConnection connupdate = new MySqlConnection(connString);
  46. MySqlCommand commandupdate = connupdate.CreateCommand();
  47.  
  48. command.CommandText = "SELECT * FROM outbox WHERE `faxstat` = 'Y' AND `fax` <> '' AND `faxpro` = 'PENDING'";
  49. //command.CommandText = "UPDATE blah blah";
  50. //conn.Open();
  51. //conn.ExecuteNonQuery();
  52. //conn.Close();
  53.  
  54. try
  55. {
  56.  
  57. conn.Open();
  58. connupdate.Open();
  59.  
  60. }
  61. catch (Exception ex)
  62. {
  63. // Console.WriteLine(Ex.Message);
  64. LogException(ex.ToString());
  65.  
  66. throw; // or whatever you want to do with it
  67. }
  68. MySqlDataReader reader = command.ExecuteReader();
  69.  
  70. if (reader.HasRows)
  71. {
  72.  
  73. while (reader.Read())
  74. {
  75. //Console.WriteLine(reader["filepath"].ToString());
  76. SendFax(reader["id"].ToString(), reader["filepath"].ToString(), @"C:FAXDOC" + reader["filepath"].ToString(), reader["account"].ToString(), reader["fax"].ToString(), reader["fax_orig"].ToString());
  77. string id = reader["id"].ToString();
  78. commandupdate.CommandText = "UPDATE outbox SET `faxpro` = 'DONE' WHERE `id` = '" + id + "'";
  79. commandupdate.ExecuteNonQuery();
  80.  
  81. }
  82. }
  83.  
  84. conn.Close();
  85. connupdate.Close();
  86. }
  87.  
  88. public static void SendFax(string DocumentId, string DocumentName, string FileName, string RecipientName, string FaxNumber, string RecipientHomePhone2)
  89. {
  90. if (FaxNumber != "")
  91. {
  92. try
  93. {
  94. FAXCOMLib.FaxServer faxServer = new FAXCOMLib.FaxServerClass();
  95. faxServer.Connect(Environment.MachineName);
  96.  
  97.  
  98. FAXCOMLib.FaxDoc faxDoc = (FAXCOMLib.FaxDoc)faxServer.CreateDocument(FileName);
  99.  
  100. faxDoc.RecipientName = RecipientName;
  101. faxDoc.FaxNumber = FaxNumber;
  102. faxDoc.BillingCode = DocumentId;
  103. faxDoc.DisplayName = DocumentName;
  104. faxDoc.RecipientHomePhone = RecipientHomePhone2;
  105.  
  106. int Response = faxDoc.Send();
  107.  
  108.  
  109. faxServer.Disconnect();
  110.  
  111. }
  112. catch (Exception Ex) {
  113.  
  114. // Console.WriteLine(Ex.Message);
  115. LogException(Ex.ToString());
  116.  
  117. throw; // or whatever you want to do with it
  118. }
  119. }
  120.  
  121.  
  122.  
  123. }
  124.  
  125. public static void LogException(string ErrorDescription)
  126.  
  127. {
  128.  
  129. // The name of our log in the event logs
  130.  
  131. string Log = "Process Faxes";
  132.  
  133.  
  134.  
  135. // Check to see fi the log for AspNetError exists on the machine
  136.  
  137. // If not, create it
  138.  
  139. if ((!(EventLog.SourceExists(Log))))
  140.  
  141. {
  142.  
  143.  
  144. EventLog.CreateEventSource(Log, Log);
  145.  
  146. }
  147.  
  148.  
  149.  
  150. // Now insert your exception information into the AspNetError event log
  151.  
  152. EventLog logEntry = new EventLog();
  153.  
  154. logEntry.Source = Log;
  155.  
  156. logEntry.WriteEntry(ErrorDescription, EventLogEntryType.Error);
  157.  
  158. }
  159.  
  160.  
  161. }
  162. }
  163.  
  164. Event Type: Error
  165. Event Source: Process Faxes
  166. Event Category: None
  167. Event ID: 0
  168. Date: 3/6/2012
  169. Time: 2:01:06 PM
  170. User: N/A
  171. Computer: FAXSERVER
  172. Description:
  173. MySql.Data.MySqlClient.MySqlException (0x80004005): Too many connections
  174. at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
  175. at MySql.Data.MySqlClient.NativeDriver.Open()
  176. at MySql.Data.MySqlClient.Driver.Open()
  177. at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
  178. at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
  179. at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
  180. at MySql.Data.MySqlClient.MySqlPool.GetConnection()
  181. at MySql.Data.MySqlClient.MySqlConnection.Open()
  182. at ProcessFaxes.Service1.Tick(Object source, ElapsedEventArgs e) in C:Documents and SettingsbruserMy DocumentsVisual Studio 2010ProjectsProcessFaxesProcessFaxesService1.cs:line 56
  183.  
  184. public static void Tick(object source, ElapsedEventArgs e)
  185. {
  186. // Prevent another Tick from happening if this takes longer than 10 minutes
  187. (source as Timer).Enabled = false;
  188.  
  189. // It would be better practice to put this in a settings or config file
  190. // so you can change it without having to recompile your application
  191. string connString = "Server=localhost;Port=3306;Database=communications;Uid=root;password=pass;";
  192.  
  193. // I won't change them here, but since these classes implement IDisposable,
  194. // you should be using a using statement around them:
  195. // using (var conn = new MySqlConnection(connString))
  196. // {
  197. // // use conn
  198. // }
  199. MySqlConnection conn = new MySqlConnection(connString);
  200. MySqlCommand command = conn.CreateCommand();
  201. MySqlCommand updateCommand = conn.CreateCommand();
  202.  
  203. command.CommandText = "SELECT * FROM outbox WHERE `faxstat` = 'Y' AND `fax` <> '' AND `faxpro` = 'PENDING'";
  204.  
  205. try
  206. {
  207. conn.Open();
  208.  
  209. MySqlDataReader reader = command.ExecuteReader();
  210.  
  211. if (reader.HasRows)
  212. {
  213. while (reader.Read())
  214. {
  215. SendFax(reader["id"].ToString(), reader["filepath"].ToString(), @"C:FAXDOC" + reader["filepath"].ToString(), reader["account"].ToString(), reader["fax"].ToString(), reader["fax_orig"].ToString());
  216. string id = reader["id"].ToString();
  217. // I would use a prepared statement with either this query
  218. // or a stored procedure with parameters instead of manually
  219. // building this string (more good practice than worrying about
  220. // SQL injection as it's an internal app
  221. updateCommand.CommandText = "UPDATE outbox SET `faxpro` = 'DONE' WHERE `id` = '" + id + "'";
  222. updateCommand.ExecuteNonQuery();
  223.  
  224. }
  225. }
  226. }
  227. catch (Exception ex)
  228. {
  229. LogException(ex.ToString());
  230. throw;
  231. }
  232. finally
  233. {
  234. // If you're not going to use using-statements, you might want to explicitly
  235. // call dispose on your disposable objects:
  236. // command.Dispose();
  237. // updateCommand.Dispose();
  238. conn.Close();
  239. // conn.Dispose();
  240. }
  241.  
  242. // Enable the timer again
  243. (source as Timer).Enabled = true;
  244. }
  245.  
  246. string connString = "Server=localhost;Port=3306;Database=communications;Uid=root;password=pass;";
  247.  
  248. using(MySqlConnection conn = new MySQLConnection(connString))
  249. {
  250. using(MySQlCommand command = conn.CreateCommand())
  251. {
  252. command.CommandText = "SELECT ...";
  253. conn.Open();
  254. using(MySqlDataReader reader = command.ExecuteReader())
  255. {
  256. //process rows...
  257. }
  258. }
  259. }
Add Comment
Please, Sign In to add comment