将任何文件保存到数据库中，只需将其转换为字节数组? [英] Saving any file to in the database, just convert it to a byte array?

问题描述

将文件转换为字节数组是否是将任何文件格式保存到磁盘或数据库 var 二进制列的最佳方式?

Is converting a file to a byte array the best way to save ANY file format to disk or database var binary column?

因此，如果有人想要保存 .gif 或 .doc/.docx 或 .pdf 文件，我可以将其转换为字节数组 UFT8 并将其作为字节流保存到数据库中吗?

So if someone wants to save a .gif or .doc/.docx or .pdf file, can I just convert it to a bytearray UFT8 and save it to the db as a stream of bytes?

推荐答案

因为没有提到您的意思是什么数据库，所以我假设使用 SQL Server.以下解决方案适用于 2005 和 2008.

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

您必须使用 VARBINARY(MAX) 作为列之一创建表.在我的示例中，我创建了表 Raporty，其中 RaportPlik 列是 VARBINARY(MAX) 列.

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.

将file从drive放入数据库的方法:

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

这个方法是从数据库中获取file并保存在drive:

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

这个方法是从数据库中获取file，并把它作为MemoryStream:

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

这个方法就是把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;
    }

这篇关于将任何文件保存到数据库中，只需将其转换为字节数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！