HELP! compare two SQL timestamps in C# byte arrays

[Next]

Please don't over think my question here This is not for concurrency.

Once I get a couple of SQL timestamps into respective C# byte arrays,
how do convert\manipulate the arrays to something I can compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have tried
every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp contained in C#
byte arrays and is willing to share their knowledge, I would be forever in
your debt.

Thanks in advance for any help you would give,
Aaron

 

[Hermit Dave]

timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the string.
use Convert.ToBase64String() to do the byte[] ==> base 64 string
and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Next]

Thank you for your reply Hermit,

I have tride ToBase64String().

The problem is I am not testing for Equality. I don't want to know if they
aren't equal.

I want to know which one is larger. You can test only for equality using
strings.

Any ideas? Anyone

Aaron

timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the string.
use Convert.ToBase64String() to do the byte[] ==> base 64 string
and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

Please don't over think my question here This is not for concurrency.

Once I get a couple of SQL timestamps into respective C# byte arrays,
how do convert\manipulate the arrays to something I can compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have tried
every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp contained in C#
byte arrays and is willing to share their knowledge, I would be forever in
your debt.

Thanks in advance for any help you would give,
Aaron

 

[Chris Barber]

The inherent 'timestamp' for a record isn't based on time and as such to compare as being larger /
smaller than a specific date value for the purposes of saying:

Select all records that have changed after a specific time

Is meaningless and cannot be achieved. You would have to use your own methods to implement a
specific field in the table that stores a datetime value.

I have seen the same issue querying a remote Sybase database with a 'timestamp' field - the values
are not date oriented and as such you can only detect if a record has changed by referring back to a
cached 'timestamp' value and detecting equality. Luckily in my case the database table also had a
datetime field 'lastchanged' that I could use.

Chris.

Thank you for your reply Hermit,

I have tride ToBase64String().

The problem is I am not testing for Equality. I don't want to know if they
aren't equal.

I want to know which one is larger. You can test only for equality using
strings.

Any ideas? Anyone

Aaron

timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the string.
use Convert.ToBase64String() to do the byte[] ==> base 64 string
and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

Please don't over think my question here This is not for concurrency.

Once I get a couple of SQL timestamps into respective C# byte arrays,
how do convert\manipulate the arrays to something I can compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have tried
every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp contained in C#
byte arrays and is willing to share their knowledge, I would be forever in
your debt.

Thanks in advance for any help you would give,
Aaron

 

[Next]

Hello and thanks again to everyone who took the time to help.

I have come up with a solution to my problem. It isn't the most elegant, so
if someone out there has a better solution, I would LOVE to see it.

Let me first start off with a couple of quotes from BOL:
"timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database."
"[timestamp] Is used to indicate the sequence of SQL Server activity on a
row, represented as an increasing number in a binary format."

Given that the timestamp is unique in a database and represented as an
increasing number, I knew that I would somehow be able to compare
timestamps. Specifically if one timestamp was greater than another
timestamp. This is easy in TSQL, but posed a problem for me in C#.

Thanks to Stephen Ahn in sqlserver.programming for inspiring the idea.

Just as a reminder. I am not using this for concurrency. It's for a service
that monitors(not changes) data. I needed a way to query all the rows that
had changed since the last query.

//two byte []
byte[] _ts = {0,0,0,0,0,0,0,0};
byte[] _tempTs;

//get the timestamp from SQL
_tempTs = (byte [])dr["timestamp"];

//compare the current row's timestamp to the saved timestamp
//first covert to hex string
//then convert to int using base 16
//finally compare the ints
if( Convert.ToInt64( ByteArrayToHexString(_tempTs), 16 ) >
Convert.ToInt64( ByteArrayToHexString(_ts), 16 ) )
{
_ts = _tempTs;
}

private string ByteArrayToHexString(byte[] b)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append("0x");

foreach (byte val in b)
{
sb.Append( val.ToString("X2") );
}

return sb.ToString();
}

The inherent 'timestamp' for a record isn't based on time and as such to compare as being larger /
smaller than a specific date value for the purposes of saying:

