Saving Any File to in the Database, Just Convert It to a Byte Array

Saving any file to in the database, just convert it to a byte array?

Since it's not mentioned what database you mean I'm assuming SQL Server. Below solution works for both 2005 and 2008.

You have to create table with VARBINARY(MAX) as one of the columns. In my example I've created Table Raporty with column RaportPlik being VARBINARY(MAX) column.

Method to put file into database from drive:

public static void databaseFilePut(string varFilePath) {
    byte[] file;
    using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read)) {
        using (var reader = new BinaryReader(stream)) {
            file = reader.ReadBytes((int) stream.Length);       
        }          
    }
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlWrite = new SqlCommand("INSERT INTO Raporty (RaportPlik) Values(@File)", varConnection)) {
        sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
        sqlWrite.ExecuteNonQuery();
    }
}

This method is to get file from database and save it on drive:

public static void databaseFileRead(string varID, string varPathToNewLocation) {
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
        sqlQuery.Parameters.AddWithValue("@varID", varID);
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                sqlQueryResult.Read();
                var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                using (var fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write)) 
                    fs.Write(blob, 0, blob.Length);
            }
    }
}

This method is to get file from database and put it as MemoryStream:

public static MemoryStream databaseFileRead(string varID) {
    MemoryStream memoryStream = new MemoryStream();
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
        sqlQuery.Parameters.AddWithValue("@varID", varID);
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                sqlQueryResult.Read();
                var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                //using (var fs = new MemoryStream(memoryStream, FileMode.Create, FileAccess.Write)) {
                memoryStream.Write(blob, 0, blob.Length);
                //}
            }
    }
    return memoryStream;
}

This method is to put MemoryStream into database:

public static int databaseFilePut(MemoryStream fileToPut) {
        int varID = 0;
        byte[] file = fileToPut.ToArray();
        const string preparedCommand = @"
                    INSERT INTO [dbo].[Raporty]
                               ([RaportPlik])
                         VALUES
                               (@File)
                        SELECT [RaportID] FROM [dbo].[Raporty]
            WHERE [RaportID] = SCOPE_IDENTITY()
                    ";
        using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        using (var sqlWrite = new SqlCommand(preparedCommand, varConnection)) {
            sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;

            using (var sqlWriteQuery = sqlWrite.ExecuteReader())
                while (sqlWriteQuery != null && sqlWriteQuery.Read()) {
                    varID = sqlWriteQuery["RaportID"] is int ? (int) sqlWriteQuery["RaportID"] : 0;
                }
        }
        return varID;
    }

how to convert the file content to byte array with php

PHP doesn't have a "byte array" data type. What it has is a string type, which is a byte array for all intents and purposes. To read the binary content of a file into a variable which is as close to a byte array as you'll ever get in PHP, do:

$content = file_get_contents($_FILES['my_file']['tmp_name']);

Yup, that's it. Nothing more to do.

I'm not particular familiar with the sqlsrv API, but perusing its documentation it appears that you can (need to?) set a flag this way to flag the data as being binary:

sqlsrv_query($conn, 'INSERT INTO files (file_data) VALUES (?)', array(
    array($content, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING, SQLSRV_SQLTYPE_BINARY)
));

Save local file from MS SQL varbinary

Unless you serialised an object when saving, which I doubt, there's no reason to try to deserialise an object when retrieving. Every field from a DataRow is returned as an Object reference, because it must support any type of object, but that object still has its own type. If you saved binary data then that type will be Byte(), i.e. a Byte array. Kinda looks like File.WriteAllBytes takes a Byte array:

File.WriteAllBytes(“C:\Users\Desktop\test.tiff”, DirectCast(inputObj, Byte()))

That is what I would expect that you need to do. It does depend on how you saved the data in the first place but if you used File.ReadAllBytes or Image.Save then this should do the job. If not, show us how you sabed the data in the first place.

Save Image to HDD or store as Byte Array in database?

Avoid getting them in the database. That will make your DB a lot larger in size just because of a few files. That also affects the backup size.

I do not see any real gain on having the actual file bytes in the database. If you have the physical path to the file in the file system, that would suffice.

That also allows you to have your own backup strategy for the files.

How to convert a file into byte array in memory?

As opposed to saving the data as a string (which allocates more memory than needed and might not work if the binary data has null bytes in it), I would recommend an approach more like

foreach (var file in files)
{
  if (file.Length > 0)
  {
    using (var ms = new MemoryStream())
    {
      file.CopyTo(ms);
      var fileBytes = ms.ToArray();
      string s = Convert.ToBase64String(fileBytes);
      // act on the Base64 data
    }
  }
}

Also, for the benefit of others, the source code for IFormFile can be found on GitHub

Can't save byte[] array data to database in C#. It's saving 0x

I was having the same problem, that is, the first time the image was saved correctly on the database side, but if subsequently validation failed and then I tried to save the image again after entering valid data I would get 0x in the image column. To solve that I did what @Ann L. said:

byte[] photo = null;

if(model.Photo != null)
{
    var stream = model.Photo.InputStream;
    stream.Position = 0;

    using(BinaryReader br = new BinaryReader(model.Photo.InputStream))
    {
        photo = br.ReadBytes(model.Photo.ContentLength);
    }
}

Convert PDF to Image Byte array to save to database

The issue is likely caused by you trying to re-use the same MemoryStream that is holding the input file bytes. You should create a new memory stream to save to.

I don't have access to devexpress but I grabbed another Nuget package that i am associated with https://www.nuget.org/packages/Leadtools.Pdf/ and tested it and this code works to save the PDF to a PNG memorystream:

using (var ms = new MemoryStream(fileBytes))
using (var codecs = new RasterCodecs())
{
   codecs.Options.Load.AllPages = true;
   using (var rasterImage = codecs.Load(ms))
   using (var outputStream = new MemoryStream())
      codecs.Save(rasterImage, outputStream, RasterImageFormat.Png, 0);
}

Image byte array is not saving properly to database

The default length of varbinary is 1 if the length isn't specified. See MSDN:

The default length is 1 when n isn't specified in a data definition or variable declaration statement

CREATE PROCEDURE [dbo].[SetProfilePicture]
    @Image VARBINARY,

needs to be given a length, as such:

CREATE PROCEDURE [dbo].[SetProfilePicture]
    @Image VARBINARY(max),

---

Related Topics