using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Data;

namespace GetMaxOnClient
{
    class Program
    {
        static void Main(string[] args)
        {
          using (SqlConnection conn = new SqlConnection("server=.;integrated security=sspi;database=adventureworks"))
          using (SqlCommand cmd1 = new SqlCommand("select top(1) productphotoid, largephoto from Production.ProductPhoto", conn))
          using (SqlCommand cmd2 = new SqlCommand("select documentid, documentsummary from Production.Document where documentsummary is not null", conn))
          {
              conn.Open();
              SqlDataReader rdr1 = cmd1.ExecuteReader(CommandBehavior.);
              rdr1.Read();

              /*
              //Console.WriteLine(rdr.GetProviderSpecificFieldType(1));
              //Object o = rdr.GetProviderSpecificValue(1);

              Console.WriteLine(rdr.GetFieldType(1));
              Object o = rdr.GetValue(1);
              Console.WriteLine(o.GetType());
              Console.WriteLine(((String)o).Length);
              */

              int buffer_size = 1000;

              // gets reference to internal buffer (SQLCLR)
              SqlBytes sb = rdr1.GetSqlBytes(1);
              if (!sb.IsNull)
              {
                  BinaryReader br = new BinaryReader(sb.Stream);
                  // do something with the reader

                  byte[] buffer = new byte[buffer_size];
                  int bytes_read;

                  do
                  {
                    bytes_read = br.Read(buffer, 0, buffer_size);
                    // do something with the buffer
                  }
                  while (bytes_read == buffer_size);
              }

              // makes a buffer-size copy into address space
              // check for NULL
              if (rdr1[1] != DBNull.Value) 
              {
                byte[] buffer2 = new byte[buffer_size];
                long long_bytes_read;
                long dataIndex = 0;

                do
                {

                  long_bytes_read = rdr1.GetBytes(1, dataIndex, buffer2, 0, buffer_size);
                  dataIndex += long_bytes_read; 
                  // do something with the buffer
                }
                while (long_bytes_read == buffer_size);
              }
             
              // copy the whole thing into memory
              SqlBinary sbin = rdr1.GetSqlBinary(1);
              if (!sbin.IsNull)
              { 
                byte[] bigbuffer = new byte[sbin.Length];
                bigbuffer = sbin.Value;
              }
          }

        }
    }
}
