none
Update a binary file saved in database? RRS feed

  • Question

  • Hi,

    I have a SQL 2008 database that I am select,update and deleting with a Windows user form using Linq from Visual Studio 2008.

    I have 4 tables and I can complete al the steps I need in 3 of the tables. The problem is 1 table where I have 2 columns, File Name and File Data. The data column is an image column and I store text files in it. I use OpenFileDialog for the user to select a file and System.IO.File.ReadAllBytes(_selectedFile); to turn the file in to bytes so I can insert into table. No problems so far.

    The problem is when it comes to updating. On the other tables and the File Name of this table I can search for the required cell and change as they are strings or int's. After submitChanges() the database is changed. The exception I get when trying to update the File Data column is 'Cannot Add An Entitiy That Already Exists'. I know it exists I want to change it!

    The way I see it I have either got to delete to cell first then add or overwrite the cell. Any ideas which way I should go??????

    Regards.

    Tuesday, April 13, 2010 3:36 PM

Answers

  • You should attach the existing entity using DataContext.Attach(entity, originalEntity).
    Don't forget to set UpdateCheck to Never for the File Data column and don't fill in this property in the originalEntity instance, fill it only in the new entity instance.
    Devart Team
    http://www.devart.com/dotconnect
    ADO.NET data providers for Oracle, MySQL, PostgreSQL, SQLite with Entity Framework and LINQ to SQL support
    Thursday, April 15, 2010 8:19 AM
  • Hello freeriderDaz,

    I agree with Devart. You could also specify a stored procedure to update entities in database. You need to add the stored procedure in your linq to sql model and call it in you code. A code snippet like below will do:
    ==============================================
    string filename = ""; // you new file name
    byte[] bytes = File.ReadAllBytes(<dirPath>+filename);
    Binary binfile = new Binary(bytes);
    TestDataContext db = new TestDataContext();
    ImageClass imgentity = db.ImageClass.Single(img => img.fileID == 12); // select the entity you want to updated.

    try
    {
        db.UpdateImageByID(imgentity.fileID, filename, binfile); // UpdateImageByID is the procedure name in my case.
        db.SubmitChanges();
        MessageBox.Show("it's done..");
    }
    catch
    {
        //....
    }

    ==============================================

    Please have a try and let us know the result.
    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Monday, April 19, 2010 4:18 PM

All replies

  • You should attach the existing entity using DataContext.Attach(entity, originalEntity).
    Don't forget to set UpdateCheck to Never for the File Data column and don't fill in this property in the originalEntity instance, fill it only in the new entity instance.
    Devart Team
    http://www.devart.com/dotconnect
    ADO.NET data providers for Oracle, MySQL, PostgreSQL, SQLite with Entity Framework and LINQ to SQL support
    Thursday, April 15, 2010 8:19 AM
  • Hello freeriderDaz,

    I agree with Devart. You could also specify a stored procedure to update entities in database. You need to add the stored procedure in your linq to sql model and call it in you code. A code snippet like below will do:
    ==============================================
    string filename = ""; // you new file name
    byte[] bytes = File.ReadAllBytes(<dirPath>+filename);
    Binary binfile = new Binary(bytes);
    TestDataContext db = new TestDataContext();
    ImageClass imgentity = db.ImageClass.Single(img => img.fileID == 12); // select the entity you want to updated.

    try
    {
        db.UpdateImageByID(imgentity.fileID, filename, binfile); // UpdateImageByID is the procedure name in my case.
        db.SubmitChanges();
        MessageBox.Show("it's done..");
    }
    catch
    {
        //....
    }

    ==============================================

    Please have a try and let us know the result.
    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Monday, April 19, 2010 4:18 PM