[Webinar] Streamline your web hosting managementRegister Today


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

Posted on 2008-01-28
Medium Priority
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

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);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                    strValue = BitConverter.ToString((byte[])reader[0]);
                return strValue;

Open in new window

Question by:countrymeister
  • 3
  • 2
LVL 22

Expert Comment

ID: 20763626
Try this:

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

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


Author Comment

ID: 20764136

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
LVL 22

Expert Comment

ID: 20764243
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);
LVL 22

Accepted Solution

JimBrandley earned 2000 total points
ID: 20764252
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.


Author Comment

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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question