如何将图像存储到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的隐式转换（最大值）不允许
。使用CONVERT函数运行此查询

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

在数据库中，图像列的数据类型为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屋！