Select all records that have changed after a specific time

Is meaningless and cannot be achieved. You would have to use your own methods to implement a
specific field in the table that stores a datetime value.

I have seen the same issue querying a remote Sybase database with a 'timestamp' field - the values
are not date oriented and as such you can only detect if a record has changed by referring back to a
cached 'timestamp' value and detecting equality. Luckily in my case the database table also had a
datetime field 'lastchanged' that I could use.

Chris.

Thank you for your reply Hermit,

I have tride ToBase64String().

The problem is I am not testing for Equality. I don't want to know if they
aren't equal.

I want to know which one is larger. You can test only for equality using
strings.

Any ideas? Anyone

Aaron

timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the string.
use Convert.ToBase64String() to do the byte[] ==> base 64 string
and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

Please don't over think my question here This is not for concurrency.

Once I get a couple of SQL timestamps into respective C# byte arrays,
how do convert\manipulate the arrays to something I can compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have tried
every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp contained

in
C#

byte arrays and is willing to share their knowledge, I would be

forever

in

 

[Nicholas Paldino [.NET/C# MVP]]

Next,

I am curious, why didn't you do this in a stored procedure on the server
side? Comparing the timestamps this way could be very costly, and would be
handled in a much more efficient manner in SQL server, IMO.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hello and thanks again to everyone who took the time to help.

I have come up with a solution to my problem. It isn't the most elegant,
so
if someone out there has a better solution, I would LOVE to see it.

Let me first start off with a couple of quotes from BOL:
"timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database."
"[timestamp] Is used to indicate the sequence of SQL Server activity on a
row, represented as an increasing number in a binary format."

Given that the timestamp is unique in a database and represented as an
increasing number, I knew that I would somehow be able to compare
timestamps. Specifically if one timestamp was greater than another
timestamp. This is easy in TSQL, but posed a problem for me in C#.

Thanks to Stephen Ahn in sqlserver.programming for inspiring the idea.

Just as a reminder. I am not using this for concurrency. It's for a
service
that monitors(not changes) data. I needed a way to query all the rows that
had changed since the last query.

//two byte []
byte[] _ts = {0,0,0,0,0,0,0,0};
byte[] _tempTs;

//get the timestamp from SQL
_tempTs = (byte [])dr["timestamp"];

//compare the current row's timestamp to the saved timestamp
//first covert to hex string
//then convert to int using base 16
//finally compare the ints
if( Convert.ToInt64( ByteArrayToHexString(_tempTs), 16 ) >
Convert.ToInt64( ByteArrayToHexString(_ts), 16 ) )
{
_ts = _tempTs;
}

private string ByteArrayToHexString(byte[] b)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append("0x");

foreach (byte val in b)
{
sb.Append( val.ToString("X2") );
}

return sb.ToString();
}

The inherent 'timestamp' for a record isn't based on time and as such to compare as being larger /
smaller than a specific date value for the purposes of saying:

Select all records that have changed after a specific time

Is meaningless and cannot be achieved. You would have to use your own methods to implement a
specific field in the table that stores a datetime value.

I have seen the same issue querying a remote Sybase database with a 'timestamp' field - the values
are not date oriented and as such you can only detect if a record has changed by referring back to a
cached 'timestamp' value and detecting equality. Luckily in my case the database table also had a
datetime field 'lastchanged' that I could use.

Chris.

Thank you for your reply Hermit,

I have tride ToBase64String().

The problem is I am not testing for Equality. I don't want to know if
they
aren't equal.

I want to know which one is larger. You can test only for equality using
strings.

Any ideas? Anyone

Aaron

timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the string.
use Convert.ToBase64String() to do the byte[] ==> base 64 string
and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
Please don't over think my question here This is not for concurrency.

Once I get a couple of SQL timestamps into respective C# byte arrays,
how do convert\manipulate the arrays to something I can compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have tried
every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp contained in
C#
byte arrays and is willing to share their knowledge, I would be

forever

in
your debt.

Thanks in advance for any help you would give,
Aaron

 

[David G.]

Hello and thanks again to everyone who took the time to help.

I have come up with a solution to my problem. It isn't the most
elegant, so if someone out there has a better solution, I would LOVE
to see it.

Let me first start off with a couple of quotes from BOL:
"timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database."
"[timestamp] Is used to indicate the sequence of SQL Server activity
on a row, represented as an increasing number in a binary format."

Given that the timestamp is unique in a database and represented as an
increasing number, I knew that I would somehow be able to compare
timestamps. Specifically if one timestamp was greater than another
timestamp. This is easy in TSQL, but posed a problem for me in C#.

Thanks to Stephen Ahn in sqlserver.programming for inspiring the idea.

Just as a reminder. I am not using this for concurrency. It's for a
service that monitors(not changes) data. I needed a way to query all
the rows that had changed since the last query.

//two byte []
byte[] _ts = {0,0,0,0,0,0,0,0};
byte[] _tempTs;

//get the timestamp from SQL
_tempTs = (byte [])dr["timestamp"];

//compare the current row's timestamp to the saved timestamp
//first covert to hex string
//then convert to int using base 16
//finally compare the ints
if( Convert.ToInt64( ByteArrayToHexString(_tempTs), 16 ) >
Convert.ToInt64( ByteArrayToHexString(_ts), 16 ) )
{
_ts = _tempTs;
}

private string ByteArrayToHexString(byte[] b)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append("0x");

foreach (byte val in b)
{
sb.Append( val.ToString("X2") );
}

return sb.ToString();
}

