Interpreting Byte[] In Stored Procedure

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 (throu

Solution 1:

Given this stored procedure:

createproceduredbo.pConvertBytesToInt

  @bytesvarbinary(4)

asselectconvert(int,@bytes)

go

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

staticint? Bytes2IntViaSQL( byte[] @bytes )
{
  int? value ;
  const stringconnectionString="Data Source=localhost;Initial Catalog=sandbox;Integrated Security=SSPI;" ;
  using ( SqlConnectionconnection=newSqlConnection( connectionString ) )
  using ( SqlCommandsql= connection.CreateCommand() )
  {
    sql.CommandType = CommandType.StoredProcedure ;
    sql.CommandText = "dbo.pConvertBytesToInt" ;

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

    sql.Parameters.Add( p1 ) ;

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

  }

  return value ;
}

This test harness

staticvoidMain(string[] args )
{
  byte[][] testcases = { newbyte[]{0x00,0x00,0x00,0x01,} ,
                         null                   ,
                         newbyte[]{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 is1
X is NULL
X is2147483647

Solution 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.