We help IT Professionals succeed at work.

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

Medium Priority
243 Views
Last Modified: 2010-04-15
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

Comment
Watch Question

Top Expert 2007

Commented:
Try this:

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

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


Jim

Author

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
Top Expert 2007

Commented:
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);
Top Expert 2007
Commented:
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

Author

Commented:
I will try it out tomorrow and let you know. Thanks for all the help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.