0

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());
                }

Second part:

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();
                .
                .
                .
            }
    }
}

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.
Improve this question

3 Answers 3

Reset to default
4

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?

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(...))
        {
            ...
        }
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I'm sorry if I didnt understand it correctly as it still complaining about the same error..I am using your code snippet in my main method and another set of it in my sendMail method(), it should be working right?
@IsaacLem: You haven't told us what error you're talking about, which makes it very hard to say what's wrong. But yes, if you're creating a separate connection in each piece of code, that should be okay from that perspective. There may be an issue in terms of transactionality (reading from a query while updating the data it works on) but that's a different matter.
I've edited my question which includes the error message. Both way giving me the same error message. Imagine the first query returning me 3 rows A, B, and C. I'm now trying to update one of the column of row A. Will there be any problem with it?
You shouldn't be getting that exception when you've got two completely separate connections, commands and readers. Are you sure your new code looks like mine, declaring new variables etc?
My bad. I tends to create another variable with the same name to let it override the old one which obviously will be giving the error. Thank you so much!
|
0

As another alternative. You could add this to your connection string for the database.

MultipleActiveResultSets=True

There is a small performance degradation. And its not supported in older SQL Servers. I think pre 2005

Improve this answer

3 Comments

I'm using sqlConnection which something like below: static SqlConnection myConnection = new SqlConnection("user id=xxx;" + "password=xxx;server=xxx;" + "Trusted_Connection=yes;" + "database=xxx; " + "connection timeout=30"); You way is not workable for this?
Depending on version of SQL Server, all you need to do is add the MultipleActiveResultSets=true to the string i.e. new SqlConnection("user id=xxx;" + "password=xxx;server=xxx;" + "Trusted_Connection=yes;" + "database=xxx; " + "connection timeout=30;multipleactiveresultsets=true");
I'm using SSMS 2008, it giving me an sqlinitialize exception
0

Try this code..`

 public static void sendEmail(string emailAdd,string userID){
  try
    {
        SqlConnection con = new SqlConnection(@"");
        con.Open();

        MailMessage mail = new MailMessage("[email protected]", emailAdd);
        SmtpClient smtpClient = new SmtpClient();
        NetworkCredential nc = new NetworkCredential("[email protected]", "test");

        smtpClient.Port = 587;
        smtpClient.Host = "smtp.gmail.com";
        smtpClient.EnableSsl = true;
        smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
        smtpClient.UseDefaultCredentials = false;
       // smtpClient.Host = "smtp.google.com";
        mail.Subject = "this is a test email.";
        mail.Body = "this is my test email body";
        smtpClient.Credentials = nc;
        smtpClient.Send(mail);

        using (SqlCommand cmd = con.CreateCommand())
        {
            string sql = "UPDATE TestTable SET IsSent = 'true' WHERE  UserID = " + userID;
            SqlCommand myCommand = new SqlCommand(sql, con);
            int rows = myCommand.ExecuteNonQuery();
        }
    }
    catch(Exception e)
    {
        string message = e.Message;
    }`
 }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.