当循环C#时，读取器只拉入一行
本文关键字：一行 循环 读取 | 更新日期: 2023-09-27 18:26:51
我有两个问题。一种是，当有几百个要发送时，它只拉一行并将其发送到ms传真。另一个是，它在第一次之后就不再拉了，而是抛出了一个错误。我以为我正在关闭我的关系。我不明白问题出在哪里。我已经包含了代码和错误。
Service1.cs
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 Settings'bruser'My Documents'Visual Studio 2010'Projects'ProcessFaxes'ProcessFaxes'Service1.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;
}
至于为什么您只收到一行，而期望收到多行，我怀疑您的SQL有问题。
您不应该使用计时器。
计时器每隔一段时间就会启动，并且不关心前一个事件是否已完成。
考虑使用后台工作程序发送传真，使其在队列上循环，然后在队列为空时暂停。
问题在于您的连接对象。您已经定义了几个连接对象。你只需要一个。
以下是两个问题：
MySqlConnection connupdate = new MySqlConnection(connString);
MySqlConnection conn = new MySqlConnection(connString);
消除与其中一个的连接。
这里有一种修复代码的方法：
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...
}
}
}