CODEWITHSUNDEEP
c#sql-serverasp.net-core
Matthijs1999
Matthijs1999

Reputation: 23

Image byte array is not saving properly to database

I'm trying to save an image to a database table, but it doesn't save the whole byte array for some reason and I can't figure out why.

This is what my table looks like after trying to insert a byte array.

database table

The data column is set to varbinary(MAX).

CREATE TABLE [dbo].[ProfilePictures] (
    [Id]     INT           IDENTITY (1, 1) NOT NULL,
    [UserId] INT           NOT NULL,
    [Data]   VARBINARY (MAX) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id])
);

This is my stored procedure.

CREATE PROCEDURE [dbo].[SetProfilePicture]
    @Image VARBINARY,
    @UserId INT
AS
    INSERT INTO ProfilePictures (UserId, [Data])
    VALUES (@UserId, @Image)
RETURN 0

Here is my code. The image parameter has the correct data.

Byte array

_connection.SqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand("SetProfilePicture", _connection.SqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@Image", SqlDbType.VarBinary, profilePicture.Image.Length).Value = profilePicture.Image;
sqlCommand.Parameters.AddWithValue("@UserId", user.Id);
sqlCommand.ExecuteNonQuery();
_connection.SqlConnection.Close();

Upvotes: 2

Views: 1259

Answers (1)

user47589
user47589

Reputation:

The default length of varbinary is 1 if the length isn't specified. See MSDN:

The default length is 1 when n isn't specified in a data definition or variable declaration statement

CREATE PROCEDURE [dbo].[SetProfilePicture]
    @Image VARBINARY,

needs to be given a length, as such:

CREATE PROCEDURE [dbo].[SetProfilePicture]
    @Image VARBINARY(max),

Upvotes: 4

Related Questions