asked on

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

JimBrandley

Try this:

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

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

Jim

countrymeister

ASKER

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

JimBrandley

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);

ASKER CERTIFIED SOLUTION

JimBrandley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

countrymeister

ASKER

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