ASP.NET 儲存與讀取 SQL Server 圖片：VARBINARY(MAX) 與 HttpHandler 完整實作

資料表設計

CREATE TABLE ImageStore (
 ID INT IDENTITY(1,1) PRIMARY KEY,
 ImgTitle NVARCHAR(100),
 ImgType NVARCHAR(50), -- Content-Type: image/jpeg, image/png
 ImgData VARBINARY(MAX) NOT NULL, -- 取代舊 image 型別
 UploadTime DATETIME DEFAULT GETDATE()
)

上傳頁面：檔案 → 資料庫

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

 string title = txtTitle.Text.Trim();
 string type = fuImage.PostedFile.ContentType;
 int len = fuImage.PostedFile.ContentLength;
 byte[] data = new byte[len];
 fuImage.PostedFile.InputStream.Read(data, 0, len);

 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 using (SqlConnection conn = new SqlConnection(connStr))
 using (SqlCommand cmd = new SqlCommand(
 "INSERT INTO ImageStore (ImgTitle, ImgType, ImgData) VALUES (@Title, @Type, @Data)", conn))
 {
 cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 100).Value = title;
 cmd.Parameters.Add("@Type", SqlDbType.NVarChar, 50).Value = type;
 cmd.Parameters.Add("@Data", SqlDbType.VarBinary, -1).Value = data; // -1 = MAX
 conn.Open();
 cmd.ExecuteNonQuery();
 }
 lblMsg.Text = "上傳成功";
}

HttpHandler 讀取圖片：ImageHandler.ashx

<%@ WebHandler Language="C#" Class="ImageHandler" %>
using System;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

public class ImageHandler : IHttpHandler
{
 public void ProcessRequest(HttpContext context)
 {
 string idStr = context.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 ImgData, ImgType FROM ImageStore WHERE ID = @ID", conn))
 {
 cmd.Parameters.AddWithValue("@ID", idStr);
 conn.Open();
 using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
 {
 if (dr.Read())
 {
 context.Response.ContentType = dr.GetString(1); // ImgType
 long len = dr.GetBytes(0, 0, null, 0, 0); // 取得長度
 context.Response.AddHeader("Content-Length", len.ToString());
 context.Response.Buffer = false;

 const int bufferSize = 1024 * 64;
 byte[] buffer = new byte[bufferSize];
 long offset = 0;
 while (offset < len && context.Response.IsClientConnected)
 {
 long read = dr.GetBytes(0, offset, buffer, 0, bufferSize);
 if (read == 0) break;
 context.Response.OutputStream.Write(buffer, 0, (int)read);
 context.Response.Flush();
 offset += read;
 }
 }
 }
 }
 }

 public bool IsReusable => false;
}

GridView 顯示圖片清單

 
 
 
 
 
 
 
 
 


protected void Page_Load(object sender, EventArgs e)
{
 if (!IsPostBack) BindGrid();
}

void BindGrid()
{
 string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 using (SqlConnection conn = new SqlConnection(connStr))
 using (SqlCommand cmd = new SqlCommand("SELECT ID, ImgTitle, ImgType FROM ImageStore", conn))
 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 {
 DataTable dt = new DataTable();
 da.Fill(dt);
 gvImages.DataSource = dt;
 gvImages.DataBind();
 }
}

關鍵最佳實踐

架構建議：大檔案外移

超過 1MB 圖片、高併發、雲端部署建議：

• 儲存至 Azure Blob / AWS S3 / MinIO / 檔案伺服器。
• 資料庫僅存 `BlobUrl`、`ThumbnailUrl`、中繼資料。
• 上傳時同步產生縮圖 (多種尺寸)，預覽走縮圖、原圖按需載入。
• CDN 加速靜態資源分發。