I already plan on doing this differently with webhooks, but for now I just need to show a PoC for this where the browser client will check on intervals if there's any changes to an SQL Server database and return a boolean value if it detects a change, where then my client side script will refresh the page if the bool value is true.
<script>
$(document).ready(function () {
function refreshPage() {
window.location.href = data.rtnurl;
}
setInterval(function () {
$.ajax({
url: 'testscript', //script to check data base update
type: 'GET',
success: function (data) {
if (data === 'true') {
refreshPage();
alert("New data was added.");
}
}
});
}, 30000);
});
</script>
Here's what I have right now, and was wondering how I would be able to use the URL to run a script to check for SQL Server updates.
Well, if the database has a "row version" column, then that value will change when that row record is updated.
Hence, a simple web method can "sum up" the time stamp column (row version column). Thus, adding of records, deleting of records, or editing of records will produce a different total for the row version column.
While the name in SQL server is "timestamp", the column is a big int, and it that value is incremented each time the row been changed, and has nothing to do with time at all, despite its name.
So, say this GridView and markup:
<asp:HiddenField ID="datacheck" runat="server" ClientIDMode="Static" />
<asp:GridView ID="GVHotels" runat="server" AutoGenerateColumns="False"
DataKeyNames="ID" CssClass="table table-hover"
Width="45%">
<Columns>
<asp:BoundField DataField="FirstName" HeaderText="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="HotelName" HeaderText="HotelName" />
<asp:BoundField DataField="Description" HeaderText="Description" />
<asp:TemplateField HeaderText="Active"
ItemStyle-HorizontalAlign="Center"
HeaderStyle-HorizontalAlign="Center" ItemStyle-Width="120px">
<ItemTemplate>
<asp:CheckBox ID="chk1" runat="server"
Checked='<%# Eval("Active")%>'
/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Button ID="cmdReLoad" runat="server"
Text="Reload data"
OnClick="cmdReLoad_Click"
ClientIDMode="Static"
/>
<script>
var mytimer
$(document).ready(function () {
mytimer = setInterval(mycheck,1000)
})
function mycheck() {
var checkvalue = $('#datacheck').val()
$.ajax({
type: "POST",
url: "SimpleGrid.aspx/GetChanged",
contentType: "application/json; charset=utf-8",
dataType: "json",
data: {},
success: function (mydata) {
if (checkvalue != mydata.d) {
// refresh grid - data has changed
$('#cmdReLoad').click()
}
},
error: function (xhr, status, error) {
var errorMessage = xhr.status + ': ' + xhr.statusText
alert('Error - ' + errorMessage)
}
});
}
</script>
We placed a hidden field, and we set the value to the sum() of the row version value.
And then client side, we test/check that value against a web method we have for that page.
Hence, code behind looks like this:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadGrid();
}
void LoadGrid()
{
string strSQL = @"SELECT * FROM tblhotelsA
ORDER BY HotelName";
GVHotels.DataSource = General.MyRst(strSQL);
GVHotels.DataBind();
datacheck.Value = GetChanged().ToString();
}
[WebMethod]
public static long GetChanged()
{
string strSQL =
"select sum(cast(ts as bigint)) as MyChanged from tblhotelsA";
DataTable dtChanged = General.MyRst(strSQL);
long MyChanged = (long)dtChanged.Rows[0]["MyChanged"];
return MyChanged;
}
protected void cmdReLoad_Click(object sender, EventArgs e)
{
LoadGrid();
}
So, if the data has been changed, then we click a button to re-load the GridView. Of course, once all is working fine, then you can hide that button that the jQuery code clicks with this:
<asp:Button ID="cmdReLoad" runat="server"
Text="Reload data"
OnClick="cmdReLoad_Click"
ClientIDMode="Static"
style="display:none"
/>
So, we check the server every 1 second, and if the data been changed, then we click the button to re-load the GridView.
As noted, returning a true/false value, or returning a value that we check for a change quite much amounts to the same result.
However, assuming you JUST looking for some web method to return true, or false, then that suggests we have to persist the "sum()" value server side, and that also can be done.
So, the server side code then becomes this:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadGrid();
}
void LoadGrid()
{
string strSQL = @"SELECT * FROM tblhotelsA
ORDER BY HotelName";
GVHotels.DataSource = General.MyRst(strSQL);
GVHotels.DataBind();
GetChanged();
}
[WebMethod ]
public static bool GetChanged()
{
string strSQL =
"select sum(cast(ts as bigint)) as MyChanged from tblhotelsA";
DataTable dtChanged = General.MyRst(strSQL);
long MyChanged = (long)dtChanged.Rows[0]["MyChanged"];
long previous = 0;
if (HttpContext.Current.Session["checksum"] == null)
{
HttpContext.Current.Session["checksum"] = MyChanged;
previous = MyChanged;
}
else
{
previous = (long)HttpContext.Current.Session["checksum"];
}
return (MyChanged != previous);
}
And client side code is now this:
function mycheck() {
$.ajax({
type: "POST",
url: "SimpleGrid.aspx/GetChanged",
contentType: "application/json; charset=utf-8",
dataType: "json",
data: {},
success: function (mydata) {
if (mydata.d) {
alert('data been changed')
}
},
error: function (xhr, status, error) {
var errorMessage = xhr.status + ': ' + xhr.statusText
alert('Error - ' + errorMessage)
}
});
}
Of course, one more addition is that when you trigger the refresh code (not clear what that routine does), then you need to re-set the session value server side. So, the 2nd example now returns true, or false from the server. Note that the data.d (the ".d") part is how asp.net web forms returns a value, and you have to use ".d" if you are using webforms.