SQL CONVERT from varbinary to varchar

Question

I'm using a C# application and it looks like its ready to insert the image to my database, but my stored procedure spits out an implicit conversion error. I read the image into a byte array and pass the byte array to my stored procedure. It expects a varbinary parameter, hence the error. So I alter my stored procedure to be:

ALTER PROCEDURE insertPlayerImage 
      @playerID varchar(9), 
      @profileImage varchar(max), 
      @pending char(1)
AS
    CONVERT(varbinary(max), @profileImage)

INSERT INTO PlayerImage(playerID, profileImage, pending)
VALUES(@playerID, @profileImage, @pending)
GO

which tells me it expects a varchar (my byte array) and convert the array to a varbinary file. Well my stored procedure doesn't like the the convert line i have. but if I simply do

SELECT CONVERT(varchar, GETDATE());  

it works. All google searches point back to converting the date, almost as if its the only thing you can use a convert on.

Answer 1

OK - you need this:

Stored procedure to get the parameter as varbinary(max) so you can insert it into a Varbinary(max) column in the database table:

CREATE PROCEDURE insertPlayerImage 
      @playerID varchar(9), 
      @profileImage varbinary(max), 
      @pending char(1)
AS
    CONVERT(varbinary(max), @profileImage)

    INSERT INTO PlayerImage(playerID, profileImage, pending)
    VALUES(@playerID, @profileImage, @pending)

C# code to get the contents of a file uploaded in ASP.NET and calling this stored procedure:

// set up connection and command
using(SqlConnection conn = new SqlConnection("your-connection-string-here"))
using(SqlCommand cmd = new SqlCommand("dbo.insertPlayerImage"))
{
    // define parameters
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@playerID", SqlDbType.VarChar, 9);
    cmd.Parameters.Add("@playerImage", SqlDbType.VarBinary, -1);
    cmd.Parameters.Add("@pending", SqlDbType.Char, 1);

    // set the parameter values
    cmd.Parameters["@playerID"].Value = Session["playerID"].ToString();
    cmd.Parameters["@playerImage"].Value = uplImage.FileBytes;
    cmd.Parameters["@pending"].Value = "Y";

    // open connection, execute stored procedure, close connection
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

There's really absolutely no need to ever convert the uploaded file's contents from byte[] to anything else (and back!) - just set the value of the varbinary(max) parameter and call that stored procedure - that's all you need to do!

Answer 2

Are you using SQL Server? If so, see this page for the SQL data type to CLR data type mappings: http://msdn.microsoft.com/en-us/library/cc716729.aspx

SQL Server char, varchar, nchar and nvarchar all map to/from a C# string (though a char[] will work as well).

SQL Server binary andvarbinarymap to/from a C#byte[]`.

What's the actual problem you're having?

Further, if you're passing binary data as a varchar to SQL Server, I would expect it to get munged in the tranformation between UTF-16 (CLR internal string encoding) to whatever code page SQL Server is using.

Another thing to note: your stored procedure:

ALTER PROCEDURE insertPlayerImage 
  @playerID varchar(9), 
  @profileImage varchar(max), 
  @pending char(1)
AS

  CONVERT(varbinary(max), @profileImage)

  INSERT INTO PlayerImage
  ( playerID , profileImage , pending )
  VALUES
  ( @playerID , @profileImage , @pending )

GO

isn't legal SQL. Convert() is a function, not a SQL statement. It doesn't even compile. If you are trying to convert your varchar parameter @profileImage to varbinary, you're going to have to do something along the lines of

 declare @image varbinary(max)
 set @image = convert(varbinary(max),@profileImage)

If you're stored procedure has the signature

create procedure dbo.insertPlayerImage

  @playerId     varchar(9) ,
  @profileImage varbinary(max) ,
  @pending      char(1)

as
...

Then this code will do you:

public int insertProfileImage( string playerId , byte[] profileImage , bool pending )
{
  if ( string.IsNullOrWhiteSpace(playerId) ) throw new ArgumentException("playerId" ) ;
  if ( profileImage == null || profileImage.Length < 1 ) throw new ArgumentException("profileImage") ;

  int rowCount ;

  string connectString = GetConnectString() ;
  using ( SqlConnection connection = new SqlConnection(connectString) )
  using ( SqlCommand command = connection.CreateCommand() )
  {

    command.CommandType = CommandType.StoredProcedure ;
    command.CommandText = "dbo.insertPlayerImage" ;

    command.Parameters.AddWithValue( "@playerId"     , playerId            ) ;
    command.Parameters.AddWithValue( "@profileImage" , profileImage        ) ;
    command.Parameters.AddWithValue( "@pending"      , pending ? "Y" : "N" ) ;

    rowCount = command.ExecuteNonQuery() ;

  }

  return rowCount ;
}

If however, you're passing a null for image data, you'll need to change how the value of the parameter gets set. Something along the lines of:

command.Parameters.AddWithValue( "@profileImage" , profileImage != null ? (object)profileImage : (object)DBNull.Value ) ;

Or

SqlParameter p = new SqlParameter( "@profileImage" , SqlDbType.VarBinary ) ;
p.Value = DBNull.Value ;
if ( profileImage != null )
{
  p.Value = profileImage ;
}
command.Parameters.Add( p ) ;

Answer 3

You should be able to use varbinary(max) as the parameter type. if not, then you are not setting up your db command object or parameters correctly before issuing Execute.

 public DataTable ExecuteParameterizedStoredProcedureObjects(string procedureName, Dictionary<string, object> parameters)
        {
            var dataTable = new DataTable();
            var _sqlConnection = new SqlConnection(_connectionString);
            var cmd = new SqlCommand(procedureName, _sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;

            var da = new SqlDataAdapter(cmd);
            foreach (var entry in parameters)
            {
                cmd.Parameters.Add(entry.Key, entry.Value);
            }

            try
            {
                _sqlConnection.Open();
                da.Fill(dataTable);
            }
            catch (Exception ex)
            {
                var errorText = string.Format("Occ Repository ExecuteQuery Error : QueryString={0} :: Error={1}", procedureName, ex.Message);
                throw new Exception(errorText, ex);
            }
            finally
            {
                da.Dispose();
                _sqlConnection.Dispose();
            }

            return dataTable;
        }

Call it with something like :

 foreach (var record in missingGridEntries)
             {
                 var parameters = new Dictionary<string, object>();
                 parameters.Add("@DataID",int.Parse(record.NodeId));
                 var results = _llDb.ExecuteParameterizedStoredProcedureObjects("listFullPath",parameters);

                 foreach(DataRow dataRow in results.Rows)
                 {
                     record.NodePath = dataRow["fullpath"].ToString();
                     record.NodeFilename = dataRow["name"].ToString();
                 }

             }