problem inserting base64String (.jpg file) data

Question

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)
    {
    }
}

Please don't use Base64-encoded text to store binary data in an RDBMS. You should be using `varbinary(max)` as your column type. — Dai, Jun 24 '23 at 09:31
Regardless of data types, learn to use parameters. Once you've done that, storing binary data as binary data instead of text is as simple as any other data type, so you won't need to create base-64 strings where you shouldn't. — jmcilhinney, Jun 24 '23 at 09:47
It would be bad enough storing files and blobs in the database as `varbinary(max)` instead of just URLs referencing them remotely in S3 or Azure blob storage. Why do you add insult to injury by making them 33% bigger with base64 encoding? i.e.: base64 encoding takes each group of three binary bytes and transforms them to four ASCII characters. — AlwaysLearning, Jun 24 '23 at 10:44
I suppose my real question is: why do you think it's only storing 64KB? What does `SELECT DATALENGTH(DocFile_64) FROM CustomerDocUnAuth;` report? If you're trying to copy-paste data out of SSMS grids then you're probably hitting its data retrieval limitations instead. — AlwaysLearning, Jun 24 '23 at 10:46
@jdweng not working for me. any way to data store in database with base64string?? — shafaetjsr, Jun 24 '23 at 11:36
I can't find any documentation on DocFile_64. Where is it defined? — jdweng, Jun 24 '23 at 14:10
@shafaetjsr You haven't read or taken-in any of our advice we've given you so far, have you? — Dai, Jun 25 '23 at 03:47
@AlwaysLearning I agree with you in this case, but there are many cases where it's overall better to store modestly-sized (say... under-1-megabyte?) blobs within the DB - the cost+complexity+risks of having to maintain two separate stores and requiring clients to make 2 separate requests (one to the DB, the other to the blob store). Also, instead of storing URIs (and if the blobs are immutable) then consider using Content-Addressable-Storage instead (i.e. name blobs after their SHA256 hash), that way the actual URI of the storage provider (S3, Azure Storage, etc) doesn't matter. — Dai, Jun 25 '23 at 03:50

score 1 · Answer 1 · answered Jun 25 '23 at 00:52

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();
}

`OpenReadStream` isn't an `async` or `Task` returning method, so `await using var` can be just `using`. — Dai, Jun 25 '23 at 06:29
@Dai If it *was* Task returning then I would have done `await using var stream = await file.OpenReadStream`. The `await using` is something else, it's for `IAsyncDisposable` which `Stream` *is*, and is used to support `DisposeAsync`. See https://stackoverflow.com/questions/58610350/what-is-the-difference-between-using-and-await-using-and-how-can-i-decide-which — Charlieface, Jun 25 '23 at 09:35
Ah, yes - most of my work in C# is still in C# 7 and C# 8 land, so no `await using` for me yet, sorry! — Dai, Jun 25 '23 at 09:45

score 0 · Answer 2 · answered Jul 02 '23 at 10:08

0

all of you thank you for your opening. But My problem was database service level and now problem solved. @Charlieface My question was only data insert but you also describe connectivity it's should avoid you because My target was only data insert in sql server.

answered Jul 02 '23 at 10:08

shafaetjsr

314
4
11

Why are you ignoring our _urgent_ advice for you to stop storing binary data as text? – Dai Jul 02 '23 at 10:16

problem inserting base64String (.jpg file) data

2 Answers2