Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- reader only pulling one row in while loop C#
- 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.Timers;
- using MySql.Data.MySqlClient;
- using FAXCOMLib;
- using FAXCOMEXLib;
- namespace ProcessFaxes
- {
- public partial class Service1 : ServiceBase
- {
- public Service1()
- {
- InitializeComponent();
- }
- public static Timer timer = new Timer();
- protected override void OnStart(string[] args)
- {
- timer.Elapsed += new ElapsedEventHandler(Tick);
- timer.Interval = 600000; // every 10 minutes
- timer.Enabled = true;
- // Console.ReadLine();
- }
- protected override void OnStop()
- {
- }
- public static void Tick(object source, ElapsedEventArgs e)
- {
- string connString = "Server=localhost;Port=3306;Database=communications;Uid=root;password=pass;";
- MySqlConnection conn = new MySqlConnection(connString);
- MySqlCommand command = conn.CreateCommand();
- MySqlConnection connupdate = new MySqlConnection(connString);
- MySqlCommand commandupdate = connupdate.CreateCommand();
- command.CommandText = "SELECT * FROM outbox WHERE `faxstat` = 'Y' AND `fax` <> '' AND `faxpro` = 'PENDING'";
- //command.CommandText = "UPDATE blah blah";
- //conn.Open();
- //conn.ExecuteNonQuery();
- //conn.Close();
- try
- {
- conn.Open();
- connupdate.Open();
- }
- catch (Exception ex)
- {
- // Console.WriteLine(Ex.Message);
- LogException(ex.ToString());
- throw; // or whatever you want to do with it
- }
- MySqlDataReader reader = command.ExecuteReader();
- if (reader.HasRows)
- {
- while (reader.Read())
- {
- //Console.WriteLine(reader["filepath"].ToString());
- SendFax(reader["id"].ToString(), reader["filepath"].ToString(), @"C:FAXDOC" + reader["filepath"].ToString(), reader["account"].ToString(), reader["fax"].ToString(), reader["fax_orig"].ToString());
- string id = reader["id"].ToString();
- commandupdate.CommandText = "UPDATE outbox SET `faxpro` = 'DONE' WHERE `id` = '" + id + "'";
- commandupdate.ExecuteNonQuery();
- }
- }
- conn.Close();
- connupdate.Close();
- }
- public static void SendFax(string DocumentId, string DocumentName, string FileName, string RecipientName, string FaxNumber, string RecipientHomePhone2)
- {
- if (FaxNumber != "")
- {
- try
- {
- FAXCOMLib.FaxServer faxServer = new FAXCOMLib.FaxServerClass();
- faxServer.Connect(Environment.MachineName);
- FAXCOMLib.FaxDoc faxDoc = (FAXCOMLib.FaxDoc)faxServer.CreateDocument(FileName);
- faxDoc.RecipientName = RecipientName;
- faxDoc.FaxNumber = FaxNumber;
- faxDoc.BillingCode = DocumentId;
- faxDoc.DisplayName = DocumentName;
- faxDoc.RecipientHomePhone = RecipientHomePhone2;
- int Response = faxDoc.Send();
- faxServer.Disconnect();
- }
- catch (Exception Ex) {
- // Console.WriteLine(Ex.Message);
- LogException(Ex.ToString());
- throw; // or whatever you want to do with it
- }
- }
- }
- public static void LogException(string ErrorDescription)
- {
- // The name of our log in the event logs
- string Log = "Process Faxes";
- // Check to see fi the log for AspNetError exists on the machine
- // If not, create it
- if ((!(EventLog.SourceExists(Log))))
- {
- EventLog.CreateEventSource(Log, Log);
- }
- // Now insert your exception information into the AspNetError event log
- EventLog logEntry = new EventLog();
- logEntry.Source = Log;
- logEntry.WriteEntry(ErrorDescription, EventLogEntryType.Error);
- }
- }
- }
- Event Type: Error
- Event Source: Process Faxes
- Event Category: None
- Event ID: 0
- Date: 3/6/2012
- Time: 2:01:06 PM
- User: N/A
- Computer: FAXSERVER
- Description:
- MySql.Data.MySqlClient.MySqlException (0x80004005): Too many connections
- at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
- at MySql.Data.MySqlClient.NativeDriver.Open()
- at MySql.Data.MySqlClient.Driver.Open()
- at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
- at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
- at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
- at MySql.Data.MySqlClient.MySqlPool.GetConnection()
- at MySql.Data.MySqlClient.MySqlConnection.Open()
- at ProcessFaxes.Service1.Tick(Object source, ElapsedEventArgs e) in C:Documents and SettingsbruserMy DocumentsVisual Studio 2010ProjectsProcessFaxesProcessFaxesService1.cs:line 56
- public static void Tick(object source, ElapsedEventArgs e)
- {
- // Prevent another Tick from happening if this takes longer than 10 minutes
- (source as Timer).Enabled = false;
- // It would be better practice to put this in a settings or config file
- // so you can change it without having to recompile your application
- string connString = "Server=localhost;Port=3306;Database=communications;Uid=root;password=pass;";
- // I won't change them here, but since these classes implement IDisposable,
- // you should be using a using statement around them:
- // using (var conn = new MySqlConnection(connString))
- // {
- // // use conn
- // }
- MySqlConnection conn = new MySqlConnection(connString);
- MySqlCommand command = conn.CreateCommand();
- MySqlCommand updateCommand = conn.CreateCommand();
- command.CommandText = "SELECT * FROM outbox WHERE `faxstat` = 'Y' AND `fax` <> '' AND `faxpro` = 'PENDING'";
- try
- {
- conn.Open();
- MySqlDataReader reader = command.ExecuteReader();
- if (reader.HasRows)
- {
- while (reader.Read())
- {
- SendFax(reader["id"].ToString(), reader["filepath"].ToString(), @"C:FAXDOC" + reader["filepath"].ToString(), reader["account"].ToString(), reader["fax"].ToString(), reader["fax_orig"].ToString());
- string id = reader["id"].ToString();
- // I would use a prepared statement with either this query
- // or a stored procedure with parameters instead of manually
- // building this string (more good practice than worrying about
- // SQL injection as it's an internal app
- updateCommand.CommandText = "UPDATE outbox SET `faxpro` = 'DONE' WHERE `id` = '" + id + "'";
- updateCommand.ExecuteNonQuery();
- }
- }
- }
- catch (Exception ex)
- {
- LogException(ex.ToString());
- throw;
- }
- finally
- {
- // If you're not going to use using-statements, you might want to explicitly
- // call dispose on your disposable objects:
- // command.Dispose();
- // updateCommand.Dispose();
- conn.Close();
- // conn.Dispose();
- }
- // Enable the timer again
- (source as Timer).Enabled = true;
- }
- string connString = "Server=localhost;Port=3306;Database=communications;Uid=root;password=pass;";
- using(MySqlConnection conn = new MySQLConnection(connString))
- {
- using(MySQlCommand command = conn.CreateCommand())
- {
- command.CommandText = "SELECT ...";
- conn.Open();
- using(MySqlDataReader reader = command.ExecuteReader())
- {
- //process rows...
- }
- }
- }
Add Comment
Please, Sign In to add comment