In MVC4, how do I upload a file (an image) to SQL Server that's part of my domain model?

I’m relatively new to MVC, and I’ve never had to deal with uploading a file (an image, specifically) to a SQL Server database. To be honest, I don’t know what I’m doing here.

Here’s what I have so far – here’s my domain model (note the HttpPostedFileBase in my model – this is what I want to upload):

  • Buffer and cache Difference?
  • How to filter out all rows matching a certain ID when a variable in one row is 'X'? SQL Server
  • Loop through column values in Temp Table with N columns
  • Match comma-separated values in SQL Server column
  • Connect to SQLServer running in a parallels VM
  • SQL Order By when DISTINCT?
  • public class Profile
    {
        [Key]
        public int Id { get; set; }
    
        [Required(ErrorMessage="Years of service is required")]
        [DisplayName("Years Service:")]
        public int YearsService { get; set; }
    
        [DataType(DataType.MultilineText)]
        [DisplayName("Notable Achivements:")]
        public string NotableAchivements { get; set; }
    
        [Required(ErrorMessage = "Technical skills are required")]
        [DataType(DataType.MultilineText)]
        [DisplayName("Technical Skills:")]
        public string TechnicalSkills { get; set; }
    
        [DisplayName("Upload Image: ")]
        public HttpPostedFileBase Photo { get; set; }
    
        public string CreatedBy { get; set; }
        public DateTime CreatedDate { get; set; }
        public string ModifiedBy { get; set; }
        public DateTime ModifiedDate { get; set; }
    }
    

    And here’s my view:

    @using (Html.BeginForm("Create", "Profiles", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
    <div class="editor-label">
        @Html.LabelFor(model => model.YearsService)
    </div>
    <div class="editor-field">
        @Html.EditorFor(model => model.YearsService)
        @Html.ValidationMessageFor(model => model.YearsService)
    </div>
    
    <div class="editor-label">
        @Html.LabelFor(model => model.NotableAchivements)
    </div>
    <div class="editor-field">
        @Html.EditorFor(model => model.NotableAchivements)
        @Html.ValidationMessageFor(model => model.NotableAchivements)
    </div>
    
    <div class="editor-label">
        @Html.LabelFor(model => model.TechnicalSkills)
    </div>
    <div class="editor-field">
        @Html.EditorFor(model => model.TechnicalSkills)
        @Html.ValidationMessageFor(model => model.TechnicalSkills)
    </div>
    
    <input type="file" name="photo" />
    <input type="submit" name="Submit" id="Submit" value="Upload" />
    }
    

    I hope there’s something glaringly obvious that I’m doing wrong. Can anyone provide advice on how to do a simple file upload to a SQL Server database?

  • How to connect to SQL Server database from another pc or on vmware
  • JDBC - Connection extremely slow on Windows, Fine on Linux
  • How does one deal with multiple TimeZones in applications that store dates and times?
  • Group records by consecutive dates when dates are not exactly consecutive
  • Change the discount in the Order Details table on all orders of products of an ordered quantity
  • upload failed: .\<filename.sql> to s3://<bucket-name>/<filename.sql> certificate verify failed (_ssl.c:661)
  • 3 Solutions collect form web for “In MVC4, how do I upload a file (an image) to SQL Server that's part of my domain model?”

    First and foremost, don’t save the image to your database. There’s countless “guides” floating around online that suggest using a byte array or “image” type in MSSQL. Databases are not for binary data storage. They’ll accommodate binary storage, sure, but just because you can shoot yourself in the foot with an AR-15, doesn’t mean you should.

    Instead, store the image where it belongs: the filesystem. And, then, merely reference it’s path in the database.

    Now, the easiest way to handle all this is with a view model. If you’re not using view models, already, now’s a great time to start. In general, you don’t want to send your actual database-backed model directly to the view. In forms, there’s potential for all sorts of nasty data-tampering by malicious users and even in simple views, you’re generally passing along more data than they need.

    So, the first step is to create a view model for Profile. It’s common to name this something like ProfileViewModel or ProfileVM. Then, on this model, you’ll only add the properties of Profile that you’ll be editing or otherwise interacting with in your view. Then, you can actually add additional properties not on your database-backed model for special-purpose view functions, as well, such as a SelectList to use with DropDownListFor, so you can actually remain strongly-typed instead of resorting to ViewBag for such things.

    For your image, you’ll want two fields. I usually go with something like the following:

    public string Photo { get; set; }
    public HttpPostedFileBase PhotoUpload { get; set; }
    

    In your edit/create view, you’ll only reference PhotoUpload, which will be your file upload field (though you can use Photo to display the currently set image, as well, if you like).

    @Html.TextBoxFor(m => m.PhotoUpload, new { type = "file" })
    

    Then, to handle the posted file in your controller action:

    if (model.PhotoUpload.ContentLength > 0) {
        // A file was uploaded
        var fileName = Path.GetFileName(model.PhotoUpload.FileName);
        var path = Path.Combine(Server.MapPath(uploadPath), fileName);
        model.PhotoUpload.SaveAs(path);
        model.Photo = uploadPath + fileName;
    }
    

    Where uploadPath should be a home directory-relative path where you want to store the uploaded image, i.e. something like ~/uploads/profile/photos. Make sure you create the directory before trying to post the form, or add in some logic to check for the directory existence and create it if necessary (this requires higher trust on the server, though, so is not ideal in most environments where security is a big concern).

    Then, you just need some way to map the data from your view model back to your database-backed model. You can do this manually, but using something like AutoMapper will make your life much easier. With automapper (where model is an instance of ProfileViewModel):

    var profile = AutoMapper.Mapper.Map<Profile>(model);
    

    Or, since with something like a profile, it’s more common that you’ll be editing an existing model than creating a new one:

    var profile = db.Profiles.Find(userId);
    ...
    Automapper.Mapper.Map(model, profile);
    

    On your actual model, then, you won’t have the PhotoUpload property, just Photo. The path you set for Photo on your view model will be mapped on your model’s property of the same name, so all that’s left is to save your updated profile.

    Also, since you’re talking about uploading photos, you’ll probably want to add some content type checking to make sure user’s are uploading an image instead of something silly like a Word document. Before your if (ModelState.IsValid) check, add:

    var validTypes = new[] { "image/jpeg", "image/pjpeg", "image/png", "image/gif" };
    if (!validTypes.Contains(model.PhotoUpload.ContentType))
    {
        ModelState.AddModelError("PhotoUpload", "Please upload either a JPG, GIF, or PNG image.");
    }
    

    Change the mime types as necessary to match your business case.

    When I was learning I used this and this to get me started on uploading files.

    I would also suggest that in your model you make the property Photo a byte[] or similar that should save back into your database better (I am assuming you are using something like LINQ to SQL or Entity Framework).

    Following the above links, I would suggest handling the upload in your controller method by:

    1. Having a parameter of type HttpPostedFileBase in your controller method and call it “photo” (so it will bind from your view where the file input field is called “photo”)
    2. In your controller method, check the parameter is not null and if it isn’t you can read the file into something like a MemoryStream via the InputStream property of HttpPostedFileBase
    3. From the MemoryStream you can get the byte[] via the ToArray() method
    4. Plug the byte[] into your model and then save it to your database

    Also, remember to dispose of the MemoryStream when done

    I’m sure there are better ways but these have always worked for me 🙂

    HTH,
    Nathan

    In my application I sotre image as base64string hence, nvarchar(max) is the data type i used. The advangage of this is that you can pass based64String image in JSON object from or to website.

    To convert image to based64string

    public static string ToBased64String(this Image image, ImageFormat format)
    {
      using (MemoryStream ms = new MemoryStream())
      {
        image.Save(ms, format);
        byte[] imageBytes = ms.ToArray();
        string based64String = Convert.ToBased64String(imageBytes);
        return based64String;
    
      }
    }
    

    then you can call your method like this

    image.ToBased64String
    

    To convert based64String to image

    public static ImageFromBased64String(string based64Image, string path)
    {
      Image image = null;
      var bytes = Convert.FromBased64String(based64String);
      using (var fileStream = new FileStream(path, FileMode.Create))
      {
        fileStream.Write(bytes, 0, bytes.Length);
        fileStream.Flush();
        image = Image.FromStream(fileStream, true);
        return image;
      }
    }
    

    please refer to my answer here

    https://stackoverflow.com/a/30057680/1554116

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.