CODEWITHSUNDEEP
c#sql-serverwindows-servicessqlexception
Steve
Steve

Reputation: 965

C# Windows Service SqlCommand Hangs

I have a Windows services running in .NET 4.5. Everything works fine. However, when my service encounters a SqlException, it hangs (turns into a zombie).

I have a timer (System.Timers) that calls process. In process, locate cmd.ExecuteReader(). If I remove EXECUTE permissions from the stored procedure, I receive a SqlException (as expected). When this happens, the service simply hangs.

I would have expected one of the try {} catch blocks to capture the exception and exit the method gracefully. However, the system appears to hang on this call. I had a number of Trace statements in the code. I removed them so it would be easier to read.

private void TimerForNotification_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
    TimerForNotification.Stop();
    int count = new GetSMSNotifications().process();
    TimerForNotification.Start();
}

public int process()
{
    int count = 0;

    // Get the ConnectionStrings collection.
    ConnectionStringSettings connections = ConfigurationManager.ConnectionStrings["DE_OLTP"];

    try
    {
        using (SqlConnection conn = new SqlConnection(connections.ConnectionString))
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("[dbo].[get_SMSToSend]", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            try
            {
                SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    // increment counter
                    count++;

                    string destinationAddress = Convert.ToString(dr[dr.GetOrdinal("DestinationAddress")]);
                    string alertMessage = Convert.ToString(dr[dr.GetOrdinal("Content")]);

                    // Send out the notification
                    sendPush(destinationAddress, alertMessage);
                }

                dr.Close();
            }
            catch (SqlException se)
            {
                DELog.Log.Error(se);
            }
        }
    }
    catch (Exception ex)
    {
        DELog.Log.Error(ex);
    }

    return count;
}

Interestingly, I created a console app that calls the above method and the try {} catch block works as expected.

I don't see any unhandled exceptions in the event log.

Thoughts?

Upvotes: 2

Views: 721

Answers (2)

Steve
Steve

Reputation: 965

Problem solved. The reason why it appeared my service was hanging is because I was a missing a reference to Entity.Framework.dll. When the service ran into an exception, the EF dll could not be found. I use the EF in my logging layer.

I was able to discover this issue by installing my service and then ATTACHing to the run process.

Upvotes: 1

Raja Nadar
Raja Nadar

Reputation: 9499

Try these things:

Add trace line to these:

TimerForNotification.Stop();

try
{
 int count = new GetSMSNotifications().process();
}
catch (Exception ex)
{
 // trace line here with ex.ToString().
}

TimerForNotification.Start();
// trace line here stating, i started at DateTime.Now;

Remove execute permissions and see if the above code generates a trace line.

If it does, then the exception details will show up. (even though your method looks very safe) Fix it accordingly.

If the catch doesn't trace, then you should see trace lines for the post start trace which means the service is working as expected.

Upvotes: 0

Related Questions