您现在的位置：程式碼之家 >> 編程語言 > >> .NET 網頁程式設計 >> ASP.NET 基礎 >> 關於ASP.NET

ASP.NET 檔案資料庫儲存與讀取：以二進位欄位實作郵件附件功能

編輯：關於ASP.NET

資料庫結構設計

CREATE TABLE Group_Files (
 ID INT IDENTITY(1,1) PRIMARY KEY,
 FileName NVARCHAR(200) NOT NULL DEFAULT '',
 FileBody VARBINARY(MAX) NULL, -- SQL Server 2005+ 建議用 MAX
 FileType NVARCHAR(50) DEFAULT '',
 UploadTime DATETIME DEFAULT GETDATE(),
 UploadUser NVARCHAR(50)
)

注意：舊版 `image` 型別已淘汰，請使用 `VARBINARY(MAX)` (支援 2GB)。

存儲過程：新增檔案記錄

CREATE PROCEDURE sp_InsertFile
 @FileName NVARCHAR(200),
 @FileBody VARBINARY(MAX),
 @FileType NVARCHAR(50),
 @UploadUser NVARCHAR(50)
AS
BEGIN
 INSERT INTO Group_Files (FileName, FileBody, FileType, UploadUser)
 VALUES (@FileName, @FileBody, @FileType, @UploadUser);
 SELECT SCOPE_IDENTITY(); -- 回傳新 ID
END

上傳頁面：儲存檔案至資料庫

<asp:FileUpload ID="fuFile" runat="server" />
<asp:Button ID="btnUpload" Text="上傳" OnClick="btnUpload_Click" runat="server" />

protected void btnUpload_Click(object sender, EventArgs e)
{
 if (!fuFile.HasFile) return;

 string fileName = Path.GetFileName(fuFile.FileName);
 string fileType = fuFile.PostedFile.ContentType;
 int fileLen = fuFile.PostedFile.ContentLength;
 byte[] fileData = new byte[fileLen];
 fuFile.PostedFile.InputStream.Read(fileData, 0, fileLen);

 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 using (SqlConnection conn = new SqlConnection(connStr))
 using (SqlCommand cmd = new SqlCommand("sp_InsertFile", conn))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("@FileName", fileName);
 cmd.Parameters.AddWithValue("@FileBody", fileData);
 cmd.Parameters.AddWithValue("@FileType", fileType);
 cmd.Parameters.AddWithValue("@UploadUser", User.Identity.Name);
 conn.Open();
 int newId = Convert.ToInt32(cmd.ExecuteScalar());
 lblMsg.Text = $"上傳成功，檔案 ID: {newId}";
 }
}

下載頁面：從資料庫讀取並輸出

// DownloadFile.aspx?id=123
protected void Page_Load(object sender, EventArgs e)
{
 string idStr = Request.QueryString["id"];
 if (string.IsNullOrEmpty(idStr)) return;

 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 using (SqlConnection conn = new SqlConnection(connStr))
 using (SqlCommand cmd = new SqlCommand("SELECT FileName, FileBody, FileType FROM Group_Files WHERE ID = @ID", conn))
 {
 cmd.Parameters.AddWithValue("@ID", idStr);
 conn.Open();
 using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) // 關鍵：串流讀取大二進位
 {
 if (dr.Read())
 {
 string fileName = dr.GetString(0);
 string fileType = dr.GetString(2);
 long fileSize = dr.GetBytes(1, 0, null, 0, 0); // 取得長度

 Response.Clear();
 Response.ContentType = fileType;
 Response.AddHeader("Content-Disposition", $"attachment; filename=\"{HttpUtility.UrlEncode(fileName, Encoding.UTF8)}\"");
 Response.AddHeader("Content-Length", fileSize.ToString());
 Response.Buffer = false;

 const int bufferSize = 1024 * 64;
 byte[] buffer = new byte[bufferSize];
 long offset = 0;
 while (offset < fileSize && Response.IsClientConnected)
 {
 long read = dr.GetBytes(1, offset, buffer, 0, bufferSize); // 逐塊讀取
 if (read == 0) break;
 Response.OutputStream.Write(buffer, 0, (int)read);
 Response.Flush();
 offset += read;
 }
 Response.End();
 }
 }
 }
}

關鍵技術點

技術	說明
CommandBehavior.SequentialAccess	DataReader 串流模式，配合 `GetBytes` 逐塊讀取，避免大檔案一次載入記憶體
GetBytes(offset, buffer, ...)	指定位移讀取，支援斷點續傳邏輯基礎
VARBINARY(MAX)	取代舊 `image` 型別，支援 > 2GB，效能更好
參數化查詢	防 SQL Injection，處理二進位參數自動編碼