问题描述
我正在建立一个站点,在该站点中必须使用文件上载控制来附加支持日志/邮件等.我认为将文件保存在数据库中将是更好的选择.
I am building a site where I have to use file upload control for attaching support logs / mails etc... I felt that saving files in a database would be the better option.
我正在使用下面的代码上传文件.但是,我无法测试它,因为我不知道如何从数据库中检索文件.有人可以帮我吗?
I am using the code below to upload files. However, I am not able to test it as I don't know how to retrieve files from a database. Can some one please help me on this?
文件类型可以是任何类型.
File type can be anything.
代码:
FileUrl = "C:\\Attachments\\"+Path.GetFileName(UploadCtrl.NavigateUrl);
FileStream fs = new FileStream(FileUrl, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
long numBytes = new FileInfo(FileUrl).Length;
buff = br.ReadBytes(Convert.ToInt32(numBytes));
SqlConnection conn = new SqlConnection(SQLSrc.ConnectionString);
conn.Open();
SqlCommand command = conn.CreateCommand();
string InsertQueryText = "insert into Attachments values ('" + Path.GetFileName(FileUrl) + "','" + MIME(Path.GetExtension(Att_Overview_Link.NavigateUrl)) + "','" + buff + "');";
command.CommandText = InsertQueryText;
command.ExecuteNonQuery();
在这里,MIME是用户定义的函数,用于获取指定文件类型的MIME值.
Here, MIME is the user defined function to get the MIME value of the specified file type.
前端:C#ASP.NET和SQL Server作为后端
Frontend: C# ASP.NET and SQL Server as backend
推荐答案
首先修复您的代码以删除 SQL注入漏洞:
Start by fixing your code to remove the SQL Injection vulnerability:
FileUrl = "C:\\Attachments\\" + Path.GetFileName(UploadCtrl.NavigateUrl);
using (SqlConnection conn = new SqlConnection(SQLSrc.ConnectionString))
using (SqlCommand command = conn.CreateCommand())
{
command.CommandText = "insert into Attachments values (@FileName, @MimeType, @FileBytes)";
command.Parameters.AddWithValue("@FileName", Path.GetFileName(FileUrl));
command.Parameters.AddWithValue("@MimeType", MIME(Path.GetExtension(Att_Overview_Link.NavigateUrl)));
command.Parameters.AddWithValue("@FileBytes", File.ReadAllBytes(FileUrl));
conn.Open();
command.ExecuteNonQuery();
}
注意::我不确定您的UploadCtrl
是什么,但是大多数文件上载控件都可以作为Stream
而不是服务器上的文件名来直接访问上载的文件.根据此特定控件的工作方式,您可能需要更改读取上传文件的方式.
NB: I'm not sure what your UploadCtrl
is, but most file upload controls provide direct access to the uploaded file as a Stream
, not a file name on the server. Depending on how this specific control works, you might need to change how you read the uploaded file.
要检索文件,您将选择相关的名称,MIME类型和字节,然后将它们写入响应:
To retrieve the file, you would select the relevant name, MIME type and bytes, and write them to the response:
using (SqlConnection conn = new SqlConnection(SQLSrc.ConnectionString))
using (SqlCommand command = conn.CreateCommand())
{
command.CommandText = "SELECT FileName, MimeType, FileBytes FROM Attachments WHERE PK = @PK";
command.Parameters.AddWithValue("@PK", Request.QueryString["pk"]);
conn.Open();
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
{
if (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("FileName"));
Response.AppendHeader("Content-Disposition", "attachment; filename=" + name);
Response.ContentType = reader.GetString(reader.GetOrdinal("MimeType"));
int startIndex = 0;
byte[] buffer = new byte[4096];
int fieldIndex = reader.GetOrdinal("FileBytes");
int bytesRead = (int)reader.GetBytes(fieldIndex, startIndex, buffer, 0, buffer.Length);
while (bytesRead != 0)
{
Response.OutputStream.Write(buffer, 0, bytesRead);
Response.Flush();
startIndex += bytesRead;
bytesRead = (int)reader.GetBytes(fieldIndex, startIndex, buffer, 0, buffer.Length);
}
}
}
}
这篇关于从SQL Server数据库检索文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!