Timeouts for process that can take hours

I have a web service that sends up to 5000 emails to our customers.

It is called asynchronously from a web application and then chuggs away sending the emails. Generally it works okay. Yesterday, it kept stopping - sometimes after sending a small number (33 first time) and then it might send another couple of hundred - then 50 or so etc. This went on all day as I kept manually calling the web service.

The data (subject, body, fromaddress, replytoaddress etc) is all kept in the database - as are the recipients.

Here's the code that gets the data from the database.

Code:

// Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConnectionString); SqlCommand myCommand = new SqlCommand("CPEmailsToSend_List", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; myCommand.CommandTimeout = 1000; // Add Parameters to SPROC myCommand.Parameters.Add("@CPEmailID", SqlDbType.Int).Value = CPEmailID; DataSet ds; try { //create the DataAdapter & DataSet myConnection.Open(); SqlDataAdapter da = new SqlDataAdapter(myCommand); ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds, "Message"); da.Fill(ds, "Attachments"); da.Fill(ds, "Recipients"); } finally { myConnection.Close(); }

So I have a dataset containing 3 sets of data - the subject, body etc., a list of attachments and a list of recipients

I then use the .net smtpClient like this:

Code:

//about to start sending emails - update the MessageSendStatus to inprogress myCommand.CommandText = "CPEmailSendStatus_Update"; myCommand.Parameters.Clear(); myCommand.Parameters.Add("@CPEmailID", SqlDbType.Int).Value = CPEmailID; myCommand.Parameters.Add("@SendStatus", SqlDbType.TinyInt).Value = 1; myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); bool ErrorHappened = false; foreach (DataRow dr1 in dtRecipients.Rows) { ErrorHappened = false; MailMessage message = new MailMessage(); message.BodyEncoding = System.Text.Encoding.UTF8; EmailAddress = dr1["EmailAddress"].ToString(); Recipient = dr1["Recipient"].ToString(); //create the views AlternateView plainView = AlternateView.CreateAlternateViewFromString(MessagePlain.ToString(), null, "text/plain"); AlternateView htmlView = AlternateView.CreateAlternateViewFromString(MessageHeader + Message.ToString() + MessageFooter, null, "text/html"); //add the views message.AlternateViews.Add(plainView); message.AlternateViews.Add(htmlView); if (isEmail(EmailAddress) == true) { try { MailAddress fromAddress = new MailAddress(ReplyToAddress, "Name of Company"); MailAddress toAddress = new MailAddress(EmailAddress, Recipient); MailAddress replytoAddress = new MailAddress(ReplyToAddress, "Name of Company"); message.From = fromAddress; message.To.Add(toAddress); message.IsBodyHtml = true; message.ReplyTo = replytoAddress; message.Subject = Subject.ToString(); foreach (DataRow dr2 in dtAttachments.Rows) { string AttachPath = dr2["Attachment"].ToString(); if (File.Exists(@AttachPath)) { message.Attachments.Add(new System.Net.Mail.Attachment(@AttachPath)); } else //if the file does not exist record an error { //already have connection and command objects myCommand.CommandText = "CPEmailError_Add"; myCommand.Parameters.Clear(); myCommand.Parameters.Add("@EmailMessageID", SqlDbType.Int).Value = CPEmailID; myCommand.Parameters.Add("@EmailRecipientID", SqlDbType.Int).Value = Convert.ToInt32(dr1["CPEmailRecipientID"].ToString()); myCommand.Parameters.Add("@ErrorDescription", SqlDbType.VarChar, 1000).Value = "Attachment " + dr2["Attachment"].ToString() + " not found."; myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } } smtpClient.Host = "AJXMailServer4"; smtpClient.Send(message); } catch (Exception ex) { ErrorHappened = true; //write the exception to the database - already have connection and command objects myCommand.CommandText = "CPEmailError_Add"; myCommand.Parameters.Clear(); myCommand.Parameters.Add("@EmailMessageID", SqlDbType.Int).Value = CPEmailID; myCommand.Parameters.Add("@EmailRecipientID", SqlDbType.Int).Value = Convert.ToInt32(dr1["CPEmailRecipientID"].ToString()); myCommand.Parameters.Add("@ErrorDescription", SqlDbType.VarChar, 1000).Value = ex.Message.ToString().Substring(0, 999); myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } finally { if (!ErrorHappened) { //to do - confirm by updating the Recipient Row using dr["DListEmailRecipientID"] //already have connection and command objects myCommand.CommandText = "CPEmailSentRecipient_Update"; myCommand.Parameters.Clear(); myCommand.Parameters.Add("@EmailRecipientID", SqlDbType.Int).Value = Convert.ToInt32(dr1["CPEmailRecipientID"].ToString()); myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); } } } message.Dispose(); } ds.Dispose(); //finished sending emails - update the MessageSendStatus to finished myCommand.CommandText = "CPEmailSendStatus_Update"; myCommand.Parameters.Clear(); myCommand.Parameters.Add("@CPEmailID", SqlDbType.Int).Value = CPEmailID; myCommand.Parameters.Add("@SendStatus", SqlDbType.TinyInt).Value = 2; myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close();

So, as you will see - I update the database to say 'message send is starting' and then, as each email is sent, update the recipient MessageSent flag - if there are any errors I write them to an error table and, when it's finished - update the SendStatus to 'finished'.

When the web service stopped (it kept stopping yesterday) - no errors were raised. I have a function on the page that checks the email address is valid before I try to send the email as I know the smtpClient does not like invalid email addresses. Normally, invalid email addresses are trapped and the loop continues and sends the next one etc. But, yesterday, it just stopped sending. No error messages - nothing.

So, I began to wonder if the Command object is timing out. With a lot of email addresses to send to (up to 5000) and half a dozen attachments - the web service can take a long time (hours) to send all the emails. So, is the fact that I am reusing the command object over and over again for different stored procedures the issue? (I do this a lot and have never had a problem before - I read somewhere that the CommandTimeout only counts the time network access is used).

Welcome any comments or ideas what else I can try. I've been sending test emails all morning (but I can only send small batches as I have a limited number of email addresses I can send test emails to) and they have all worked okay ... but, yesterday, trying to send about 5000 emails took about 30 attempts.