ASP.NET 建立圖片資料庫管理介面：DataGrid 顯示縮圖、編輯、刪除與關鍵字搜尋

功能需求

管理 ImageData 資料表：欄位 ID、Name、ContentType、FileSize、Picture (image)、Description。介面需支援：縮圖顯示、就地編輯名稱與描述、刪除、新增導向上傳頁、關鍵字搜尋 (Name/Description)。

VB.NET 完整程式碼 (List A)

Const connString As String = "Data Source=localhost;Initial Catalog=Northwind;User Id=username;Password=password;"

Sub Page_Load(sender As Object, e As EventArgs)
    If Not Page.IsPostBack Then BindDataSet(String.Empty)
End Sub

Sub BindDataSet(searchString As String)
    Dim conn As SqlConnection, comm As SqlCommand, sql As String
    If searchString = String.Empty Then
        sql = "SELECT ID, Name, ContentType, FileSize, Picture, Description FROM ImageData ORDER BY Name"
    Else
        sql = "SELECT ID, Name, ContentType, FileSize, Picture, Description FROM ImageData WHERE Name LIKE '%" & searchString & "%' OR Description LIKE '%" & searchString & "%' ORDER BY Name"
    End If
    Try
        conn = New SqlConnection(connString)
        comm = New SqlCommand(sql, conn)
        conn.Open()
        dgImages.DataSource = comm.ExecuteReader()
        dgImages.DataBind()
    Catch ex As SqlException
        Response.Write("Data error occurred: " & ex.ToString())
    End Try
End Sub

Function ImageLink(strArgument) As String
    Return "displayImageVB.aspx?id=" & strArgument
End Function

Sub dgImages_Edit(sender As Object, e As DataGridCommandEventArgs)
    dgImages.EditItemIndex = e.Item.ItemIndex
    BindDataSet(String.Empty)
End Sub

Sub dgImages_Cancel(sender As Object, e As DataGridCommandEventArgs)
    dgImages.EditItemIndex = -1
    BindDataSet(String.Empty)
End Sub

Sub dgImages_Update(sender As Object, e As DataGridCommandEventArgs)
    Dim strName As String = CType(e.Item.Cells(1).Controls(0), TextBox).Text
    Dim strDesc As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
    Dim strSQL As String = "UPDATE ImageData SET Name='" & strName & "', Description='" & strDesc & "' WHERE ID=" & e.Item.Cells(0).Text
    Dim conn As New SqlConnection(connString)
    conn.Open()
    Dim comm As New SqlCommand(strSQL, conn) With {.CommandType = CommandType.Text}
    comm.ExecuteNonQuery()
    conn.Close()
    dgImages.EditItemIndex = -1
    BindDataSet(String.Empty)
End Sub

Sub dgImages_Delete(sender As Object, e As DataGridCommandEventArgs)
    Dim sql As String = "DELETE FROM ImageData WHERE ID=" & e.Item.Cells(0).Text
    Dim conn As New SqlConnection(connString)
    conn.Open()
    Dim comm As New SqlCommand(sql, conn) With {.CommandType = CommandType.Text}
    comm.ExecuteNonQuery()
    conn.Close()
    dgImages.EditItemIndex = -1
    BindDataSet(String.Empty)
End Sub

Sub AddImage(sender As Object, e As EventArgs)
    Response.Redirect("addImage.aspx")
End Sub

Sub DoSearch(sender As Object, e As EventArgs)
    BindDataSet(txtSearch.Text)
End Sub

Sub ClearSearch(sender As Object, e As EventArgs)
    If txtSearch.Text <> "" Then
        BindDataSet(String.Empty)
        txtSearch.Text = ""
    End If
End Sub

C# 等效程式碼 (List B)

const string connString = "Data Source=Pentium4;Initial Catalog=Northwind;User Id=sa;Password=;";

private void Page_Load(object sender, EventArgs e) {
    if (!Page.IsPostBack) BindDataSet(String.Empty);
}

