c# - SqlDependency.OnChange firing but SqlDataReader is not returning with data -
when execute query datetime column filter
where [order].createdon >= @createdon using sqldependency, change on data source fires sqldependency.onchange event sqldatareader associated sqlcommand doesn't return data (reader.hasrows returns false).
when change filter condition in sql statement
where [order].statusid = 1" it works fine , sqldatareader returns data (reader.hasrows returns true)
code:
using system; using system.collections.generic; using system.configuration; using system.data; using system.data.sqlclient; using system.linq; using system.web; using system.web.ui; using system.web.ui.webcontrols; namespace signalrserver { public partial class departmentscreen : system.web.ui.page { protected void page_load(object sender, eventargs e) { var u = system.security.principal.windowsidentity.getcurrent().user; var username = u.translate(type.gettype("system.security.principal.ntaccount")).value; checkforneworders(datetime.now); } private void checkforneworders(datetime dt) { string json = null; string constr = configurationmanager.connectionstrings["connstring"].connectionstring; using (sqlconnection connection = new sqlconnection(constr)) { string query = string.format(@" select [order].orderid [dbo].[order] [order].createdon >= @createdon"); // query = string.format(@" // select [order].orderid // [dbo].[order] // [order].statusid = 1"); using (sqlcommand command = new sqlcommand(query, connection)) { command.parameters.add("@createdon", sqldbtype.datetime); command.parameters["@createdon"].value = datetime.now; command.notification = null; sqldependency dependency = new sqldependency(command); dependency.onchange += new onchangeeventhandler(dependency_onchange); connection.open(); sqldatareader reader = command.executereader(); if (reader.hasrows) { reader.read(); json = reader[0].tostring(); } } } signalrhub hub = new signalrhub(); hub.orderreceived(json, null); } private void dependency_onchange(object sender, sqlnotificationeventargs e) { if (e.type == sqlnotificationtype.change) { checkforneworders(datetime.now); } else { //do somthing here //console.writeline(e.type); } } } } images:
when passing datetime.now reference date not retrieve records have been created @ point in time (except if records created in future , therefore have problem server time or column name "createdon" missleading).
to latest records based on update date need this:
- create global variable saves max created date have retrieved (
_refdatein example, initialized value choose, datetime.minvalue in case records in first call , them incrementally, can take datetime.now start @ 1 moment in time) - trigger checkforneworders query
- when retrieve results send createdon column , save maximum retrieved createdon date new reference date
- when value changes in db ,
dependency_onchangeevent gets triggered need trigger query last value of_refdatein order haven't retrieved yet - update value of
_refdateagain , on ..
not tested should work (take care of _refdate acccessible globally)
public partial class departmentscreen : system.web.ui.page { protected void page_load(object sender, eventargs e) { var u = system.security.principal.windowsidentity.getcurrent().user; var username = u.translate(type.gettype("system.security.principal.ntaccount")).value; checkforneworders(_refdate); } private datetime _refdate = datetime.minvalue; private void checkforneworders(datetime dt) { string json = null; string constr = configurationmanager.connectionstrings["connstring"].connectionstring; using (sqlconnection connection = new sqlconnection(constr)) { string query = string.format(@" select [order].orderid, [order].createdon [dbo].[order] [order].createdon >= @createdon"); // query = string.format(@" // select [order].orderid // [dbo].[order] // [order].statusid = 1"); using (sqlcommand command = new sqlcommand(query, connection)) { command.parameters.add("@createdon", sqldbtype.datetime); command.parameters["@createdon"].value = dt; command.notification = null; sqldependency dependency = new sqldependency(command); dependency.onchange += new onchangeeventhandler(dependency_onchange); connection.open(); sqldatareader reader = command.executereader(); if (reader.hasrows) { while (reader.read()) { //json = reader[0].tostring(); var date = convert.todatetime(reader["createdon"]); if (date > _refdate) { _refdate = date; } } } } } //signalrhub hub = new signalrhub(); //hub.orderreceived(json, null); } private void dependency_onchange(object sender, sqlnotificationeventargs e) { if (e.type == sqlnotificationtype.change) { checkforneworders(_refdate); } else { //do somthing here //console.writeline(e.type); } } } }
Comments
Post a Comment