The inherent 'timestamp' for a record isn't based on time and as
such to compare as being larger / smaller than a specific date value
for the purposes of saying:

Select all records that have changed after a specific time

Is meaningless and cannot be achieved. You would have to use your
own methods to implement a specific field in the table that stores a
datetime value.

I have seen the same issue querying a remote Sybase database with a
'timestamp' field - the values are not date oriented and as such you
can only detect if a record has changed by referring back to a
cached 'timestamp' value and detecting equality. Luckily in my case
the database table also had a datetime field 'lastchanged' that I
could use.

Chris.

Thank you for your reply Hermit,

I have tride ToBase64String().

The problem is I am not testing for Equality. I don't want to know
if they aren't equal.

I want to know which one is larger. You can test only for equality
using strings.

Any ideas? Anyone

Aaron

timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the
string. use Convert.ToBase64String() to do the byte[] ==> base 64
string and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
Please don't over think my question here This is not for
concurrency.

Once I get a couple of SQL timestamps into respective C# byte
arrays, how do convert\manipulate the arrays to something I can
compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have
tried every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp
contained in C# byte arrays and is willing to share their
knowledge, I would be forever in your debt.

Thanks in advance for any help you would give,
Aaron

Why not check for inequality instead of "greater than". That should work
fine.

 

[Next]

I was concerned myself about the cost, but couldn't think of an alternative
solution. I am open to any suggestions

The sp looks something like this:
----------------------
getMonitoredRows_sp @tstamp timestamp

SELECT *
FROM MyTable
WHERE timestamp > @tstamp
AND ...
---------------------

When the service is started and calls the sp, it can initally return 100 to
10,000 rows.
I store these rows in a collection which I used to monitor the data.
I need to make sure this collection is updated\inserted\removed with any
changes that may have been made.
When I was designing the service, I thought querying for only the changed
rows would be better than getting all the rows for each elapsed time.
Thus I needed to store the Max timestamp for each query, so I could pass it
back to the sp.
I didn't think that it would be this difficult...hehe

hmmm...Do you think retrieving the 100 rows for each query would be more
efficient? The query will run approximately every 20 seconds.

Next,

I am curious, why didn't you do this in a stored procedure on the server
side? Comparing the timestamps this way could be very costly, and would be
handled in a much more efficient manner in SQL server, IMO.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hello and thanks again to everyone who took the time to help.

I have come up with a solution to my problem. It isn't the most elegant,
so
if someone out there has a better solution, I would LOVE to see it.

