將圖片插入資料庫並使用asp.net讀取出來的正確方

書寫本文是因為今天見到CSDN的首頁上一篇存在明顯失誤的名為“在Asp.Net中從sqlserver檢索(retrieve)圖片”的文章。不說其錯誤是因為用其方法確實能從資料庫中讀取出圖片並顯示在瀏覽器，說其失誤是因為代碼的意圖不能被完全的實現，作者也似乎對http協議以及瀏覽器在處理http資料的流程一知半解。

1、如何出錯

以下是這片文章提到的方法：

Public Sub Page_Load(sender As Object, e As EventArgs)
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("Select * from Person", myConnection)
Try
myConnection.Open()
Dim myDataReader as SqlDataReader
myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Do While (myDataReader.Read())
Response.ContentType = myDataReader.Item("PersonImageType")
Response.BinaryWrite(myDataReader.Item("PersonImage"))
Loop

myConnection.Close()
Response.Write("Person info successfully retrieved!")
Catch SQLexc As SqlException
Response.Write("Read Failed : " & SQLexc.ToString())
End Try
End Sub

顯然，編程者是想將Person表中所有的記錄中的PersonImage欄位所儲存的圖片一次性地輸出到瀏覽器中，並且在輸出成功地情況下在已輸出的圖片的下方列印出“Person info successfully retrieved!”資訊。然而事實上上述代碼僅僅能正確地輸出第一條記錄中的圖片。對於瀏覽器來說，一個http請求擷取一個檔案（html或者圖片），所以以上代碼的輸出將被作為一個檔案（類型依據Response.ContentType = myDataReader.Item("PersonImageType")定）被瀏覽器處理。如果http相應的類型是image/jpeg之類的圖片，則瀏覽器使用相應的圖片解析功能對這一個圖片檔案進行解析。因此，上述代碼的顯示結果只能是第一條記錄PersonImage欄位的圖片。後面的記錄輸出的圖片資料將成為第一張圖片的多餘資料（此點具有普遍性，但並非絕對，依圖片的格式而定），從而後面的“Person info successfully retrieved！”的資訊也自然無法本顯示出來，因為這些資訊已經是圖片檔案裡面的編碼了。

2、正確的做法

A、將圖片輸入到資料庫中，以下是一個將圖片輸入到資料庫的代碼片斷：（完整的DEMO程式見附錄一）

FileStream fs=File.OpenRead(filePath.Text);

byte[] content=new byte[fs.Length];

fs.Read(content, 0,content.Length);

fs.Close();

SqlConnection conn=new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseImage;Data Source=(local)");

conn.Open();

SqlCommand comm=conn.CreateCommand();

comm.CommandText="insert into Images(Image, contentType) values(@image, @contentType)";

comm.CommandType=CommandType.Text;

comm.Parameters.Add("@image", SqlDbType.Image).Value=content;

comm.Parameters.Add("@contentType", SqlDbType.NVarChar).Value=

GetContentType(new FileInfo(filePath.Text).Extension.Remove(0,1));

if(comm.ExecuteNonQuery()==1)

{

MessageBox.Show("Successfully insert image into database!");

}

else

{

MessageBox.Show("Failed to insert image into database");

}

conn.Close();

B、將資料庫中的圖片讀出來的代碼片斷：（完整DEMO程式見附錄二）

try{

SqlConnection conn=new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseImage;Data Source=(local)");

conn.Open();

SqlCommand comm=conn.CreateCommand();

comm.CommandText="select * from Images where id=@id";

comm.CommandType=CommandType.Text;

comm.Parameters.Add("@id", SqlDbType.BigInt).Value=int.Parse(Request["id"]);

SqlDataReader reader=comm.ExecuteReader();

while(reader.Read())

{

Response.ContentType=reader["contentType"].ToString();

Response.BinaryWrite((byte[])reader["Image"]);

}

Response.End();

conn.Close();

}

catch

{

Response.End();

}

這段代碼可置於Page_Load事件中，資料圖片要注意的兩點是：

