0

I am trying to save an image to a database and ultimately retrieve it based on the User Id of the user.

I used the file selector so I can get the image from the user's pc then convert it to Base64 string so I can pass it to the webapi then will be saved to the database. The problem is: when a pretty large image is selected, it creates a very long base64 string that can't fit in a database field whether it be a text data type. Please see code below:

Server side:

public int EditAccount(NewData newData, string image)
{
    var result = 0;
    //if (UserNameExists(newData.UserName)) return "Username is already taken";

    using (var cmd = new SqlCommand("UpdateUserAccount", _dbConnection) { CommandType = CommandType.StoredProcedure})
    {
        try
        {
            _dbConnection.Open();

            cmd.Parameters.AddWithValue("@UserId", newData.UserId);
            cmd.Parameters.AddWithValue("@UserName", newData.UserName);
            cmd.Parameters.AddWithValue("@Email", newData.Email);
            cmd.Parameters.AddWithValue("@Image", image);

            result = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            _dbConnection.Close();
        }
    }

    return result;
}

Stored procedure:

ALTER PROCEDURE [dbo].[UpdateUserAccount]
    (@UserId NVARCHAR(128),
     @UserName NVARCHAR(256),
     @Email NVARCHAR(256), 
     @Image TEXT)
AS
BEGIN
    UPDATE [User]
    SET UserName = @UserName, Email = @Email
    WHERE Id = @UserId

    SELECT COUNT(Id) 
    FROM UserImage
    WHERE UserId = @UserId

    IF (SELECT 1 FROM UserImage WHERE UserId = @UserId) = 1
    BEGIN
        UPDATE [UserImage]
        SET UserImage = @Image
        WHERE UserId = @UserId
    END
    ELSE
    BEGIN
        INSERT INTO [UserImage] (UserId, UserImage)
        VALUES (@UserId, @Image)
    END
END

In my component.ts:

OnSubmit(form: NgForm) {
    this.userService.updataUserAccount(form.value, this.base64textString)
       .subscribe((data: any) => {
    if (data === 1) {
      this.userClaims.UserName = this.newData.UserName;
      this.userClaims.Email = this.newData.Email;
      this.resetForm(form);
      this.editMode = false;

      this.toastr.success("Successfully edited data");
    }
    else
      this.toastr.error(data.Errors[0]);
  });
}
// file handler
handleFileSelect(evt){
    var files = evt.target.files;
    var file = files[0];

  if (files && file) {
      var reader = new FileReader();

      reader.onload =this._handleReaderLoaded.bind(this);

      reader.readAsBinaryString(file);
  }
}

_handleReaderLoaded(readerEvt) {
  console.log("Even Happened");
  var binaryString = readerEvt.target.result;
  var x = btoa(binaryString);
  if(x != this.base64textString){
    this.base64textString= btoa(binaryString);
    console.log("No Match");
   }
}

I am using Angular 5 for this and my backend is WebApi. Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
stack questions
  • 862
  • 2
  • 15
  • 29
  • 2
    `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Mar 28 '18 at 09:24
  • I would love to use nvarchar(max) the problem is my base64 string is too long for it. – stack questions Mar 28 '18 at 09:27
  • 1
    `nvarchar(max)` can store up to 2 **billion** bytes (2 GB) of data - or **1 billion characters** - you're telling me this is **not enough for you**?!?!! That's several hundred times the volume of Leo Tolstoi's "War and Peace" novel.,...... that's **A WHOLE LOT** of text!! – marc_s Mar 28 '18 at 09:28
  • 1
    Why even store as Base64? Use binary: https://stackoverflow.com/questions/5613898/storing-images-in-sql-server Also: Base64 can use `varchar(max)` because it is plain ASCII. – Peter B Mar 28 '18 at 09:28
  • 1
    Please add the exception inviting message, type, stacktrace, and repeat for inner exceptions recursively. – Igor Mar 28 '18 at 09:30
  • Also why `_dbConnection`? Does the controller have a static connection instance? – Igor Mar 28 '18 at 09:32
  • @marc_s: Come to think of it, you're right. It should be able to accomodate the whole of it. I think I found out what is going on. It can actually save the whole string, it's just that it cannot be seen when using the right-click and edit records of the sql server. I have to do the select * from UserImage to see the inserted record. – stack questions Mar 28 '18 at 09:51
  • 1
    BTW storing as `VARBINARY(MAX)` would be the best choice here. It is much more efficient in space which also equates to better performance when you upload and download the file between the server / client(s). It also saves you from having to do any conversion client side or server side. There are plenty of examples out there on how to post a file to an asp.net web api controller or from an angular service/controller. – Igor Mar 28 '18 at 10:07
  • @Igor: You're absolutely right. I would be upgrading to that way as soon as I get a little adept at angular 2. Thank you. – stack questions Mar 28 '18 at 10:09

0 Answers0