none
Keeping images in database RRS feed

  • Question

  • Hi, there. The motive of posting this question in MSDN is to ask you guys for a recommendation.

    I am planning to work on a project for an old department, they used to keep all the records in a notebook and now they are planning to digitalize the documents and keep them preserved in PDF format. Every notebook has its unique name and may have more than 100+ pages.

    I want to work on this project and I am a bit confused, should I store the files in my database or should I keep the file on disk and that link should be saved in the database, can someone me up of which approach should I take.

    Thank You

    Sunday, March 25, 2018 8:50 AM

Answers

  • Hi! As you mentioned you need recommendation so let me give mine. Its not a professional practice to store images into databases unless you don't have any other option left.

    It is always preferred to save files in any folder and save the path into database instead of whole file. It will keep your database optimized and lesser in size.

    Or you can upload files on cloud and save the url in your database against any record.

    Please mark it as answer and vote it up if you find my recommendation helpful. 

    Sunday, March 25, 2018 11:09 AM

All replies

  • Hello,

    SQL-Server can easily handle working with images while MS-Access can it's not would be best to avoid storing images in a database as it's prone to corruption and blotting.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, March 25, 2018 9:13 AM
    Moderator
  • If I store images in the database, will that reduce the performance and increase the size of the database ? As I will be storing thousands of images, I want my database to perform well.
    Sunday, March 25, 2018 10:02 AM
  • If I store images in the database, will that reduce the performance and increase the size of the database ? As I will be storing thousands of images, I want my database to perform well.

    Going with SQL-Server without any configuration I doubt very much there would be any performance issues. Now if you had say over one hundred images you would need to tweak various setting in SQL-Server to adjust for this many images.

    Example, in my last position we stored billions of images in SQL-Server with zero performance hits, we also did the same with IBM-DB2 database, no performance issues. What I don't know is if SQL-Server Express edition is the same in regards to performance as from my experience I've never tried storing this many images in the express edition.

    The only true method to try this is to try in regards to SQL-Server Express edition. With the paid version of SQL-Server you will be fine.  

    In short, the paid version of SQL-Server will not blink with the amount of images you are working with.

    Options to implement with code

    • SqlClient connection and command objects to work with the data.
    • Entity Framework to work with the data.

    Options not to use as they are much harder to work through this task

    • TableAdapters
    • DataAdapters



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, March 25, 2018 10:53 AM
    Moderator
  • Hi! As you mentioned you need recommendation so let me give mine. Its not a professional practice to store images into databases unless you don't have any other option left.

    It is always preferred to save files in any folder and save the path into database instead of whole file. It will keep your database optimized and lesser in size.

    Or you can upload files on cloud and save the url in your database against any record.

    Please mark it as answer and vote it up if you find my recommendation helpful. 

    Sunday, March 25, 2018 11:09 AM
  • Yes Exactly. Don't save the images in the database. Try saving them in folder and save the path in database to retrieve them.
    Sunday, March 25, 2018 11:11 AM
  • Hi! As you mentioned you need recommendation so let me give mine. Its not a professional practice to store images into databases unless you don't have any other option left.

    It is always preferred to save files in any folder and save the path into database instead of whole file. It will keep your database optimized and lesser in size.

    Or you can upload files on cloud and save the url in your database against any record.

    Please mark it as answer and vote it up if you find my recommendation helpful. 

    Yes it is professional practice to store images in databases and we can say using the file system is a best practice which is all dependent on resources, skill of those coding and setting things up.

    I've given one example for storing billions of images in a database. My former company has been doing this for 30 years with SQL-Server and IBM-DB2 and sister companies in 17 US states have been doing this too. My current company does the same with Oracle. With that it's considered a professional best practice but storing them in the files system is also a best practice.

    Contradictory? No because it's dependent on how the database is setup. Storing images in the file system has it's issues and so does database storage. When either is done properly you have a solid solution but if done wrong the opposite. 


     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, March 25, 2018 11:52 AM
    Moderator
  • Going with this debate, Amazon uses databases to store images. One would think that if there was an issue they would be using the file system rather than a database.

    https://aws.amazon.com/simpledb/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, March 25, 2018 11:57 AM
    Moderator
  • Sid,

    It depends on the version of SQL Server, but for sure the new ones do store the images (blobs) not anymore as bitmaps inside the tables. Default they store a path to a special folder which holds the images. (Be aware, not your path but a path SQL server uses)


    Success
    Cor



    • Edited by Cor Ligthert Sunday, March 25, 2018 4:20 PM dislex
    Sunday, March 25, 2018 12:17 PM
  • Well I don't know that a .PDF is necessarily just an image since that would depend on the .PDF type in question but even an image type .PDF has .PDF instructions in it. However if it is just an image perhaps it would be best to use compression on .PDF document but I have no idea really or do I know if they would readily compress to a lesser size.

    How large is a 100+ page .PDF document? It seems the department may have numerous to a plethora of digitized notebooks as the end result.

    Is only your program to access these documents or will they be stored as "images" so other programs can access them as "images" too? Is storing an image the same as storing a .PDF?


    La vida loca

    Sunday, March 25, 2018 9:17 PM
  • Actually, with something like SQL Server you can do a little bit of both by using a FileTable. It's basically a part of the file system that is managed by SQL Server so you can link the files with the SQL Server native database table data. 

    https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server

    You might want to consider this method if some of your files are relatively large.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 27, 2018 2:42 AM
  • @Cor, I agree with this approach more than putting the records into the mdf and ndf files of SQL Server.  However, I'm at a lost now on how to use the FILESTREAM / FILE TABLE features since EF Core does not support them.  Have you heard of a preferred practice now?

    Best Wishes, Keith Nicholson

    Thursday, June 7, 2018 2:04 PM
  • @Paul, as I asked above to @Cor, have you discovered how to use FileTable with EF Core?  It doesn't appear to be supported.

    Best Wishes, Keith Nicholson

    Thursday, June 7, 2018 2:05 PM
  • @Paul, as I asked above to @Cor, have you discovered how to use FileTable with EF Core?  It doesn't appear to be supported.

    Best Wishes, Keith Nicholson


    I don't believe the OP was asking about an EF solution and I'm not going to change the topic. Please if you have question that is different than that of the OP then you should post a new question in the forum.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 8, 2018 1:33 AM
  • I feel it does belong with the OP's question.  It was asked how to add files to a database.  Answers in this thread recommended SQL Server.  Your post suggested FileTables.  In order to use SQL FILESTREAM or FileTables, the solution needs to access the API in order to do that.  Today that exists in one of three ways:

    Either way, no harm, no foul.

    Peace,

    Keith


    Best Wishes, Keith Nicholson

    Friday, June 8, 2018 3:52 AM