从MS SQL下载/上传文件后,文件变得不可读

本文关键字:文件 下载 SQL MS | 更新日期: 2023-09-27 18:10:07

我有ASP MVC应用程序,MS SQL和c#。我下载一个文件到MS SQL,然后我从数据库上传文件。在此之后,文件变得不可读。我不明白发生了什么。下面的代码,将文件返回给客户端。

public string CreateFile(HttpPostedFileBase file)
    {
        string stream_id = String.Empty;
        try
        {
            int size = file.ContentLength;
            string name = file.FileName;               
            string contentType = file.ContentType;
            byte[] bytes = new byte[size];
            file.InputStream.Read(bytes, 0, size);
            string constr = ConfigurationManager.ConnectionStrings["PokrovConnectionString"].ConnectionString;
            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    string query = "DECLARE @MyTableVar TABLE (stream_id uniqueidentifier);"
                        + "INSERT INTO Files(name, file_stream) OUTPUT INSERTED.stream_id INTO @MyTableVar VALUES(@name, @file_stream);"
                        + "SELECT TOP (1) @Identity = stream_id FROM @MyTableVar;";
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = Path.GetFileName(file.FileName);
                        cmd.Parameters.Add("@file_stream", SqlDbType.VarBinary).Value = bytes;
                        SqlParameter idParam = cmd.Parameters.Add("@Identity", SqlDbType.NVarChar, 1000);
                        idParam.Direction = ParameterDirection.Output;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                        stream_id = (string)idParam.Value;
                    }
                }
                ts.Complete();
            }
        }
        catch { }
        return stream_id;
    }
    public FileContentResult GetFile(Guid stream_id,  string contentType = "application/octet-stream")
    {
        SqlDataReader rdr;
        byte[] fileContent = null;
        string mimeType = "";
        string fileName = "";
        string connect = onfigurationManager.ConnectionStrings["PokrovConnectionString"].ConnectionString;
        bool success = false;
        using (var conn = new SqlConnection(connect))
        {
            var qry = "SELECT file_stream, name, file_type FROM Files WHERE stream_id = @stream_id";
            var cmd = new SqlCommand(qry, conn);
            cmd.Parameters.AddWithValue("@stream_id", stream_id);
            conn.Open();
            try
            {
                rdr = cmd.ExecuteReader();
                if (rdr.HasRows)
                {
                    rdr.Read();
                    fileContent = (byte[])rdr["file_stream"];
                    mimeType = rdr["file_type"].ToString();
                    fileName = rdr["name"].ToString();
                }
                success = true;
            }
            catch
            {
                return null;
            }
        }
        if (success == true)
        {
            FileContentResult newFile = new FileContentResult(fileContent, contentType);
            newFile.FileDownloadName = fileName;
            return newFile;
        }
        return null;
    }

从MS SQL下载/上传文件后,文件变得不可读

从查看存储已发布文件到数据库的代码-很可能您忘记复制实际文件内容,只是初始化带有已发布文件长度的空字节数组,并将其保存到db.

。Net有一个类,它可以帮助您将流保存到sql server中,而无需分配缓冲区并首先读取整个流- SqlBytes。试着替换这一行:

byte[] bytes = new byte[file.ContentLength];

与这个:

SqlBinary bytes = new SqlBinary(file.InputStream);

你也可以在这篇文章中看看SqlBinary的用法