Concept Of Handling Sqlcommand In C#

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

Solution 1:

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

Solution 2:

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

Solution 3:

Try this code..`

publicstaticvoidsendEmail(string emailAdd,string userID){
  try
    {
        SqlConnectioncon=newSqlConnection(@"");
        con.Open();

        MailMessagemail=newMailMessage("example@gmail.com", emailAdd);
        SmtpClientsmtpClient=newSmtpClient();
        NetworkCredentialnc=newNetworkCredential("example@gmail.com", "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 (SqlCommandcmd= con.CreateCommand())
        {
            stringsql="UPDATE TestTable SET IsSent = 'true' WHERE  UserID = " + userID;
            SqlCommandmyCommand=newSqlCommand(sql, con);
            introws= myCommand.ExecuteNonQuery();
        }
    }
    catch(Exception e)
    {
        stringmessage= e.Message;
    }`
 }