如何存储在数据库中的文件？ [英] How to store the file in the database?

问题描述

如何存放这是由用户对数据库上载的文件？我想将文件存储在数据库中，我们如何能做到这一点？在后端我使用与C＃.NET应用程序的SQL。

How to store the file which is uploaded by the user to the database ? I want to store the file in the database how can we do that ? In the back-end I am using sql with c#.net application.

推荐答案

该解决方案适用于SQL SERVER 2005/2008。

This solution works for SQL SERVER 2005/2008.

您必须创建表 VARBINARY（MAX）作为列。在我来说，我已经创建表 Raporty 与列 RaportPlik 是 VARBINARY（MAX）列。

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

下面有几个支持功能，你可以修改你的需要：

Below there are couple of support functions you can modify for your needs:

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

第一种方法是将文件转换成从驱动数据库，第二种方法是获取文件并将其保存在驱动器，和第3个方法是从数据库中获取文件，并把它作为的MemoryStream 这样你就可以一些其他的东西与它然后就写它来驱动。

First method is to put file into database from drive, second method is to get file and save it on drive, and 3rd method is to get file from database and put it as MemoryStream so you can some other stuff with it then just writing it to drive.

这第4个方法是将的MemoryStream 到数据库：

This 4th 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屋！