Let me first start off with a couple of quotes from BOL:
"timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database."
"[timestamp] Is used to indicate the sequence of SQL Server activity on a
row, represented as an increasing number in a binary format."

Given that the timestamp is unique in a database and represented as an
increasing number, I knew that I would somehow be able to compare
timestamps. Specifically if one timestamp was greater than another
timestamp. This is easy in TSQL, but posed a problem for me in C#.

Thanks to Stephen Ahn in sqlserver.programming for inspiring the idea.

Just as a reminder. I am not using this for concurrency. It's for a
service
that monitors(not changes) data. I needed a way to query all the rows that
had changed since the last query.

//two byte []
byte[] _ts = {0,0,0,0,0,0,0,0};
byte[] _tempTs;

//get the timestamp from SQL
_tempTs = (byte [])dr["timestamp"];

//compare the current row's timestamp to the saved timestamp
//first covert to hex string
//then convert to int using base 16
//finally compare the ints
if( Convert.ToInt64( ByteArrayToHexString(_tempTs), 16 ) >
Convert.ToInt64( ByteArrayToHexString(_ts), 16 ) )
{
_ts = _tempTs;
}

private string ByteArrayToHexString(byte[] b)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append("0x");

foreach (byte val in b)
{
sb.Append( val.ToString("X2") );
}

return sb.ToString();
}

The inherent 'timestamp' for a record isn't based on time and as such

to
compare as being larger /

smaller than a specific date value for the purposes of saying:

Select all records that have changed after a specific time

Is meaningless and cannot be achieved. You would have to use your own methods to implement a
specific field in the table that stores a datetime value.

I have seen the same issue querying a remote Sybase database with a 'timestamp' field - the values
are not date oriented and as such you can only detect if a record has changed by referring back to a
cached 'timestamp' value and detecting equality. Luckily in my case the database table also had a
datetime field 'lastchanged' that I could use.

Chris.

Thank you for your reply Hermit,

I have tride ToBase64String().

The problem is I am not testing for Equality. I don't want to know if
they
aren't equal.

I want to know which one is larger. You can test only for equality using
strings.

Any ideas? Anyone

Aaron


timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the
string.
use Convert.ToBase64String() to do the byte[] ==> base 64 string
and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
Please don't over think my question here This is not for
concurrency.

Once I get a couple of SQL timestamps into respective C# byte arrays,
how do convert\manipulate the arrays to something I can compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have tried
every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp

contained
in

C#
byte arrays and is willing to share their knowledge, I would be forever
in
your debt.

Thanks in advance for any help you would give,
Aaron

 

