5 Replies - 1573 Views - Last Post: 12 November 2013 - 08:08 AM Rate Topic: -----

#1 Moogoo   User is offline

  • D.I.C Head
  • member icon

Reputation: 10
  • View blog
  • Posts: 82
  • Joined: 06-January 10

Converted file to byte array but truncating in SQL db

Posted 08 November 2013 - 12:40 PM

Hello, I have an issue where I'm converting a .docx file into a byte array to save to a database. The problem is when I run the code to commit the byte array to the database, .NET throws an exception that says String or binary will be truncated. Terminating transaction (that's not exact, but you get the outcome). Also, my column in the datatable is varbinary(max) I was hoping someone would have some insight as to what I need to do to fix this:

Here's the model:
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DisasterPlan.Models
{
    [Table("Server")]
    public class Server
    {
        [Key]
        public int ID { get; set; }
        public string Name { get; set; }
        public string CostCenter { get; set; }
        public string CreatedBy { get; set; }
        public System.DateTime CreatedDate { get; set; }
        public string ModifiedBy { get; set; }
        public Nullable<System.DateTime> ModifiedDate { get; set; }
        public int VendorID { get; set; }
        public int EnvironmentID { get; set; }
        public int OSPatchingID { get; set; }
        public int OSRequiredID { get; set; }
        public int RemoteAccessID { get; set; }
        public int ServerTypeID { get; set; }
        public int DepartmentID { get; set; }
        public string Resources { get; set; }
        public byte[] ProcedureFile { get; set; }
        public string ProcedureFileName { get; set; }
        public string ProcedureContentType { get; set; }
        public virtual Department Department { get; set; }
        public virtual Environment Environment { get; set; }
        public virtual OSPatching OSPatching { get; set; }
        public virtual OSRequired OSRequired { get; set; }
        public virtual RemoteAccess RemoteAccess { get; set; }
        public virtual ServerType ServerType { get; set; }
        public virtual Vendor Vendor { get; set; }        
    }
}




Here's the call to add it to database:
[HttpPost]
        public ActionResult NewServer(Server server)
        {
            using (DisasterPlanContext db = new DisasterPlanContext())
            {
                Server newServer = new Server
                {
                    Name = server.Name,
                    CostCenter = server.CostCenter,
                    CreatedBy = WindowsIdentity.GetCurrent().Name,
                    CreatedDate = DateTime.Now,
                    DepartmentID = server.DepartmentID,
                    EnvironmentID = server.EnvironmentID,
                    ServerTypeID = server.ServerTypeID,
                    RemoteAccessID = server.RemoteAccessID,
                    OSPatchingID = server.OSPatchingID,
                    OSRequiredID = server.OSRequiredID,
                    Resources = server.Resources
                };

                var attachment = Request.Files[0];
                if (attachment.ContentLength != 0)
                {
                    string filePath = Path.GetFullPath(attachment.FileName);
                    string fileName = Path.GetFileName(filePath);
                    string content = attachment.ContentType;
                    FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                    BinaryReader br = new BinaryReader(fs);
                    Byte[] fileImage = br.ReadBytes((Int32)fs.Length);
                    newServer.ProcedureFileName = fileName;
                    newServer.ProcedureContentType = content;
                    newServer.ProcedureFile = fileImage;
                }

                Vendor newVendor;
                if (server.VendorID == 0)
                {
                    newVendor = new Vendor
                    {
                        Contact = server.Vendor.Contact,
                        Phone = server.Vendor.Phone,
                        Email = server.Vendor.Email,
                        Address1 = server.Vendor.Address1,
                        Address2 = server.Vendor.Address2,
                        City = server.Vendor.City,
                        State = server.Vendor.State,
                        ZIP = server.Vendor.ZIP
                    };
                    db.Vendors.Add(newVendor);
                    db.SaveChanges();
                }
                else
                {
                    newServer.VendorID = server.VendorID;
                }
                db.Servers.Add(newServer);
                db.SaveChanges();
                return View("CreateServer");
            }
        }
    }



Thanks for your help in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Converted file to byte array but truncating in SQL db

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6818
  • View blog
  • Posts: 28,229
  • Joined: 12-December 12

Re: Converted file to byte array but truncating in SQL db

Posted 08 November 2013 - 12:52 PM

Quote

that's not exact, but you get the outcome

You should post the full (exact) error message, together with confirmation as to which line it refers to in your posted code.
Was This Post Helpful? 0
  • +
  • -

#3 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Converted file to byte array but truncating in SQL db

Posted 08 November 2013 - 01:45 PM

It's also entirely possible that the problem isn't with your image, it could be one of your string fields. Make sure that none of your string lengths are longer than the field definitions.
Was This Post Helpful? 0
  • +
  • -

#4 optix212   User is offline

  • D.I.C Addict
  • member icon

Reputation: 30
  • View blog
  • Posts: 540
  • Joined: 10-October 09

Re: Converted file to byte array but truncating in SQL db

Posted 08 November 2013 - 02:24 PM

I actually wanted to store files in my database as well at first.. But then I thought to myslef: "What if users want to add multiple files?"

So then I realized I would need a different table to hold all files for each row in my database, and that would have just been a mess...

So, what I ended up doing was creating directories on the server that hosts the database that represent each row. This allows for storing multiple files. Not to mention, it's a lot less of a headache. On the column that I had originally set up to hold the files in the database, I turned into a DataGridViewButtonColumn. Once clicked, it will open an explorer window to that directory on the server computer and allow you to view all files that are associated with that row in your database.

Also, another cool little feature, is Drag and Dropping files onto a row. That was fun to code (:

I don't know if this is going to apply to your project, but hopefully it helps.

If this isn't the best way to do it (maybe your connecting to a server 100 miles away, and would literally have to remote to it), then maybe consider and FTP Library to help you connect to an ftp server that you can use to store files. I definitely do not recommend storing any file type other than an image in a database.

This post has been edited by optix212: 08 November 2013 - 02:28 PM

Was This Post Helpful? 0
  • +
  • -

#5 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Converted file to byte array but truncating in SQL db

Posted 08 November 2013 - 02:43 PM

Quote

So then I realized I would need a different table to hold all files for each row in my database, and that would have just been a mess


Just for clarification, that's not a mess, that's standard relational DB design. You might have a Users table and a Files table, and the Files table would have a foreign key relation to the Users table. So you could grab all files where UserId = whatever. However, many people do choose to use the underlying file system for file storage rather than the DB, and that's a perfectly acceptable way to go. It even has some benefits (being able to directly access the files is one).

Edit: misunderstood what you wrote. Still useful info, I think.
Was This Post Helpful? 0
  • +
  • -

#6 Moogoo   User is offline

  • D.I.C Head
  • member icon

Reputation: 10
  • View blog
  • Posts: 82
  • Joined: 06-January 10

Re: Converted file to byte array but truncating in SQL db

Posted 12 November 2013 - 08:08 AM

Never mind. My code is good. The truncation was coming from a string column. I apologize.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1