一、 設定正確的ContentType（http中的content-type），圖片的content-type格式一般為image/*，如jpeg為image/jpeg，bmp為image/bmp等等。

二、 僅僅輸出一張圖片二進位流，asp.net 中Page_Load事件先於頁面輸出被觸發，因此圖片的輸出可以在此事件中進行，直接操作Reponse對象，避免輸出與圖片無關的而外資訊（額外的第二張圖片或者文字）。圖片的二進位流輸出後及時使用Response.End()方法結束http響應，避免頁面中的額外資訊被asp.net的引擎預設輸出到用戶端。

希望此文能夠起到拋磚引玉的作用！^_^

附錄一：

MainForm.cs

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Windows.Forms;

namespace InsertImageToDatabase

{

public class MainForm : System.Windows.Forms.Form

{

private System.Windows.Forms.OpenFileDialog openFileDlg;

private System.Windows.Forms.TextBox filePath;

private System.Windows.Forms.Button browseButton;

private System.Windows.Forms.Button insertButton;

/// <summary>

/// Required designer variable.

/// </summary>

private System.ComponentModel.Container components = null;

public MainForm()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

/// <summary>

/// Clean up any resources being used.

/// </summary>

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

/// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

private void InitializeComponent()

{

this.openFileDlg = new System.Windows.Forms.OpenFileDialog();

this.filePath = new System.Windows.Forms.TextBox();

this.browseButton = new System.Windows.Forms.Button();

this.insertButton = new System.Windows.Forms.Button();

this.SuspendLayout();

//

// openFileDlg

//

this.openFileDlg.DefaultExt = "*.jpg;*.gif;*.bmp;*.png";

this.openFileDlg.Filter = "Image Files|*.jpg;*.gif;*.bmp;*.png|All Files|*.*";

//

// filePath

//

this.filePath.Location = new System.Drawing.Point(16, 16);

this.filePath.Name = "filePath";

this.filePath.ReadOnly = true;

this.filePath.Size = new System.Drawing.Size(168, 20);

this.filePath.TabIndex = 0;

this.filePath.Text = "";

//

// browseButton

//

this.browseButton.Location = new System.Drawing.Point(200, 16);

this.browseButton.Name = "browseButton";

this.browseButton.TabIndex = 1;

this.browseButton.Text = "&Browse";

this.browseButton.Click += new System.EventHandler(this.browseButton_Click);

//

// insertButton

//

this.insertButton.Enabled = false;

this.insertButton.Location = new System.Drawing.Point(200, 56);

this.insertButton.Name = "insertButton";

this.insertButton.TabIndex = 2;

this.insertButton.Text = "&Insert";

this.insertButton.Click += new System.EventHandler(this.insertButton_Click);

//

// MainForm

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(292, 273);

this.Controls.Add(this.insertButton);

this.Controls.Add(this.browseButton);

this.Controls.Add(this.filePath);

this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;

this.MaximizeBox = false;

this.Name = "MainForm";

this.Text = "Insert Image to Database";

this.ResumeLayout(false);

}

#endregion

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main()

{

Application.Run(new MainForm());

}

private void browseButton_Click(object sender, System.EventArgs e)

{

if(openFileDlg.ShowDialog()==DialogResult.OK)

{

filePath.Text=openFileDlg.FileName;

insertButton.Enabled=true;

}

}

private void insertButton_Click(object sender, System.EventArgs e)

{

FileStream fs=File.OpenRead(filePath.Text);

byte[] content=new byte[fs.Length];

fs.Read(content, 0,content.Length);

fs.Close();

SqlConnection conn=new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseImage;Data Source=(local)");

conn.Open();

SqlCommand comm=conn.CreateCommand();

comm.CommandText="insert into Images(Image, contentType) values(@image, @contentType)";

comm.CommandType=CommandType.Text;

SqlParameter param=comm.Parameters.Add("@image", SqlDbType.Image);

param.Value=content;

comm.Parameters.Add("@contentType", SqlDbType.NVarChar).Value=

GetContentType(new FileInfo(filePath.Text).Extension.Remove(0,1));

if(comm.ExecuteNonQuery()==1)

{

MessageBox.Show("Successfully insert image into database!");

}

else

{

MessageBox.Show("Failed to insert image into database");

}

conn.Close();

}

private string GetContentType(string extension)

{

string type="jpeg";

if(extension=="jpg")

{

type="jpeg";

}else

{

type=extension;

}

return "image/"+type;

}

}

}

附錄二：

ReadImage.aspx

<%@ Page language="c#" Codebehind="ReadImage.aspx.cs" AutoEventWireup="false" Inherits="ReadImage.ReadImage"%>

ReadImage.aspx.cs

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace ReadImage

{

/// <summary>

/// Summary description for ReadImage.

/// </summary>

public class ReadImage : System.Web.UI.Page

{

private void Page_Load(object sender, System.EventArgs e)

{

try{

SqlConnection conn=new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseImage;Data Source=(local)");

conn.Open();

SqlCommand comm=conn.CreateCommand();

comm.CommandText="select * from Images where id>@id";

comm.CommandType=CommandType.Text;

comm.Parameters.Add("@id", SqlDbType.BigInt).Value=int.Parse(Request["id"]);

SqlDataReader reader=comm.ExecuteReader();

while(reader.Read())

{

Response.ContentType=reader["contentType"].ToString();

Response.BinaryWrite((byte[])reader["Image"]);

}

Response.Write("aaaaaa");

Response.End();

conn.Close();

}

catch

{

Response.End();

}

}

#region Web Form Designer generated code

override protected void OnInit(EventArgs e)

{

//

// CODEGEN: This call is required by the ASP.NET Web Form Designer.

//

InitializeComponent();

base.OnInit(e);

}

/// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

private void InitializeComponent()

{

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

}

}