Database corrupted: data update notification

Please Help!!I have been trying to think of a solution to a problem i have and still not sure how to go about it.

I have a database filled with message data that is sent by one user to another.

What i am trying to do is have it so (if users are online) they are notified - by a popup or something - that a new message has arrived forthem.

My idea was to create a trigger on the message table to update a change table with sender and recipient ids and date of change in it.

Not a problem so far but what is troubling me is that i want the recipient to automatically query this change table every say 30 seconds to check if new message has arrived with their id.

Tryed using ajax update panel and timer but naturally that kept posting back whole page - looks really terriable :( - tryed sqlnotification but as far as i can see that is only changing cached data for current data - not the same issue i think.

Is there a way to query db table regualry without the client side knowing? - thinking along lines of how outlook requeries server for new messages and displays popup to say new message but doesnt interupt you if doing something else.

Any help would begreatly appreaciated.

Thanks

I am using the following in one of my apps. In case you need help understanding some part, get back to me. What is happening is that I have a webservice that I call on a fixed interval from my page using javascript. Based on the result returned by the webservice, I am using javascript to inform the user by setting certain UI elements.

In aspx page

<asp:ScriptManager ID="ScriptManager1" runat="server" ScriptMode="Release" LoadScriptsBeforeUI="true">
<Services>
<asp:ServiceReference Path="CargoListDB.asmx" />
</Services>
</asp:ScriptManager>

function GetFleetFlagStatus() {
var userContext;
CargoListDB.GetFleetFlagStatus(lastChecked, OnSucceededGetFleetFlagStatus, null, userContext, null);
}
function OnSucceededGetFleetFlagStatus(result, userContext) {

//result is the result returned by webservice

//your code
}

</script>

Webservice

<%@. WebService Language="VB" Class="CargoListDB" %
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.OracleClient

<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<System.Web.Script.Services.ScriptService()> _
Public Class CargoListDB
Inherits System.Web.Services.WebService

<WebMethod()> _
Public Function GetFleetFlagStatus(ByVal lastChecked As String) As String
'your code
Return result
End Function

End Class|||

Hey Prashant,

Thanks for replying - been trying to play around with this webservice idea but not really getting anywhere unfortunatly - doesnt even seem to be calliing it.

Will keep playing with it tho.

Cheers

|||

Post your code (both webservice and the calling code). I might be able to help you fix it.

|||

// in head of aspx page

// scriptmanager

<asp:ScriptManager ID="ScriptManager1" runat="server" ScriptMode=Release LoadScriptsBeforeUI=true>
<Services >
<asp:ServiceReference Path="MessageService.asmx" />
</Services>
</asp:ScriptManager>

// webservice page

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

/// <summary>
/// Summary description for MessageService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class MessageService : System.Web.Services.WebService
{

public MessageService ()
{

//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public string getMessage()
{

string connString = ConfigurationManager.ConnectionStrings["EMS_App_DBConnectionString"].ConnectionString;

SqlConnection conn = new SqlConnection(connString);

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM EMS_ChangeEvent_Msg WHERE EMS_ChangeEvent_Msg_ID = @.EMS_ChangeEvent_Msg_ID";
cmd.Parameters.Add("@.EMS_ChangeEvent_Msg_ID", SqlDbType.Int, Int32.MaxValue).Value = 8;

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

// get returned data and place in data table -
DataTable taskTable = new DataTable();
taskTable.Load(reader);

string info = taskTable.Rows[0]["EMS_ChangeEvent_Msg_Date"].ToString();
conn.Close();

return info;
}

}

if i just run the webservice in debug it works - gets value, hits breakpoints etc so am guessing my complete lack of
javascript understanding is to blame or do i need to initialise webservice anywhere?

Am running on localhost during this test - call is in master page

WebServices are another new thing to me so not up to play yet

Thanks for looking at

|||

In order for your webservice to be callable by clientscript, you need to have the following attribute defined for the webservice

System.Web.Script.Services.ScriptService()

/// <summary>
/// Summary description for MessageService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class MessageService : System.Web.Services.WebService
{

//your code

In web.config, you need the following in the configuration section (it might already be there, just confirm)

Refer to the following link on the configuration requirements

http://ajax.asp.net/files/Migration_Guide_Beta2_to_RC.aspx

|||

Hey,

Still no go unfortunatly.

If run app and have breakpoints in webservice .cs they never get hit so never being called in first place.

Can it be run in local host? - shld be i'm sure but never know.

|||

Just to make sure, as per your code, the page and webservice should be in the same folder.

Can you email your complete test project (page, webservice and web.config)? Easier to fix that way.

|||

Hey, Thanks for taking the time here are the pages - the service is in the same folder - as in the asmx - but the .cs file was put in the app code folder which from what i've read is just where it goes automatically.

As this is an add on to my project that has all sorts of other stuff going on :) waswondering does authentication make a diff?

Anyway the pages-

web-config ( have had system.webserver with the http handlers in there also at end just took back out before post)

<?xml version="1.0"?>

<configuration>
<appSettings>
<add key="webReference.MessageService" value="http://localhost:1035/EMS_Application_01/MessageService.asmx"/>
</appSettings>
<connectionStrings>
<add name="EMS_App_DBConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EMS_App_DB.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add connectionStringName="EMS_App_DBConnectionString" name="EMS_App_DB" pollTime="1000"/>
</databases>
</sqlCacheDependency>
</caching>


<compilation debug="true" defaultLanguage="c#">

<compilers>
<compiler language="c#" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" extension=".cs" compilerOptions="/d:DEBUG;TRACE"/>
</compilers>

<assemblies>
<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

<add assembly="CrystalDecisions.ReportSource, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
<add assembly="CrystalDecisions.Enterprise.Framework, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
<add assembly="CrystalDecisions.Enterprise.InfoStore, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>

<add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add assembly="System.Web.Extensions.Design, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add assembly="System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
<add assembly="System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
<add assembly="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
<add assembly="System.Web.Services, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.DirectoryServices.Protocols, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.ServiceProcess, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="System.Web.RegularExpressions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies>
</compilation>

<authentication mode="Forms">
<forms loginUrl="SiteLogon.aspx" name="sqlAuthCookie" timeout="10" path="/" protection="All">
</forms>
</authentication>
<authorization>
<deny users="?"/>
<allow users="*"/>
</authorization>

<sessionState mode="InProc" cookieless="false" timeout="10">
</sessionState>

<xhtmlConformance mode="Legacy"/>
<httpHandlers>

<remove verb="*" path="*.asmx"/>
<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
<add verb="GET" path="CrystalImageHandler.aspx" type="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
</httpHandlers>

</system.web
</configuration>

.master page

<%@. Master Language="C#" AutoEventWireup="true" EnableTheming="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %>
<%@. Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
Namespace="System.Web.UI" TagPrefix="asp" %
<%@. Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<BASE target="_self"></BASE
<title></title>
<meta http-equiv="Page-Exit" content="progid:DXImageTransform.Microsoft.Fade(duration=.3)" />

<script language=javascript>

function testWin()
{

window.open('SetMsgOptions.aspx','_blank','height=200,width=500,status=yes,toolbar=no,menubar=no, chromeless=true,titlebar=no,location=no,left=window.event.clientY,top=window.event.clientX')

}
function winClose()
{
window.close();
}
</script>



<script language=javascript type="text/javascript">
window.setInterval("getMessage()", 10000);

function getMessage()
{
var userContext;
userContext = document.getElementById("lbltime").value
MessageService.getMessage(lastChecked, OnSuccededGetMessage, null, userContext, null);

}

function OnSuccededGetMessage(result, userContext)
{

window.alert("new message");

}

</SCRIPT>

</head>
<body >
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" ScriptMode=Release LoadScriptsBeforeUI=true>
<Services >
<asp:ServiceReference Path="MessageService.asmx" />
</Services>
</asp:ScriptManager>





<div id=title>

<asp:Label ID="lblPageTitle" runat="server" ></asp:Label>



<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:HiddenField ID="lbltime" Value="24/05/2007 10:42:20 p.m." runat="server" />

</div>

<div id=headerPos >
<div id=toprightheadcorner></div>
<div id=bottomleftheadcorner></div>

<div id=user>

<table width=100% align=right class=normalFont>
<tr><td valign=middle align=right style="height: 65px">

<asp:Label ID="lblEntity" runat="server" Text=""></asp:Label>

</td>
<td width=20px style="height: 55px">
<asp:Image ID="imgEntity" runat="server" />
</td></tr>
</table>

</div>

<div >
<table width=100% align=right class=normalFont>
<tr><td valign=bottom align=right style="height: 55px">

<asp:LoginName ID="LoginName1" runat="server" Font-Bold="True" Font-Names="Verdana" Font-Size="Smaller" FormatString="Signed into: {0} " />
||
<asp:LoginStatus ID="LoginStatus1" CssClass=normalLinkFont runat="server" LogoutAction="RedirectToLoginPage" LogoutPageUrl="~/SiteLogon.aspx" OnLoggingOut="LoginStatus1_LoggingOut" Font-Bold="True" Font-Names="Verdana" Font-Size="Smaller" />
</td>
<td width=20px style="height: 55px"></td></tr>
</table>
</div>

</div>





<div id=navTag>

<div id=infolabel>
<asp:Label ID="lblInfo" runat="server" CssClass=infoFont Text="Open Events:"></asp:Label>
</div>

</div>

MessageService page

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

/// <summary>
/// Summary description for MessageService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class MessageService : System.Web.Services.WebService
{

public MessageService ()
{

//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public string getMessage()
{

string connString = ConfigurationManager.ConnectionStrings["EMS_App_DBConnectionString"].ConnectionString;

SqlConnection conn = new SqlConnection(connString);

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM EMS_ChangeEvent_Msg WHERE EMS_ChangeEvent_Msg_ID = @.EMS_ChangeEvent_Msg_ID";
cmd.Parameters.Add("@.EMS_ChangeEvent_Msg_ID", SqlDbType.Int, Int32.MaxValue).Value = 8;

//24/05/2007 10:42:20 p.m.

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

// get returned data and place in data table -
DataTable taskTable = new DataTable();
taskTable.Load(reader);

string info = taskTable.Rows[0]["EMS_ChangeEvent_Msg_Date"].ToString();
conn.Close();

return info;
}

}

Hope this makes sense in its context

Cheers

|||

Firstly, the security does affect the webservice

To make the webservice accessible to all, add the following to the configuration section of your web.config

Secondly, userContext expects a json object e.g.
var userContext = [cgoId, elemToUpdate];

It can later be used in the onSucceeded function as
var cgoId = userContext[0];
var elemToUpdate = userContext[1];

My guess is the security issue is causing the problem. Allowing anonymous access to the webservice should solve the problem. Try and let me know.

|||

To help me understand a little better could you please tell me whats happening here -

MessageService.getMessage(lastChecked, OnSuccededGetMessage, null, userContext, null);

what are being passed, how, why?

Reality is i want the webservice to look up db with two values that i would have to pass from .aspx page so they are the objects need to send.

This is driving me a bit insane at moment - last try the error console in firefox was saying that the problem was in the ScriptResource.asxd - dont even know where/what this is!

I appreciate the help but if this looks like going on forever i understand help must stop at some point ;)

cheers

|||

All the parameters before the function reference of the onsucceeded function are those that are expected by the webservice.

userContext is a JSON object which is used to associate relevant data with a particular call. (google JSON to learn how they work)
Since a call here is asynchronous, you need a means to identify which specific call is returning the current result. The userContext object helps you with that.

Sorry, I think I forgot to post the relevant links. Refer to the following links to understand how this works

http://msdn.microsoft.com/msdnmag/issues/07/01/ExtremeASPNET/default.aspx

http://ajax.asp.net/docs/tutorials/ConsumingWebServicesWithAJAXTutorial.aspx

This is a simple webservice call using XMLHttp (not using the ScriptManager)

http://forums.asp.net/t/1112930.aspx

|||

Hey Prashant,

Thanks for the effort - finally got there :). This problem has been vexing me for a while and this solution does just what i need so cheers.

this link - http://ajax.asp.net/docs/tutorials/ConsumingWebServicesWithAJAXTutorial.aspx - was most helpful as gave the essential grounding on what i was actually doing - somtimes better to read properly rather than hack eh?

Cheers much

Friday, February 24, 2012

data update notification

No comments:

Post a Comment

Database corrupted

Blog Archive

About Me