SQL Server problem inserting base64String (.jpg file) data

du7egjpx 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(104)

I am trying upload image from ASP.NET Core 6 with Blazor to SQL Server. if file size small then it gets uploaded, but if file size is 2 MB or more, then SQL Server insert only size 64 KB, 65535 chars, but my string size is a lot more. My actual image size 2.64 MB.

My SQL Server table structure

CREATE TABLE [dbo].[CustomerDocUnAuth]
(
    [CusDocTypeId] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DocFile_64] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[CustomerDocUnAuth] 
    ADD CONSTRAINT [PK_CustomerDocUnAuth] 
        PRIMARY KEY CLUSTERED ([CusDocTypeId]) 
        WITH (FILLFACTOR=90, PAD_INDEX=ON) ON [PRIMARY]
GO

And this is my C# code

protected async Task SingleUpload(InputFileChangeEventArgs e)
{
    try
    {
        var file = e.File;

        if (!string.IsNullOrEmpty(file.Name))
        {
            base64String = "";
            long maxFileSize = 1024L * 1024L * 1024L * 2L;

            await using MemoryStream fs = new MemoryStream();
            await file.OpenReadStream(maxFileSize).CopyToAsync(fs);
            byte[] somBytes = GetBytes(fs);
            base64String = Convert.ToBase64String(somBytes, 0, somBytes.Length);

            string sql = $@"INSERT INTO CustomerDocUnAuth (CusDocTypeId, DocFile_64) VALUES('{id}', '{base64String}')";
            int i = da.Insert(sql);
        }
    }
    catch (Exception ex)
    {
    }
}

sql-server

来源：https://stackoverflow.com/questions/76545369/problem-inserting-base64string-jpg-file-data

1条答案

按热度按时间

xfb7svmp1#

There is a lot wrong with your code. I have no idea if any of this will fix your issue, but there are often issues when you directly inject data into TSQL. Always use parameters.

Furthermore:

Don't store binary data as Base64, it's unnecessarily wasteful. SQL Server is perfectly capable of accepting and storing varbinary(max) directly.
Don't use a global connection or data adapter. Create one when you need it, and dispose with using .
A data adapter is not necessary for a straight insert. Use SqlCommand.ExecuteNonQuery instead.
Your insert is in any case not async . Also consider using a CancellationToken .
Don't swallow exceptions. Handle thm and return an error to the user at the top of the event chain.
You are abandoning the filestream without disposing it. You need a using .
Once you use parameters, you don't need the MemoryStream either, which is also wasteful. You can stream the file directly via the parameter.

protected async Task SingleUpload(InputFileChangeEventArgs e)
{
    var file = e.File;
    if (string.IsNullOrEmpty(file.Name))
        return;
    
    const string sql = @"
INSERT INTO CustomerDocUnAuth
  (CusDocTypeId, DocFile)
VALUES
  (@id, @file);
";
    await using var stream = file.OpenReadStream(maxFileSize);
    await using var conn = new SqlConnection(YourConnectionString);
    await using var comm = new SqlCommand(sql, conn);
    comm.Parameters.Add("@id", SqlDbType.VarChar, 14).Value = id;
    comm.Parameters.Add("@file", SqlDbType.VarBinary, -1).Value = stream;
    await conn.OpenAsync();
    await comm.ExecuteNonQueryAsync();
}

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server problem inserting base64String (.jpg file) data

1条答案

相关问题

热门标签

最新问答