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 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.
