Error updating using SqlCommand

Question

Error executing the code, I am using C# & SQL Server 7 / 2000.

Steps I am doing are:

• get all the tickets
• then I read each ticket
• pass to web service method for validation
• if the return is true I need to update a table.

Please review my code, appreciate feedback on how to fix it.. as it is been driving me crazy for couple of days! Thanks!

    public void Execute()
    {
        SqlConnection conn = null;
        SqlConnection conn2 = null; 
        SqlDataReader rdr = null;

        try
        {
            Helper helper = new Helper();
            using (conn = new SqlConnection(helper.MISDBConnectionString))
            {
                conn.Open();

                string ticketid = null;
                bool bTerm = false;
                int rowsAffected = 0;

                if (rdr != null) { rdr.Close(); }
                SqlCommand selectCommand = new SqlCommand();
                selectCommand.CommandText = "SELECT ...";
                selectCommand.Connection = conn;
                rdr = selectCommand.ExecuteReader();

                while (rdr.Read())
                {
                    ticketid = rdr["ticketid"].ToString();
                    bTerm = calling webserver for validation

                    if (bTerm)
                    {                           
                        using (conn2 = new SqlConnection(helper.MISDBConnectionString))
                        {
                            conn2.Open();
                            SqlCommand updateCommand = new SqlCommand();

                            updateCommand.CommandText = "UPDATE ticket  SET code = @code WHERE ticketid = @ticketid";
                            updateCommand.CommandType = CommandType.Text;
                            updateCommand.CommandTimeout = 120;
                            updateCommand.Parameters.AddWithValue("@code", 8);
                            updateCommand.Parameters.AddWithValue("@ticketid", ticketid);
                            updateCommand.Connection = conn2;

                            rowsAffected = updateCommand.ExecuteNonQuery(); //fails here
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            logger.Error(ex.ToString());
        }
        finally
        {
            if (conn != null) { conn.Close(); }
            if (conn2 != null) { conn2.Close(); }
            if (rdr != null) { rdr.Close(); }
        }
    }

I need to use two connections because it gives me an error if I use one that the dataReader needs to be closed before I do the update

The error I am currently getting:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at GDR.UpdateTicket.Execute()

Answer 1

It's hard to tell what is causing your timeout, most likely you are creating a deadlock. But, here is what I think you should change first:

Don't use second connection, read all your data first, close reader and then process it using the same or another connection. If it's too much data - do it in pages.

I mean something like this

List<string> ids = new List<string>

while (rdr.Read())
{
  ids.add(rdr["ticketid"].ToString())
}

rdr.Close();

foreach(id in ids)
{
 // ...

Also, you don't need to do this

if (conn != null) { conn.Close(); }
if (conn2 != null) { conn2.Close(); } 

using(conn=new ...) statement does that for you.