[Nicholas Paldino [.NET/C# MVP]]

Next,

I think that what you should do is have another table which stores the
primary key, and the timestamp for the record of the table being monitored.
Then, when the table state is next retrieved, you would look in that table.
You would take everything from the original table where the primary key is
not in the lookup table, and the records in the original table where the
primary key is in the lookup table, and the timestamp is not equal to the
one in the lookup table.

Then, when the operation is complete, you would delete all of the rows
from the lookup table, and then insert the primary keys and the timestamps
from the original table.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I was concerned myself about the cost, but couldn't think of an alternative
solution. I am open to any suggestions

The sp looks something like this:
----------------------
getMonitoredRows_sp @tstamp timestamp

SELECT *
FROM MyTable
WHERE timestamp > @tstamp
AND ...
---------------------

When the service is started and calls the sp, it can initally return 100
to
10,000 rows.
I store these rows in a collection which I used to monitor the data.
I need to make sure this collection is updated\inserted\removed with any
changes that may have been made.
When I was designing the service, I thought querying for only the changed
rows would be better than getting all the rows for each elapsed time.
Thus I needed to store the Max timestamp for each query, so I could pass
it
back to the sp.
I didn't think that it would be this difficult...hehe

hmmm...Do you think retrieving the 100 rows for each query would be more
efficient? The query will run approximately every 20 seconds.



in
message news:eY5bb%[email protected]...

Next,

I am curious, why didn't you do this in a stored procedure on the server
side? Comparing the timestamps this way could be very costly, and would be
handled in a much more efficient manner in SQL server, IMO.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hello and thanks again to everyone who took the time to help.

I have come up with a solution to my problem. It isn't the most
elegant,
so
if someone out there has a better solution, I would LOVE to see it.

Let me first start off with a couple of quotes from BOL:
"timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database."
"[timestamp] Is used to indicate the sequence of SQL Server activity on a
row, represented as an increasing number in a binary format."

Given that the timestamp is unique in a database and represented as an
increasing number, I knew that I would somehow be able to compare
timestamps. Specifically if one timestamp was greater than another
timestamp. This is easy in TSQL, but posed a problem for me in C#.

Thanks to Stephen Ahn in sqlserver.programming for inspiring the idea.

Just as a reminder. I am not using this for concurrency. It's for a
service
that monitors(not changes) data. I needed a way to query all the rows that
had changed since the last query.

//two byte []
byte[] _ts = {0,0,0,0,0,0,0,0};
byte[] _tempTs;

//get the timestamp from SQL
_tempTs = (byte [])dr["timestamp"];

//compare the current row's timestamp to the saved timestamp
//first covert to hex string
//then convert to int using base 16
//finally compare the ints
if( Convert.ToInt64( ByteArrayToHexString(_tempTs), 16 ) >
Convert.ToInt64( ByteArrayToHexString(_ts), 16 ) )
{
_ts = _tempTs;
}

private string ByteArrayToHexString(byte[] b)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append("0x");

foreach (byte val in b)
{
sb.Append( val.ToString("X2") );
}

return sb.ToString();
}



The inherent 'timestamp' for a record isn't based on time and as such to
compare as being larger /
smaller than a specific date value for the purposes of saying:

Select all records that have changed after a specific time

Is meaningless and cannot be achieved. You would have to use your own
methods to implement a
specific field in the table that stores a datetime value.

I have seen the same issue querying a remote Sybase database with a
'timestamp' field - the values
are not date oriented and as such you can only detect if a record has
changed by referring back to a
cached 'timestamp' value and detecting equality. Luckily in my case
the
database table also had a
datetime field 'lastchanged' that I could use.

Chris.

Thank you for your reply Hermit,

I have tride ToBase64String().

The problem is I am not testing for Equality. I don't want to know if
they
aren't equal.

I want to know which one is larger. You can test only for equality using
strings.

Any ideas? Anyone

Aaron


timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the
string.
use Convert.ToBase64String() to do the byte[] ==> base 64 string
and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
Please don't over think my question here This is not for
concurrency.

Once I get a couple of SQL timestamps into respective C# byte arrays,
how do convert\manipulate the arrays to something I can compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have
tried
every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp contained
in
C#
byte arrays and is willing to share their knowledge, I would be
forever
in
your debt.

Thanks in advance for any help you would give,
Aaron

 

[Next]

Thank you Nicholas for helping me with this,

alien2_51 suggested using BINARY_CHECKSUM from SQL BOL. It is similar to
what you are suggesting. What do you think about this method?

I liked the idea but it seemed like a lot of "maintenance" regarding table
state. That's why I didn't go that route. I'm guessing your suggestion is
the common "Best Practice" approach because SQL server is doing all the
work?

I will definitely try your suggestion. It certainly will make my C# code
easier to write

Thanks again!

Next,

I think that what you should do is have another table which stores the
primary key, and the timestamp for the record of the table being monitored.
Then, when the table state is next retrieved, you would look in that table.
You would take everything from the original table where the primary key is
not in the lookup table, and the records in the original table where the
primary key is in the lookup table, and the timestamp is not equal to the
one in the lookup table.

Then, when the operation is complete, you would delete all of the rows
from the lookup table, and then insert the primary keys and the timestamps
from the original table.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I was concerned myself about the cost, but couldn't think of an alternative
solution. I am open to any suggestions

The sp looks something like this:
----------------------
getMonitoredRows_sp @tstamp timestamp

SELECT *
FROM MyTable
WHERE timestamp > @tstamp
AND ...
---------------------

When the service is started and calls the sp, it can initally return 100
to
10,000 rows.
I store these rows in a collection which I used to monitor the data.
I need to make sure this collection is updated\inserted\removed with any
changes that may have been made.
When I was designing the service, I thought querying for only the changed
rows would be better than getting all the rows for each elapsed time.
Thus I needed to store the Max timestamp for each query, so I could pass
it
back to the sp.
I didn't think that it would be this difficult...hehe

hmmm...Do you think retrieving the 100 rows for each query would be more
efficient? The query will run approximately every 20 seconds.



in
message news:eY5bb%[email protected]...

Next,

I am curious, why didn't you do this in a stored procedure on the server
side? Comparing the timestamps this way could be very costly, and

would
be

handled in a much more efficient manner in SQL server, IMO.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hello and thanks again to everyone who took the time to help.

I have come up with a solution to my problem. It isn't the most
elegant,
so
if someone out there has a better solution, I would LOVE to see it.

Let me first start off with a couple of quotes from BOL:
"timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database."
"[timestamp] Is used to indicate the sequence of SQL Server activity

on
a

row, represented as an increasing number in a binary format."

Given that the timestamp is unique in a database and represented as an
increasing number, I knew that I would somehow be able to compare
timestamps. Specifically if one timestamp was greater than another
timestamp. This is easy in TSQL, but posed a problem for me in C#.

Thanks to Stephen Ahn in sqlserver.programming for inspiring the idea.

Just as a reminder. I am not using this for concurrency. It's for a
service
that monitors(not changes) data. I needed a way to query all the rows that
had changed since the last query.

//two byte []
byte[] _ts = {0,0,0,0,0,0,0,0};
byte[] _tempTs;

//get the timestamp from SQL
_tempTs = (byte [])dr["timestamp"];

//compare the current row's timestamp to the saved timestamp
//first covert to hex string
//then convert to int using base 16
//finally compare the ints
if( Convert.ToInt64( ByteArrayToHexString(_tempTs), 16 ) >
Convert.ToInt64( ByteArrayToHexString(_ts), 16 ) )
{
_ts = _tempTs;
}

private string ByteArrayToHexString(byte[] b)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.Append("0x");

foreach (byte val in b)
{
sb.Append( val.ToString("X2") );
}

return sb.ToString();
}



