如何将图像存储到 varbinary(max) 列? [英] How to store images to a varbinary(max) column?

问题描述

我在向 sql server 2008 中插入图像时遇到了以下 sql 异常.

I am getting this follwing sql exception while inserting an Image into sql server 2008.

从数据类型 nvarchar 到 varbinary(max) 的隐式转换不是允许.使用 CONVERT 函数运行此查询

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query

数据库中Image列的数据类型是Varbinary(MAX).

In the database Image column datatype is Varbinary(MAX).

编辑

从评论中提取的代码

paramaters.Add(getParam("@imageFilePath", DbType.AnsiString, imageFilePath));

推荐答案

使用这个将文件读入字节数组:

Use this to read the file into a byte array:

    // 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 中的字节数组):

Then save the image data using this (I am using an image class "instance" that contains my image information and byte array in instance.Data):

   using(SqlCommand cm = new SqlCommand("SaveImage", connection, transaction)){
       cm.CommandType = CommandType.StoredProcedure;
       cm.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int,0, ParameterDirection.InputOutput, false, 10, 0, "Id", DataRowVersion.Current, (SqlInt32)instance.Id));
       cm.Parameters.Add(new SqlParameter("@Title", SqlDbType.NVarChar,50, ParameterDirection.Input, false, 0, 0, "Title", DataRowVersion.Current, (SqlString)instance.Title));
       if (instance.Data.Length > 0)
       {
           cm.Parameters.Add(new SqlParameter("@Data", SqlDbType.VarBinary,instance.Data.Length, ParameterDirection.Input, false, 0, 0, "Data", DataRowVersion.Current, (SqlBinary)instance.Data));
       }
       else
       {
           cm.Parameters.Add(new SqlParameter("@Data", SqlDbType.VarBinary,0, ParameterDirection.Input, false, 0, 0, "Data", DataRowVersion.Current, DBNull.Value));                    
       }

       cm.ExecuteNonQuery();
   )

这是一个示例存储过程:

And here is an example stored procedure:

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
)

这篇关于如何将图像存储到 varbinary(max) 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！