Getting binary column from SQL Server database

Question

I need to import a column from a SQL Server database using C#.

When I use SQL Server Enterprise, it is shown as <binary> and when I run query on the SQL Server, it returns the right binary values.

However, when I try coding with C# like so:

SqlConnection conn = new SqlConnection("Server=portable;Database=data;Integrated Security=true;");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT bnry FROM RawData", conn);
SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read())
{
    Console.WriteLine(reader. //I do not know what to put here
}

reader.Close();
conn.Close();

When I put reader.GetSqlBinary(0));, I only get many SqlBinary<4096>s as output.

When I look at SQL Server Query Analyzer, when I try the same command, it gets me 0x0000.. type of code.

What should I put after reader. or is there another method of getting this data from the database?

Answer 1

You should access data from SqlDataReader indexer (MSDN).

So it would look like this:

//..
Console.WriteLine((byte[])reader["bnry"]); 
//..

UPDATE:

I guess I finally got where is your problem. We are not on the same page here. I will try to be as simple as possible.

For start you need to understand that all the information in computers are stored in memory as a bunch of bytes. It is quite cumbersome to work directly with bytes in memory, so different data types were introduced (int, string, Image, etc) to ease programmers life. Most objects in .NET still can be converted to their internal representation as a byte array in one or other way. During this conversion you loose the information about what byte array contains - it can easily be Image, string or even int array. To get back from binary representation, you need to know what byte array contains.

In your example you are trying to write out byte array directly. As output always needs to be text, byte array somehow needs to be converted to string. This is done by calling .ToString() function on byte array. Unfortunately for you, default implementation of .ToString() for complex objects just returns type name. That's where all those System.Byte[] and SqlBinary<4096> lines are coming from.

To get over this issue, before displaying result you need to convert byte array to necessary type. It seems that in your case byte array contains some textual information, so I guess you need to convert byte array to string. To do that, you need to know encoding (way how string is stored in memory) of the text.

Basically, your code should look like this:

SqlConnection conn = new SqlConnection("Server=portable;Database=data;Integrated Security=true;");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT bnry FROM RawData", conn);
SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read())
{
    var valueAsArray = (byte[])reader["bnry"];

    //as there are different encodings possible, you need to find encoding what works for you
    var valueAsStringDefault = System.Text.Encoding.Default.GetString(valueAsArray);
    Console.WriteLine(valueAsStringDefault);

    //...or...
    var valueAsStringUTF8 = System.Text.Encoding.UTF8.GetString(valueAsArray);
    Console.WriteLine(valueAsStringUTF8);

    //...or...
    var valueAsStringUTF7 = System.Text.Encoding.UTF7.GetString(valueAsArray);
    Console.WriteLine(valueAsStringUTF7);

    //...or any other encoding. Most of them you can find in System.Text.Encoding namespace...
}

reader.Close();
conn.Close();

Answer 2

Given this table

create table dbo.bin_test
(
  c1 binary(8)    not null ,
  c2 binary(8)        null ,
  c3 varbinary(8) not null ,
  c4 varbinary(8)     null ,
)
insert dbo.bin_test values ( 0x1234     , null       , 0x1234     , null       )
insert dbo.bin_test values ( 0x012345678 , 0x12345678 , 0x12345678 , 0x12345678 )

This code (IF you're going to use SQLBinary)

string connectString = "Server=localhost;Database=sandbox;Trusted_Connection=True;" ;
using ( SqlConnection connection = new SqlConnection(connectString) )
using ( SqlCommand    cmd        = connection.CreateCommand() )
{

  cmd.CommandText = "select * from dbo.bin_test" ;
  cmd.CommandType = CommandType.Text ;

  connection.Open() ;
  using ( SqlDataReader reader = cmd.ExecuteReader() )
  {
    int row = 0 ;
    while ( reader.Read() )
    {
      for ( int col = 0 ; col < reader.FieldCount ; ++col )
      {
        Console.Write( "row{0,2}, col{1,2}: " , row , col ) ;
        SqlBinary octets = reader.GetSqlBinary(col) ;
        if ( octets.IsNull )
        {
          Console.WriteLine( "{null}");
        }
        else
        {
          Console.WriteLine( "length={0:##0}, {{ {1} }}" , octets.Length , string.Join( " , " , octets.Value.Select(x => string.Format("0x{0:X2}",x)))) ;
        }
      }
      Console.WriteLine() ;
      ++row ;
    }
  }
  connection.Close() ;
}

should produce:

row 0, col 0: length=8, { 0x12 , 0x34 , 0x00 , 0x00 , 0x00 , 0x00 , 0x00 , 0x00 }
row 0, col 1: {null}
row 0, col 2: length=2, { 0x12 , 0x34 }
row 0, col 3: {null}

row 1, col 0: length=8, { 0x00 , 0x12 , 0x34 , 0x56 , 0x78 , 0x00 , 0x00 , 0x00 }
row 1, col 1: length=8, { 0x12 , 0x34 , 0x56 , 0x78 , 0x00 , 0x00 , 0x00 , 0x00 }
row 1, col 2: length=4, { 0x12 , 0x34 , 0x56 , 0x78 }
row 1, col 3: length=4, { 0x12 , 0x34 , 0x56 , 0x78 }

But as noted, it's probably cleaner to simply do this:

byte[] octets = reader[0] as byte[] ;
if ( octets == null )
{
  Console.WriteLine( "{null}");
}
else
{
  Console.WriteLine( "length={0:##0}, {{ {1} }}" , octets.Length , string.Join( " , " , octets.Select(x => string.Format("0x{0:X2}",x)))) ;
}

And get the same result.

Answer 3

The better way to do this is with a stream so that you properly dispose of the stream (probably coming via a socket connection) when finished. So I would use GetStream()

using(Stream stream = reader[0].GetStream())
{
  //do your work on the stream here.
}

Answer 4

You're getting back an array of bytes, so use :

byte[] bytes = (byte[])reader[0];

What you do with them from there depends on what the bytes represent.