The inherent 'timestamp' for a record isn't based on time and as

such
to

compare as being larger /
smaller than a specific date value for the purposes of saying:

Select all records that have changed after a specific time

Is meaningless and cannot be achieved. You would have to use your own
methods to implement a
specific field in the table that stores a datetime value.

I have seen the same issue querying a remote Sybase database with a
'timestamp' field - the values
are not date oriented and as such you can only detect if a record has
changed by referring back to a
cached 'timestamp' value and detecting equality. Luckily in my case
the
database table also had a
datetime field 'lastchanged' that I could use.

Chris.

Thank you for your reply Hermit,

I have tride ToBase64String().

The problem is I am not testing for Equality. I don't want to know if
they
aren't equal.

I want to know which one is larger. You can test only for equality using
strings.

Any ideas? Anyone

Aaron


timestamp equivalent in c# is byte[] as you already know.
what you could do is use convert it to base64string and compare the
string.
use Convert.ToBase64String() to do the byte[] ==> base 64 string
and then normal string ==

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
Please don't over think my question here This is not for
concurrency.

Once I get a couple of SQL timestamps into respective C# byte arrays,
how do convert\manipulate the arrays to something I can compare.

I want to know which timestamp is greater and store it:

//this is what I am trying to do:
if ( _ts2 > _ts1)
_ts1 = _ts2;

I have been searching for litterally 3 days for an answer. I have
tried
every conversion I could think of, but no success.

If someone out there knows how to compare two SQL timestamp contained
in
C#
byte arrays and is willing to share their knowledge, I would be
forever
in
your debt.

Thanks in advance for any help you would give,
Aaron