ReadMultiple (SOAP Webservice), New Record Late Detection

puz_me

Hi all,

I am developing a form based solution, using c#, which will read and write data from SQLite database to Dynamics NAV 2015 database, vice versa.

In a part of my solution, I need to check whether there is any new record in a NAV database table every second. If yes, then I need to replicate that record as soon as possible in my SQLite database. So I read the NAV records from my application using NAV’s SOAP Web service and write the same information to my SQLite database.

Actually this is working as expected except for the interval between the new record being written to NAV database until it is read through the SOAP Web service. It is about 30 second (localhost connection) until the readmultiple can get the new inserted or modified records. In the real life environment, I need the records available in the SQLite database less than a couple of seconds (close to real-time if possible).

Here is the function I use to consume the SOAP webservice and update my SQLiteDatabase:

private void UpdateCallEntryFromDynamics()
{
QueueCallEntryService_Service CallEntryWS = new QueueCallEntryService_Service();
CallEntryWS.Credentials = new NetworkCredential("user", "xxxxxx");

List<QueueCallEntryService_Filter> CallEntryFilters = new List<QueueCallEntryService_Filter>();
QueueCallEntryService_Filter StatusFilter = new QueueCallEntryService_Filter();
StatusFilter.Field = QueueCallEntryService_Fields.Status;
StatusFilter.Criteria = "NEW";
CallEntryFilters.Add(StatusFilter);

QueueCallEntryService.QueueCallEntryService[] Entries = CallEntryWS.ReadMultiple(CallEntryFilters.ToArray(), null, 200);
QueueCallEntryService.QueueCallEntryService CallEntry = new QueueCallEntryService.QueueCallEntryService();

foreach (QueueCallEntryService.QueueCallEntryService Entry in Entries)
{
connectToDatabase();
string sql =
"INSERT INTO CallEntry"
+ "(Date,EntryNo,QueueEntryNo,QueueTypeID,QueueNo,CounterNo,Status,CallTime)"
+ "VALUES ("
+ "'" + Entry.Date.ToString("yyyy-MM-dd") + "'"
+ ",'" + Entry.EntryNo.ToString() + "'"
+ ",'" + Entry.QueueEntryNo.ToString() + "'"
+ ",'" + Entry.QueueTypeID.ToString() + "'"
+ ",'" + Entry.QueueNo.ToString() + "'"
+ ",'" + Entry.CounterNo.ToString() + "'"
+ ",'NEW'"
+ ",'" + Entry.Time.ToString("hh:mm:ss") + "'"
+ ")";
SQLiteCommand cmd = new SQLiteCommand(sql, Conn);
cmd.ExecuteNonQuery();

CallEntry = Entry;
CallEntry.Status = "ON QMS";
CallEntryWS.Update(ref CallEntry);
}
CallEntryWS.Dispose();
CallEntryFilters.Clear();
}

Is there any information regarding how we can reduce the time interval? Thanks in advance.

Regrads,

Philipus

puz_me

yes, the problem's gone. Here what I did (not sure which solve the problems):
[*] Compile the page
[*] un-check and recheck the publish on web service page
[*] Restart the service instance
[*] Update the web reference on Visual Studio projects

When I test my solution, new record (insert / modified) immediately read on readmultiple command.

SilverX

Do you use multiple NST? Is it possible, that data is written on NST1 while you read it from NST2? Then it could be the cache synchronization period of 30 seconds. If so, try SELECTLATESTVERSION before actually reading data.

puz_me

amazingly, i change the credential type to Windows and the problem is gone. the new inserted / modified record can be read instantly. Any suggestion on how to resolve this?

puz_me

after so long the problem's gone, now I'm facing the same problem again. Is there any cause and how to work arround it?

puz_me

yes, the problem's gone. Here what I did (not sure which solve the problems):
[*] Compile the page
[*] un-check and recheck the publish on web service page
[*] Restart the service instance
[*] Update the web reference on Visual Studio projects

When I test my solution, new record (insert / modified) immediately read on readmultiple command.

archer89

if the nav table does not contain flowfields you could load the data direct ftom database. for instant notification of new records work with sql triggers.

SilverX

Do you use multiple NST? Is it possible, that data is written on NST1 while you read it from NST2? Then it could be the cache synchronization period of 30 seconds. If so, try SELECTLATESTVERSION before actually reading data.

puz_me

SilverX wrote: »

Do you use multiple NST? Is it possible, that data is written on NST1 while you read it from NST2? Then it could be the cache synchronization period of 30 seconds. If so, try SELECTLATESTVERSION before actually reading data.

yes, this might be the case. I often separate Client service and the SOAP web service. Is there anyway we could reduce the cache synchronization period?

thx.

SilverX

Yes. Have a look at this article: Data Access.