Interpreting byte[] in stored procedure

Question

A proc we have searches an encrypted field by encrypting the search field and comparing these encrypted values. What I need though to be able to do is to pass into the proc (through Entity Framework 4) the encrypted value (as the code encrypts it), but also allow null if the value is not provided.

So I need to pass in a byte[] but it also needs to accept nulls... is this even possible, or what is a workaround if its not? Again, I'm calling a stored procedure through entity framework.

Thanks.

Answer 1

Given this stored procedure:

create procedure dbo.pConvertBytesToInt

  @bytes varbinary(4)

as

  select convert(int,@bytes)

go

The following code will execute it, passing NULL if the parameter passed is null:

static int? Bytes2IntViaSQL( byte[] @bytes )
{
  int? value ;
  const string connectionString = "Data Source=localhost;Initial Catalog=sandbox;Integrated Security=SSPI;" ;
  using ( SqlConnection connection = new SqlConnection( connectionString ) )
  using ( SqlCommand    sql        = connection.CreateCommand() )
  {
    sql.CommandType = CommandType.StoredProcedure ;
    sql.CommandText = "dbo.pConvertBytesToInt" ;

    SqlParameter p1 = new SqlParameter( "@bytes" , SqlDbType.VarBinary ) ;
    if ( @bytes == null ) { p1.Value = System.DBNull.Value ; }
    else                  { p1.Value = @bytes              ; }

    sql.Parameters.Add( p1 ) ;

    connection.Open() ;
    object result = sql.ExecuteScalar() ;
    value = result is DBNull ? (int?)null : (int?)result ;
    connection.Close() ;

  }

  return value ;
}

This test harness

static void Main( string[] args )
{
  byte[][] testcases = { new byte[]{0x00,0x00,0x00,0x01,} ,
                         null                   ,
                         new byte[]{0x7F,0xFF,0xFF,0xFF,} ,
                       } ;

  foreach ( byte[] bytes in testcases )
  {
      int? x =  Bytes2IntViaSQL( bytes ) ;
      if ( x.HasValue ) Console.WriteLine( "X is {0}" , x ) ; 
      else              Console.WriteLine( "X is NULL" ) ;
  }

  return ;
}

produces the expected results:

X is 1
X is NULL
X is 2147483647

Answer 2

We ended up getting it to work by pushing it as a string, and then parsing it in the proc. That worked. But I believe I read there is a Binary object that represents the byte[] array, and that would have worked too.