我在将一个
Image插入sql server 2008时遇到了这个以下的sql异常.
Implicit conversion from data type nvarchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query
在数据库中,Image列的数据类型是Varbinary(MAX).
编辑
代码解除了评论
paramaters.Add(getParam("@imageFilePath",DbType.AnsiString,imageFilePath));
解决方法
使用它将文件读入字节数组：
// Old fashioned way
public static byte[] ReadFile(string filePath)
{
byte[] buffer;
FileStream fileStream = new FileStream(filePath,FileMode.Open,FileAccess.Read);
try
{
int length = (int)fileStream.Length; // get file length
buffer = new byte[length]; // create buffer
int count; // actual number of bytes read
int sum = 0; // total number of bytes read
// read until Read method returns 0 (end of the stream has been reached)
while ((count = fileStream.Read(buffer,sum,length - sum)) > 0)
sum += count; // sum is a buffer offset for next reading
}
finally
{
fileStream.Close();
}
return buffer;
}
要么
// Thanks Magnus!
byte[] data = System.IO.File.ReadAllBytes(filePath);
然后使用此保存图像数据(我使用的图像类“实例”包含我在instance.Data中的图像信息和字节数组)：
using(sqlCommand cm = new sqlCommand("SaveImage",connection,transaction)){
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new sqlParameter("@Id",sqlDbType.Int,ParameterDirection.InputOutput,false,10,"Id",DaTarowVersion.Current,(sqlInt32)instance.Id));
cm.Parameters.Add(new sqlParameter("@Title",sqlDbType.NVarChar,50,ParameterDirection.Input,"Title",(sqlString)instance.Title));
if (instance.Data.Length > 0)
{
cm.Parameters.Add(new sqlParameter("@Data",sqlDbType.VarBinary,instance.Data.Length,"Data",(sqlBinary)instance.Data));
}
else
{
cm.Parameters.Add(new sqlParameter("@Data",dbnull.Value));
}
cm.ExecuteNonQuery();
)
这是一个示例存储过程：
CREATE PROCEDURE SaveImage ( @Id int OUTPUT,@Title nvarchar(50),@Data varbinary(MAX) ) AS SET NOCOUNT ON SET XACT_ABORT ON IF @Id IS NULL OR @Id <= 0 BEGIN SELECT @Id = ISNULL(MAX([Id]),0) + 1 FROM [dbo].[Images] END INSERT INTO [dbo].[Images] ( [Id],[Title],[Data] ) VALUES ( @Id,@Title,@Data )