Concept of handling SqlCommand in C#

Question

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.

score 4 · Accepted Answer · answered Sep 11 '13 at 09:28

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

answered Sep 11 '13 at 09:28

Jon Skeet

1,421,763
867
9,128
9,194

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? – SuicideSheep Sep 11 '13 at 09:51
@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. – Jon Skeet Sep 11 '13 at 09:57
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? – SuicideSheep Sep 11 '13 at 10:03
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? – Jon Skeet Sep 11 '13 at 10:12
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! – SuicideSheep Sep 11 '13 at 10:23
I'm having another question, by using the code snippet u've given, after I retrieve a list of records, now I would like to store each of the result into another table. Do I have to use another new `SqlConnection`? – SuicideSheep Sep 13 '13 at 01:51
@IsaacLem: You probably don't *have* to, but I certainly would. I would create a new connection for each "block" of database work. – Jon Skeet Sep 13 '13 at 05:57

score 0 · Answer 2 · answered Sep 11 '13 at 09:33

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

answered Sep 11 '13 at 09:33

Valeklosse

1,017
3
19
36

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? – SuicideSheep Sep 11 '13 at 10:04
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"); – Valeklosse Sep 11 '13 at 10:08
I'm using SSMS 2008, it giving me an sqlinitialize exception – SuicideSheep Sep 11 '13 at 10:11

score 0 · Answer 3 · answered Sep 11 '13 at 13:16

Try this code..`

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

        MailMessage mail = new MailMessage("example@gmail.com", emailAdd);
        SmtpClient smtpClient = new SmtpClient();
        NetworkCredential nc = new NetworkCredential("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 (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;
    }`
 }

Concept of handling SqlCommand in C#

3 Answers3

Linked