New Posts New Posts RSS Feed: Querying on array fields
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Querying on array fields

 Post Reply Post Reply
stevenr View Drop Down

Joined: 09-Nov-2009
Posts: 9
Post Options Post Options   Quote stevenr Quote  Post ReplyReply Direct Link To This Post Topic: Querying on array fields
    Posted: 21-Feb-2011 at 7:56am

My application is a distributed system where data entered by multiple users gets saved to a central database and then each user must be able to synchronize with the central database to receive the latest changes.  The system  uses timestamp columns to track whether records in certain tables have changed. In the synchronization method, I would like to formulate a query to download only those records that have changed on the server, rather than downloading the tables in their entirety. In other words, on a given client, I would like to get the highest timestamp value locally and then query the server for higher timestamp values to receive only the records that have changed since then. The tables do have datetime auditing fields, but I'd rather not rely on querying these because if the clock is not set properly on the client or the server, this would cause problems.
The timestamp columns are added to my model as fields of type byte[]. How do I write a query that uses this byte[] field in the where clause? I've tried the obvious:

// maxLocalRowVersion contains the maximum local row version
qry.AddClause(MyType.RowVersionEntityColumn, EntityColumnOp.GT, maxLocalRowVersion);

This statement fails with the error: The 'In' and 'Between' operators can only be used with Collection values (and vice versa)
I've also tried to set the field type to Int64 in the model, but then I get an exception that the byte[] type cannot be converted to Int64. How do I handle this?

Back to Top
kimj View Drop Down

Joined: 09-May-2007
Posts: 1391
Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 21-Feb-2011 at 1:08pm
I don't think you can query on the Timestamp column using an RdbQuery, but you can do this with a PassthruRdbQuery.  Something like the following:
RdbParameter parm = new RdbParameter("ts"DbType.Binary);
parm.Value = maxLocalRowVersion;
ParameterizedSql sql = new ParameterizedSql("select * from mytable where lastupdate > ?", parm);
PassthruRdbQuery passThruQuery = new PassthruRdbQuery(typeof(MyType), sql);
You can use the AdoHelper if you need to use provider-specific parameter and placeholder names.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down