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

my c# program connects user request sql database, gets first open request, process , mark request closed. next open request.

the problem same request sql query although marked 'closed'. suspect cached result instead of updated data. don't know how clear cache.

i tried dispose sqldataadpater , create new 1 every time. tried add random number parameter sql select stored procedure. none of them worked.

can please me on issue? thanks.

the sql query is:

select top(1) requestid, requesttype, requestxml request  requeststatus='op'  

sql update command:

begin tran   update request set requeststatus=@requeststatus requestid=@requestid;   if (@requestxml not null)        update request set requestxml=@requestxml 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 next request process     da.fill(dt);     cn.close();  } 

i did checked database , record marked closed.

try calling dt.clear() before filling again.

fill method adds rows existing datatable