Can't Get Byte Array from SQL database using Enterprise Library

Question

I'm working on a web API using Enterprise Library 5.0.505 AND I'm having trouble getting a byte array from a database. My insert statement works just fine using DbType.Binary but when I try to return it using SqlStringAccessor it comes up null.

I've tried researching it but most posts use a reader and my project doesn't allow for that route.

My Get method:

public IEnumerable<User> UserSearch(string username)
        {
            string sql = "SELECT * FROM Users WHERE Username = @Username";

            var accessor = Database.CreateSqlStringAccessor<User>(sql, new NamedParameterMapper("@Username"));
            return accessor.Execute(username);
        }

The properties of my User type:

public int UserId { get; set; }
    public string Username { get; set; }
    public byte[] SaltedAndHashedPassword { get; set; }
    public byte[] Salt { get; set; }
    public bool LoggedIn { get; set; }

The Insert code I have that works correctly:

using (DbCommand cmd = Database.GetStoredProcCommand("[HeadCount_Ver01].[dbo].[AddUser]"))
            {
                Database.AddInParameter(cmd, "@Username", DbType.String, user.Username);
                Database.AddInParameter(cmd, "@SaltedAndHashedPassword", DbType.Binary, user.SaltedAndHashedPassword);
                Database.AddInParameter(cmd, "@Salt", DbType.Binary, user.Salt);

                return Database.ExecuteNonQuery(cmd) > 0;
            }

Any help is really appreciated, thanks.

Answer 1

It's been awhile since I've done this but if I remember correctly one of the easier ways was to use the SqlDbType.Binary as an output parameter then simply pull the value with a query.

The code below is untested and modified from another example using this method. The conversion from a SqlDbType.Binary to a byte array might need some attention or modification but should be really close.

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("select @bytearray = dbcolumn from table", conn))
{
   SqlParameter outputByteParam = new SqlParameter("@bytearray", SqlDbType.Binary)
   { 
      Direction = ParameterDirection.Output 
   };

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(outputByteParam);

   conn.Open();
   cmd.ExecuteNonQuery();

   byte[] result = outputByteParam.GetValueOrDefault<byte[]>();  // this line might need attention 
}