public void BindDataSet(string searchString) {
    SqlConnection conn; SqlCommand comm; string sql;
    if (searchString == String.Empty)
        sql = "SELECT ID, Name, ContentType, FileSize, Picture, Description FROM ImageData ORDER BY Name";
    else
        sql = "SELECT ID, Name, ContentType, FileSize, Picture, Description FROM ImageData WHERE Name LIKE '%" + searchString + "%' OR Description LIKE '%" + searchString + "%' ORDER BY Name";
    try {
        conn = new SqlConnection(connString);
        comm = new SqlCommand(sql, conn);
        conn.Open();
        dgImages.DataSource = comm.ExecuteReader();
        dgImages.DataBind();
    } catch (SqlException ex) {
        Response.Write("Data Error: " + ex.ToString());
    }
}

public string ImageLink(object strArgument) {
    return "displayImageVB.aspx?id=" + strArgument.ToString();
}

public void dgImages_Edit(object sender, DataGridCommandEventArgs e) {
    dgImages.EditItemIndex = e.Item.ItemIndex; BindDataSet(String.Empty);
}
public void dgImages_Cancel(object sender, DataGridCommandEventArgs e) {
    dgImages.EditItemIndex = -1; BindDataSet(String.Empty);
}
public void dgImages_Update(object sender, DataGridCommandEventArgs e) {
    string strName = ((TextBox)(e.Item.Cells[1].Controls[0])).Text;
    string strDesc = ((TextBox)(e.Item.Cells[4].Controls[0])).Text;
    string strSQL = "UPDATE ImageData SET Name='" + strName + "', Description='" + strDesc + "' WHERE ID=" + e.Item.Cells[0].Text;
    SqlConnection conn = new SqlConnection(connString);
    conn.Open();
    SqlCommand comm = new SqlCommand(strSQL, conn) { CommandType = CommandType.Text };
    comm.ExecuteNonQuery();
    conn.Close();
    dgImages.EditItemIndex = -1; BindDataSet(String.Empty);
}
public void dgImages_Delete(object sender, DataGridCommandEventArgs e) {
    string sql = "DELETE FROM ImageData WHERE ID=" + e.Item.Cells[0].Text;
    SqlConnection conn = new SqlConnection(connString);
    conn.Open();
    SqlCommand comm = new SqlCommand(sql, conn) { CommandType = CommandType.Text };
    comm.ExecuteNonQuery();
    conn.Close();
    dgImages.EditItemIndex = -1; BindDataSet(String.Empty);
}
public void AddImage(object sender, EventArgs e) {
    Response.Redirect("sqlimagesexample1vb.aspx");
}
public void DoSearch(object sender, EventArgs e) { BindDataSet(txtSearch.Text); }
public void ClearSearch(object sender, EventArgs e) {
    if (txtSearch.Text != "") { BindDataSet(String.Empty); txtSearch.Text = ""; }
}

ASPX 頁面關鍵標記

• <asp:DataGrid id="dgImages" OnEditCommand="dgImages_Edit" OnCancelCommand="dgImages_Cancel" OnUpdateCommand="dgImages_Update" OnDeleteCommand="dgImages_Delete" ...>
• <asp:ButtonColumn ButtonType="LinkButton" CommandName="Edit" Text="編輯" />、Delete 同理。
• <asp:TemplateColumn> 含 ItemTemplate 顯示縮圖 (<asp:Image ImageUrl='<%# ImageLink(Container.DataItem("ID")) %>' Width="50" Height="40" />)。
• 搜尋區：<asp:TextBox id="txtSearch" />、<asp:Button Text="搜尋" OnClick="DoSearch" />、<asp:Button Text="清除" OnClick="ClearSearch" />、<asp:Button Text="新增圖片" OnClick="AddImage" />。

關鍵技術點

• DataGrid 事件模型：EditCommand/CancelCommand/UpdateCommand/DeleteCommand 對應子程序。
• BoundColumn 將資料欄位繫結至網格欄；TemplateColumn 自訂縮圖顯示。
• 搜尋使用 LIKE 配合萬用字元，支援 Name 與 Description 兩欄位。
• 實際圖片由獨立頁面 displayImageVB.aspx 以 Response.BinaryWrite 輸出二進位資料。