从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;
}
从查看存储已发布文件到数据库的代码-很可能您忘记复制实际文件内容,只是初始化带有已发布文件长度的空字节数组,并将其保存到db.
。Net有一个类,它可以帮助您将流保存到sql server中,而无需分配缓冲区并首先读取整个流- SqlBytes。试着替换这一行:
byte[] bytes = new byte[file.ContentLength];
SqlBinary bytes = new SqlBinary(file.InputStream);
你也可以在这篇文章中看看SqlBinary的用法