SQL CONVERT 从 varbinary 到 varchar [英] SQL CONVERT from varbinary to varchar

问题描述

我正在使用 C# 应用程序，看起来它已准备好将图像插入到我的数据库中，但我的存储过程会吐出一个隐式转换错误.我将图像读入一个字节数组并将该字节数组传递给我的存储过程.它需要一个 varbinary 参数，因此出现错误.所以我将我的存储过程更改为:

ALTER PROCEDURE insertPlayerImage@playerID varchar(9),@profileImage varchar(max),@pending 字符(1)作为转换(varbinary(max)，@profileImage)INSERT INTO PlayerImage(playerID, profileImage, pending)值(@playerID，@profileImage，@pending)走

告诉我它需要一个 varchar(我的字节数组)并将数组转换为 varbinary 文件.好吧，我的存储过程不喜欢我拥有的转换行.但如果我只是这样做

SELECT CONVERT(varchar, GETDATE());

它有效.所有谷歌搜索都指向转换日期，几乎好像它是唯一可以使用转换的东西.

解决方案

您在使用 SQL Server 吗?如果是这样，请参阅此页面以了解 SQL 数据类型到 CLR 数据类型的映射:http://msdn.microsoft.com/en-us/library/cc716729.aspx

SQL Server char、varchar、nchar 和 nvarchar 都映射到/来自 C# string(尽管 char[] 也可以使用).

SQL Server binary 和varbinary映射到/从 C#byte[]`.

您遇到的实际问题是什么?

此外，如果您将二进制数据作为 varchar 传递给 SQL Server，我希望它会在 UTF-16(CLR 内部字符串编码)与 SQL Server 使用的任何代码页之间的转换中被修改.

另一件事要注意:您的存储过程:

ALTER PROCEDURE insertPlayerImage@playerID varchar(9),@profileImage varchar(max),@pending 字符(1)作为转换(varbinary(max)，@profileImage)插入播放器图像( playerID , profileImage , 待定)价值观( @playerID , @profileImage , @pending )走

不是合法的 SQL.Convert() 是一个函数，而不是 SQL 语句.它甚至不编译.如果您尝试将 varchar 参数 @profileImage 转换为 varbinary，您将不得不按照>

 声明@image varbinary(max)set @image = convert(varbinary(max),@profileImage)

如果你的存储过程有签名

创建程序dbo.insertPlayerImage@playerId varchar(9) ,@profileImage varbinary(max) ,@pending 字符(1)作为...

那么这段代码就可以了:

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 ;字符串 connectString = GetConnectString() ;使用 ( SqlConnection 连接 = 新 SqlConnection(connectString) )使用( SqlCommand 命令 = 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() ;}返回行数；}

但是，如果您为图像数据传递 null，则需要更改参数值的设置方式.类似的东西:

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

或

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

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.

解决方案

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 ) ;

这篇关于SQL CONVERT 从 varbinary 到 varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！