您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 儲存與輸出圖片完整實作：VARBINARY(MAX)/IMAGE 欄位、檔案上傳、二進位輸出

編輯：關於SqlServer

資料表設計


CREATE TABLE ImageStore (
    ID int IDENTITY PRIMARY KEY,
    ImgTitle varchar(50) NOT NULL,           -- 使用者友善標題
    ImgType varchar(50) NOT NULL,            -- MIME 類型: image/jpeg, image/png...
    ImgData varbinary(max) NOT NULL          -- SQL 2005+ 用 varbinary(max); 2000 用 image
);

前端上傳 (ASP.NET WebForms 範例)



      
      
    


protected void BtnUpload_Click(object sender, EventArgs e)
{
    Stream fs = File1.PostedFile.InputStream;
    int len = File1.PostedFile.ContentLength;
    string type = File1.PostedFile.ContentType;
    string title = TextBox1.Text;

    byte[] img = new byte[len];
    fs.Read(img, 0, len);

    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand(
        "INSERT INTO ImageStore (ImgTitle, ImgType, ImgData) VALUES (@t, @tp, @d)", conn))
    {
        cmd.Parameters.Add("@t", SqlDbType.VarChar, 50).Value = title;
        cmd.Parameters.Add("@tp", SqlDbType.VarChar, 50).Value = type;
        cmd.Parameters.Add("@d", SqlDbType.VarBinary, -1).Value = img;  // -1 = max
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

輸出圖片至瀏覽器 (HTTP Handler / 專用頁面)


private void Page_Load(object sender, EventArgs e)
{
    string id = Request.QueryString["imgid"];
    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand(
        "SELECT ImgData, ImgType FROM ImageStore WHERE ID = @id", conn))
    {
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(id);
        conn.Open();
        using (var dr = cmd.ExecuteReader())
        {
            if (dr.Read())
            {
                Response.ContentType = dr["ImgType"].ToString();
                Response.BinaryWrite((byte[])dr["ImgData"]);  // 關鍵：BinaryWrite 而非 Write
                Response.End();
            }
        }
    }
}

前端引用：<img src="ShowImage.aspx?imgid=123" />