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()

Anri · Accepted Answer · 2014-02-10T21:22:32.933

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.

edited Feb 10 '14 at 21:22

answered Feb 10 '14 at 21:11

Anri

6,175
3
37
61

Thx, i think this could work, what i wanted to do is not to store the tickets anywhere, wanted to grab each ticketid then check it, if true update it, then go to the next ticketid..etc does this makes sense? – Benk Feb 10 '14 at 21:17
Well, if you don't want to store them (although I don't see why not) you can read them one by one using ExecuteScalar() since you only return one value. This will be much less efficient. – Anri Feb 10 '14 at 21:19
I have to agree with Anri, unless you have like 100+ columns in your SELECT statement, why wouldn't you want to store the result in a DataTable and then iterate through the DataTable? Are you running this on a TRS-DOS80? – Aaron Hawkins Feb 10 '14 at 21:26

score 0 · Answer 2 · answered Feb 10 '14 at 21:22

I agree with Anri that this is most likely due to a locking issue. Have you tried using WITH (NOLOCK) in your SELECT statement to see if that resolves the issue? This should allow you to update the table while iterating through the results without the SELECT statement holding a read lock which prevents the UPDATE from executing.

Error updating using SqlCommand

2 Answers2