ASP.NET 將圖片上傳至 SQL Server Image 欄位完整範例

資料庫物件

CREATE TABLE Person (
    PersonID INT IDENTITY,
    PersonEmail VARCHAR(255),
    PersonName VARCHAR(255),
    PersonSex CHAR(1),
    PersonDOB DATETIME,
    PersonImage IMAGE,
    PersonImageType VARCHAR(255)
);

CREATE PROCEDURE sp_person_isp
    @PersonEmail VARCHAR(255), @PersonName VARCHAR(255), @PersonSex CHAR(1),
    @PersonDOB DATETIME, @PersonImage IMAGE, @PersonImageType VARCHAR(255)
AS
INSERT INTO Person (PersonEmail, PersonName, PersonSex, PersonDOB, PersonImage, PersonImageType)
VALUES (@PersonEmail, @PersonName, @PersonSex, @PersonDOB, @PersonImage, @PersonImageType);

ASPX 頁面 (VB.NET)

<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Page Language="vb" %>
<script runat="server">
Public Sub AddPerson(sender As Object, e As EventArgs)
    Dim imgSize As Int64 = PersonImage.PostedFile.ContentLength
    Dim imgType As String = PersonImage.PostedFile.ContentType
    Dim imgStream As Stream = PersonImage.PostedFile.InputStream
    Dim imgBytes(imgSize) As Byte
    imgStream.Read(imgBytes, 0, imgSize)

    Dim connStr As String = "Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;"
    Using conn As New SqlConnection(connStr)
        Using cmd As New SqlCommand("sp_person_isp", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@PersonEmail", txtPersonEmail.Text)
            cmd.Parameters.AddWithValue("@PersonName", txtPersonName.Text)
            cmd.Parameters.AddWithValue("@PersonSex", If(sexMale.Checked, "M", "F"))
            cmd.Parameters.AddWithValue("@PersonDOB", txtPersonDob.Text)
            cmd.Parameters.Add("@PersonImage", SqlDbType.Image).Value = imgBytes
            cmd.Parameters.AddWithValue("@PersonImageType", imgType)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
    Response.Write("新增成功！")
End Sub
</script>
<form enctype="multipart/form-data" runat="server">
    Email: <asp:TextBox ID="txtPersonEmail" Runat="server" /><br/>
    Name: <asp:TextBox ID="txtPersonName" Runat="server" /><br/>
    Sex: <asp:RadioButton ID="sexMale" Text="Male" GroupName="sex" Runat="server" />
         <asp:RadioButton ID="sexFemale" Text="Female" GroupName="sex" Runat="server" /><br/>
    DOB: <asp:TextBox ID="txtPersonDob" Runat="server" /><br/>
    Image: <input type="file" id="PersonImage" runat="server" /><br/>
    <asp:Button Text="新增" OnClick="AddPerson" Runat="server" />
</form>

關鍵點

• enctype="multipart/form-data" 讓瀏覽器以二進位上傳。
• 參數化命令防止 SQL Injection，SqlDbType.Image 對應二進位陣列。
• PostedFile.InputStream.Read 一次讀取完整檔案內容。