I need to retrieve the value from a column which is stored in binary format using an sql command

I have a C# program where I have to excute a select statement, and get the timestamp which is stored in binary format.

The column definition is XTime(binary(8),null)

I am using a SQLDataReader to fetch the query command
here is my code attached
please note the query string which is passed to the method is
SELECT MIN(XTimeStamp) FROM TableData (nolock) WHERE LastModifyTime BETWEEN '01/28/2008' AND '01/28/200812:00:00.000'

When I run this in SQL Server 2005, the output I get is
0x000000010FA3A855,

but when I run the code im my C# program using the above Sql as my query string, I get the strValue = 00-00-00-01-0F-A3-A8-55

Is it possible to get the exact same value?

string querystring = SELECT MIN(XTimeStamp) FROM TableData (nolock) WHERE LastModifyTime BETWEEN '01/28/2008' AND '01/28/200812:00:00.000'  
 
public static string DBReader(string queryString, string connectionString)
        {
            string strValue = Constants.Empty;
            using (SqlConnection connection = new SqlConnection(
                       connectionString) )
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                SqlDataReader reader = command.ExecuteReader();
               
                while (reader.Read())
                {
                    strValue = BitConverter.ToString((byte[])reader[0]);
                                 
                }
                reader.Close();
                return strValue;
                
                
 
            }
        }

Open in new window

LVL 1
countrymeisterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimBrandleyCommented:
Try this:

    string pattern = @"\-";
    Regex rgx = new Regex(pattern);

    string input = BitConverter.ToString((byte[])reader[0]);
    string output = rgx.Replace( input, "");


Jim
0
countrymeisterAuthor Commented:
JimBrandley

If I do as you suggested it will merely remove the dash "-".
The issue i have is the return value in SQL Server displays 0x000000010FA3A855,
while the value from the BitConverter shows 00000000010FA3A855.

The second character is 0 in BitConverter conversion in the C# code, but SQL has x as the second character.

So my question is , do these two values mean the same
00000000010FA3A855 and 0x000000010FA3A855.
Say I was pasing this value to a stored procedure to do some other processing after  get the value
0
JimBrandleyCommented:
We can do that pretty easily. Drop the first two characters and insert "0x" in their place, as:
output = "0x" + rgx.Replace( input, "").Substring(2);
0
JimBrandleyCommented:
BTW: These (00000000010FA3A855 and 0x000000010FA3A855) would not be the same. The presence of "0x" tells the reader to interpret the remainder of the string as hex digits.

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
countrymeisterAuthor Commented:
I will try it out tomorrow and let you know. Thanks for all the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.