Good day.

There is a database in which user accounts are stored. Two binary files are associated with each user. The first file takes about 30KB, the second takes about 300KB. Over time, the number of users can be in the hundreds of thousands.

The first option is to store this data in a database. The Data field in the table will be of the varbinary (MAX) type:

public ActionResult GetWorld() { var world = db.GetWorld(...); return File(world.Data, "application/octet-stream"); } public ActionResult SaveWorld(HttpPostedFileBase worldData) { var world = db.GetWorld(...); world.Data = new byte[worldData.ContentLength]; worldData.InputStream.Read(world.Data, 0, worldData.ContentLength); }

The second option is to store this data as separate files:

 public ActionResult GetWorld() { var world = db.GetWorld(...); string pathToWorldData = Server.MapPath( string.Format("~/App_Data/Worlds/{0}.dat", world.Id)); return File(pathToWorldData, "application/octet-stream"); } public ActionResult SaveWorld(HttpPostedFileBase worldData) { var world = db.GetWorld(...); string pathToWorldData = Server.MapPath( string.Format("~/App_Data/Worlds/{0}.dat", world.Id)); worldData.SaveAs(pathToWorldData); }

There is another option - varbinary (MAX) and FILESTREAM, but how is it essentially different from the second? Only there will be a lot of fuss in the code.

    3 answers 3

    If you store the data as a table field, the base will swell. Using the FILESTREAM option allows you to store data as separate files, but with the support of transaction. True, the database log will still swell, it will need to be cleaned periodically.

    Now about the problems. FILESTREAM works only with Windows authentication when accessing the database. Perhaps I did not find something, but I did not succeed in another way. Combined with the fact that the Windows Services Manager likes to lose passwords after a couple of reboots, it’s fun to work with this option. I also had such a garbage that after installing some of the Windows updates, it was not included.

    Taking into account the fact that Microsoft likes to add all the time new features of their products, not bringing to mind the old ones, use better files. If you need to do transactional support in this way, look towards distributed transactions.

      Binary data is best stored in files. Pictures, videos and music no one stores in the database. Do not once again complicate things. To store a large number of files usually create a lot of subfolders, for example, you can store files for the first thousand entries in the folder / 0 /, for the second in / 1 /, etc.

      • Store, still as, only in specially trained / prepared this database. Basho, won, Riak CS sells, for example. But not in SQL Server. There is an article on MS Research about this (albeit an old one, dated 2006). - drdaeman

      Well, if you have money, then in the database, because 500 megabytes standard like, further already $$$

      and also from the base it will be twitching longer, fact

      the field must not be selected varchar exactly, since you will be storing bytes there, better varbinary (by the way, for xml files there is an xml type in 2008)

      I would advise only to store the path to the files in the database, and pull the file from the file system

      • Keep track is not necessary. Optimally in the application configuration, specify the root directory for the files, and build the relative path in the code based on the ID, etc. In the end - minus one column of data, plus the ability to customize. - Shad