我目前正在进行一个C#项目,并试图获得从MySQL数据阅读器返回的行数。
我知道没有直接的函数,所以我尝试写我自己的。在函数中,我将MySQLDataReader对象传递给它,然后循环遍历MySQL数据读取器,并增加一个计数器并返回计数器的值。
这似乎锁定了程序,我猜,因为我是Reader.read()后,我已经得到了计数,我已经结束了。相反,我尝试创建一个读取器副本,然后遍历临时版本,但是我得到了相同的结果。
下面是我执行查询和调用函数的代码。
string query = "SELECT * FROM reports, software, platforms, versions "
+ "WHERE EmailVerified = @verified AND reports.SoftwareID = software.id AND reports.PlatformID = platforms.id "
+ "AND reports.VersionID = versions.id AND BugReportAcceptedNotificationSent = @notificationSent";
using (MySqlCommand cmd = new MySqlCommand(query, db.conn))
{
cmd.Parameters.AddWithValue("@verified", "1");
cmd.Parameters.AddWithValue("@notificationSent", "0");
using (MySqlDataReader reader = cmd.ExecuteReader())
{
totalEmails = HelperClass.totalRowsInMySQLDataReader(reader);
while (reader.Read())
{
currentEmailCount++;
EmailNotifications emailNotification = new EmailNotifications(reader);
emailNotification.sendNewBugReportAfterVerificationEmail(currentEmailCount, totalEmails);
}
}
}下面是我的函数,它获取行计数
public static int totalRowsInMySQLDataReader(MySqlDataReader reader)
{
MySqlDataReader tempReader = reader;
ILibraryInterface library = GeneralTasks.returnBitsLibrary(Configuration.databaseSettings, Configuration.engineConfig.logFile);
string methodInfo = classDetails + MethodInfo.GetCurrentMethod().Name;
try
{
int count = 0;
while (tempReader.Read())
{
count++;
}
tempReader = null;
return count;
}
catch (Exception ex)
{
string error = string.Format("Failed to get total rows in MySQL Database. Exception: {0}", ex.Message);
library.logging(methodInfo, error);
library.setAlarm(error, CommonTasks.AlarmStatus.Medium, methodInfo);
return -1;
}
}发布于 2014-02-20 13:49:13
为了避免多个查询,在select本身中包含总计如何?
SELECT COUNT(*) AS TotalNORows, * FROM reports, software, platforms, versions etc发布于 2014-02-20 13:31:40
使用DataTable加载结果。
DataTable dt = new DataTable();
dt.Load(reader);
int numberOfResults = dt.Rows.Count;然后,您还可以遍历行来读取值。
foreach(DataRow dr in dt.Rows)
{
var value = dr["SomeResultingColumn"]
}另一个选项是发出两个单独的SQL语句,但是您需要确保这两个语句都包含在具有Serializable隔离级别的事务中,这是为了确保在执行两个SQL语句之间不插入记录。
发布于 2014-02-23 04:03:08
我认为如果不执行另一个命令,它就不是possible...as,在读取器类中没有可用的计数方法
你可以试试这个。如果成功的话..。
string query = "SELECT * FROM reports, software, platforms, versions "
+ "WHERE EmailVerified=@verified AND reports.SoftwareID=software.id AND reports.PlatformID=platforms.id "
+ "AND reports.VersionID=versions.id AND BugReportAcceptedNotificationSent=@notificationSent";
using (MySqlCommand cmd = new MySqlCommand(query, db.conn))
{
cmd.Parameters.AddWithValue("@verified", "1");
cmd.Parameters.AddWithValue("@notificationSent", "0");
using (MySqlDataReader reader = cmd.ExecuteReader())
{
// create a new connection db.conn2 then
MySqlCommand cmd2 = new MySqlCommand(query, db.conn2))
cmd2.Parameters.AddWithValue("@verified", "1");
cmd2.Parameters.AddWithValue("@notificationSent", "0");
MySqlDataReader reader2 = cmd2.ExecuteReader();
int numberofrow=0;
while(reader2.Read())
numberofrow++;
//your codes......
}https://stackoverflow.com/questions/21837407
复制相似问题