How to force SQL query return updated data in .NET?

My C# program connects to a user request SQL database, gets the first open request, process it and mark the request as closed. Then I get the next open request.

The problem is that I always get the same request back from SQL Query although it is marked as 'closed'. I suspect I get a cached result instead of updated data. But I don't know how to clear that cache.

I tried to dispose the SQLDataAdpater and create new one every time. I also tried to add a random number as parameter to the SQL Select stored procedure. None开发者_C百科 of them worked.

Can anyone please help me on this issue? Thanks.

The Sql query is:

Select Top(1) RequestID, RequestType, RequestXML from Request 
where RequestStatus='OP' 

SQL Update command:

begin tran  
Update Request Set RequestStatus=@RequestStatus where RequestID=@RequestID;  
if (@RequestXML is not null)   
    Update Request Set RequestXML=@RequestXML where RequestID=@RequestID;  
commit tran  

C# Code:

SqlDataAdapter da = new SqlDataAdapter("SrvGetOpenRequest", cn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlCommand cmd = new SqlCommand("SrvUpdateRequest", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("RequestID", SqlDbType.Int);
cmd.Parameters.Add("RequestStatus", SqlDbType.Char);
cmd.Parameters.Add("RequestXML", SqlDbType.Xml);
DataTable dt = new DataTable();
cn.Open();
da.Fill(dt);
cn.Close();
while (dt.Rows.Count > 0)
{
 // Process returned datatable here.
   ..............

    cmd.Parameters["RequestStatus"].Value = "CL";
    cn.Open();
    cmd.ExecuteNonQuery();
    // fetch the next request to process
    da.Fill(dt);
    cn.Close();
 }

I did checked the database and the record was marked as closed.

Try calling dt.Clear() before filling it again.

Fill method adds rows to existing DataTable

Post some code, please. It's possible that you're not actually persisting the changes you make locally back to your actual database, which is why you keep getting the original unchanged data back from your query. It may also be possible that you're making the changes inside a transaction, but then not committing the transaction.

Does DataAdapter.Fill clear the DataTable first? I wonder if you're appending new rows to the end of the DataTable each time?