C#中处理SqlCommand的概念 [英] Concept of handling SqlCommand in C#

问题描述

我正在尝试从数据库中选择一个用户列表，并根据条件 isSent == false 向每个用户发送电子邮件.向他们发送电子邮件后，此 false 的值应更新为 true.下面的代码是我从数据库中检索用户列表并向每个用户调用方法 sendEmail() 的方式.

I'm trying to select a list of users from the database and send email to each of every users based on a condition isSent == false. After send email to them, the value of this false should be update to true. Below code is the way i retrieve list of users from database and call method sendEmail() to each of them.

           myConnection.Open();
            //*******
            try
            {
                SqlDataReader myReader = null;
                string sql = "SELECT * FROM testTable where isSent = false";
                SqlCommand myCommand = new SqlCommand(sql, myConnection);
                myReader = myCommand.ExecuteReader();

                while (myReader.Read())
                {
                    sendEmail(myReader["emailAdd"].ToString(), 
                              myReader["UserID"].ToString());
                }

第二部分:

public static void sendEmail(string emailAdd,string userID){
    .
    .
    .
    try
    {
        smtpClient.Send(mail);
        try
            {
                string sql = "UPDATE testTable SET isSent = 1 WHERE  UserID = " + userID;
                SqlCommand myCommand = new SqlCommand(sql, myConnection);
                int rows = myCommand.ExecuteNonQuery();
                .
                .
                .
            }
    }
}

我面临的问题是，从 main 方法开始，我已经有 SqlDataReader 可供读取，所以我现在无法更新.对我有什么解决办法吗?我得到的错误信息如下:

The problem I'm facing is that since from main method I already have SqlDataReader being hold to read and so I cant update now. Any work around for me? The error message that I get is as below:

There is already an open DataReader associated with this Command which must be closed first.

推荐答案

我面临的问题是，从 main 方法开始，我已经有 SqlDataReader 可供读取，所以我现在无法更新.对我有什么解决办法吗?

The problem I'm facing is that since from main method I already have SqlDataReader being hold to read and so I cant update now. Any work around for me?

这只是一个问题，因为您正在共享连接 (myConnection).不要那样做.每次要执行数据库操作时都创建一个新的 SqlConnection，并让连接池基础结构处理使其高效.另外，对与数据库相关的资源使用 using 语句:

That's only a problem because you're sharing the connection (myConnection). Don't do that. Create a new SqlConnection every time you want to perform a database operation, and let the connection pool infrastructure handle making it efficient. Also, use using statements for database-related resources:

using (var connection = new SqlConnection(...))
{
    using (var command = new SqlCommand(...))
    {
        using (var reader = command.ExecuteReader(...))
        {
            ...
        }
    }
}

这篇关于C#中处理SqlCommand的概念的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！