Is Storing Files as Byte Arrays in a Database the Most Efficient Method?

Store files of any format into the database as byte arrays

Saving files in various formats to a database or disk requires careful consideration. A common approach is to convert the file to a byte array before storing. This method allows files of any format to be stored, but it raises the question of whether it is the best method.

To illustrate this process, consider the following code snippet provided in the answer:

Insert file as byte array into SQL Server database:

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();
    }
}

Retrieve file from database as byte array:

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();
                byte[] blob = new byte[(int)sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue)];
                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                memoryStream.Write(blob, 0, blob.Length);
            }
    }
    return memoryStream;
}

Use MemoryStream to insert the file into the database as a byte array:

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;
    }

These code snippets demonstrate the process of converting a file into a byte array and then storing it in a database column of type VARBINARY(MAX). When needed, the file can be retrieved as a byte array and saved as a new file or streamed to an application.

The provided text is already quite concise and well-written. The primary change made is to correct a minor issue in the databaseFileRead function where the byte array length was not correctly cast to an integer before allocation. No other significant alterations were deemed necessary for effective paraphrasing while maintaining the original meaning and structure.

The above is the detailed content of Is Storing Files as Byte Arrays in a Database the Most Efficient Method?. For more information, please follow other related articles on the PHP Chinese website!